0

I have this code in C# that delete rows from some tables. Does sqlconnection have a default transaction? What happens If one of these queries returns an error? Do I need to declare an explicitly transaction?

using (SqlConnection sqlCOn = new SqlConnection(ler()))
            {
                var query = $"delete from aux_evento where id_pessoa={id}; " +
                                $"delete from aux_lista where id_pessoa = {id};" +
                                $" delete from convite where Id = {id};" +
                                $" delete from contacto where Id = {id};" +
                                $" delete from ficha where id_pessoa = {id};"
                sqlCOn.Open();
                SqlCommand sqlCmd = new SqlCommand(query, sqlCOn);
                sqlCmd.ExecuteNonQuery();

            }

Thank you

  • It depends on which database you are using. SQL Server by default uses *implicit transactions*, each statement is treated as a transaction. If you require multiple statements to be atomic, you need to explicitely specify a transaction. – Stu Aug 18 '21 at 21:40
  • https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection – mjwills Aug 18 '21 at 23:27
  • Best way to make this atomic is actually not to use `BeginTransaction`, but to use `SET XACT_ABORT ON; BEGIN TRAN; ... stuff here... COMMIT TRAN;` Side note: You are injecting data into your query. Do NOT do this, instead parameterize your queries properly. – Charlieface Aug 18 '21 at 23:27
  • _Why_ is that a better way @Charlieface? – mjwills Aug 18 '21 at 23:29
  • @mjwills Because `transaction.Commit` requires extra network traffic and roudtrip; and in case of a broken connection, can leave the transaction hanging until the server realizes. Whereas doing it all in one batch removes an extra roundtrip, and that combined with `XACT_ABORT ON` also means that there is no possibility of a hanging transaction, as soon as the batch finishes it is committed or rolled back. – Charlieface Aug 18 '21 at 23:34
  • @Charlieface Are there additionally risks related to possible typos in the SQL etc? I ask only since I haven't seen this suggested before - so wondering why no-one else is doing it. – mjwills Aug 18 '21 at 23:36
  • @mjwills Not really: I normally code the whole thing in SSMS anyway in order to check execution plan, if not you could anyway have typos. It's pretty simple boilerplate TBH. There are lots of things that *everyone* isn't doing, such as parameterization, good isolation levels, good DB design, indexing etc. – Charlieface Aug 18 '21 at 23:37

0 Answers0