By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

SqlCommand Best Practices

100+
P: 233
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. private void CreateOpportunity(string PODoc)
  3. {
  4. string Conn = "Data Source=server11;Initial Catalog=Main;User ID=uid;Password=pw";
  5. string Account = "Name";
  6. OpportunityID = NewID("opportunity");
  7. AccountID = "A6UJ9A002N65";
  8. using (SqlConnection slxConn = new SqlConnection(Conn))
  9. {
  10. 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)";
  11. SqlCommand cmd = new SqlCommand(sql, slxConn);
  12. cmd.Parameters.AddWithValue("@opportunityid", OpportunityID);
  13. cmd.Parameters.AddWithValue("@accountid", AccountID);
  14. cmd.Parameters.AddWithValue("@description", PONum);
  15. cmd.Parameters.AddWithValue("@closed", "F");
  16. cmd.Parameters.AddWithValue("@status", "In-Process");
  17. cmd.Parameters.AddWithValue("@seccodeid", "SYST00000001");
  18. cmd.Parameters.AddWithValue("@createuser", "Admin");
  19. cmd.Parameters.AddWithValue("@createdate", DateTime.Now);
  20. cmd.Parameters.AddWithValue("@modifyuser", "Admin");
  21. cmd.Parameters.AddWithValue("@modifydate", DateTime.Now);
  22. try
  23. {
  24. slxConn.Open();
  25. cmd.ExecuteNonQuery();
  26. }
  27. catch (SqlException ex)
  28. {
  29. string str;
  30. str = "Source:" + ex.Source;
  31. str += "\n" + "Message:" + ex.Message;
  32. }
  33. finally
  34. {
  35. if (slxConn.State == ConnectionState.Open)
  36. {
  37. slxConn.Close();
  38. }
  39. //method to send error message
  40. //SendError(str);
  41. }
  42. }
  43.  
Jan 8 '09 #1
Share this Question
Share on Google+
3 Replies


balabaster
Expert 100+
P: 797
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)
Jan 8 '09 #2

100+
P: 233
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.
Jan 8 '09 #3

balabaster
Expert 100+
P: 797
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.
Jan 8 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.