Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

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

share|improve this question
 
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
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
 
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
add comment
up vote 4 down vote

Could be as simple as amending your connection string:

add MultipleActiveResultSets=True to connection string

share|improve this answer
 
Spot-On!! Thank you very much!! I love this place! –  Stuart Feb 21 '13 at 15:40
 
@Stuart Very welcome. And me too. (: –  Grant Thomas Feb 21 '13 at 15:43
add comment
up vote 0 down vote

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();
   }
}
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.