如何从VB.NET/C#批量插入多行到SQL Server？代码执行正常但未入库排查

你的问题背景 #

你有一个包含3条Employee数据的List，想通过存储过程批量插入数据库，不想逐行访问数据库。但你写的SaveMethod返回True却没数据落地，相关信息如下：

示例数据 #

empObj.Id = 3, empObj.Name = abc, emp.Value = Yes 
empObj.Id = 4, empObj.Name = xyz, emp.Value = No 
empObj.Id = 5, empObj.Name = pqr, emp.Value = Yes 

你的实现代码 #

Public Overridable Function SaveMethod(businessEntity As List(Of Employee)) As Boolean 
    Using scope As System.Transactions.TransactionScope = New System.Transactions.TransactionScope() 
        'DECLARE CONNECTION VARIABLE 
        Dim objSqlConn As SqlConnection = Nothing 
        'DECLARE SQL PARAMS VARIABLE 
        Dim objSqlParams As SqlParameter() = Nothing 
        'DECLARE BOOLEAN VARIABLE 
        Dim bolReturnValue As Boolean = False 

        'SET THE CONNECTION 
        objSqlConn = GetCSSConnection() 

        'SET THE PARAMETERS TO THE STORED PROCEDURE 
        objSqlParams = New SqlParameter(3) {} 
        For i As Integer = 0 To businessEntity.Count - 1 
            objSqlParams(0) = New SqlParameter("@myParam1", SqlDbType.Int) 
            objSqlParams(0).Value = 2 ' businessEntity(i).Id 
            objSqlParams(1) = New SqlParameter("@myParam2", SqlDbType.Int) 
            objSqlParams(1).Value = businessEntity(i).Name 
            objSqlParams(2) = New SqlParameter("@myParam3", SqlDbType.Bit) 
            objSqlParams(2).Value = businessEntity(i).value 
        Next 

        'BUILD NEW SQL CONNECTION AND EXECUTE THE STORED PROCEDURE 
        'ASSIGN THE RESULT TO BOOLEAN VARIABLE 
        bolReturnValue = (Microsoft.VisualBasic.IIf(ExecuteNonQuery(objSqlConn, CommandType.StoredProcedure, "myStoredProcedure", objSqlParams) > 0, True, False)) 

        CloseConnection(objSqlConn) 
        Return bolReturnValue 'RETURNS BOOLEAN VALUE 
    End Using 
End Function 

为啥数据没保存？3个核心问题 #

1. 事务根本没提交！ #

你用了TransactionScope，但从头到尾没调用scope.Complete()——这个类的机制是：如果没调用Complete()，事务会自动回滚，哪怕ExecuteNonQuery返回了正数，最后所有操作都会被撤销，这是最关键的原因！

2. 循环只改参数，没执行存储过程 #

你的For循环只是反复覆盖objSqlParams的参数值，循环结束后才执行了一次ExecuteNonQuery——这意味着最后只有List里的最后一条数据会被尝试插入，前面两条完全没执行插入逻辑。

3. 参数类型不匹配 #

你把businessEntity(i).Name（字符串类型）赋值给了SqlDbType.Int类型的@myParam2，这会导致隐式转换失败，就算代码没抛错，存储过程也可能因为参数不对直接跳过插入逻辑。

另外还有个小问题：objSqlParams = New SqlParameter(3) {}在VB里是创建长度为4的数组（VB数组初始化是写上限值），但你只用到0-2索引，属于冗余写法。

当前方案正确吗？ #

完全不正确，它既没实现批量插入的逻辑，也没正确处理事务，还存在参数类型错误，根本达不到你的需求。

更优的实现方式 #

方案1：修复现有代码（逐行插入，保证正确性） #

如果暂时不想修改存储过程，先把代码改成能正常工作的版本：

Public Overridable Function SaveMethod(businessEntity As List(Of Employee)) As Boolean 
    Dim bolReturnValue As Boolean = True
    ' 初始化事务范围
    Using scope As New System.Transactions.TransactionScope()
        Dim objSqlConn As SqlConnection = Nothing
        Try
            objSqlConn = GetCSSConnection()
            ' 遍历每条数据，单独执行存储过程
            For Each emp In businessEntity
                ' 正确定义参数类型和值
                Dim objSqlParams As SqlParameter() = {
                    New SqlParameter("@myParam1", SqlDbType.Int) With {.Value = emp.Id}, ' 替换硬编码的2，用实际的Id
                    New SqlParameter("@myParam2", SqlDbType.VarChar, 50) With {.Value = emp.Name}, ' Name是字符串，改成VarChar类型
                    New SqlParameter("@myParam3", SqlDbType.Bit) With {.Value = (emp.Value.Equals("Yes", StringComparison.OrdinalIgnoreCase))} ' 把Yes/No转成Bit值
                }
                ' 执行存储过程，判断是否成功
                If ExecuteNonQuery(objSqlConn, CommandType.StoredProcedure, "myStoredProcedure", objSqlParams) <= 0 Then
                    bolReturnValue = False
                    Exit For ' 一条失败就终止后续操作
                End If
            Next
            ' 只有全部成功才提交事务
            If bolReturnValue Then
                scope.Complete()
            End If
        Catch ex As Exception
            bolReturnValue = False
            ' 这里可以加日志记录异常信息，方便排查
        Finally
            ' 确保连接关闭
            If objSqlConn IsNot Nothing Then
                CloseConnection(objSqlConn)
            End If
        End Try
    End Using
    Return bolReturnValue
End Function

方案2：表值参数（TVP）实现真正的批量插入（强烈推荐） #

这是SQL Server批量插入的最优方案，只需要一次数据库往返，性能比逐行插入高很多，代码也更简洁：

步骤1：在数据库创建表值类型

CREATE TYPE EmployeeType AS TABLE (
    Id INT,
    Name VARCHAR(50),
    [Value] BIT
)

步骤2：修改存储过程，接收表值参数

CREATE PROCEDURE myStoredProcedure
    @Employees EmployeeType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    -- 批量插入所有数据
    INSERT INTO YourEmployeeTable (Id, Name, [Value])
    SELECT Id, Name, [Value] FROM @Employees
END

步骤3：数据层代码实现

Public Overridable Function SaveMethod(businessEntity As List(Of Employee)) As Boolean 
    Dim bolReturnValue As Boolean = False
    Using scope As New System.Transactions.TransactionScope()
        Dim objSqlConn As SqlConnection = Nothing
        Try
            objSqlConn = GetCSSConnection()
            ' 创建表值参数
            Dim tvpParam As New SqlParameter("@Employees", SqlDbType.Structured)
            tvpParam.TypeName = "EmployeeType" ' 对应数据库创建的表值类型名称
            ' 将List转换为DataTable，作为表值参数的值
            Dim employeeTable As New DataTable()
            employeeTable.Columns.Add("Id", GetType(Integer))
            employeeTable.Columns.Add("Name", GetType(String))
            employeeTable.Columns.Add("Value", GetType(Boolean))
            ' 填充数据
            For Each emp In businessEntity
                employeeTable.Rows.Add(emp.Id, emp.Name, emp.Value.Equals("Yes", StringComparison.OrdinalIgnoreCase))
            Next
            tvpParam.Value = employeeTable
            ' 执行存储过程
            If ExecuteNonQuery(objSqlConn, CommandType.StoredProcedure, "myStoredProcedure", {tvpParam}) > 0 Then
                bolReturnValue = True
                scope.Complete() ' 提交事务
            End If
        Catch ex As Exception
            ' 记录异常日志
        Finally
            ' 关闭连接
            If objSqlConn IsNot Nothing Then
                CloseConnection(objSqlConn)
            End If
        End Try
    End Using
    Return bolReturnValue
End Function

这个方案不仅效率高，还能避免多次数据库连接的开销，事务处理也更可靠。

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