Pass DataReader from the method to the .cs page - use CommandBehavior.CloseConnection but the connection does not close

advertisements

I use the following method in a class - which I call from .cs page filling a datareader:

public static SqlDataReader getinfo(string username, string url)
{
    //Initialise connection, string stored in Web.config
    SqlConnection conNs = new SqlConnection(ConfigurationManager.ConnectionStrings["conNs"].ConnectionString);
    SqlCommand cmdNs = new SqlCommand("usp_getinfo", conNs);
    cmdNs.CommandType = CommandType.StoredProcedure;

    cmdNs.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar, 50));
    cmdNs.Parameters["@username"].Value = username;
    cmdNs.Parameters.Add(new SqlParameter("@url", SqlDbType.VarChar, 50));
    cmdNs.Parameters["@url"].Value = url;

    //Execute Command
    conNs.Open();
    SqlDataReader dr = cmdNs.ExecuteReader(CommandBehavior.CloseConnection);

    //Return data
    return dr;
}

Notice 'commendbehavior.closeconnection'.

Then in a .cs page I call it like this:

SqlDataReader drInfo = dataAccess.getinfo(username, url);
//do some stuff
drInfo.Close();

That should close the connection too no?

I am having a problem with the apppool filling up with open connections. Is there a problem with my technique?

Speed is my priority here.

Thanks


I think you need to return a datatable instead of SQLDataReader and you need to close the connection right after you finish reading from the data reader.