Is it necesserly to use sqltransaction with using keyword in C#

Question

I have following with two using block:

using (SqlConnection connection = new SqlConnection(_connectionString))
{
    String query = "query...";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        command.Parameters.AddWithValue("@param", "paramValue");

        connection.Open();
        int result = command.ExecuteNonQuery();

        // Check Error
        if (result < 0)
            Console.WriteLine("Error inserting data into Database!");
    }
}

Is it enought to make this query safety or is it require to declare a transaction like in this post?

Answer 1

You are asking for safety, and that could be related from a resources viewpoint or from a database data viewpoint.

The using statement

The using statement is syntactic sugar for a try-catch-finally statement where unmanaged resources are freed.

Do note that this has nothing to do with your database code, it only handles the IDisposable objects, in your code the SQLConnection and SQLCommand.

You could choose to not write the using statement, but is so useful and I would advice you using the using statement... And not only to database connections but for other unmanaged resources as well.

The SQLTransaction

A database transaction would be needed if there were more than one operation and you cared to make sure they behave in an atomic way (either all complete or nothing changes).

You can have database transactions directly in your SQL code or declared in your .net code using a SQLTransaction:

In your SQL code:

BEGIN TRANS myTrans
Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description');
Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description');
COMMIT TRANS myTrans

or declared in .NET:

try
{
    command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
    command.ExecuteNonQuery();
    command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
    command.ExecuteNonQuery();

    // Attempt to commit the transaction.
    transaction.Commit();
    Console.WriteLine("Both records are written to database.");
}

Answer 2

You don't need transactions for single operations because a single operation creates an implicit transaction. Transactions make sense when you are dealing with multiple operations that should be treated "atomic", i.e. if one fails you can roll back all changes (there are some more uses for transactions, but I try to keep it simple)