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();
          conn.Open();
          reader = cmd.ExecuteReader();
          dt.Load(reader);
          conn.Close();
        }
      }
        return dt;
    }
 private DataView GetSelectedRecords(DataTable dt, int nStartIndex)
  {
     DataView dv = new DataView(dt);
     //gets Top 5 records
     dv.RowFilter = "RowNumber < 6";
     return dv;
 }

Advertisements
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