3
\$\begingroup\$

I was wondering if there is a better way to code this:

using (SqlConnection connection = new SqlConnection(ConnectionString)) {
   using (SqlCommand cmdDelete = new SqlCommand("DELETE FROM table WHERE 
         (TestNumber = '" + testNumber + "')", connection)) {
      try
      {
         connection.Open();
         int recordExists = deletePolicyFromTdm.ExecuteNonQuery();
         connection.Close();

         if(recordExists == 1)
            result.Append("Record has been deleted");
         else
           result.Append("Record does not exist");
      ...

Couple of assumptions:

  • testNumber is unique and no duplicate records and was input tested, at least according to bus rules. So I realize that if more than one record is present the if statement won't work properly
  • Table has a PK but testNumber isn't it
  • Only one roundtrip to db
  • Can't change how DB is set up
  • SQL command will eventually be replaced with a sproc and parameter

The idea is that if user clicks a button and gets either a message that no record exists or that the records were deleted.

\$\endgroup\$

closed as off-topic by ChrisWue, syb0rg, Heslacher, Kid Diamond, svick Dec 23 '14 at 23:31

This question appears to be off-topic. The users who voted to close gave this specific reason:

If this question can be reworded to fit the rules in the help center, please edit the question.

4
\$\begingroup\$

A few quick remarks:

  1. In your second using statement, you declare a SqlCommand variable named cmdDelete. In the body of that using statement, however, you call ExecuteNonQuery on a variable named deletePolicyFromTdm. I don't see a declaration for deletePolicyFromTdm in your code, so I wonder if it should be cmdDelete instead.

  2. I'm not sure where testNumber is coming from, but your approach potentially opens the door for SQL injection. I recommend defining it as a parameterized query (e.g. DELETE FROM table WHERE TestNumber = @TestNumber); it's a very small amount of work and will be one less thing to do when you switch to a sproc later.

  3. Your brace style is not consistent. The braces following using are on the same line; the brace following try are on a separate line.

\$\endgroup\$
  • \$\begingroup\$ I appreciate taking then time to reply. I neglected to change the deletePolicyFromTdm as I didn't want to show actual name, so my bad. The testNumber is being passed as aparameter from a calling method and is passed through validation. The braces, while are inconsistent, don't really take away from effectivness and will a make the correction. My purpose for asking for a review was in regards to the recordsExists and how I check for records existance without making a separate data call to such as SELECT COUNT(*)... \$\endgroup\$ – Risho Dec 23 '14 at 17:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.