使用多个数据读取器 [英] Using Multiple Data Readers
问题描述
我正在 .net 框架上用 C Sharp 开发 WinForm 应用程序.我现在使用的数据库字符串是
I am developing a WinForm Application in C Sharp on the .net framework. The database string I am using as of now is
<add key="Conn" value="Data Source=MNTCON016; Database=Overtime_Calculator;Trusted_Connection=True;MultipleActiveResultSets=true" />
由于我使用 Microsoft SQL Server 2005 进行开发，因此我可以同时使用 2 个数据读取器，将 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.
用于调用2个数据读取器的方法如下:
The Method used to invoke the 2 data readers is as follows:
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();
}
}
}
一切正常.现在真正的问题.我被告知应用程序上线的生产 SQL 服务器是 Microsoft SQL server 2000.经过一番研究，我了解到 Microsoft server 2000 不支持多个活动结果集属性.简而言之，它不允许我同时使用 2 个数据读取器.
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 Server 2000，我需要知道如何同时从 2 个不同的表中读取数据.
I need to know how to read the data from 2 different tables, simultaneously, with regards to SQL Server 2000.
是否还有其他方法可以读取我在代码中提到的数据..
Are there any other ways that i can read data as I have mentioned in the code..
请帮忙..应用程序已基本完成并准备投入生产.但是 MS Server 2000 不允许应用程序相应地工作...
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...
请帮忙
推荐答案
只需创建两个连接，您就可以在 Sql Server 2000 中拥有两个活动的数据读取器.
You can have two active datareaders in Sql Server 2000 by simply creating two connections.
为了证明这一点，我必须首先斥责您使用两种非常糟糕的做法:动态 sql 和数组列表.在您的代码中都没有任何位置.您还应该阅读 using 结构，但如果您仍在使用 .net 1.1，我对使用"和数组列表"表示歉意和哀悼.
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.
也就是说，代码应该如下所示:
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());
}
}
此外，查看您的代码，我怀疑您真正需要做的是重新编写您的 sql.您可以将所有这些组合成一个直接绑定到网格的查询.
Also, looking at your code I suspect what you really need to do is re-write your sql. You can combine all those into a single query that you just bind directly to the grid.
这篇关于使用多个数据读取器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
