Delete not working in transaction with sql bulk insert

Question

I need to delete some records from table and then insert some records to the same table. This delete and insert process should be in transaction.

Below is what i did.

 using (SqlConnection sqlConn = new SqlConnection(connectionString))
        {
            sqlConn.Open();
            using (SqlTransaction sqlTran = sqlConn.BeginTransaction())
            {
                string deleteQuery = "delete from dbo.MyTable where Col1 =" + colValue;
                SqlCommand sqlComm = new SqlCommand(deleteQuery, sqlConn,sqlTran);
                sqlComm.ExecuteNonQuery();
                using (SqlBulkCopy sqlcopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, sqlTran))
                {
                    sqlcopy.BatchSize = 10;
                    sqlcopy.DestinationTableName = "MyTable";
                    try
                    {
                        sqlcopy.WriteToServer(dsDataSet.Tables[0]);
                        sqlTran.Commit();
                    }
                    catch (Exception ex)
                    {
                        sqlTran.Rollback();
                    }
                }
            }
        }

But, i guess as the delete operation is not getting performed before insert, i get duplicate key errors. Can some one help.?

Answer 1

I tried your sample code, and it seemed to work for me. I created a PK on one of the columns to make sure a duplicate insert would throw an error. Then I run the code twice -- the first time through it puts in some dummy data (no errors thrown), the second time through it tries to delete the data and then reinsert within the same transaction. From your question, I was hoping to get an exception the 2nd time through but it worked. Not sure it matters, but I am running SQL Server 2008 R2 SP1.

Here is the complete test code which I used, perhaps it will help you identify your issue.

First the SQL to create a sample table:

CREATE TABLE [dbo].[MyTable](
    [Col1] [nvarchar](20) NOT NULL,
    [Col2] [nvarchar](20) NULL,
    [Col3] [nvarchar](30) NULL,
    CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
    (
        [Col1] ASC
    )
)

And the C#:

public static void Main()
{
    DataTable t = new DataTable();
    t.Columns.Add(new DataColumn("Col1"));
    t.Columns.Add(new DataColumn("Col2"));
    t.Columns.Add(new DataColumn("Col3"));
    for (int i = 0; i < 5; i++)
    {
        var r1 = t.NewRow();
        r1["Col1"] = "1" + i.ToString();
        r1["Col2"] = "2" + i.ToString();
        r1["Col3"] = "3" + i.ToString();
        t.Rows.Add(r1);
    }
    t.AcceptChanges();
    var connectionString = new SqlConnectionStringBuilder();
    connectionString.DataSource = "localhost";
    connectionString.InitialCatalog = "testdb";
    connectionString.IntegratedSecurity = true;

    using (SqlConnection sqlConn = new SqlConnection(connectionString.ToString()))
    {
        sqlConn.Open();
        using (SqlTransaction sqlTran = sqlConn.BeginTransaction())
        {
            string deleteQuery = "delete from MyTable"; // just delete them all
            SqlCommand sqlComm = new SqlCommand(deleteQuery, sqlConn, sqlTran);
            sqlComm.ExecuteNonQuery();
            using (SqlBulkCopy sqlcopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, sqlTran))
            {
                sqlcopy.BatchSize = 10;
                sqlcopy.DestinationTableName = "MyTable"; 
                try
                {
                    sqlcopy.WriteToServer(t);
                    sqlTran.Commit();
                }
                catch (Exception ex)
                {
                    sqlTran.Rollback();
                }
            }
        }
    }

}

Update on your question By the way, you didn't get an error because 11/23/2011 was evaluated as a math expression ('/' is division), resulting in the value 0, which was then implicitly cast to a datetime as 1900/01/01. Try running the query "select CONVERT(datetime, 11/23/2011)" and you will see what I mean.