Loading certain number of rows from DataReader into datatable

Question

I have a process which loads a DataGridView from a TSql view. There has been a debate amongst my users on whether to return all the rows or a certain number (im in the latter camp: TOP 100). I decided that the best approach would be to return the number of rows dynamically based on a field set by the user. I was expecting this to be a fairly trivial task. I was expecting to load a datatable with a while loop and a read function and stop at a certain number of rows. Is this possible? Is there a huge benefit in keeping the code as it is below, but cutting the datatable (dt) down to the desired number of rows and then loading the datagridview from there?

         public void Load_DGV(DataGridView dgv, string sqlx)
    {
        DateTime d1 = DateTime.Now;
        SqlCommand command = new SqlCommand(sqlx.ToString());

        SqlConnection connection = new SqlConnection(Properties.Settings.Default._CONNSTRING);
        command.Connection = connection;

        bool Success = true;
        DataTable dt = new DataTable();
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
       dt.Load(command.ExecuteReader());

        connection.Close();

        dgv.DataSource = null;
        dgv.DataSource = dt;
        Color_DGV(dgvProposal);

        dgvProposal.Sort(dgvProposal.Columns[m_Current_Menu_Item.OrderByCol], ListSortDirection.Descending);
        DateTime d2 = DateTime.Now;
        Console.WriteLine("Execution of " + sqlx + " took " + (d2 - d1).Seconds.ToString() + "s. ");
    }

Thats probably the best way around this situation. Unfortunately my Query is absolutely massive and needs to be updated frequently, so i placed it in a view which cannot have parameters. The other alternative is a stored procedure, which is my fall back worst case scenario. Thanks for the idea! — brhardwick
– brhardwick, Commented Jan 19, 2015 at 17:18
yes. but it doesnt support parameters. The rows i return must be dynamic at runtime — brhardwick
– brhardwick, Commented Jan 19, 2015 at 17:37
A view doesn't support parameters? How about a Where clause? ADO.NET supports parameterized SQL Strings natively without having to create a sproc. stackoverflow.com/questions/542510/… — Jeremy
– Jeremy, Commented Jan 19, 2015 at 18:04
yes, but i dont want to hardcode an 82 line sql query in my code as i need to change it. Everything ive read so far is that views dont easily and natively support parameters. What you are suggesting is what i think i will do for a stored procedure. stackoverflow.com/questions/1687279/… — brhardwick
– brhardwick, Commented Jan 19, 2015 at 18:11

Grant Winney · Accepted Answer · 2015-01-19 17:53:31Z

1

You'll get way better performance if you change things up so you can pass a parameter indicating the number of TOP rows to return.

Otherwise, once you load all the records into a new DataTable, you can use LINQ to TAKE the first xx rows, and assign that to the DataSource.

dataGridView1.DataSource = dt.Rows.Cast<DataRow>().Take(100).CopyToDataTable();

You might save some processing time if you used reader.Read() to iterate through the first 100 records, and manually filled a DataTable. At least then you're not filling a DataTable with thousands of records just to discard it and create another, smaller DataTable.

answered Jan 19, 2015 at 17:53

Grant Winney

66.6k13 gold badges120 silver badges167 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

brhardwick Over a year ago

Thats what i was thinking, but then you have overhead of converting the read() record to a datarow. I think your TOP suggestion is the best bet. Ill have to refactor some code.

Collectives™ on Stack Overflow

Loading certain number of rows from DataReader into datatable

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related