SQLBulkTool批量插入报错：参数必须为SqlConnection实例

测试环境 #

• SQL Server 2019
• Windows 11
• Visual Studio 2022

操作步骤与代码 #

1. 数据库表结构 #

创建SimpleBook表：

Id Int IDENTITY(1,1) NOT NULL,
ISBN varchar(30) NOT NULL,
Description varchar(50) NULL

2. 实体类定义 #

namespace SQLBulkToolTest.DataObjs
{
    public class SimpleBook
    {
        public int Id { get; set; }
        public string ISBN { get; set; }
        public string Description { get; set; }

        public SimpleBook() { }
        public SimpleBook(string iSBN, string description)
        {
            ISBN = iSBN;
            Description = description;
        }
    }
}

3. 批量插入测试代码 #

public BulkOperations bulk = new BulkOperations();
public DataTableOperations dtOps = new DataTableOperations();
        
public List<SimpleBook> GetBooks()
{
    List<SimpleBook> bookList = new();
    bookList.Add(new SimpleBook("ISBN001", "Next Gen Computing"));
    bookList.Add(new SimpleBook("ISBN002", "Program development patterns"));
    bookList.Add(new SimpleBook("ISBN003", "Learn c#"));

    return bookList;
}

public bool InsertData()
{
    bool isOk = false;

    try
    {
        List<SimpleBook> bookList = GetBooks();

        using (TransactionScope trans = new TransactionScope())
        {
            using (SqlConnection conn = new SqlConnection(dapperConnStr))
            {
                bulk.Setup<SimpleBook>()
                    .ForCollection(bookList)
                    .WithTable("SimpleBook")
                    .AddAllColumns()
                    .BulkInsert()
                    .SetIdentityColumn(x => x.Id)
                    .Commit(conn);
            }
            trans.Complete();
        }

        isOk = true;
    }
    catch(Exception ex)
    {
        _log.Error(ex.Message);
        throw;
    }
    return isOk;
}

问题现象 #

执行InsertData方法时触发异常，错误信息：

Error message: Parameter must be a SqlConnection instance

Stack Trace:
at SqlBulkTools.BulkInsert`1.Commit(IDbConnection connection, IDbTransaction transaction)
at SQLBulkToolTest.DbBulkOps.InsertData() in C:\Projects\my_tryout\SQLBulkToolTest\SQLBulkToolTest\DbBulkOps.cs:line 71

连接字符串已验证有效，但无法定位问题。

解决方案 #

1. 显式打开数据库连接 #

SqlBulkTools的Commit方法要求传入的SqlConnection必须处于已打开状态，修改代码在使用连接前显式打开：

public bool InsertData()
{
    bool isOk = false;

    try
    {
        List<SimpleBook> bookList = GetBooks();

        using (TransactionScope trans = new TransactionScope())
        {
            using (SqlConnection conn = new SqlConnection(dapperConnStr))
            {
                conn.Open(); // 新增：显式打开连接
                bulk.Setup<SimpleBook>()
                    .ForCollection(bookList)
                    .WithTable("SimpleBook")
                    .AddAllColumns()
                    .BulkInsert()
                    .SetIdentityColumn(x => x.Id)
                    .Commit(conn);
            }
            trans.Complete();
        }

        isOk = true;
    }
    catch(Exception ex)
    {
        _log.Error(ex.Message);
        throw;
    }
    return isOk;
}

2. 确认SqlClient包版本兼容性 #

检查项目中引用的SqlClient包：

• 如果使用的是Microsoft.Data.SqlClient，尝试切换为System.Data.SqlClient（部分旧版本SqlBulkTools依赖旧版SqlClient）
• 确保SqlClient版本与SqlBulkTools v2.1.13兼容

3. 传递事务参数（可选） #

如果需要更严谨的事务控制，可以将TransactionScope中的事务传递给Commit方法：

using (TransactionScope trans = new TransactionScope())
{
    using (SqlConnection conn = new SqlConnection(dapperConnStr))
    {
        conn.Open();
        using (var sqlTrans = conn.BeginTransaction())
        {
            bulk.Setup<SimpleBook>()
                .ForCollection(bookList)
                .WithTable("SimpleBook")
                .AddAllColumns()
                .BulkInsert()
                .SetIdentityColumn(x => x.Id)
                .Commit(conn, sqlTrans);
            sqlTrans.Commit();
        }
    }
    trans.Complete();
}

内容的提问来源于stack exchange，提问作者bedrock