Home > Uncategorized > Select Top 10 records from a DataTable in ADO.NET

Select Top 10 records from a DataTable in ADO.NET

October 30th, 2006 Leave a comment Go to comments

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;
   }


}

Categories: Uncategorized Tags:
  1. No comments yet.