CODEWITHSUNDEEP
c#asp.netconnectionsqldatareadersqlcommand
Stuart
Stuart

Reputation: 1574

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

Upvotes: 2

Views: 23617

Answers (3)

Phil
Phil

Reputation: 43021

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.

Upvotes: 3

शेखर
शेखर

Reputation: 17604

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

Upvotes: 3

Grant Thomas
Grant Thomas

Reputation: 45058

Could be as simple as amending your connection string:

add MultipleActiveResultSets=True to connection string

Upvotes: 6

Related Questions