SQL Encapsulation Functions

  • Thread starter Thread starter mmorvant
  • Start date Start date
M

mmorvant

I have made some functions to help with some of the more routine things
I do. I just wanted to see if they were "safe" for a production
setting?

public static System.Data.SqlClient.SqlConnection DbConnect()
{
System.Data.SqlClient.SqlConnection tConn = new
SqlConnection(strConn);
tConn.Open();
return tConn;
}

public static void ExecuteNonQuery(string strQuery)
{
SqlConnection tConn = DbConnect();
SqlCommand cmd = new SqlCommand(strQuery,tConn);
cmd.ExecuteNonQuery();
cmd.Dispose();
tConn.Close();
tConn.Dispose();
}

public static string ExecuteScalar(string strQuery)
{
string tString = "";
SqlConnection tConn = DbConnect();
SqlCommand cmd = new SqlCommand(strQuery,tConn);
tString = cmd.ExecuteScalar().ToString();
cmd.Dispose();
tConn.Close();
tConn.Dispose();

return tString;
}

public static SqlDataReader ExecuteReader(string strQuery)
{
SqlConnection drConn = DbConnect();
SqlCommand drCmd = new SqlCommand(strQuery,drConn);
return drCmd.ExecuteReader(CommandBehavior.CloseConnection);
}

Please excuse the poor wrapping....
 
I have made some functions to help with some of the more routine things
I do. I just wanted to see if they were "safe" for a production
setting?
Click to expand...

They're not quite - if an exception is thrown, you'll leak the
connection/command. The using statement makes that easy to avoid
though. For example:

public static void ExecuteNonQuery(string strQuery)
{
using (SqlConnection tConn = DbConnect())
{
using (SqlCommand cmd = new SqlCommand(strQuery,tConn))
{
cmd.ExecuteNonQuery();
}
}
}

Personally I'd get rid of the DataReader one - it makes it harder to
close the connection reliably in code, doesn't actually save much, and
shouldn't be used that often anyway.
 
Jon Skeet said:
They're not quite - if an exception is thrown, you'll leak the
connection/command. The using statement makes that easy to avoid
though. For example:

public static void ExecuteNonQuery(string strQuery)
{
using (SqlConnection tConn = DbConnect())
{
using (SqlCommand cmd = new SqlCommand(strQuery,tConn))
{
cmd.ExecuteNonQuery();
}
}
}

Personally I'd get rid of the DataReader one - it makes it harder to
close the connection reliably in code, doesn't actually save much, and
shouldn't be used that often anyway.
Click to expand...

Ok, what should I use for object binding if DataReaders shouldn't be used?
Also, I have been using DataReaders much like I used ADO recordsets in
classic ASP.

Matthew
 
Matthew Morvant said:
Ok, what should I use for object binding if DataReaders shouldn't be used?
Click to expand...

DataTables, usually.
Also, I have been using DataReaders much like I used ADO recordsets in
classic ASP.
Click to expand...

I haven't used straight ADO much, so can't really comment on it, but
using a disconnected model is generally preferred these days. There are
some people who think DataReaders shouldn't even be exposed - I
disagree with that, as there are some cases where it's handy to be able
to process everything in an "as you go" fashion, but I think it's
relatively rare.
 
Back
Top