删除在带有sql批量插入的事务中不起作用

Question

我需要从表中删除一些记录，然后将一些记录插入到同一个表中。这个删除和插入过程应该在事务中。

下面是我所做的。

 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();
                    }
                }
            }
        }

但是，我想由于在插入之前没有执行删除操作，所以我收到重复的键错误。有人可以帮忙吗？

Answer 1

我尝试了你的示例代码，它似乎对我有用。我在其中一列上创建了一个 PK，以确保重复插入会引发错误。然后我运行代码两次——第一次运行时放入一些虚拟数据（没有抛出错误），第二次运行时尝试删除数据，然后在同一事务中重新插入。从你的问题来看，我希望第二次能得到例外，但它成功了。不确定这是否重要，但我正在运行 SQL Server 2008 R2 SP1。

这是我使用的完整测试代码，也许它可以帮助您确定问题。

首先是创建示例表的 SQL：

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
    )
)

以及 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();
                }
            }
        }
    }

}

更新您的问题
顺便说一句，您没有收到错误，因为 11/23/2011 被计算为数学表达式（“/”是除法），导致值 0，然后隐式转换为日期时间为 1900/01/ 01.尝试运行查询“select CONVERT(datetime, 11/23/2011)”，您就会明白我的意思。