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?

"Is it enought to make this query safety" - well, what does the query *do*? if it is a simple `insert` of a single row, then it will already have some atomicity - although fine-grained control over, for example, isolation levels requires more work (if required); if it has multiple steps, it *may* require a transaction, *depending on your environment and requirements and the query*, none of which we know — Marc Gravell, Jul 14 '21 at 11:15
It *might* not be a problem for your query, we don't have the query though, but you should be using `Parameters.Add`. [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) [It's Evil](https://www.dbdelta.com/addwithvalue-is-evil/). — Thom A, Jul 14 '21 at 11:17
I think you are conflating safety (will the requisite objects you are creating be disposed off correctly) and safety with regards to atomicity at the db level. You would have to show your query, for us to know if you should be using a transaction. — Stuart, Jul 14 '21 at 11:17

Cleptus · Accepted Answer · 2021-07-14T11:51:44.597

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.");
}

score 2 · Answer 2 · answered Jul 14 '21 at 11:17

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)

answered Jul 14 '21 at 11:17

taronyu

86
6

... and even if you do make several queries atomic, you don't need to use `using`. You could also use "try/catch/finally". – Fildor Jul 14 '21 at 11:20
1

@Fildor or both; transactions are arguably a case where a `catch` for explicit abort is useful, but it should still be disposed, which IMO is best done with `using` – Marc Gravell Jul 14 '21 at 11:22
@MarcGravell Sure. Fair enough. All I wanted to say was "you do not absolutely always _have to_". It sure makes sense to do so, though. – Fildor Jul 14 '21 at 11:23
1

I think this answer lacks addressing that the `using` statement does nothing to the database data, it only makes sure the connections are correctly handled... I have added an asnwer of my own. – Cleptus Jul 14 '21 at 11:48

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

2 Answers2

The using statement

The SQLTransaction