C# & SQL Server: executing another query while Data Reader is opened

Question

I am intending to perform a call to ExecuteNonQuery() while the SqlDataReader is opened.

Code:

string commandString = "" //command string

using (SqlDataReader reader = cmd2.ExecuteReader())
{
    while (reader.Read())
    {
        string temp = Convert.ToString(reader["RequestID"]);
        string date;

        using (SqlCommand cmd3 = new SqlCommand(commandString, con))
        {
            date = Convert.ToString(cmd3.ExecuteScalar());
        }
    }
}

I tried executing this but I got the error:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll
There is already an open DataReader associated with this Command which must be closed first.

I have the MultipleActiveResultSets set to True in the connection string. May I know if it is possible to do it?

MultipleActiveResultSets is exactly what you need to run this code. I suggest to recheck your code with the debugger at the moment in which you create the connection used here. — Steve
– Steve, Commented Jun 13, 2021 at 13:35
Why would you want to do this, why not just return all the results with one batch command? — Charlieface
– Charlieface, Commented Jun 13, 2021 at 15:32

David Browne - Microsoft · Accepted Answer · 2021-06-13 14:05:11Z

3

While MARS should work, it's usually not the best choice. Simply load the results of the first query into a DataTable and iterate the rows.

var dt = new DataTable();
using (SqlDataReader reader = cmd2.ExecuteReader())
{
    dt.Load(reader);
}
foreach (DataRow row in dt.Rows)
{
    string temp = Convert.ToString(row["RequestID"]);
    string date;

    SqlCommand cmd3 = new SqlCommand(commandString, con)
    date = Convert.ToString(cmd3.ExecuteScalar());
}

answered Jun 13, 2021 at 14:05

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

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

2 Comments

Steve Over a year ago

I am curious now. Why is not the best choice against a Sql Server database? References?

David Browne - Microsoft Over a year ago

MARS has performance overhead to manage the multiple logical sessions that is typically greater than the cost of buffering the result into client memory.

Sayem · Accepted Answer · 2021-06-13 14:09:46Z

You need to add MultipleActiveResultSets in your connection string, though you already added. Try below code that's executed successfully,

string conStr = "Data Source=DB_SERVER;Initial Catalog=DB_NAME;User Id=userId; Password=password;MultipleActiveResultSets=True";            
            using (SqlConnection con = new SqlConnection(conStr)) {
                try {
                    con.Open();
                    string commandText = @"Select Id from Table1";
                    string commandText1 = @"Select CreatedDate FROM Table2 Where table1_Id = @table1_Id";

                    SqlCommand sqlCommand = new SqlCommand(commandText, con);
                    var dataReader = sqlCommand.ExecuteReader();
                    string date;
                    while (dataReader.Read()) {
                        var vId = dataReader["Id"].ToString();
                        var sqlCommand1 = new SqlCommand(commandText1, con);                        
                        sqlCommand1.Parameters.AddWithValue("@table1_Id", vId);
                        date= sqlCommand1.ExecuteScalar().ToString();

                    }
                    con.Close();
                } catch (Exception ex) {
                    //Handle Exception                    
                }
            }

Collectives™ on Stack Overflow

C# & SQL Server: executing another query while Data Reader is opened

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related