Reputation: 1049
I am getting the error: "ExecuteReader requires an open connection" and I know the fix is to add a connection.Open() / connection.Close(). My question pertaining to this error is more for me to understand exactly what happen under the hood.
I am currently using the "USING" statement which I expect it to open and close/dispose the connection for me. So I guess I don't understand why it didn't work as expected and I needed to explicitly code the connection.Open() / connection.Close() myself to fix the issue. I did some research and found people experienced similar issue because they were using static connection. In my case, I am creating a new instance of the connection... hence, it bothers me and hoping to get to the bottom of this instead of just fix it and move on. Thank you in advance.
Here is the code:
try
{
using (SqlConnection connection = new SqlConnection(myConnStr))
using (SqlCommand command = new SqlCommand("mySPname", connection))
{
command.CommandType = CommandType.StoredProcedure;
//add some parameters
SqlParameter retParam = command.Parameters.Add("@RetVal", SqlDbType.VarChar);
retParam.Direction = ParameterDirection.ReturnValue;
/////////////////////////////////////////////////
// fix - add this line of code: connection.Open();
/////////////////////////////////////////////////
using(SqlDataReader dr = command.ExecuteReader())
{
int success = (int)retParam.Value;
// manually close the connection here if manually open it. Code: connection.Close();
return Convert.ToBoolean(success);
}
}
}
catch (Exception ex)
{
throw;
}
Upvotes: 1
Views: 245
Reputation: 1780
For the SqlConnection, you have to explicitly open it inside the using block, you just don't need to close it though.
I also notice that you are missing a set of brackets {} around the using SqlConnection. Maybe that's the issue? It should be like this:
try
{
using (SqlConnection connection = new SqlConnection(myConnStr))
{
connection.Open();
using (SqlCommand command = new SqlCommand("InsertProcessedPnLFile", connection))
{
command.CommandType = CommandType.StoredProcedure;
//add some parameters
SqlParameter retParam = command.Parameters.Add("@RetVal", SqlDbType.VarChar);
retParam.Direction = ParameterDirection.ReturnValue;
/////////////////////////////////////////////////
// fix - add this line of code: connection.Open();
/////////////////////////////////////////////////
using(SqlDataReader dr = command.ExecuteReader())
{
int success = (int)retParam.Value;
// manually close the connection here if manually open it. Code: connection.Close();
return Convert.ToBoolean(success);
}
}
}
}
Upvotes: 0
Reputation: 2210
Using does not open any connections, it only disposes of any allocated memory after calling End Using.
Upvotes: 2