C# 使用多个数据读取器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2057534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Multiple Data Readers
提问by reggie
I am developing a WinForm Application in C Sharp on the .net framework. The database string I am using as of now is
我正在 .net 框架上用 C Sharp 开发 WinForm 应用程序。我现在使用的数据库字符串是
<add key="Conn" value="Data Source=MNTCON016; Database=Overtime_Calculator;Trusted_Connection=True;MultipleActiveResultSets=true" />
As I am using Microsoft SQL Server 2005 for development, I can use 2 data readers simultaneously using the MultipleActiveResultSets property to true as mentioned above.
由于我使用 Microsoft SQL Server 2005 进行开发，因此我可以同时使用 2 个数据读取器，将 MultipleActiveResultSets 属性设置为 true，如上所述。
The Method used to invoke the 2 data readers is as follows:
用于调用2个数据读取器的方法如下：
public static void SignUpControllerDay(DateTime Date, System.Windows.Forms.DataGridView PassedGrid)
{
string sql_SignUp = String.Format(@"SELECT Emp_ID as Emp_ID, Name as Name, Sum(Sum) as Sum FROM
(SELECT DISTINCT o.Date, e.Emp_ID as Emp_ID,
e.First_Name+ ' ' +e.Last_Name as Name,
o.Quantity as Sum
FROM Employee e,OT_Hours o,Position p,Signup_Sheet s
WHERE e.Emp_ID=o.Emp_ID
and e.Emp_ID = s.Employee_ID
and s.Day_Shift = 1
and e.Position_ID = p.Position_ID
and p.Position_Name = 'Controller'
and o.Quantity NOT IN(0.3)
and s.Date = '{0}'
and o.Date <= CONVERT(VARCHAR,'{0}',101) AND o.Date > CONVERT(VARCHAR,DATEADD(YYYY,-1,'{0}'),101) )
as OVERTIME
GROUP BY Emp_ID,Name
ORDER BY Sum", Date);
SqlConnection sqlConn = null;
SqlCommand cmd_SignUp;
SqlDataReader dr_SignUp;
try
{
sqlConn = new SqlConnection(databaseConnectionString);
sqlConn.Open();
cmd_SignUp = new SqlCommand(sql_SignUp, sqlConn);
dr_SignUp = cmd_SignUp.ExecuteReader();
while (dr_SignUp.Read())
{
ArrayList arrPhone = new ArrayList();
string sql_Phone = String.Format("SELECT Phone_Number FROM Contact_Details WHERE Emp_ID = {0}", dr_SignUp["Emp_ID"]);
SqlCommand cmd_Phone = new SqlCommand(sql_Phone, sqlConn);
SqlDataReader dr_Phone = cmd_Phone.ExecuteReader();
while (dr_Phone.Read())
{
arrPhone.Add(dr_Phone["Phone_Number"].ToString());
}
//--Retrieving Sectors
ArrayList arrSector = new ArrayList();
string sql_Sector = String.Format(@"SELECT e1.EMP_ID,
( SELECT cast(Sector_ID as varchar(10)) + ';'
FROM Employee_Sector_relationship e2
WHERE e2.Emp_ID = e1.Emp_ID
ORDER BY Sector_ID
FOR XML PATH('') ) AS Sectors
FROM Employee_Sector_Relationship e1
WHERE Emp_ID = {0}
GROUP BY Emp_ID ", dr_SignUp["Emp_ID"]);
SqlCommand cmd_Sector = new SqlCommand(sql_Sector, sqlConn);
SqlDataReader dr_Sector = cmd_Sector.ExecuteReader();
while (dr_Sector.Read())
{
arrSector.Add(dr_Sector["Sectors"].ToString());
}
if (arrSector.Count == 0)
{ arrSector.Add(" "); }
if (arrPhone.Count == 0)
{ arrPhone.Add(" "); }
//--
if (arrPhone.Count == 2)
{
PassedGrid.Rows.Add(dr_SignUp["Emp_ID"].ToString(), dr_SignUp["Name"].ToString(), arrSector[0], dr_SignUp["Sum"], arrPhone[0], arrPhone[1]);
}
else
{
PassedGrid.Rows.Add(dr_SignUp["Emp_ID"].ToString(), dr_SignUp["Name"].ToString(), arrSector[0], dr_SignUp["Sum"], arrPhone[0]);
}
}
}
catch (Exception e)
{
MessageBox.Show("Error found in SignUpControllerDay..." + Environment.NewLine + e.ToString());
}
finally
{
if (sqlConn != null)
{
sqlConn.Close();
}
}
}
Everything works fine. Now the real problem. I have been informed that the production SQL server for the application to go live is Microsoft SQL server 2000. After doing a bit research, I came to know that Microsoft server 2000 does not support multiple active results sets propery. In short, it does not allow me to use 2 data readers simultaneously.
一切正常。现在真正的问题。我被告知应用程序上线的生产 SQL 服务器是 Microsoft SQL server 2000。经过一番研究，我了解到 Microsoft server 2000 不支持多个活动结果集属性。简而言之，它不允许我同时使用 2 个数据读取器。
I need to know how to read the data from 2 different tables, simultaneously, with regards to SQL Server 2000.
关于 SQL Server 2000，我需要知道如何同时从 2 个不同的表中读取数据。
Are there any other ways that i can read data as I have mentioned in the code..
有没有其他方法可以读取我在代码中提到的数据..
Please help.. the application is almost done and is ready to go to production. but MS server 2000 doesnt allow the applcaition to work accordingly...
请帮助.. 应用程序几乎完成并准备投入生产。但是 MS Server 2000 不允许应用程序相应地工作...
please help
请帮忙
采纳答案by Joel Coehoorn
You can have two active datareaders in Sql Server 2000 by simply creating two connections.
通过简单地创建两个连接，您可以在 Sql Server 2000 中拥有两个活动的数据读取器。
To demonstrate this, I must first berate you for using two very poor practices: dynamic sql and arraylists. Neither have any place in your code. You should also read up on the using construct, though you have my apologies and condolences on "using" and "arraylists" if you're still using .net 1.1.
为了证明这一点，我必须首先斥责您使用两种非常糟糕的做法：动态 sql 和数组列表。在您的代码中都没有任何位置。您还应该阅读 using 结构，但如果您仍在使用 .net 1.1，则对“using”和“arraylists”表示歉意和哀悼。
That said, here's how the code should look:
也就是说，代码应该如下所示：
string sql_Phone = "SELECT Phone_Number FROM Contact_Details WHERE Emp_ID = @EmpID";
using (SqlConnection cn2 = new Sqlconnection(databaseConnectionString))
using (SqlCommand cmd_Phone = new SqlCommand(sql_Phone, cn2))
{
cmd_Phone.Parameters.Add("@EmpID", SqlDbType.Int);
cn2.Open();
while (dr_SignUp.Read())
{
List<string> arrPhone = new List<string>();
cmd_Phone.Parameters[0].Value = dr_SignUp["Emp_ID"];
using (SqlDataReader dr_Phone = cmd_Phone.ExecuteReader())
{
while (dr_Phone.Read())
{
arrPhone.Add(dr_Phone["Phone_Number"].ToString());
}
}
Also, looking at your code I suspect what you reallyneed to do is re-write your sql. You can combine all those into a single query that you just bind directly to the grid.
另外，查看您的代码，我怀疑您真正需要做的是重新编写您的 sql。您可以将所有这些组合成一个直接绑定到网格的查询。
回答by Darin Dimitrov
Sure:
当然：
public void SignUpControllerDay()
{
using (var conn = new SqlConnection(ConnectionString))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "SELECT ...";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var phone = reader["Phone_Number"].ToString();
Bar(phone);
}
}
}
}
public void Bar(string phone)
{
using (var conn = new SqlConnection(ConnectionString))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "SELECT ..."; // use phone to prepare statement
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// Fill the grid
}
}
}
}
回答by Zenon
You could open multiple database connections with 1 reader per connection
您可以打开多个数据库连接，每个连接有 1 个读取器
回答by canuto
so, it is not possible! as simple as that the only answer!
所以，这是不可能的！就这么简单，唯一的答案！
multiple connection is a workaroud!
多重连接是一种解决方法！
one connection can not handle multiple recordsets simultaneously
一个连接不能同时处理多个记录集
