Is this way sufficient to execute DataSet?

Question

I query one of my table using Stored Procedure

Here is an example of method I have. I would like to make sure it's the most sufficient way and in case of 10000 of requests it will not cause overheat and close connection properly.

public DataSet GetDetailsByUserId(int userid)
{
    var arParms = new SqlParameter[1];
    arParms[0] = new SqlParameter("@UserID", SqlDbType.Int) { Value = userid };
    var dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["ConnectionString"], CommandType.StoredProcedure, "StoredProcedureName", arParms);
                return dt.Tables.Count == 0 ? null : dt;

}

Answer 1

Your code is fine. Connection pooling will be utilized. This will prevent a brand-spankin' new connection from having to be created and then disposed of for each request.

However, I would highly recommend renaming var dt = to var DS because ExecuteDataSet returns a DataSet, rather than a table.

var DS = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["ConnectionString"], CommandType.StoredProcedure, "StoredProcedureName", arParms);

return DS.Tables.Count == 0 ? null : dt; // Dataset owns 1 or more tables...