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

Answer 1

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.

Answer 2

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".

Answer 3

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;");