Question about SQL connection and statement in C#

Question

I currently have my SQL statements like this in my C# program.

        SqlConnection connection = DataBase.GetConnection();
        string deleteStatement = "DELETE FROM People WHERE ID = @ID";
        SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
        deleteCommand.Parameters.AddWithValue("@ID", Id);

        try
        {
            connection.Open();
            deleteCommand.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {

            throw ex;
        }
        finally
        {
            connection.Close();
        }

But visual studio 2019 is suggesting to use a using statement instead. Is there any benefit to using ether or?

Why include the catch block here? What do you think it is providing you? — mason, Oct 15 '19 at 00:50
FYI: [Is there a difference between “throw” and “throw ex”?](https://stackoverflow.com/questions/730250/is-there-a-difference-between-throw-and-throw-ex) — ProgrammingLlama, Oct 15 '19 at 00:57
I would think it providing an error to be thrown if the SQL server wasn't live any more. Because the SQL server is not running on the same computer as the application. The application connects to a remote SQL server.. — Maverick, Oct 15 '19 at 01:07
Read the link @John provided. `throw ex` is far worse than not catching the exception at all. — stuartd, Oct 15 '19 at 01:22
Using block destroys the object automatically after scope goes out of iteration. For example if you use `using (SqlConnection connection = DataBase.GetConnection())` then there is no need to write `connection.Close();` as using block automatically destroy the connection in end of scope even after any exceptions occurred. — Prakash Mhasavekar, Oct 15 '19 at 05:06
Now is it good practice to do your using statement like you just did @Prakash Mhasavekar or use a variable like in Fabio post? — Maverick, Oct 15 '19 at 13:26
It's All upto you @Maverick. Both are valid. In many tutorials I have seen above method... Currently I'm also using this one.... Only difference is creating direct instance of SQL connection and storing it inside variable — Prakash Mhasavekar, Oct 16 '19 at 12:56

score 3 · Accepted Answer · answered Oct 15 '19 at 01:04

3

In addition to the accepted answer:

You could/should dispose SqlCommand as well and using will handle it.

using (var connection = DataBase.GetConnection())
using (var command = connection.CreateCommand())
{
    command.CommandText = "DELETE FROM People WHERE ID = @ID";
    var parameter = new SqlParameter
    {
        ParameterName = "@ID",
        SqlDbType = SqlDbType.Int,
        Value = Id
    };

    command.Parameters.Add(parameter);

    connection.Open();
    command.ExecuteNonQuery();
}

answered Oct 15 '19 at 01:04

Fabio

31,528
4
33
72

So what is the benefit to creating your parameter the way you wrote it to mine? – Maverick Oct 15 '19 at 01:10
AddWithValue is considered to be bad practise - https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ - if you don’t specify what data type to use then it will be inferred, with potentially negative consequences – stuartd Oct 15 '19 at 01:19

score 1 · Answer 2 · answered Oct 15 '19 at 00:56

1

using will automatically dispose of the connection (which also closes the connection).

answered Oct 15 '19 at 00:56

tymtam

31,798
8
86
126

Question about SQL connection and statement in C#

2 Answers2