SqlCommand Best Practices 
January 8th, 2009, 07:59 PM
| | Familiar Sight | | Join Date: Jul 2007
Posts: 180
| |
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);
-
}
-
}
-
Last edited by Frinavale; January 9th, 2009 at 02:35 PM.
Reason: Moved to C# from .NET
|
January 8th, 2009, 08:11 PM
|  | Moderator | | Join Date: Mar 2007 Location: Canada
Posts: 757
| | | re: SqlCommand Best Practices
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)
Last edited by balabaster; January 8th, 2009 at 08:12 PM.
Reason: correct typo
|
January 8th, 2009, 08:30 PM
| | Familiar Sight | | Join Date: Jul 2007
Posts: 180
| | | re: SqlCommand Best Practices
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.
|
January 8th, 2009, 08:41 PM
| | Moderator | | Join Date: Mar 2007 Location: Canada
Posts: 757
| | | re: SqlCommand Best Practices
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.
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 225,702 network members.
|
