如何批量快速更新DataGridView复选框选中行的数据库记录

Great question—iterating through every checked row and running individual UPDATE commands is a common pitfall that kills performance, especially with large datasets. Let’s first cover why your current code is lagging, then jump into three high-performance batch solutions tailored to your ADO.NET setup.

Why Your Current Code Is Slow #

Your existing approach has two major bottlenecks:

• Connection overhead: You’re opening/closing a database connection for every single row. Establishing connections is expensive—this alone adds massive latency.
• Individual query execution: Each UPDATE is sent to the database separately, forcing SQL Server to compile and run hundreds/thousands of tiny queries instead of one efficient batch operation.

Solution 1: Table-Valued Parameters (TVP) (Recommended) #

SQL Server’s Table-Valued Parameters let you pass an entire table of data to a query in one go. This is the cleanest, most efficient method for batch updates.

Step 1: Create a Custom Table Type in SQL Server #

First, run this once in your database to define a table type that matches the key columns you need for updates:

CREATE TYPE AirbillUpdateType AS TABLE (
    BranchID INT,
    AirbillNo INT,
    TrackingNo INT
);

Step 2: VB.NET Code for Batch Update #

This code collects all checked rows into a DataTable, then sends it to SQL Server in one batch:

' 1. Collect all checked rows into a DataTable
Dim updateBatch As New DataTable()
updateBatch.Columns.Add("BranchID", GetType(Integer))
updateBatch.Columns.Add("AirbillNo", GetType(Integer))
updateBatch.Columns.Add("TrackingNo", GetType(Integer))

For Each row As DataGridViewRow In PreviewBilling.DataGridView1.Rows
    If row.Cells(0).Value = True Then
        updateBatch.Rows.Add(
            branchID_CreateBilling,
            row.Cells(1).Value,
            branchID_CreateBilling & 2 ' Match your original TrackingNo logic
        )
    End If
Next

' 2. Execute batch update using TVP
Using con As New SqlConnection(YourConnectionString) ' Replace with your connection string logic
    con.Open()
    Using cmd As New SqlCommand("UPDATE a 
                                SET BillingTrid = @btrid
                                FROM AIRBILLS a
                                INNER JOIN @UpdateBatch ut 
                                    ON a.BranchID = ut.BranchID
                                    AND a.AirbillNo = ut.AirbillNo
                                    AND a.TrackingNo = ut.TrackingNo", con)
        ' Add the batch parameter
        cmd.Parameters.Add("@btrid", SqlDbType.Int).Value = billtrid
        
        Dim tvpParam As SqlParameter = cmd.Parameters.AddWithValue("@UpdateBatch", updateBatch)
        tvpParam.SqlDbType = SqlDbType.Structured
        tvpParam.TypeName = "AirbillUpdateType" ' Match the SQL table type name

        cmd.ExecuteNonQuery()
    End Using
End Using

Solution 2: SqlDataAdapter Batch Update #

Since you’re already using SqlDataAdapter to fill your DataTable, you can leverage its built-in batch update support. This avoids rewriting too much of your existing code.

Modified Fill & Update Code #

' When filling the DataTable, include key columns (BranchID, TrackingNo) that you need for updates
Dim adapt As New SqlDataAdapter()
Dim dt As New DataTable()
Dim con As SqlConnection = YourConnection() ' Replace with your server_connection() logic

' Select command (added BranchID and TrackingNo to the SELECT list)
Dim selectCmd As New SqlCommand("SELECT AirbillNo As [Airbill], SubAirbill as [Sub], BRANCHES.BranchName as [Branch], 
                                DateOfAirbill as [Date], AIRBILLCLASS.ClassName, SERVICE.ServiceName,
                                net + taxs as [Gross], NET, TAXS, Addressee, Sender, SCOPE_TOWN.Towns as [Destination1],
                                AIRBILLS.QTY as [QTY], Valuation, Insurance, PickupFee, OtherFee, ExcessWeightAmount,
                                Lenght, Width, Height, Discounts, BillingTrid, StatementTRID,
                                AIRBILLS.BranchID, AIRBILLS.TrackingNo ' Include key columns
                                FROM AIRBILLS 
                                INNER JOIN BRANCHES ON AIRBILLS.BranchID = BRANCHES.ID 
                                INNER JOIN AIRBILLCLASS ON AIRBILLCLASS.ID = AIRBILLS.ClassofAirbill 
                                INNER JOIN SCOPE_TOWN on SCOPE_TOWN.ID = AIRBILLS.TownDestinationID 
                                INNER JOIN SERVICE on SERVICE.ID = AIRBILLS.ServiceID 
                                WHERE ClientID = @Clid AND DateOfAirbill BETWEEN @d1 AND @d2 AND BillingTrid IS NULL", con)
selectCmd.Parameters.AddWithValue("Clid", CreateBilling.CID.Text)
selectCmd.Parameters.AddWithValue("d1", CreateBilling.begindate.Value.Date)
selectCmd.Parameters.AddWithValue("d2", CreateBilling.enddate.Value.Date)
adapt.SelectCommand = selectCmd

' Configure UpdateCommand for batch operations
Dim updateCmd As New SqlCommand("UPDATE AIRBILLS 
                                SET BillingTrid = @btrid 
                                WHERE BranchID = @bid AND AirbillNo = @abno AND TrackingNo = @tno", con)
updateCmd.Parameters.Add("@btrid", SqlDbType.Int, 0, "BillingTrid")
updateCmd.Parameters.Add("@bid", SqlDbType.Int, 0, "BranchID")
updateCmd.Parameters.Add("@abno", SqlDbType.Int, 0, "AirbillNo")
updateCmd.Parameters.Add("@tno", SqlDbType.Int, 0, "TrackingNo")
adapt.UpdateCommand = updateCmd

' Enable batch updates (set to 0 for unlimited batch size, or a number like 100 for controlled batches)
adapt.UpdateBatchSize = 100

' Fill the DataTable and bind to DataGridView
adapt.Fill(dt)
PreviewBilling.DataGridView1.DataSource = dt

' ------------------------------
' When ready to update checked rows:
' ------------------------------
For Each row As DataRow In dt.Rows
    ' Find the corresponding DataGridView row to check if it's selected
    Dim dgvRow As DataGridViewRow = PreviewBilling.DataGridView1.Rows(dt.Rows.IndexOf(row))
    If dgvRow.Cells(0).Value = True Then
        row("BillingTrid") = billtrid
        row.SetModified() ' Mark the row as changed
    End If
Next

' Execute batch update in one go
adapt.Update(dt)

Solution 3: Bulk Insert to Temporary Table + Join Update #

If TVPs aren’t an option (e.g., older SQL Server versions), you can use a temporary table to bulk insert your checked rows, then run a single UPDATE joined to the temp table.

Using con As New SqlConnection(YourConnectionString)
    con.Open()

    ' 1. Create temporary table
    Using createTempCmd As New SqlCommand("CREATE TABLE #TempAirbills (BranchID INT, AirbillNo INT, TrackingNo INT)", con)
        createTempCmd.ExecuteNonQuery()
    End Using

    ' 2. Bulk insert checked rows into temp table
    Dim bulkCopy As New SqlBulkCopy(con)
    bulkCopy.DestinationTableName = "#TempAirbills"
    bulkCopy.ColumnMappings.Add("BranchID", "BranchID")
    bulkCopy.ColumnMappings.Add("AirbillNo", "AirbillNo")
    bulkCopy.ColumnMappings.Add("TrackingNo", "TrackingNo")

    ' Collect checked rows into DataTable (same as Solution 1)
    Dim updateBatch As New DataTable()
    updateBatch.Columns.Add("BranchID", GetType(Integer))
    updateBatch.Columns.Add("AirbillNo", GetType(Integer))
    updateBatch.Columns.Add("TrackingNo", GetType(Integer))

    For Each row As DataGridViewRow In PreviewBilling.DataGridView1.Rows
        If row.Cells(0).Value = True Then
            updateBatch.Rows.Add(branchID_CreateBilling, row.Cells(1).Value, branchID_CreateBilling & 2)
        End If
    Next

    bulkCopy.WriteToServer(updateBatch)

    ' 3. Run batch update joined to temp table
    Using updateCmd As New SqlCommand("UPDATE a 
                                      SET BillingTrid = @btrid
                                      FROM AIRBILLS a
                                      INNER JOIN #TempAirbills ta 
                                          ON a.BranchID = ta.BranchID
                                          AND a.AirbillNo = ta.AirbillNo
                                          AND a.TrackingNo = ta.TrackingNo", con)
        updateCmd.Parameters.Add("@btrid", SqlDbType.Int).Value = billtrid
        updateCmd.ExecuteNonQuery()
    End Using

    ' 4. Clean up temp table
    Using dropTempCmd As New SqlCommand("DROP TABLE #TempAirbills", con)
        dropTempCmd.ExecuteNonQuery()
    End Using
End Using

Bonus Optimization Tips #

• Reuse connections: Use Using statements to manage connections properly (they automatically close/release connections to the pool, avoiding overhead).
• Add indexes: Create a composite index on AIRBILLS (BranchID, AirbillNo, TrackingNo) to speed up the UPDATE join operations.
• Use transactions: Wrap your batch update in a transaction if you need all updates to succeed/fail together (add Using tran As SqlTransaction = con.BeginTransaction() then set cmd.Transaction = tran, finally tran.Commit()).

内容的提问来源于stack exchange，提问作者Regime Evangelista Lesmoras