Use 1 DataReader For Multiple Database Calls

Question

I am just learning to use Parallel.Invoke() and am trying to wrap my mind around returning multiple DataSets() For example, let's take the sample syntax below - how could I use the method ExecuteSqlQuery() for each one of the 3 DataSets() I need returned?

protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    Parallel.Invoke(
        new Action(GetFirstGraders),
        new Action(GetSecondGraders),
        new Action(GetThirdGraders)
        );
  }
}

private void GetFirstGraders()
{
  datasetttt1stgrade = FirstGraders();
  gv1.DataSource = datasetttt1stgrade;
  gv1.DataBind();
}

private void GetSecondGraders()
{
  datasetttt2ndgrade = SecondGraders();
  gv2.DataSource = datasetttt2ndgrade;
  gv2.DataBind();
}

private void GetThirdGraders()
{
  datasetttt3rdgrade = ThirdGraders();
  gv3.DataSource = datasetttt3rdgrade;
  gv3.DataBind();
}

public DataSet FirstGraders()
{
  datasetttt = new DataSet();
  SqlQueryBuilder = new StringBuilder();
  SqlQueryBuilder.Append("exec FirstGraders ");
  datasetttt = ExecuteSqlQuery(databaseConnection, SqlQueryBuilder.ToString());
  datagridInfo.DataSource = datasetttt;
  datagridInfo.DataBind();
}

public DataSet SecondGraders()
{
  datasetttt = new DataSet();
  SqlQueryBuilder = new StringBuilder();
  SqlQueryBuilder.Append("exec SecondGraders ");
  datasetttt = ExecuteSqlQuery(databaseConnection, SqlQueryBuilder.ToString());
  datagridInfo.DataSource = datasetttt;
  datagridInfo.DataBind();
}

public DataSet ThirdGraders()
{
  datasetttt = new DataSet();
  SqlQueryBuilder = new StringBuilder();
  SqlQueryBuilder.Append("exec ThirdGraders ");
  datasetttt = ExecuteSqlQuery(databaseConnection, SqlQueryBuilder.ToString());
  datagridInfo.DataSource = datasetttt;
  datagridInfo.DataBind();
}

public DataSet ExecuteSqlQuery(string connectionString, string sqlQuery)
{
  try
  {           
    connstring = System.Configuration.ConfigurationManager.AppSettings[connectionString].ToString();    
    dbconn = new SqlConnection(connstring);
    cm = new SqlCommand(sqlQuery, dbconn);
    dbconn.Open();
    cm.CommandTimeout = 0;   
    datasetttt = new DataSet();
    da = new SqlDataAdapter(cm);            
    da.Fill(datasetttt, "Data");
    return datasetttt;
  }
  catch (Exception exception) { throw exception; }
  finally
  {
    dbconn.Close();
    cm.Dispose();
    da.Dispose();
  }
}

My Target Framework for this project is Net Framework 4

Answer 1

Why don't you use async/await methods and register them into the page in async mode? http://www.asp.net/web-forms/overview/performance-and-caching/using-asynchronous-methods-in-aspnet-45

UPDATE: I will share a sample of doing async processes using ASP.NET WebForms in 4.0. First I define an extension method utility for generating instances of PageAsyncTask:

public static class PageAsyncTaskGenerator
{
    public static PageAsyncTask ToPageAsyncTask(this Task task, Action<IAsyncResult> onTimeout = null)
    {
        Func<Task> func = () => task;
        return new PageAsyncTask(
            (sender, e, cb, extraData) => func.BeginInvoke(cb, extraData),
            ar => func.EndInvoke(ar).Wait(),
            ar =>
            {
                if (onTimeout != null)
                {
                    onTimeout(ar);
                }
            }, 
            null);
    }
}

Then, in the aspx file, you need to mark the page with the Async property as true:

<%@ Page Language="C#" AutoEventWireup="true" Async="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication1.Default" %>

Then, in your code-behind, just create a Task and register it like this sample:

protected void Page_Load(object sender, EventArgs e)
{
    RegisterAsyncTask(LoadDataAsync().ToPageAsyncTask());
}

private Task LoadDataAsync()
{
    var t1 = ExecuteQueryAsync(databaseConnection, "exec FirstGraders ");
    var t2 = ExecuteQueryAsync(databaseConnection, "exec SecondGraders ");
    var t3 = ExecuteQueryAsync(databaseConnection, "exec ThirdGraders ");
    return Task.Factory.ContinueWhenAll(new[] { t1, t2, t3 }, _ => {
        gv1.DataSource = t1.Result;
        gv1.DataBind();
        gv2.DataSource = t2.Result;
        gv2.DataBind();
        gv3.DataSource = t3.Result;
        gv3.DataBind();
    });
}

private Task<DataSet> ExecuteSqlQueryAsync(string connectionString, string sqlQuery)
{
    return Task.Factory.StartNew(
    () =>
    {
        try
    {
        connstring = System.Configuration.ConfigurationManager.AppSettings[connectionString].ToString();
        dbconn = new SqlConnection(connstring);
        cm = new SqlCommand(sqlQuery, dbconn);
        dbconn.Open();
        cm.CommandTimeout = 0;
        datasetttt = new DataSet();
        da = new SqlDataAdapter(cm);
        da.Fill(datasetttt, "Data");
        return datasetttt;
    }
    catch (Exception exception) { throw exception; }
    finally
    {
        dbconn.Close();
        cm.Dispose();
        da.Dispose();
    }
    });
}

Answer 2

Try this solution and see if it will work in your instance.

Software Cafe

(Please note this is a direct copy/paste in case the site goes down at any time)

protected void Page_Load(object sender, EventArgs e)
{
  SqlConnection cnn1 = new SqlConnection("Your connection string");
  SqlConnection cnn2 = new SqlConnection("Your connection string");
  SqlCommand cmd1;
  SqlCommand cmd2;
  IAsyncResult result1;
  IAsyncResult result2;
  SqlDataReader reader1;
  SqlDataReader reader2;

  try
  {
    cnn1.Open();

    cmd1 = new SqlCommand("SP1", cnn1);
    cmd1.CommandType = System.Data.CommandType.StoredProcedure;
    result1 = cmd1.BeginExecuteReader(CommandBehavior.SingleRow);

    cnn2.Open();
    cmd2 = new SqlCommand("SP2", cnn2);
    cmd2.CommandType = System.Data.CommandType.StoredProcedure;
    result2 = cmd2.BeginExecuteReader(CommandBehavior.SingleRow);

    reader1 = cmd1.EndExecuteReader(result1);

    if (reader1.Read())
    {
        Literal1.Text = reader1[0].ToString();
    }

    reader1.Close();

    reader2 = cmd2.EndExecuteReader(result2);

    if (reader2.Read())
    {
        Literal2.Text = reader2[0].ToString();
    }

    reader2.Close();
  }
  catch (Exception ex)
  {  
    // raise an exception or do whatever logic you want
  }
  finally
  {
    if (cnn1.State != System.Data.ConnectionState.Closed)
        cnn1.Close();

    if (cnn2.State != System.Data.ConnectionState.Closed)
        cnn2.Close();
  }
}

I do not have a compiler on this machine, but something similar to this should allow you to return a dataset for each stored procedure, or this should at least be a good starting point. Ideally you would want to use a using() statement for each connection to ensure it disposes properly, but again this is a starting point

protected void Page_Load(object sender, EventArgs e)
{
SqlCommand _sqlCommand1;
SqlCommand _sqlCommand2;
SqlCommand _sqlCommand3;
DataSet ds1 = new DataSet();
DataSet ds2 = new DataSet();
DataSet ds2 = new DataSet();
SqlDataAdapter _sqlDataAdapter1 = new SqlDataAdapter();
SqlDataAdapter _sqlDataAdapter2 = new SqlDataAdapter();
SqlDataAdapter _sqlDataAdapter3 = new SqlDataAdapter();
SqlConnection _sqlDatabaseConnection1;
SqlConnection _sqlDatabaseConnection2;
SqlConnection _sqlDatabaseConnection3;

try
{
  _connectionString = System.Configuration.ConfigurationManager.AppSettings[connectionString].ToString();
  _sqlDatabaseConnection1 = new SqlConnection(_connectionString);
  _sqlCommand1 = new SqlCommand("SP1", _sqlDatabaseConnection1);
  _sqlDatabaseConnection1.Open();
  _sqlCommand1.CommandTimeout = 0;
  ds1 = new DataSet();
  _sqlDataAdapter1 = new SqlDataAdapter(_sqlCommand1);
  _sqlDataAdapter1.Fill(ds1, "Data");
  return ds1;
  _sqlDatabaseConnection1.Close();
  _sqlCommand1.Dispose();
  _sqlDataAdapter1.Dispose();
  _connectionString = System.Configuration.ConfigurationManager.AppSettings[connectionString].ToString();
  _sqlDatabaseConnection2 = new SqlConnection(_connectionString);
  _sqlCommand2 = new SqlCommand("SP2", _sqlDatabaseConnection1);
  _sqlDatabaseConnection2.Open();
  _sqlCommand2.CommandTimeout = 0;
  ds2 = new DataSet();
  _sqlDataAdapter2 = new SqlDataAdapter(_sqlCommand1);
  _sqlDataAdapter2.Fill(ds2, "Data");
  return ds2;
  _sqlDatabaseConnection2.Close();
  _sqlCommand2.Dispose();
  _sqlDataAdapter2.Dispose();   
  _connectionString = System.Configuration.ConfigurationManager.AppSettings[connectionString].ToString();
  _sqlDatabaseConnection3 = new SqlConnection(_connectionString);
  _sqlCommand3 = new SqlCommand("SP3", _sqlDatabaseConnection1);
  _sqlDatabaseConnection3.Open();
  _sqlCommand3.CommandTimeout = 0;
  ds2 = new DataSet();
  _sqlDataAdapter3 = new SqlDataAdapter(_sqlCommand1);
  _sqlDataAdapter3.Fill(ds3, "Data");
  return ds3;
  _sqlDatabaseConnection3.Close();
  _sqlCommand3.Dispose();
  _sqlDataAdapter3.Dispose();

}
catch (Exception ex) { }
}