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, Jan 19 '15 at 17:18
yes. but it doesnt support parameters. The rows i return must be dynamic at runtime — brhardwick, Jan 19 '15 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. http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i — Jeremy, Jan 19 '15 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. http://stackoverflow.com/questions/1687279/can-we-pass-parameter-to-a-view-in-sql — brhardwick, Jan 19 '15 at 18:11

score 1 · Answer 1 · answered Jan 19 '15 at 17:53

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.

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. — brhardwick, Jan 19 '15 at 17:57

Loading certain number of rows from DataReader into datatable

1 Answers1