SQL Encapsulation Functions

[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.CloseConnectio n);
}

Please excuse the poor wrapping....

[Jon Skeet [C# MVP]]

<mm******@gmail.com> wrote:

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?

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 - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Matthew Morvant]

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...

<mm******@gmail.com> wrote:

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?

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.

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

[Jon Skeet [C# MVP]]

Matthew Morvant <ne**@bigfatreddog.com> wrote:

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.
Ok, what should I use for object binding if DataReaders shouldn't be used?

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

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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too