c# closing sqlconnection and sqldatareader or not?
I have this piece of code:
SqlConnection conn;
string strconString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLCONN"].ToString();
conn = new SqlConnection(strconString);
string cmdstr = "select status from racpw where vtgid = " + vtgid;
SqlCommand cmdselect = new SqlCommand(cmdstr, conn);
conn.Open();
SqlDataReader dtr = cmdselect.ExecuteReader();
if (dtr.Read())
{
return;
}
else
{
...
}
dtr.Close();
conn.Close();
Now my question is. If return, does my connection and dtr get closed automatically or should I use a bool variable and perform a return after my connections get closed?
You have to close connection before return. The best way to do it is USING block, because SqlConnection implements IDisposable interface. In that case you don't have to keep in mind that you have to close connection even if exception was thrown.
See the example below:
using (var conn = new SqlConnection(strconString))
{
string cmdstr =
"select status from racpw where vtgid = " + vtgid;
using (var cmdselect = new SqlCommand(cmdstr, conn))
{
conn.Open();
using(var dtr = cmdselect.ExecuteReader())
{
if (dtr.Read())
{
return;
}
else
{
...
}
}
}
}
Here's how to improve your code:
var connectionString = System.Configuration.ConfigurationManager
.ConnectionStrings["SQLCONN"].ToString();
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText =
"select status from racpw where vtgid = @vtgid";
cmd.Parameters.AddWithValue("@vtgid", vtgid);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
...
}
}
}
}
This way you don't need to worry about closing, disposing, ...
Your best bet it to use a using block instead. This will enforce a call to Dispose even if you return in the middle of the method:
string strconString = System.Configuration.ConfigurationManager
.ConnectionStrings["SQLCONN"].ToString();
using (SqlConnection conn = new SqlConnection(strconString))
{
string cmdstr =
"select status from racpw where vtgid = " + vtgid;
using(SqlCommand cmdselect = new SqlCommand(cmdstr, conn))
{
conn.Open();
using( SqlDataReader dtr = cmdselect.ExecuteReader())
{
if (dtr.Read())
{
return;
}
else
{
...
}
}
}
}
This works because using is in fact a try/finally block, and even if you return, the finally block is executed and runs Dispose on your SqlCommand and SqlDataReader .