“已经存在与该命令相关联的打开的DataReader，必须首先将其关闭." [英] "There is already an open DataReader associated with this Command which must be closed first."

问题描述

我正在开发需要连接到另一个数据库以获取一些数据的应用程序，为此，我决定使用SqlConnection，阅读器等.

I'm working on application which needs to connect to another database to get some data, and to do that, I decided to use SqlConnection, reader etc..

我需要执行一些查询，例如，首先我需要获取某个用户的CARD ID，然后我需要通过该CARD ID获取一些数据.

And I need to execute few queries, for example first I need to get CARD ID for some user, after that I need to get some data by that CARD ID..

这是我的代码:

#region Connection to another Database

SqlConnection sqlConnection1 = new SqlConnection("Data Source=ComputerOne; Initial Catalog=TestDatabase;Integrated Security=False; User ID=test; Password=test123;");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;

cmd.CommandText = "Select * From Users Where CardID=" + "'" + user.CardID + "'";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

reader = cmd.ExecuteReader();

string cardID = "";
string quantity="";

while (reader.Read())
{
    cardID = reader["CardID"].ToString();
}
//HOW COULD I WRITE ANOTHER QUERY NOW, FOR EXAMPLE, OK I GOT CARDID NOW GIVE ME SOME OTHER THINGS FROM THAT DATABASE BY THAT cardID
//here I tried to change CommandText and to keep working with reader.. but its not working like this because its throwing me exception mention in question title.

cmd.CommandText = "Select T1.CardID, T2.Title, Sum(T1.Quantity) as Quantity From CardTransactions as T1 JOIN Adds as T2 ON T1.AddsID = T2.AddsID Where T1.CardID =" + cardID + "AND T1.Type = 1 Group By T1.CardID, T2.Title";

reader = cmd.ExecuteReader();

while (reader.Read())
{
    quantity = reader["Quantity"].ToString();
}

// Data is accessible through the DataReader object here.

sqlConnection1.Close();

#endregion 

伙计们，我如何使用此示例执行几个查询语句?

So guys how could I execute few queries statemens using this example.

非常感谢！干杯

推荐答案

您的问题是您没有处置正在使用的对象.为此，最好始终使用 using 结构，因为它将保证您一切都将被处置.请尝试以下代码:

Your problem is that you are not disposing the objects you are using. For that purpose is better to always use using structure, since it will guarantee you that everithing is gonna be disposed. Try the code below:

SqlConnection sqlConnection1 = new SqlConnection("Data Source=ComputerOne; Initial Catalog=TestDatabase;Integrated Security=False; User ID=test; Password=test123;");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
string cardID = "";
string quantity="";

using(sqlConnection1 = new SqlConnection("Data Source=ComputerOne; Initial Catalog=TestDatabase;Integrated Security=False; User ID=test; Password=test123;"))
{
    sqlConnection1.Open();

    using(cmd = new SqlCommand())
    {
        cmd.CommandText = "Select * From Users Where CardID=" + "'" + user.CardID + "'";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = sqlConnection1;

        using(reader = cmd.ExecuteReader())
        {


            while (reader.Read())
            {
                cardID = reader["CardID"].ToString();
            }
        } //reader gets disposed right here
    } //cmd gets disposed right here

    using(cmd = new SqlCommand())
    {
        cmd.CommandText = "Select T1.CardID, T2.Title, Sum(T1.Quantity) as Quantity From CardTransactions as T1 JOIN Adds as T2 ON T1.AddsID = T2.AddsID Where T1.CardID =" + cardID + "AND T1.Type = 1 Group By T1.CardID, T2.Title";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = sqlConnection1;

        using(reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                quantity = reader["Quantity"].ToString();
            }
        } //reader gets disposed right here
    } //cmd gets disposed right here
    sqlConnection1.Close();
} //sqlConnection1 gets disposed right here

这篇关于“已经存在与该命令相关联的打开的DataReader，必须首先将其关闭."的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！