使用 1 个 DataReader 进行多个数据库调用 [英] Use 1 DataReader For Multiple Database Calls

问题描述

我刚刚学习使用 Parallel.Invoke() 并试图将我的思想围绕返回多个 DataSets() 例如，让我们采用下面的示例语法- 对于需要返回的 3 个 DataSets() 中的每一个，我如何使用 ExecuteSqlQuery() 方法?

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();
  }
}

我的这个项目的目标框架是 Net Framework 4

My Target Framework for this project is Net Framework 4

推荐答案

试试这个解决方案，看看它是否适用于您的实例.

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

软件咖啡厅

(请注意这是直接复制/粘贴，以防网站随时宕机)

(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();
  }
}

我在这台机器上没有编译器，但类似的东西应该允许你为每个存储过程返回一个数据集，或者这至少应该是一个很好的起点.理想情况下，您希望对每个连接使用 using() 语句以确保它正确处理，但这也是一个起点

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) { }
}

这篇关于使用 1 个 DataReader 进行多个数据库调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！