Select Top 10 records from a DataTable in ADO.NET
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;
}
}