none
ADO.NET connection times out and throws a SQLException and there is not a transaction RRS feed

  • Question

  • If I use ADO.Net to create a connection to a database and execute a SQL statement to delete all the rows from one table and the ADO.NET connection times out and throws a SQLException will the delete roll back on the table or does the delete still go through even though that exception happened?

    Thursday, January 7, 2010 7:20 PM

Answers

  • If command execution does not finish within predefined time and SQL Server throws timeout exception, then yes, all the deletes should be rolled back since single DELETE SQL statement is an atomic operation.
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by asisurpher Monday, January 11, 2010 2:11 PM
    Monday, January 11, 2010 10:57 AM
    Moderator

All replies

  • I assume that you mean a command timeout. If getting a connection timeout the command wouldn't be able to execute.

    If having a table like this:

    create table ToDelete (cid int, ctxt nvarchar(10))
    insert into ToDelete values (1, 'one')
    insert into ToDelete values (2, 'two')
    insert into ToDelete values (3, 'three')

    And executing code like this:

                string sql = "DELETE FROM ToDelete WHERE cid = 1;";
                sql += "DELETE FROM ToDelete WHERE cid = 2";
                sql += "WAITFOR DELAY '00:00:20';";
                sql += "DELETE FROM ToDelete WHERE cid = 3";
    
                using (SqlConnection con = new SqlConnection(cs))
                {
                    try
                    {
                        con.Open();
                        SqlCommand cmd = con.CreateCommand();
                        cmd.CommandTimeout = 10;
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex);
                    }
                }
    It will timeout, rows 1 and 2 are gone, row 3 remains. So no automatic rollback.

    HTH
    //Michael

    This posting is provided "AS IS" with no warranties.
    Friday, January 8, 2010 8:41 AM
  • If command execution does not finish within predefined time and SQL Server throws timeout exception, then yes, all the deletes should be rolled back since single DELETE SQL statement is an atomic operation.
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by asisurpher Monday, January 11, 2010 2:11 PM
    Monday, January 11, 2010 10:57 AM
    Moderator