Wednesday, July 16, 2014

Get DataTable using asynchronous Programming

   public static async Task<DataTable> GetDataAsync(string connectionString,string query) 
      {
          DataTable resultTable = new DataTable();
          try
          {
              ConnectionStringSettings connectionStringSettings = System.Configuration.ConfigurationManager.ConnectionStrings[connectionString];
              DbProviderFactory factory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
              using (DbConnection connection = factory.CreateConnection())
              {
                  connection.ConnectionString = connectionStringSettings.ConnectionString;
                  connection.Open();
                  DbCommand command = connection.CreateCommand();
                  command.CommandText = query;
               
                  DbDataReader readers = command.ExecuteReader(); 
                  DataTable schemaTable = readers.GetSchemaTable();
                  foreach (DataRow dataRow in schemaTable.Rows)
                  {
                      DataColumn dataColumn = new DataColumn();
                      dataColumn.ColumnName = dataRow[0].ToString();
                      dataColumn.DataType = Type.GetType(dataRow["DataType"].ToString());
                      resultTable.Columns.Add(dataColumn);
                  }
                  readers.Close();
                  command.CommandTimeout = 30000;
                  using (DbDataReader reader = await command.ExecuteReaderAsync())
                  {
                      while (await reader.ReadAsync())
                      {
                          DataRow dataRow = resultTable.NewRow();
                          for (int i = 0; i < resultTable.Columns.Count ; i++)
                          {
                              dataRow[i] = reader[i];
                          }
                           Console.WriteLine(string.Format("From thread {0}-and data-{1}",System.Threading.Thread.CurrentThread.ManagedThreadId,dataRow[0]));
                          resultTable.Rows.Add(dataRow);
                      }
                  }
              }
          }
          catch (System.Exception ex)
          {
              Console.WriteLine(ex.Message);
              throw;
          }
          return resultTable;
      }