SqlDataReader and SqlCommand

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() ?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Could be as simple as amending your connection string:

add MultipleActiveResultSets=True to connection string

Method 2

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.

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0