how to close sqldatareader when error occur

[mimi]

Hi

Please help me out, I can't find a way to close a sqldatareader when error
occur at statement cmd.ExecuteReader(). I can't close it in catch because
it is local in try scope and I can't declare it outside try scope either
since we have to call cmd.executeReader to create sqldatareader

public string GetLogs(int logID)

{

string notes = String.Empty;

// cannot declare like SqlDataReader dr;

try

{

SqlCommand cmd = new SqlCommand("GetLogs", oConn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@ID",SqlDbType.Int).Value = logID;

SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())

{

notes = dr["Notes"].ToString();

}

dr.Close();

}

catch(SqlException ex)

{

//can't call dr.Close() because it's not global

}

return notes;

}

 

[Guest]

You can declare this:

SqlDataReader dr = null;

you can let the framework handle that though:

using(SqlConnection oConn = new SqlConnection("My Conn String"))
using(SqlCommand cmd = new SqlCommand("GetLogs", oConn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = logID;
using(SqlDataReader dr = cmd.ExecuteReader())
{
if(dr.Read())
notes = dr["Notes"].ToString();
}
}

 

[Kan Coon]

Yes you can declare it out side the try block:

Dim dr as datareader

Try
dr = cmd.ExecuteReader();
Catch ex as SQlException

Finally
dr.close
End try

You should also be closing that datareader in the finally section of your
exception handler.

 

[Kevin Spencer]

Hi mimi,

You certainly CAN declare it outside the try scope. All you are declaring is
a variable, not a SqlDataReader. The variable is like a box to put the
SqlDataReader IN. You should also set it to null, and check for null when
you attempt to close it. And you should close it in the finally block, which
ALWAYS executes. Example:

public string GetLogs(int logID)

{

string notes = String.Empty;

SqlDataReader dr = null;

try

{

SqlCommand cmd = new SqlCommand("GetLogs", oConn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@ID",SqlDbType.Int).Value = logID;

dr = cmd.ExecuteReader();

if (dr.Read())

{

notes = dr["Notes"].ToString();

}

dr.Close();

}

catch(SqlException ex)

{
...
}
finally
{
if (dr != null)
dr.Close()
return notes;
}


}

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
The sun never sets on
the Kingdom of Heaven

 

[mimi]

thanks. It works for me with setting SqlDataReader to null

public string GetLogs(int logID)
{
string notes = String.Empty;
SqlDataReader dr = null;

try
{
SqlCommand cmd = new SqlCommand("GetLogs", oConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID",SqlDbType.Int).Value = logID;
dr = cmd.ExecuteReader();

if (dr.Read())
{
notes = dr["Notes"].ToString();
}
}
catch(SqlException ex)
{
...
}
finally
{
if (dr != null)
dr.Close()
}
return notes;

}

Hi mimi,

You certainly CAN declare it outside the try scope. All you are declaring is
a variable, not a SqlDataReader. The variable is like a box to put the
SqlDataReader IN. You should also set it to null, and check for null when
you attempt to close it. And you should close it in the finally block, which
ALWAYS executes. Example:

public string GetLogs(int logID)

{

string notes = String.Empty;

SqlDataReader dr = null;

try

{

SqlCommand cmd = new SqlCommand("GetLogs", oConn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@ID",SqlDbType.Int).Value = logID;

dr = cmd.ExecuteReader();

if (dr.Read())

{

notes = dr["Notes"].ToString();

}

dr.Close();

}

catch(SqlException ex)

{
...
}
finally
{
if (dr != null)
dr.Close()
return notes;
}


}

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
The sun never sets on
the Kingdom of Heaven

Hi

Please help me out, I can't find a way to close a sqldatareader when error
occur at statement cmd.ExecuteReader(). I can't close it in catch because
it is local in try scope and I can't declare it outside try scope either
since we have to call cmd.executeReader to create sqldatareader

public string GetLogs(int logID)

{

string notes = String.Empty;

// cannot declare like SqlDataReader dr;

try

{

SqlCommand cmd = new SqlCommand("GetLogs", oConn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@ID",SqlDbType.Int).Value = logID;

SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())

{

notes = dr["Notes"].ToString();

}

dr.Close();

}

catch(SqlException ex)

{

//can't call dr.Close() because it's not global

}

return notes;

}