CODEWITHSUNDEEP
c#asp.netmultithreadingwebforms
nospaceshuttle leftinthisnatio
nospaceshuttle leftinthisnatio

Reputation: 63

Filling SqlDataAdapter

I am running a SQLQuery that takes roughly 45 seconds to run and display results. I am using Task<DataSet> to populate two drop downs on my page. Well the 1st drop down populates fine (the query is completed in about 2 seconds), the second it seems that the adapter.Fill(dataSet) is not waiting on the query to complete before it begins to fill the drop down with a null dataset. What should I alter so that the code execution halts until the query executes completely?

Task.Factory.ContinueWhenAll(new[]
{
  One("Data Source=server;Initial Catalog=db;Integrated Security=True;MultipleActiveResultSets=True"),
  Two("Data Source=server;Initial Catalog=db;Integrated Security=True;MultipleActiveResultSets=True"),
}, tasks =>
{
  try
  {
    this.ddl1.DataSource = tasks[0].Result.Tables[0];
    this.ddl1.DataTextField = "One";
    this.ddl1.DataValueField = "ID";
    this.ddl1.DataBind();
    int indexOfLastItem = this.ddl1.Items.Count - 1;
    this.ddl1.SelectedIndex = indexOfLastItem;
    ddl2.DataSource = tasks[1].Result.Tables[0];
    this.ddl2.DataTextField = "Two";
    this.ddl2.DataValueField = "ID";
    this.ddl2.DataBind();
    this.ddl2.Items.Insert(0, new ListItem(Constants.All, Constants.All));
  }
  catch (Exception exception) { throw exception; }
}, CancellationToken.None, TaskContinuationOptions.None, TaskScheduler.FromCurrentSynchronizationContext());

public System.Threading.Tasks.Task<DataSet> One(string databaseConnection)
{
  return FillDS("Select * from activeemployees", databaseConnection);
}
public System.Threading.Tasks.Task<DataSet> Two(string databaseConnection)
{
  return FillDS("Select * from mastersalesdatabase", databaseConnection);
}
public System.Threading.Tasks.Task<DataSet> FillDS(string sqlQuery, string connectionString)
{
  try
  {
    var dataSet = new DataSet();
    using (var adapter = new SqlDataAdapter(sqlQuery, connectionString))
    {
        adapter.Fill(dataSet);
        return dataSet;
    }
  }
  catch (Exception exception) { throw exception; }
}

My query Select * from activeemployees completes in about 2 seconds and populates fine, my query Select * from mastersalesdatabase takes roughly 45 seconds and it seems the code just moves on w/o a delay to let the query execute to completion.

Upvotes: 1

Views: 1203

Answers (1)

mason
mason

Reputation: 32702

If you're going to do async to retrieve data into a datatable, it should look more like this:

public static async Task<DataTable> GetDataTableAsync(string connectionString, SqlCommand command)
{
    using (var connection = new SqlConnection(connectionString))
    {
        command.Connection = connection;
        await connection.OpenAsync();
        using (var dataReader = await command.ExecuteReaderAsync())
        {
            var dataTable = new DataTable();
            dataTable.Load(dataReader);
            return dataTable;
        }
    }
}

Notice there's no need for a dataset.

Then in WebForms, we have to handle async code differently.

protected void Page_Load(object sender, EventArgs e)
{
    RegisterAsyncTask(new PageAsyncTask(DoWorkAsync));
}

private async Task DoWorkAsync()
{
    ActiveEmployeesDropDownList.DataSource = GetDataTableAsync(databaseConnection, new SqlCommand("select * from activeemployees"));
    ActiveEmployeesDropDownList.DataBind();
}

Notice I renamed the control from ddl1 to ActiveEmployeesDropDownList because ddl1 is a horrible name. Your names should have semantic meaning.

You'll need to add the async=true attribute to your page according to MSDN.

And you should also fix your query to not take 45 seconds, but that's a separate question entirely.

Upvotes: 2

Related Questions