如何基于CheckBox从DataGridView批量插入数据至数据库

Hey there! Let's work through this together. You want to keep your existing Pagamentos() function intact while adding a new feature to batch-insert rows you've selected via checkboxes in your DataGridView. Here's a straightforward way to pull this off:

First, you need a checkbox column to let users select rows. You can add this either in the designer (drag a DataGridViewCheckBoxColumn onto your DataGridView) or via code:

// Add checkbox column programmatically (if you prefer)
var checkBoxCol = new DataGridViewCheckBoxColumn();
checkBoxCol.HeaderText = "Select";
checkBoxCol.Name = "chkSelect"; // Important: we'll reference this name later
yourDataGridView.Columns.Add(checkBoxCol);

We'll build a new PagamentosBatch() function that handles bulk insertion, leaving your original Pagamentos() untouched. This function will:

• Loop through all rows in the DataGridView
• Collect data from rows where the checkbox is checked
• Insert the collected data safely (with transactions and parameterized queries to avoid SQL injection)

Here's the code:

// Your existing single-insert function remains as-is
private void Pagamentos()
{
    // Keep your original code here!
}

// New batch insert function
private void PagamentosBatch()
{
    // Replace with your actual database connection string
    string connString = "Your_Connection_String";
    
    // Collect selected payment data (create a simple model class to hold this)
    var selectedPayments = new List<Payment>();
    
    foreach (DataGridViewRow row in yourDataGridView.Rows)
    {
        // Skip the auto-generated "new row" if your grid allows adding rows
        if (row.IsNewRow) continue;
        
        // Check if the checkbox is checked
        bool isSelected = Convert.ToBoolean(row.Cells["chkSelect"].Value);
        if (isSelected)
        {
            // Pull data from the row (adjust column names and data types to match your grid)
            var payment = new Payment
            {
                PaymentId = Convert.ToInt32(row.Cells["PaymentId"].Value),
                Amount = Convert.ToDecimal(row.Cells["Amount"].Value),
                PaymentDate = Convert.ToDateTime(row.Cells["PaymentDate"].Value),
                // Add other fields as needed
            };
            selectedPayments.Add(payment);
        }
    }
    
    // Exit if no rows are selected
    if (selectedPayments.Count == 0)
    {
        MessageBox.Show("No rows selected for insertion!");
        return;
    }
    
    // Use a transaction to ensure all inserts succeed or fail together
    using (var conn = new SqlConnection(connString))
    {
        conn.Open();
        using (var transaction = conn.BeginTransaction())
        {
            try
            {
                // Parameterized insert query (replace with your table/column names)
                string insertQuery = @"INSERT INTO Pagamentos (PaymentId, Amount, PaymentDate)
                                       VALUES (@PaymentId, @Amount, @PaymentDate)";
                
                foreach (var payment in selectedPayments)
                {
                    using (var cmd = new SqlCommand(insertQuery, conn, transaction))
                    {
                        // Add parameters to prevent SQL injection
                        cmd.Parameters.AddWithValue("@PaymentId", payment.PaymentId);
                        cmd.Parameters.AddWithValue("@Amount", payment.Amount);
                        cmd.Parameters.AddWithValue("@PaymentDate", payment.PaymentDate);
                        // Add other parameters for additional fields
                        
                        cmd.ExecuteNonQuery();
                    }
                }
                
                // Commit the transaction if all inserts work
                transaction.Commit();
                MessageBox.Show("Payments inserted successfully!");
                
                // Optional: Clear checkboxes after successful insertion
                foreach (DataGridViewRow row in yourDataGridView.Rows)
                {
                    if (!row.IsNewRow)
                    {
                        row.Cells["chkSelect"].Value = false;
                    }
                }
            }
            catch (Exception ex)
            {
                // Roll back if anything goes wrong
                transaction.Rollback();
                MessageBox.Show($"Error inserting payments: {ex.Message}");
            }
        }
    }
}

// Simple model class to hold payment data (customize fields to match your needs)
public class Payment
{
    public int PaymentId { get; set; }
    public decimal Amount { get; set; }
    public DateTime PaymentDate { get; set; }
    // Add other properties here
}

If you're dealing with hundreds or thousands of rows, SqlBulkCopy is much faster than looping with individual inserts. Here's a quick variant using SqlBulkCopy:

private void PagamentosBulkInsert()
{
    string connString = "Your_Connection_String";
    var selectedPayments = new List<Payment>();
    
    // Collect selected rows (same as before)...
    
    if (selectedPayments.Count == 0)
    {
        MessageBox.Show("No rows selected!");
        return;
    }
    
    // Convert list to DataTable for SqlBulkCopy
    var dataTable = new DataTable();
    dataTable.Columns.Add("PaymentId", typeof(int));
    dataTable.Columns.Add("Amount", typeof(decimal));
    dataTable.Columns.Add("PaymentDate", typeof(DateTime));
    
    foreach (var payment in selectedPayments)
    {
        dataTable.Rows.Add(payment.PaymentId, payment.Amount, payment.PaymentDate);
    }
    
    using (var conn = new SqlConnection(connString))
    {
        conn.Open();
        using (var bulkCopy = new SqlBulkCopy(conn))
        {
            bulkCopy.DestinationTableName = "Pagamentos";
            
            // Map DataTable columns to database table columns (skip if names match exactly)
            bulkCopy.ColumnMappings.Add("PaymentId", "PaymentId");
            bulkCopy.ColumnMappings.Add("Amount", "Amount");
            bulkCopy.ColumnMappings.Add("PaymentDate", "PaymentDate");
            
            try
            {
                bulkCopy.WriteToServer(dataTable);
                MessageBox.Show("Bulk insert completed successfully!");
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Bulk insert error: {ex.Message}");
            }
        }
    }
}

Add a button to your form and hook up its click event to call the new function:

private void btnBatchInsert_Click(object sender, EventArgs e)
{
    PagamentosBatch();
    // Or use PagamentosBulkInsert() for large datasets
}

Key Notes: #

• Double-check that the checkbox column's Name (we used "chkSelect") matches what you reference in the code.
• Replace all placeholders (connection string, table/column names, data types) with your actual database details.
• Using transactions ensures you don't end up with partially inserted data if something fails mid-process.
• Parameterized queries protect you from SQL injection attacks—never concatenate user input into SQL strings!

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