Parameterized Queries

BBatts

Is there any benefit to adding BEGIN TRANSACTION; and COMMIT; to the beginning and end of a parameterized query in C#. Also, is there any real advantage to stored procedures vs parameterized queries? For Example:

using (SqlConnection dbConnection = new SqlConnection(GSettings.SQLConnectionString))
{
dbConnection.Open();

            for (int i = 0; i < DBUpdateList.Count; i++)
            {
                string selectSQL = "BEGIN TRANSACTION; UPDATE TABLE SET Dog = [@Dog](/Members/dog); COMMIT;";

                using (SqlCommand dbCommand = new SqlCommand(selectSQL, dbConnection))
                {
                    dbCommand.Parameters.AddWithValue("[@Dog](/Members/dog)", "Poodle");
                    dbCommand.ExecuteNonQuery();
                }
            }
        }

Simon_Whale

Personally I would only use BEGIN TRANSACTION; END in a stored procedure. If you want to use transactions with your ADO.NET query I would read this MSDN transactionscope[^]

Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON

PIEBALDconsult

BBatts wrote:

Is there any benefit

No.

BBatts wrote:

stored procedures vs parameterized queries

Those aren't mutually exclusive; even if you use stored procedures, you should still use a parameterized command to provide values (as necessary). I also recommend that you not keep reinstantiating the Command, but reuse one:

instantiate command
add parameter(s)
(begin transaction if desired)
loop:
set Value(s)
Execute
(commit transaction)

Lost User

BBatts wrote:

Is there any benefit to adding BEGIN TRANSACTION; and COMMIT; to the beginning and end of a parameterized query in C#

I'd recommend getting a SQL manual, and that's not meant as a snarky remark; programming often involves databases, and you'll need to dig into transactions sooner or later. Life also becomes easier when you can look up what each statement does :)

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

BBatts

Got one you would recommended?