c# - 如何在bulkCopy.WriteToServer之后返回结果

根据接受的答案进行更新：

bool success = false;
using (var bulkCopy = new SqlBulkCopy(connection)) //using!
{
    connection.Open();

    //explicit isolation level is best-practice
    using (var tran = connection.BeginTransaction(IsolationLevel.ReadCommitted))
    {
        bulkCopy.DestinationTableName = "table";
        bulkCopy.ColumnMappings...

        using (var dataReader = new ObjectDataReader<SomeObject>(paths))
        {
            bulkCopy.WriteToServer(dataReader);
            success = true;
        }

        tran.Commit(); //commit, will not be called if exception escapes
    }
}
return success;

我将BulkCopy类用于大插入，并且工作正常。
执行WriteToServer并将数据保存到数据库后，我不知道所有数据是否都已成功保存，因此我可以返回true/false，因为我需要保存全部还是不保存？

    var bulkCopy = new SqlBulkCopy(connection);

    bulkCopy.DestinationTableName = "table";

    bulkCopy.ColumnMappings...

    using (var dataReader = new ObjectDataReader<SomeObject>(paths))
    {
        try
        {
        bulkCopy.WriteToServer(dataReader);
        }
        catch(Exception ex){ ... }
}

最佳答案

如果对WriteToServer的调用无一例外地完成，则所有行均已保存并在磁盘上。这只是SQL Server DML的标准语义。批量复制没有什么特别的。

像所有其他DML一样，SqlBulkCopy也不是全部。除非您配置的批次大小没有设置。

using (var bulkCopy = new SqlBulkCopy(connection)) //using!
{
    connection.Open();

    //explicit isolation level is best-practice
    using (var tran = connection.BeginTransaction(IsolationLevel.ReadCommitted))
    {
        bulkCopy.DestinationTableName = "table";
        bulkCopy.ColumnMappings...

        using (var dataReader = new ObjectDataReader<SomeObject>(paths))
        {
            //try
            //{
            bulkCopy.WriteToServer(dataReader, /*here you set some options*/);
            //}
            //catch(Exception ex){ ... } //you need no explicit try-catch here
        }

        tran.Commit(); //commit, will not be called if exception escapes
    }
}

我为您添加了符合最佳实践的示例代码。