SQL Server RowNum and DataView

In SQL Server we can not use rownum in the WHERE clause, but in DataView we can!! Really!!!
I mean, get the entire resultset from SQL Server in a DataView.
Set the RowFilter something similar to this
dv.RowFilter = “RowNumber < 6”;
Another bonus out of this is — since TSql’s Top  clause does not work with DataView
the rownum column can easily will let us run a top query on the DataView.

An example highlighting the concept

private DataTable GetData()
    SqlDataReader reader = null;
    DataTable dt = new DataTable();
    using (SqlConnection conn = new SqlConnection())
       conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnStr2"].ConnectionString;
       using (SqlCommand cmd = new SqlCommand())
          cmd.CommandText = "SELECT ROW_NUMBER () OVER (ORDER BY AddressID) AS RowNumber, City, PostalCode FROM [AdventureWorks].[Person].[Address] where AddressLine1 like '105%' ORDER BY AddressID";
          cmd.Connection = conn;
          StringBuilder sb = new StringBuilder();
          reader = cmd.ExecuteReader();
        return dt;
 private DataView GetSelectedRecords(DataTable dt, int nStartIndex)
     DataView dv = new DataView(dt);
     //gets Top 5 records
     dv.RowFilter = "RowNumber < 6";
     return dv;

This entry was posted in General ASP.Net C#. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s