How to limit the number of rows in dataset

Question

Code to refer

 [WebMethod]

 public static string GetData()
 {
      string query = "SELECT * FROM tblCountry";
      SqlCommand cmd = new SqlCommand(query);
      return GetData(cmd).GetXml();
 }
 private static DataSet GetData(SqlCommand cmd)
 {
      string strConnString = @"Data Source=.\sqlExpress;Initial Catalog=dbTest;Integrated  Security=SSPI; pooling=false";
      using (SqlConnection con = new SqlConnection(strConnString))
      {
          using (SqlDataAdapter sda = new SqlDataAdapter())
          {
             cmd.Connection = con;
             sda.SelectCommand = cmd;
             using (DataSet ds = new DataSet())
             {
                sda.Fill(ds);
                return ds;
             }
          }
      }
 }

See more at: Link

The query returns 20 rows and i need to show ten rows alone. Without any alteration in query is there any possibility to limit the data in dataset.Suggest some ideas

Rather than using `sda.Fill...`, maybe use a DataReader in a (limited) loop — Chris Hammond, Jan 31 '14 at 09:58
Sometimes you may want to limit records returned without altering the query because you are not in control of the query. You may want to let your users run a query but you dont want to bring back gigs of data. This is the exact reason I want to do the same. — Dave Thompson, Oct 27 '14 at 11:02

Sachin · Answer 1 · 2014-01-31T10:09:05.800

5

You can try this

var rows = ds.Tables[0].AsEnumerable().Take(10);

Now you can also convert these rows into a datatable like this

DataTable limitedTable = rows.CopyToDataTable<DataRow>();

edited Jan 31 '14 at 10:09

answered Jan 31 '14 at 10:00

Sachin

40,216
7
90
102

Was about to post this answer. Remember to add reference to your project for System.Data.DataSetExtensions or this won't work. – Esko Jan 31 '14 at 10:02

score 1 · Accepted Answer · answered Jan 31 '14 at 10:20

You can use an overload of Fill that takes the start and end record index.

var ds = new DataSet; //using means the DataSet will go out of scope so you can't return it!
sda.Fill(1, 10, ds.Tables.Add("MyTable"));
return ds; //it now contains a table called "MyTable".

You can find more details here

But like most commenters here, I'd be much more in favour of modifying the query if at all possible

score 0 · Answer 3 · answered Jan 31 '14 at 10:18

0

How strict is your requirement to not alter the query? Can you prepend some text to the query?

If so, you can use SET ROWCOUNT.

Example:

string query = "SET ROWCOUNT 10;"
query += "SELECT * FROM tblCountry";

answered Jan 31 '14 at 10:18

mohlman3

311
3
7

score -2 · Answer 4 · answered Jan 31 '14 at 10:24

-2

string query = "SELECT TOP 10 FROM tblCountry";

You can make a query like this, to return only 10 rows.

answered Jan 31 '14 at 10:24

Muneeb Zulfiqar

1,003
2
13
31

How to limit the number of rows in dataset

4 Answers4