CODEWITHSUNDEEP
c#datareader
Marcus3329
Marcus3329

Reputation: 85

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

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

Upvotes: 8

Views: 26712

Answers (4)

Ravi Gadag
Ravi Gadag

Reputation: 15851

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

Upvotes: 6

Marc Gravell
Marc Gravell

Reputation: 1062484

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

Upvotes: 5

Icarus
Icarus

Reputation: 63956

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.

Upvotes: 9

RQDQ
RQDQ

Reputation: 15569

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

Two possible solutions come to mind:

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

Upvotes: 1

Related Questions