129

I'm doing a basic thing in C# (MS VS2008) and have a question more about proper design than specific code.

I am creating a datatable and then trying to load the datatable from a datareader (which is based on an SQL stored procedure). What I'm wondering is whether the most efficient way to load the datatable is to do a while statement, or if there is a better way.

To me the only drawback is I have to manually type in the fields I want to add in my while statement, but I also don't know of way to automate that anyways since I don't want all fields from the SP just select ones, but that's not a huge deal in my eyes.

I've included code snippets below the totality of what I do, though to me the code itself isn't remarkable or even what I'm asking about. Moreso wondering about my methodology, I'll pester for code help later if my strategy is wrong/inefficient.

var dtWriteoffUpload = new DataTable();
dtWriteoffUpload.Columns.Add("Unit");
dtWriteoffUpload.Columns.Add("Year");
dtWriteoffUpload.Columns.Add("Period");
dtWriteoffUpload.Columns.Add("Acct");
dtWriteoffUpload.Columns.Add("Descr");
dtWriteoffUpload.Columns.Add("DEFERRAL_TYPE");
dtWriteoffUpload.Columns.Add("NDC_Indicator");
dtWriteoffUpload.Columns.Add("Mgmt Cd");
dtWriteoffUpload.Columns.Add("Prod");
dtWriteoffUpload.Columns.Add("Node");
dtWriteoffUpload.Columns.Add("Curve_Family");
dtWriteoffUpload.Columns.Add("Sum Amount");
dtWriteoffUpload.Columns.Add("Base Curr");
dtWriteoffUpload.Columns.Add("Ledger");  

cmd = util.SqlConn.CreateCommand();
cmd.CommandTimeout = 1000;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "proc_writeoff_data_details";
cmd.Parameters.Add("@whoAmI", SqlDbType.VarChar).Value = 

WindowsIdentity.GetCurrent().Name;

cmd.Parameters.Add("@parmEndDateKey", SqlDbType.VarChar).Value = myMostRecentActualDate;
cmd.Parameters.Add("@countrykeys", SqlDbType.VarChar).Value = myCountryKey;
cmd.Parameters.Add("@nodekeys", SqlDbType.VarChar).Value = "1,2";
break;


dr = cmd.ExecuteReader();
while (dr.Read())                    
{
    dtWriteoffUpload.Rows.Add(dr["country name"].ToString(), dr["country key"].ToString());
}
johnnyRose
  • 7,310
  • 17
  • 40
  • 61
Ryan Ward
  • 1,523
  • 4
  • 15
  • 23
  • 1
    Duplicate question: http://stackoverflow.com/questions/4089471/how-do-i-fill-a-datatable-using-datareader – vapcguy Nov 17 '16 at 19:31

5 Answers5

366

You can load a DataTable directly from a data reader using the Load() method that accepts an IDataReader.

var dataReader = cmd.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(dataReader);
Musakkhir Sayyed
  • 7,012
  • 13
  • 42
  • 65
Sagi
  • 8,972
  • 3
  • 33
  • 41
18

If you're trying to load a DataTable, then leverage the SqlDataAdapter instead:

DataTable dt = new DataTable();

using (SqlConnection c = new SqlConnection(cString))
using (SqlDataAdapter sda = new SqlDataAdapter(sql, c))
{
    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
    sda.SelectCommand.Parameters.AddWithValue("@parm1", val1);
    ...

    sda.Fill(dt);
}

You don't even need to define the columns. Just create the DataTable and Fill it.

Here, cString is your connection string and sql is the stored procedure command.

Mike Perrenoud
  • 66,820
  • 29
  • 157
  • 232
  • 1
    Only problem here is that if you find a column/value causes an exception during the fill, it doesn't give you any detail, like you might be able to get using a `SqlDataReader` and reading them in using a loop through the fields. – vapcguy Nov 18 '16 at 01:17
13

As Sagi stated in their answer DataTable.Load is a good solution. If you are trying to load multiple tables from a single reader you do not need to call DataReader.NextResult. The DataTable.Load method also advances the reader to the next result set (if any).

// Read every result set in the data reader.
while (!reader.IsClosed)
{
    DataTable dt = new DataTable();
    // DataTable.Load automatically advances the reader to the next result set
    dt.Load(reader);
    items.Add(dt);
}
ghawkes
  • 553
  • 1
  • 5
  • 14
  • It works, albeit significantly slower than using a DataAdapter and the Fill method. – MC9000 Mar 03 '22 at 10:59
  • I had been calling NextResult on a two-table set and was wondering why the second table was empty. Thanks for setting me straight! – Eric Eggers Dec 05 '22 at 21:37
  • (damn 5-minute comment edit time limit...) I would, however, replace the Add statement with the following lines, since during my use of this, the reader never closed and it kept filling items up with empty tables: if (dt.Columns.Count == 0) { break; } else { items.Add(dt); } – Eric Eggers Dec 05 '22 at 22:02
9

I looked into this as well, and after comparing the SqlDataAdapter.Fill method with the SqlDataReader.Load funcitons, I've found that the SqlDataAdapter.Fill method is more than twice as fast with the result sets I've been using

Used code:

    [TestMethod]
    public void SQLCommandVsAddaptor()
    {
        long AdapterFillLargeTableTime, readerLoadLargeTableTime, AdapterFillMediumTableTime, readerLoadMediumTableTime, AdapterFillSmallTableTime, readerLoadSmallTableTime, AdapterFillTinyTableTime, readerLoadTinyTableTime;

        string LargeTableToFill = "select top 10000 * from FooBar";
        string MediumTableToFill = "select top 1000 * from FooBar";
        string SmallTableToFill = "select top 100 * from FooBar";
        string TinyTableToFill = "select top 10 * from FooBar";

        using (SqlConnection sconn = new SqlConnection("Data Source=.;initial catalog=Foo;persist security info=True; user id=bar;password=foobar;"))
        {
            // large data set measurements
            AdapterFillLargeTableTime = MeasureExecutionTimeMethod(sconn, LargeTableToFill, ExecuteDataAdapterFillStep);
            readerLoadLargeTableTime = MeasureExecutionTimeMethod(sconn, LargeTableToFill, ExecuteSqlReaderLoadStep);
            // medium data set measurements
            AdapterFillMediumTableTime = MeasureExecutionTimeMethod(sconn, MediumTableToFill, ExecuteDataAdapterFillStep);
            readerLoadMediumTableTime = MeasureExecutionTimeMethod(sconn, MediumTableToFill, ExecuteSqlReaderLoadStep);
            // small data set measurements
            AdapterFillSmallTableTime = MeasureExecutionTimeMethod(sconn, SmallTableToFill, ExecuteDataAdapterFillStep);
            readerLoadSmallTableTime = MeasureExecutionTimeMethod(sconn, SmallTableToFill, ExecuteSqlReaderLoadStep);
            // tiny data set measurements
            AdapterFillTinyTableTime = MeasureExecutionTimeMethod(sconn, TinyTableToFill, ExecuteDataAdapterFillStep);
            readerLoadTinyTableTime = MeasureExecutionTimeMethod(sconn, TinyTableToFill, ExecuteSqlReaderLoadStep);
        }
        using (StreamWriter writer = new StreamWriter("result_sql_compare.txt"))
        {
            writer.WriteLine("10000 rows");
            writer.WriteLine("Sql Data Adapter 100 times table fill speed 10000 rows: {0} milliseconds", AdapterFillLargeTableTime);
            writer.WriteLine("Sql Data Reader 100 times table load speed 10000 rows: {0} milliseconds", readerLoadLargeTableTime);
            writer.WriteLine("1000 rows");
            writer.WriteLine("Sql Data Adapter 100 times table fill speed 1000 rows: {0} milliseconds", AdapterFillMediumTableTime);
            writer.WriteLine("Sql Data Reader 100 times table load speed 1000 rows: {0} milliseconds", readerLoadMediumTableTime);
            writer.WriteLine("100 rows");
            writer.WriteLine("Sql Data Adapter 100 times table fill speed 100 rows: {0} milliseconds", AdapterFillSmallTableTime);
            writer.WriteLine("Sql Data Reader 100 times table load speed 100 rows: {0} milliseconds", readerLoadSmallTableTime);
            writer.WriteLine("10 rows");
            writer.WriteLine("Sql Data Adapter 100 times table fill speed 10 rows: {0} milliseconds", AdapterFillTinyTableTime);
            writer.WriteLine("Sql Data Reader 100 times table load speed 10 rows: {0} milliseconds", readerLoadTinyTableTime);

        }
        Process.Start("result_sql_compare.txt");
    }

    private long MeasureExecutionTimeMethod(SqlConnection conn, string query, Action<SqlConnection, string> Method)
    {
        long time; // know C#
        // execute single read step outside measurement time, to warm up cache or whatever
        Method(conn, query);
        // start timing
        time = Environment.TickCount;
        for (int i = 0; i < 100; i++)
        {
            Method(conn, query);
        }
        // return time in milliseconds
        return Environment.TickCount - time;
    }

    private void ExecuteDataAdapterFillStep(SqlConnection conn, string query)
    {
        DataTable tab = new DataTable();
        conn.Open();
        using (SqlDataAdapter comm = new SqlDataAdapter(query, conn))
        {
            // Adapter fill table function
            comm.Fill(tab);
        }
        conn.Close();
    }

    private void ExecuteSqlReaderLoadStep(SqlConnection conn, string query)
    {
        DataTable tab = new DataTable();
        conn.Open();
        using (SqlCommand comm = new SqlCommand(query, conn))
        {
            using (SqlDataReader reader = comm.ExecuteReader())
            {
                // IDataReader Load function
                tab.Load(reader);
            }
        }
        conn.Close();
    }

Results:

10000 rows:
Sql Data Adapter 100 times table fill speed 10000 rows: 11782 milliseconds
Sql Data Reader  100 times table load speed 10000 rows: 26047 milliseconds
1000 rows:
Sql Data Adapter 100 times table fill speed 1000 rows: 984  milliseconds
Sql Data Reader  100 times table load speed 1000 rows: 2031 milliseconds
100 rows:
Sql Data Adapter 100 times table fill speed 100 rows: 125 milliseconds
Sql Data Reader  100 times table load speed 100 rows: 235 milliseconds
10 rows:
Sql Data Adapter 100 times table fill speed 10 rows: 32 milliseconds
Sql Data Reader  100 times table load speed 10 rows: 93 milliseconds

For performance issues, using the SqlDataAdapter.Fill method is far more efficient. So unless you want to shoot yourself in the foot use that. It works faster for small and large data sets.

user1306322
  • 8,561
  • 18
  • 61
  • 122
martijn
  • 1,417
  • 1
  • 16
  • 26
  • this doesn't make much sense since the data adapter USES the data reader under the covers. very odd results, indeed and MS BOL is completely wrong saying the reader should be faster – MC9000 Mar 02 '22 at 18:24
  • 1
    This test focuses on filling a DataTable using either a data reader or a data adaptor. The DataTable reads the reader data using the load function and the adaptor uses the fill function to fill the DataTable. These functions seem to have different implementations which leads to the adaptor being faster. The data reader could still be faster in direct read actions. This test merely shows the fill method is quicker. – martijn Mar 03 '22 at 09:16
  • The DataTable Load function is the bottle neck, for sure. I switched everything back to DataAdapter - no matter how I wrote my datareader method, it was always significantly slower. – MC9000 Mar 03 '22 at 10:54
1

Probably the most elegant solution is:

DataTable dt = reader.GetSchemaTable();

It's a one liner, so go ahead and try! Once you get a reader open, you simply call GetSchemaTable. The beauty is that it works even when the query doesn't return any rows, and yet you can get the information on the columns. There are 31 attributes all told, including if a column is NOT NULL or not at the SQL server, It even gets any Extended Properties you had set on the column at SQL server. As an added bonus, you can easily see anything and everything it gets via Dataset Visualizer in Visual Studio simply by mousing over "DataTable dt". screen cap of Visualizer

Jun Sato
  • 111
  • 2
  • Nice to know, but that doesn't really answer the question. Getting the actual data via DR is always slower than using DA no matter how you write your code. There isn't an example anywhere that shows this not to be the case. – MC9000 Mar 03 '22 at 10:58
  • I think that this solution returns a datatable that represents the metadata of the reader. This means that per column of the database you will get a row with not only the value but the type, the configuration, etc. So in the end, a lot of unnecessary data. – srzsanti May 09 '22 at 09:46