If my C# times out with a stored procedure call, does the procedure continue running?

Question

I have just a general type of question. If I have a C# application that calls a SQL Server stored procedure, and the C# application times out, does the procedure call on the server continue running to it's completion?

Answer 1

No. Below is a reproduction. When the timeout occurs the running process will be killed, halting it immediately. If you do not have a transaction specified, work that has been done in the stored procedure prior to the timeout will be persisted. Similarly, if the connection to the server is severed by some outside force, SQL Server will kill the running process.

using (var conn = new SqlConnection(@"Data Source=.;Initial Catalog=Test;Integrated Security=True"))
{
    conn.Open();

    using (var setupTable = new SqlCommand(@"
        IF NOT EXISTS (
            SELECT *
            FROM
                sys.schemas s
                    INNER JOIN sys.tables t ON
                        t.[schema_id] = s.[schema_id]
            WHERE
                s.name = 'dbo' AND
                T.name = 'TimeoutTest')
        BEGIN
            CREATE TABLE dbo.TimeoutTest
            (
                ID int IDENTITY(1,1) PRIMARY KEY,
                CreateDate datetime DEFAULT(getdate())
            );
        END

        -- remove any rows from previous runs
        TRUNCATE TABLE dbo.TimeoutTest;", conn))
    {
        setupTable.ExecuteNonQuery();
    }

    using (var checkProcExists = new SqlCommand(@"
        SELECT COUNT(*)
        FROM
            sys.schemas s
                INNER JOIN sys.procedures p ON
                    p.[schema_id] = s.[schema_id]
        WHERE
            s.name = 'dbo' AND
            p.name = 'AddTimeoutTestRows';", conn))
    {
        bool procExists = ((int)checkProcExists.ExecuteScalar()) == 1;

        if (!procExists)
        {
            using (var setupProc = new SqlCommand(@"
                CREATE PROC dbo.AddTimeoutTestRows
                AS
                BEGIN

                    DECLARE @stop_time datetime;

                    SET @stop_time = DATEADD(minute, 1, getdate());

                    WHILE getdate() < @stop_time
                    BEGIN
                        INSERT INTO dbo.TimeoutTest DEFAULT VALUES;

                        -- wait 10 seconds between inserts
                        WAITFOR DELAY '0:00:10';
                    END

                END", conn))
            {
                setupProc.ExecuteNonQuery();
            }
        }
    }

    bool commandTimedOut = false;

    try
    {
        using (var longExecution = new SqlCommand("EXEC dbo.AddTimeoutTestRows;", conn))
        {
            // The time in seconds to wait for the command to execute.
            // Explicitly setting the timeout to 30 seconds for clarity.
            longExecution.CommandTimeout = 30;

            longExecution.ExecuteNonQuery();
        }
    }
    catch (SqlException ex)
    {
        if (ex.Message.Contains("Timeout"))
        {
            commandTimedOut = true;
        }
        else
        {
            throw;
        }
    }

    Console.WriteLine(commandTimedOut.ToString());

    // Wait for an extra 30 seconds to let any execution on the server add more rows.
    Thread.Sleep(30000);

    using (var checkTableCount = new SqlCommand(@"
        SELECT COUNT(*)
        FROM
            dbo.TimeoutTest t;", conn))
    {
        // Only expecting 3, but should be 6 if server continued on without us.
        int rowCount = (int)checkTableCount.ExecuteScalar();

        Console.WriteLine(rowCount.ToString("#,##0"));
    }
}

Console.ReadLine();

produces the following output

True
3

even though running the stored procedure from Management Studio will add 6 rows in the one minute time frame.