Reza Alirezaei’s Blog

As far as I know, there is no way to perform a SELECT TOP 10 query on a disconnected Recordset in ADO.NET. However, you can create a DataView over the target table and use the technique described below to extract Top 10 records.  

As you can see in the below excerpt, I am trying to return Top 10 records of my ds dataset using a method called “GetTopTen”. First of all, I have assigned the defaultView of the only DataTable in input dataset named “SITE_DT” to a DataView object and then have sorted the result based on “Total_Size” field. This guarantees that our result (no matter how many records is returned) are always sorted in a descending manner.

Next, I have cloned the schema of first DateTable to another DataTable and added the new DataTable to a completely new DataSet. ADO.NET normally does not let you add a DataTable (neither a DataRow) directly from one dataset to another, so you have to clone it in a way. Finally I’ve just grunt it out the old-fashioned way by looping through each row of the DataView object and adding them to a new DataTable. My method ends by returning the new dataset which contains the Top 10 records based on “Total_Size”. This might not be the best option for you, but perfectly works in my case.

public DataSet GetTopTen(DataSet ds)

{
    Try
   {
    DataView dwDs= ds.Tables[“SITE_DT”].DefaultView;
    dwDs.Sort=”Total_Size DESC”;
 
    DataSet dss  = new DataSet();
    DataTable dt = dwDs.Table.Clone();
    dss.Tables.Add(dt);

    int counter = 0;

    if(dwDs.Count > 10)    
     
     counter = 10;     
    else
     counter = dwDs.Count;

    
    for (int i=0; i < counter; i++)
      {
         DataRow dv = dwDs[i].Row;
         dss.Tables[0].ImportRow(dv);
      }

    dss.AcceptChanges();
    return(dss);

   }
           
   catch(Exception e)
   {
    throw e;
   }

}