SqlDataReader and SqlCommand

Question

I have the following code.

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
   connection.Open();
   SqlCommand select = new SqlCommand("SELECT RTRIM(LTRIM(PART_NO)) AS PART_NO, record FROM [RMAData].[dbo].[IMPORTING_ORDER_EDI] WHERE sessionID = '" + Session.SessionID + "'", connection);

   SqlDataReader reader = select.ExecuteReader();

   if (reader.HasRows)
   {
      while (reader.Read())
      {
         if (!currentPart.IsActive)
         {
            // this part is not active, set the active flag in sql to 0
            SqlCommand update = new SqlCommand("UPDATE [RMAData].[dbo].[IMPORTING_ORDER_EDI] SET valid = 0, active = 0 WHERE record = " + reader["record"].ToString() + ";", connection);

            update.ExecuteNonQuery();
         }
         else
         {
            ///blah
         }
      }

      reader.Close();
   }
}

but this causes the following exception...

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

I need to read each row returned, do some validation on the data and make an update if necessary, then continue to the next record. How can I achieve this if I can't use a SqlCommand while looping through reader.Read() ?

Grant Thomas's answer is correct, but it would be more efficient for you to record the PK of each record you wish to set to inactive and then do a single UPDATE ... WHERE IN (...) after you close the reader. — Phil, Feb 21 '13 at 15:36
interesting... like build an array of the PK's you mean then loop through that? — Stuart, Feb 21 '13 at 15:41
Why do you need to do select and loop each update? Why not just do an update with the same where clause as the select statement? — adrianm, Feb 21 '13 at 18:19

score 6 · Answer 1 · answered Feb 21 '13 at 15:31

6

Could be as simple as amending your connection string:

add MultipleActiveResultSets=True to connection string

answered Feb 21 '13 at 15:31

Grant Thomas

44,454
10
85
129

Spot-On!! Thank you very much!! I love this place! – Stuart Feb 21 '13 at 15:40
Love it when it's this simple! – fuzzy_logic Aug 09 '18 at 02:57

शेखर · Answer 2 · 2013-02-21T15:42:47.123

You need to either create multiple instances of you connection.
As only one command can be excuted against a connection in general
or

do as suggested by @grantThomas
Or you can use multiple connection as follows

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
   connection.Open();
   SqlCommand select = new SqlCommand("SELECT RTRIM(LTRIM(PART_NO)) AS PART_NO, record FROM [RMAData].[dbo].[IMPORTING_ORDER_EDI] WHERE sessionID = '" + Session.SessionID + "'", connection);

   SqlDataReader reader = select.ExecuteReader();

   if (reader.HasRows)
   {
      while (reader.Read())
      {
         if (!currentPart.IsActive)
         {
            // this part is not active, set the active flag in sql to 0
            using (SqlConnection connection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
           {
               SqlCommand update = new SqlCommand("UPDATE [RMAData].[dbo].[IMPORTING_ORDER_EDI] SET valid = 0, active = 0 WHERE record = " + reader["record"].ToString() + ";", connection1);


            update.ExecuteNonQuery();
           }
         }
         else
         {
            ///blah
         }
      }

      reader.Close();
   }
}

This scenario of a SqlDataReader loop and then needing to access other data for each row is a common one. Another way to get the connect string for the new one is to copy the ConnectionString property from the original connection that is "busy" with the datareader, e.g., SqlConnection newConn = new SqlConnection(originalConnection.ConnectionString); — Developer63, Nov 13 '15 at 18:26
The question is that if one is updating a session temp table, would that temp table be seen by the 2nd connection? I wouldn't think so but not 100% sure. — user441521, Jan 05 '17 at 15:45

score 3 · Accepted Answer · answered Feb 21 '13 at 18:13

An alternative is not add MultipleActiveResultSets=True - there is a small performance penalty for doing so - and so something like this:

using (SqlConnection connection = new ...))
{
   connection.Open();
   SqlCommand select = new SqlCommand(...);

   SqlDataReader reader = select.ExecuteReader();

   var toInactivate = new List<string>();

   if (reader.HasRows)
   {
      while (reader.Read())
      {
         if (!currentPart.IsActive)
         {
            toInactivate.Add(reader["record"].ToString());
         }
         else
         {
            ///blah
         }
      }

      reader.Close();
   }

   SqlCommand update = new SqlCommand("UPDATE ... SET valid = 0, active = 0 " +
       "WHERE record IN(" + string.Join(",", toInactivate) +  ");", connection);

   update.ExecuteNonQuery();
}

which has the advantage of updating all the required records in a single SQL statement.

And of course the whole thing would be so much neater using EF and Linq.

as a learner, it's quite reassuring that i've actually done exactly this before i'd read it. Grant Thomas' answer did work, but I also took heed of your comment and updated my code. :) Thanks for the help! — Stuart, Feb 22 '13 at 11:07

SqlDataReader and SqlCommand

3 Answers3

Linked