C# & SQL Server : stuck at inserting 32,765 records in a Transaction

Question

I seem to understand what is happening but I'm lost really. I have a transaction in Visual Studio, Winforms, C#

using (SqlConnection connection = new SqlConnection(Program.connectionString))
{
    connection.Open();

    SqlTransaction processTransaction = connection.BeginTransaction();

    try
    {
        for (int i = 0; i < dataGridView1.Rows.Count; i++)
        {
            ...
        }

        processTransaction.Commit();
        processTransaction.Dispose();

        MessageBox.Show("Success");
    }
    catch (SqlException ex)
    {
        Console.WriteLine(ex.Message);

        processTransaction.Rollback();
        processTransaction.Dispose();
    }
}

The problem is: I'm inserting and looping a dataset of 93'000 rows, more or less, but it is stopping at 32,765. I googled and researched and said that the limit of int is 2,147,483,647. Then the limit for int in Turbo C is I think more or less 32767. I have a Backgroundworker but it just displays how many loops I did

this.label1.Text = (int.Parse(this.label1.Text) + 1).ToString();

What seems to be the problem?

EDIT: The exception that I'm receiving is that the loop is completed. The same error as "zombie transaction".

This SqlTransaction has completed; it is no longer usable.

Answer 1

The limit of smallint is 32,767 so I guess you need to change the column type to greater one.