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
– Phil, Commented Feb 21, 2013 at 15:36
interesting... like build an array of the PK's you mean then loop through that? — Stuart
– Stuart, Commented Feb 21, 2013 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
– adrianm, Commented Feb 21, 2013 at 18:19

Grant Thomas · Accepted Answer · 2013-02-21 15:31:07Z

6

Could be as simple as amending your connection string:

add MultipleActiveResultSets=True to connection string

answered Feb 21, 2013 at 15:31

Grant Thomas

45.1k10 gold badges88 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stuart Over a year ago

Spot-On!! Thank you very much!! I love this place!

fuzzy_logic Over a year ago

Love it when it's this simple!

Phil · Accepted Answer · 2013-02-21 18:13:20Z

3

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.

answered Feb 21, 2013 at 18:13

Phil

43.2k9 gold badges102 silver badges102 bronze badges

1 Comment

Stuart Over a year ago

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!

शेखर · Accepted Answer · 2013-02-21 15:42:47Z

3

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();
   }
}

edited Feb 21, 2013 at 15:42

answered Feb 21, 2013 at 15:37

शेखर

17.6k14 gold badges64 silver badges123 bronze badges

2 Comments

Developer63 Over a year ago

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);

user441521 Over a year ago

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.

Collectives™ on Stack Overflow

SqlDataReader and SqlCommand

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related