C#应用技术问询：数据库数据获取与DataGrid更新实现

Hey there! Let's walk through how to implement database updates and keep your DataGrid synced with the latest data, building on the code you already have.

1. First: Refine Data Fetching & Bind to DataGrid #

First, let's polish your existing data-fetching code to properly populate a DataTable (perfect for DataGrid binding) and ensure we clean up database resources with using statements (this prevents connection leaks):

private DataTable GetProductData(string barcode)
{
    DataTable productTable = new DataTable();
    string connectionString = "Server = localhost;Database = Bilanc; Integrated Security = true";

    using (SqlConnection con = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand("Product", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        // Instead of AddWithValue, specify parameter type explicitly (safer and avoids implicit conversion issues)
        cmd.Parameters.Add("@Barcode", SqlDbType.VarChar).Value = barcode;

        con.Open();
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
            adapter.Fill(productTable);
        }
    }
    return productTable;
}

// Call this to load data into the DataGrid (e.g., when Enter is pressed)
private void LoadProductToDataGrid()
{
    if (string.IsNullOrEmpty(txtcode.Text)) return;
    
    DataTable productData = GetProductData(txtcode.Text);
    // Assuming your DataGrid is named dataGridProducts
    dataGridProducts.DataSource = productData;
}

Hook this up to your Enter key event like so:

if (e.Key == Key.Enter)
{
    LoadProductToDataGrid();
}

2. Implement Database Update Functionality #

First, create a stored procedure for updating product data (let's name it UpdateProduct). Example SQL Server stored procedure:

CREATE PROCEDURE UpdateProduct
    @Barcode VARCHAR(50),
    @Qty INT,
    @Price DECIMAL(18,2),
    @Total DECIMAL(18,2)
AS
BEGIN
    UPDATE YourProductTable -- Replace with your actual table name
    SET Qty = @Qty, Price = @Price, Total = @Total
    WHERE Barcode = @Barcode;
END

Then write a C# method to execute this update:

private bool UpdateProductInDatabase(string barcode, int qty, decimal price, decimal total)
{
    string connectionString = "Server = localhost;Database = Bilanc; Integrated Security = true";
    
    using (SqlConnection con = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand("UpdateProduct", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@Barcode", SqlDbType.VarChar).Value = barcode;
        cmd.Parameters.Add("@Qty", SqlDbType.Int).Value = qty;
        cmd.Parameters.Add("@Price", SqlDbType.Decimal).Value = price;
        cmd.Parameters.Add("@Total", SqlDbType.Decimal).Value = total;

        con.Open();
        int rowsAffected = cmd.ExecuteNonQuery();
        // Return true if at least one row was updated
        return rowsAffected > 0;
    }
}

3. Sync DataGrid After Update #

Once the database is updated, you need to refresh the DataGrid to show the latest data. You have two solid options:

Option 1: Re-fetch & Rebind (Simple & Reliable) #

After a successful update, re-run the data-fetch method to refresh the DataGrid:

// Example: Trigger update when a "Save" button is clicked
private void btnSave_Click(object sender, EventArgs e)
{
    // Get values from your UI (adjust control names to match your app)
    string barcode = txtcode.Text;
    int qty = int.Parse(txtQty.Text);
    decimal price = decimal.Parse(txtPrice.Text);
    decimal total = qty * price; // Or pull from txtTotal.Text if you calculate it there

    try
    {
        bool updateSuccess = UpdateProductInDatabase(barcode, qty, price, total);
        if (updateSuccess)
        {
            MessageBox.Show("Product updated successfully!");
            // Refresh DataGrid with latest data
            LoadProductToDataGrid();
        }
        else
        {
            MessageBox.Show("No product found with that barcode, or update failed.");
        }
    }
    catch (SqlException ex)
    {
        MessageBox.Show($"Database error: {ex.Message}");
    }
    catch (FormatException ex)
    {
        MessageBox.Show($"Invalid input: {ex.Message}");
    }
}

Option 2: Update Existing DataTable Row (More Efficient) #

If you don't want to re-fetch the entire dataset, update the current row directly in the bound DataTable:

private void UpdateDataGridRow(string barcode, int qty, decimal price, decimal total)
{
    DataTable productTable = dataGridProducts.DataSource as DataTable;
    if (productTable == null) return;

    foreach (DataRow row in productTable.Rows)
    {
        if (row["Barcode"].ToString() == barcode)
        {
            row["Qty"] = qty;
            row["Price"] = price;
            row["Total"] = total;
            break;
        }
    }
    // Refresh the DataGrid to show changes
    dataGridProducts.Refresh();
}

Call this after a successful update instead of re-fetching:

if (updateSuccess)
{
    MessageBox.Show("Product updated successfully!");
    UpdateDataGridRow(barcode, qty, price, total);
}

Key Best Practices #

• Always use using statements for SqlConnection, SqlCommand, and SqlDataAdapter to ensure resources are properly disposed.
• Avoid AddWithValue – specifying SqlDbType explicitly prevents potential type conversion bugs.
• Add error handling (like the try-catch blocks above) to gracefully handle database issues or invalid user input.

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