[c#] Populate data table from data reader

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.

Examples related to c#

How can I convert this one line of ActionScript to C#? Microsoft Advertising SDK doesn't deliverer ads How to use a global array in C#? How to correctly write async method? C# - insert values from file into two arrays Uploading into folder in FTP? Are these methods thread safe? dotnet ef not found in .NET Core 3 HTTP Error 500.30 - ANCM In-Process Start Failure Best way to "push" into C# array

Examples related to .net

You must add a reference to assembly 'netstandard, Version=2.0.0.0 How to use Bootstrap 4 in ASP.NET Core No authenticationScheme was specified, and there was no DefaultChallengeScheme found with default authentification and custom authorization .net Core 2.0 - Package was restored using .NetFramework 4.6.1 instead of target framework .netCore 2.0. The package may not be fully compatible Update .NET web service to use TLS 1.2 EF Core add-migration Build Failed What is the difference between .NET Core and .NET Standard Class Library project types? Visual Studio 2017 - Could not load file or assembly 'System.Runtime, Version=4.1.0.0' or one of its dependencies Nuget connection attempt failed "Unable to load the service index for source" Token based authentication in Web API without any user interface

Examples related to ado.net

Error: No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SqlClient' How to fill a datatable with List<T> Populate data table from data reader Increasing the Command Timeout for SQL command how to resolve DTS_E_OLEDBERROR. in ssis Convert DataSet to List How to connect access database in c# MySQL Data Source not appearing in Visual Studio How do I get values from a SQL database into textboxes using C#? Connection to SQL Server Works Sometimes