SqlDataReader and SqlCommand
Solution 1
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.
Solution 2
Could be as simple as amending your connection string:
add MultipleActiveResultSets=True to connection string
Solution 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();
}
}
Stuart
BY DAY: Software Engineer - .Net C# (Web forms and MVC), HTML5, CSS3, JQuery, Angular, SQL. BY NIGHT: Husband, Father and Cook! FOR FUN: My Daughter, My Wife, Gardening, Cooking, DIY. General hands on type stuff!
Updated on February 22, 2020Comments
-
Stuart about 4 years
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 throughreader.Read()
? -
Stuart about 11 yearsSpot-On!! Thank you very much!! I love this place!
-
Stuart about 11 yearsas 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!
-
Developer63 over 8 yearsThis 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 7 yearsThe 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.
-
fuzzy_logic over 5 yearsLove it when it's this simple!