none
Read SQL table asynchronously RRS feed

  • Question

  • Dear experts,

    In my old project under vs2010 i used to populate data table as under

    string sqlcmd = @"select * from GetBillDetailsView where 
                             finyear=@finyear and convert(date,doc_dt) between @fromdate and @todate order by connect2,doc_no,srl";
    SqlCommand cmd = new SqlCommand(sqlcmd, con);
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.Add(new SqlParameter("@finyear", finYear));
    SqlParameter("@PlantID", ErpMdi1.ID));
    cmd.CommandTimeout = 10000;
    cmd.Parameters.Add(new SqlParameter("@fromdate", ErpMdi1.dtBegin.ToString("yyyyMMdd")));
    cmd.Parameters.Add(new SqlParameter("@todate", ErpMdi1.dtLoggedAt.ToString("yyyyMMdd")));
    ds.Tables["billWithItem"].Clear();
    // Async Example 
    try
    {
      // create AsyncCallback delegate 
      AsyncCallback callback = new AsyncCallback(RetrieveDataCallback);
     cmd.BeginExecuteReader(callback, cmd,   CommandBehavior.CloseConnection);
    }
    catch (Exception ex)
    {
      MessageBox.Show("Message 3:" + ex.ToString());
    }
    private delegate void AddRecordsDelegate(SqlDataReader dr,DataTable myDataTable,string Message);
      private void AddRecords(SqlDataReader dr,DataTable myDataTable,string msg)
            {
                //                DataTable myDataTable = ds.Tables["billWithItem"];
                if (dr != null && myDataTable != null)
                {
                    //MessageBox.Show(dr["billkey"].ToString() + " "  + dr["doc_dt"].ToString());
                    int fieldCount = dr.FieldCount;
                    DataRow newRow = myDataTable.NewRow();
                        for (int i = 0; i < fieldCount; i++)
                        {
                            newRow[i] = dr[i];
                        }
                        myDataTable.Rows.Add(newRow);
                        lblRecordValue.Text = msg + " " + ds.Tables["billWithItem"].Rows.Count.ToString();
                    //MissingBills missingbills = new MissingBills(dr);
                    //_MissingBills.Add(missingbills);
                }
                else
                {
                    this.Cursor = Cursors.Default;
                    lblRecordValue.Text = msg  + " " + ds.Tables["billWithItem"].Rows.Count.ToString();
                    timer1.Enabled = false;
                    pgbBillItemQuery.Value = pgbBillItemQuery.Maximum;
                    //lblquerystatus.Text = Message;
                    //pgbPosting.Value = pgbPosting.Maximum;
                    //cmbUtilities.Enabled = true;
                    //btnDoIt.Enabled = true;
                    //timer1.Enabled = false;
                    //dgvUtilities.DataSource = null;
                    //dgvUtilities.DataSource = _MissingBills;
                    //dgvUtilities.Refresh();
    
                }
            }
    private void RetrieveDataCallback(IAsyncResult result)
            {
                SqlDataReader dr = null;
                DataTable myDataTable = ds.Tables["billWithItem"];
                SqlCommand command = (SqlCommand)result.AsyncState;
                try
                {
                    //MissingBills  missingbills = null;
                    dr = command.EndExecuteReader(result);
    
                    while (dr.Read())
                    {
                        Invoke(new AddRecordsDelegate(AddRecords), dr,myDataTable,"Reading In Progress...");
                    }
    
                }
                catch (Exception ex)
                {
                    Invoke(new AddRecordsDelegate(AddRecords), null,null,ex.ToString());
                }
                finally
                {
                    try
                    {
                        if (dr != null & !dr.IsClosed)
                        {
                            dr.Close();
                            if (command.Connection.State.Equals(ConnectionState.Open))
                            {
                                command.Connection.Close();
                                MessageBox.Show("Conn CLosed Manually.Unsuccessful Completion");
                            }
                        }
                        Invoke(new AddRecordsDelegate(AddRecords), null,null,"Successfully Completed");
                        // refer dgv1 row head double click event on 23rd dec 2012 by san
                        //this.FormMasterChildFill();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show( ex.ToString());
                    }
    
                    //Increment value to notify that the call is complete.
                    Interlocked.Increment(ref counter);
                }
            }

    how the same could be done efficiently using visual studio 2013 , framework 4.5 




    Monday, November 28, 2016 2:58 PM

All replies

  • You can use Parallel.ForEach

    See https://social.msdn.microsoft.com/Forums/en-US/53ad8694-0b48-4e6c-a5d1-280d4a0f17da/parallelforeach-with-datatable-rows-collection?forum=parallelextensions for example



    Visual C++ MVP

    Monday, November 28, 2016 4:31 PM
  • Hi,

    According to your description, it seems that you asynchronously ado.net, please refer to the following code, which you need to install .NetFramework 4.5.

    using System;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Threading.Tasks;
    
    class A {
       static async Task PerformDBOperationsUsingProviderModel(string connectionString, string providerName) {
          DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
          using (DbConnection connection = factory.CreateConnection()) {
             connection.ConnectionString = connectionString;
             await connection.OpenAsync();
    
             DbCommand command = connection.CreateCommand();
             command.CommandText = "SELECT * FROM AUTHORS";
    
             using (DbDataReader reader = await command.ExecuteReaderAsync()) {
                while (await reader.ReadAsync()) {
                   for (int i = 0; i < reader.FieldCount; i++) {
                      // Process each column as appropriate
                      object obj = await reader.GetFieldValueAsync<object>(i);
                      Console.WriteLine(obj);
                   }
                }
             }
          }
       }
    
       public static void Main() 
       {
           SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
           // replace these with your own values
           builder.DataSource = "your_server";
           builder.InitialCatalog = "pubs";
           builder.IntegratedSecurity = true;
           string provider = "System.Data.SqlClient";
    
           Task task = PerformDBOperationsUsingProviderModel(builder.ConnectionString, provider);
           task.Wait();
       }
    }

    For more information, please refer to:

    https://msdn.microsoft.com/en-us/library/hh211418(v=vs.110).aspx

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 29, 2016 1:12 AM
    Moderator
  • dear cole wu,

    i have cancelled proposed as answer because the

    question is re framed

    The way i under stood and tries it the application gets hanged

    i modified the my program as under to test a table fill on button click, but the application gets hung

    any simplified suggestion or misunderstanding by me in using it. please correct it as  I am  new to sql and c#

    timer1.Enabled = true;
                pgbBillItemQuery.Value = pgbBillItemQuery.Minimum;
                this.Cursor = Cursors.WaitCursor;
                GridFilterSql = null;
                //To check Async
                //+";Asynchronous Processing=true"
                ds.Tables["billWithItem"].Clear();
                Task task = 
                    PerformDBOperationsUsingProviderModel
                    (
                        Properties.Settings.Default.kingsConnectionString + ";Asynchronous Processing=true",
                        "System.Data.SqlClient", 
                        ds.Tables["billWithItem"]
                    );
                task.Wait();
    
            //Async 4.5
            static async Task PerformDBOperationsUsingProviderModel(
            string connectionString, string providerName, DataTable dt)
            {
                DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
                using (DbConnection connection = factory.CreateConnection())
                {
                    connection.ConnectionString = connectionString;
                    await connection.OpenAsync();
    
                    string selectcmd = @"select * from GetBillDetailsView where 
                             finyear=@finyear and convert(date,doc_dt) between @fromdate and @todate order by connect2,doc_no,srl ";
    
    
    
                    DbCommand command = connection.CreateCommand();
                    command.CommandText = selectcmd;
                    command.Parameters.Add(new SqlParameter("@finyear", ErpMdi1.dtLoggedAt.ToString("yyyyMMdd")));
                    command.Parameters.Add(new SqlParameter("@fromdate",ErpMdi1.dtBegin.ToString("yyyyMMdd")));
                    command.Parameters.Add(new SqlParameter("@todate", ErpMdi1.dtLoggedAt.ToString("yyyyMMdd")));
    
                    using (DbDataReader reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                // Process each column as appropriate
                                object obj = await reader.GetFieldValueAsync<object>(i);
                                //Console.WriteLine(obj);
                                dt.Rows.Add(obj);
                            }
                        }
                    }
                }
            }
    

     

    Thursday, December 1, 2016 1:29 PM
  • Hi,

    If you want to result a Datatable, please modify the method like this:

    static async Task<DataTable> PerformDBOperationsUsingProviderModel(string connectionString, string providerName)
            {
                DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
                using (DbConnection connection = factory.CreateConnection())
                {
                    connection.ConnectionString = connectionString;
                    await connection.OpenAsync();
    
                    DbCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT * FROM [User]";
                    using (DbDataReader reader = await command.ExecuteReaderAsync())
                    {
                        var dataTable = new DataTable();
                        dataTable.Load(reader);
    
                        return dataTable;
                     }
                }
            }

    Usage:

    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                // replace these with your own values
                builder.DataSource = "(local)";
                builder.InitialCatalog = "TestDeployDb";
                builder.IntegratedSecurity = true;
                string provider = "System.Data.SqlClient";
    
                Task<DataTable> task = PerformDBOperationsUsingProviderModel(builder.ConnectionString, provider);
                task.Wait();
    
                DataTable dt = task.Result;

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 2, 2016 7:19 AM
    Moderator
  • Dear Cole wu

    thanks for your valuable time and effort , 

    i modified the program as under

     static async Task<DataTable> PerformDBOperationsUsingProviderModel(string connectionString, string providerName)
            {
                DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
                using (DbConnection connection = factory.CreateConnection())
                {
    
                    string selectcmd = @"select * from GetBillDetailsView where 
                             finyear=@finyear and convert(date,doc_dt) between @fromdate and @todate order by connect2,doc_no,srl ";
    
                    DbCommand command = connection.CreateCommand();
                    command.CommandText = selectcmd;
                    command.Parameters.Add(new SqlParameter("@finyear", ErpMdi1.finYr));
                    command.Parameters.Add(new SqlParameter("@fromdate",ErpMdi1.dtBegin.ToString("yyyyMMdd")));
                    command.Parameters.Add(new SqlParameter("@todate", ErpMdi1.dtLoggedAt.ToString("yyyyMMdd")));
    
                    connection.ConnectionString = connectionString;
                    await connection.OpenAsync();
                    using (DbDataReader reader = await command.ExecuteReaderAsync())
                    {
                        var dataTable = new DataTable();
                        dataTable.Load(reader);
    
                        return dataTable;
                    }
                }
            }
    
            private void btnGo_Click(object sender, EventArgs e)
            {
                //timer1.Enabled = true;
                //pgbBillItemQuery.Value = pgbBillItemQuery.Minimum;
                //this.Cursor = Cursors.WaitCursor;
                //GridFilterSql = null;
                //To check Async
                //+";Asynchronous Processing=true"
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                // replace these with your own values
                builder.DataSource = "kings";
                builder.InitialCatalog = "mf";
                builder.IntegratedSecurity = true;
                string provider = "System.Data.SqlClient";
    
                ds.Tables["billWithItem"].Clear();
                Task<DataTable> task = PerformDBOperationsUsingProviderModel(builder.ConnectionString, provider);
                //Task<DataTable> task = PerformDBOperatiosUsingProviderModel(Properties.Settings.Default.kingsConnectionString + ";Asynchronous Processing=true", "System.Data.SqlClient");
                task.Wait();
                DataTable dt = task.Result;
            }

    in ssms it took around 4 minuts 13 seconds to return 36460 rows.

    but in vs2013 after hitting button even after 10 minutes nothing is seen and the control does not return to  the following break point 

    nor as in earlier framework 4 asysnc table data fetch i could see the data as it was being read.

    nothing is happening.

    any guess where i m going wrong

    DataTable dt = task.Result;

    Friday, December 2, 2016 12:39 PM
  • You should be awaiting your code. Wait and Result are both blocking calls. Additionally, SQL Command is not truly asynchronous until you enable Asynchronous Processing=true.

    Here is small example (not tested) of how to load a DataTable asynchronously:

        var data = await GetData();     
           
        public async Task<DataTable> GetData()
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder("your connection string")
                {
                    AsynchronousProcessing = true
                };
    
                var data = new DataTable();
                using (SqlConnection conn = new SqlConnection(builder.ConnectionString))
                {
                    await conn.OpenAsync();
                    using (SqlCommand cmd = new SqlCommand("select * from sometable", conn))
                    {
                        var reader = await cmd.ExecuteReaderAsync();
    
                        if (reader.HasRows)
                            data.Load(reader);
                    }
                }
    
                return data;
            } 


    william xifaras



    Monday, December 12, 2016 9:18 PM