There is already an open DataReader associated with this Command which must be closed first

Question

This is the code I have.

/// <summary>
/// Method calls stored procedure and fills DataSet of contacts associated with Lead
/// </summary>
/// <param name="leadID">The ID associated with a Lead</param>
/// <returns>contacts list as DataSet</returns>
public static DataSet GetContactResultSetByLead(int leadID)
{
    SqlCommand Sqlmd = new SqlCommand("dbo.proc_contact");
    Sqlmd.CommandType = CommandType.StoredProcedure;
    Sqlmd.Parameters.Add("@LeadInfoID", SqlDbType.Int).Value = leadID;

    Sqlmd.Connection = m_ConStr;
    SqlDataAdapter da = new SqlDataAdapter(Sqlmd);

    DataSet data = new DataSet();
    try
    {
        da.Fill(data);
    }

    finally
    {
        m_ConStr.Close();
    }

    return data;
}

change this line Sqlmd.Parameters.Add("@LeadInfoID", SqlDbType.Int).Value = leadID; to Sqlmd.Parameters.AddWithValue("@LeadInfoID", leadID); If you want to keep the Global Connect then Check for the State of that Connection if open then close it prior to using it. REFACTOR YOUR CODE AND USE MEANINGFUL VARIABLE NAMES....! Wrap your Connection around a using() {} — MethodMan, Jan 26 '12 at 18:43
Sqlmd will be executed during the da.Fill call. m_ConStr appears to be a poorly named variable - it appears to refer to the connection itself, not the connection string. — RQDQ, Jan 26 '12 at 18:45

score 9 · Accepted Answer · answered Jan 26 '12 at 18:44

9

Your problem is that you apparently have one instance of m_ConStr; if the method is called concurrently only one of them will be able to use the connection and the other one will fail with the exception you are receiving.

Use this pattern instead:

using (SqlConnection conn  = new SqlConnection())
{
    conn.Open();
    Sqlmd.Connection = conn;
    SqlDataAdapter da = new SqlDataAdapter(Sqlmd);
   //...etc
}

In other words, don't define connection as a global variable to the class.

answered Jan 26 '12 at 18:44

Icarus

63,293
14
100
115

"don't define connection as a global variable": but then how to avoid to create and open a connection any time I have to execute a sp. In my case I've to execute a lot of sp, and without instanciate one the connection, the alternative is to create and open every time. Any hints? – ff8mania Feb 06 '14 at 11:09

Ravi Gadag · Answer 2 · 2012-01-26T19:05:09.090

6

i suggest You can using block to ensure proper disposing of sqlconnection.

using (SqlConnection conn  = new SqlConnection())
{
    conn.Open();
    Sqlmd.Connection = conn;
    SqlDataAdapter da = new SqlDataAdapter(Sqlmd);
    Dataset ds = new Datasest
    da.Fill(ds)
}

the other way is you can also set MARS property in your connection, if you needed.

SqlConnection m_ConStr;= new SqlConnection("Server= serverName;Database=yourDatabase;
        MultipleActiveResultSets=true;");

edited Jan 26 '12 at 19:05

answered Jan 26 '12 at 18:49

Ravi Gadag

15,735
5
57
83

2

The connection should be declared locally (all sorts of side effects will happen if you use a shared instance). Also, why wouldn't you just employ a using statement to manage the lifetime of the connection? – RQDQ Jan 26 '12 at 18:53

score 5 · Answer 3 · answered Jan 26 '12 at 18:56

All of your short-lived IDisposable objects there are lacking a "using". By extension, then, it is possible that you've done something like:

var reader = anotherCommand.ExecuteReader();
...

But this does not dispose / close the reader. If this is the case, add "using":

using(var reader = anotherCommand.ExecuteReader()) {
    ...
}

Which closes the reader, regardless of how we exit. Commands, connections, readers and transactions are all disposable and should all usually use "using".

score 1 · Answer 4 · answered Jan 26 '12 at 18:44

1

You're trying to run multiple actice result sets (aka MARS).

Two possible solutions come to mind:

Open open a new connection in your GetContractResultSetByLead
Enable MARS on your database server (described in the above link).

answered Jan 26 '12 at 18:44

RQDQ

15,461
2
32
59

You can also try to add this in the web.config into the connection string: connectionString="MultipleActiveResultSets=True;user=..." – Nestor Apr 12 '13 at 01:58

There is already an open DataReader associated with this Command which must be closed first

4 Answers4