SqlCommand Best Practices

[mcfly1204]

I am tryign to figure out what the best practice for exception handling is in regards to SqlCommand. I am mostly concerned with data exceptions when the command is executed. Here is what I have started on:

 
private void CreateOpportunity(string PODoc)
{
string Conn = "Data Source=server11;Initial Catalog=Main;User ID=uid;Password=pw";
string Account = "Name";
OpportunityID = NewID("opportunity");
AccountID = "A6UJ9A002N65";
using (SqlConnection slxConn = new SqlConnection(Conn))
{
string sql = "INSERT INTO sysdba.OPPORTUNITY (OPPORTUNITYID,ACCOUNTID,DESCRIPTION,CLOSED,STATUS,SECCODEID,CREATEUSER,CREATEDATE,MODIFYUSER,MODIFYDATE) VALUES (@opportunityid,@accountid,@description,@closed,@status,@seccodeid,@createuser,@createdate,@modifyuser,@modifydate)";
SqlCommand cmd = new SqlCommand(sql, slxConn);
cmd.Parameters.AddWithValue("@opportunityid", OpportunityID);
cmd.Parameters.AddWithValue("@accountid", AccountID);
cmd.Parameters.AddWithValue("@description", PONum);
cmd.Parameters.AddWithValue("@closed", "F");
cmd.Parameters.AddWithValue("@status", "In-Process");
cmd.Parameters.AddWithValue("@seccodeid", "SYST00000001");
cmd.Parameters.AddWithValue("@createuser", "Admin");
cmd.Parameters.AddWithValue("@createdate", DateTime.Now);
cmd.Parameters.AddWithValue("@modifyuser", "Admin");
cmd.Parameters.AddWithValue("@modifydate", DateTime.Now);
try
{
slxConn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
string str;
str = "Source:" + ex.Source;
str += "\n" + "Message:" + ex.Message;
}
finally
{
if (slxConn.State == ConnectionState.Open)
{
slxConn.Close();
}
//method to send error message
//SendError(str);
}
}
 

[balabaster]

As a rule, exceptions are a relatively expensive operation so where they can be handled ahead of time, you should. Put as little as you can get away with inside a try {} catch {}.

Consequently you should predict any potential data errors creating your parameters and avoid having to handle exceptions. Usually the only exceptions you'll get are data type or null - so a simple check to make sure that the data is there and the right format will perform a lot better than handling exceptions on them.

When creating commands, the only thing I put in the try {} catch {} is the execution. If I throw an exception outside that, then it should be caught by my unit tests. There should be no excuse for invalid data to make it as far as parameter creation, in either direction (from the db -> GUI or from GUI -> db)

[mcfly1204]

So it would make more sense to check the length of the parameters, and their content prior to executing the command? Though I was not aware of the expense of exceptions, it now makes sense. Thanks for the prompt reply as always.

[balabaster]

I usually check the value as I'm assigning it to my parameters to make sure that no exception will occur. If all of my parameters are created successfully then my command will have the correct amount of parameters which I check prior to execution of my command.

And in fact, if all your parameters haven't been created properly you can avoid having to even open the database connection.