closing and opening DataReader associated with a Command

Question

string connectionString = ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString;
                const string query = "my Select query here";

                List<long> myList = new List<long>();
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    con.Open();
                    using (SqlCommand selectCommand = new SqlCommand(query, con))
                    {
                        selectCommand.CommandType = CommandType.Text;
                        SqlDataReader sqlreader = selectCommand.ExecuteReader();

                        while (sqlreader.Read())
                        {
                           long Id = (long)sqlreader["Id"];
                            List.Add(Convert.ToInt32(sqlreader[0].ToString()));

                            using (SqlCommand insertCommand = new SqlCommand("dbo.SP_Data", con))
                             {

                                 insertCommand.CommandType = CommandType.StoredProcedure;
                                 insertCommand.Parameters.Add("@Id", SqlDbType.BigInt).Value = Id;
                                 insertCommand.Parameters.Add("@StatusId", SqlDbType.BigInt).Value = 1;
                                 insertCommand.Parameters.Add("@ReportDate", SqlDbType.DateTime).Value = DateTime.Now;
                                 insertCommand.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                                 insertCommand.Parameters.Add("@CreatedBy", SqlDbType.UniqueIdentifier).Value = DefaultUser();

                                 insertCommand.ExecuteNonQuery();
                             }
                        }
                    }
                }

I am getting the error "There is already an open DataReader associated with this Command which must be closed first." at the last line [insertCommand.ExecuteNonQuery();

You need to enalbe MARS in your connection string (MARS = Multiple Active Result Sets) http://stackoverflow.com/questions/510899/multipleactiveresultsets-true-or-multiple-connections — Steve, Feb 17 '14 at 13:51
@Steve: thanks, thats all i needed to do. I'll accept your comment as the answer, so post it as the answer. — StackTrace, Feb 17 '14 at 14:09

score 1 · Accepted Answer · answered Feb 17 '14 at 14:16

You need to enalbe MARS in your connection string (MARS = Multiple Active Result Sets)

In short, this particular flag, when enabled in the connection string, allows to use the same connection used by the SqlDataReader also for executing commands. Otherwise, as stated by MSDN the connection is busy serving the SqlDataReader and cannot execute other commands.
Before Sql Server 2005 the developpers were forced to create, open and use another connection. (Still possible if your environment doesn't allow to change the connection string)

More info about MARS could be found on this MSDN article
Examples of connection string that uses MARS

In case the link showing the syntax dies the example is: Server=myServerAddress;Database=myDataBase;Trusted_Connection=True; MultipleActiveResultSets=true; — d219, Nov 07 '19 at 11:44

closing and opening DataReader associated with a Command

1 Answers1