SqlDataReader conflicted with 2 SqlCommand

Question

I have two SqlCommand which I want use both in a single method, thus I don't understand why it didn't work. This method will simultaneously update a Column based on every Read() ItemId.

Is it even possible for me to execute SqlCommand during SqlDataReader.Read()?

Here's the exception I got:

There is already an open DataReader associated with this Command which must be closed first.

My code:

private void UpdateStock()
{
    string thisUpdateCommandText = "UPDATE [Item] SET [Stock] += @Quantity WHERE [ItemId] = @ItemId";
    string thisSelectCommandText = "SELECT [ItemId], [Quantity] FROM [PackageItem] WHERE [PackageId] = @PackageId";

    using (SqlConnection thisSqlConnection = new SqlConnection(theConnectionString))
    using (SqlCommand thisSelectSqlCommand = new SqlCommand(thisSelectCommandText, thisSqlConnection))
    {
        thisSqlConnection.Open();
        thisSelectSqlCommand.Parameters.Add("@PackageId", SqlDbType.Int).Value = thePackageId;

        using (SqlDataReader thisSqlDataReader = thisSelectSqlCommand.ExecuteReader())
        {
            while (thisSqlDataReader.Read())
            {
                using (SqlCommand thisUpdateSqlCommand = new SqlCommand(thisUpdateCommandText, thisSqlConnection))
                {
                    thisUpdateSqlCommand.Parameters.Add("@ItemId", SqlDbType.Int).Value = thisSqlDataReader.GetInt32(0);
                    thisUpdateSqlCommand.Parameters.Add("@Quantity", SqlDbType.Int).Value = thisSqlDataReader.GetInt32(1);
                    thisUpdateSqlCommand.ExecuteNonQuery();
                }
            }
        }
    }
}

Look dude, I want to execute a command inside a Reader, which a total different from above. — Fransisco Wijaya, Jul 12 '17 at 06:38
Another similar issue: https://stackoverflow.com/questions/3839569/error-there-is-already-an-open-datareader-associated-with-this-command-which-mu. Instead of using `ExecuteNonQuery` inside a `DataReader`, use a `DataSet` or `DataTable` & execute second query from them (the first command needs to be disposed first). — Tetsuya Yamamoto, Jul 12 '17 at 06:41
Lol the answer didn't describe it was supposed to solve both problems. — Fransisco Wijaya, Jul 12 '17 at 06:46

score 0 · Accepted Answer · answered Jul 12 '17 at 06:44

You're trying to use the tool in a way it was not meant to be used.

Namely:

Only one SqlDataReader per associated SqlConnection may be open at a time, and any attempt to open another will fail until the first one is closed. Similarly, while the SqlDataReader is being used, the associated SqlConnection is busy serving it until you call Close.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.read(v=vs.110).aspx

So just find yourself another approach.

One possible proposal: Wrap your 2 queries into single sql transaction, send parameters to it and do everything there. There are many benefits going that way (beside solving this problem).

Thanks, I just need to turn on the MARS, never heard about it :( — Fransisco Wijaya, Jul 12 '17 at 06:47
Be very careful with MARS. In examples like this you will most probably create many more serious problems using MARS. In particular, your data consistency might just go off. — dee zg, Jul 12 '17 at 06:48

Hope Mystery · Answer 2 · 2017-07-12T07:02:52.043

in case of SqlDataReader, if you have used Two calls at the same time, you must have two different SqlConnections for each one. SqlDataReader reads in buffer this is why its very fast. but another way is to save data in DataSet or DataTable or List of objects then call the other SqlDataReader since you have saved data in objects and closed the datareader so the buffer is closed. Using SqlDataReader makes a direct connection and kept opened and prevents you from using the SqlConnection until the SqlDataReader is closed. then it returns the SqlConnection to you to use in another SqlCommand. But in DataSet or DataTable the buffer opened and Saves the data then closed this why its slower. but each has its case and you need to learn how and when and why to use.

And in case of SqlDataReader with another Command its the same. One Connection is reserved by SqlDataReader buffer until its closed and the other must use another SqlConnection since the first one is reserved by SqlDataReader buffer.

Beware of MARS and its issues: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-multiple-active-result-sets

i prefer not to use it unless you have high experience and know what you are doing exactly

My recommendation never use MARS unless you are forced to

SqlDataReader conflicted with 2 SqlCommand

2 Answers2