如何在VB/WPF+SQL Server环境下无指定组件时实现DataGrid数据自动更新至数据库

Hey there, let's work through this together—since you're using VB/WPF without MVVM, DataTables, DataSets, or even buttons, saving your DataGrid's donation data to SQL Server can feel like hitting a wall. Here's a step-by-step implementation that fits your setup:

First off, since you're using a CollectionViewSource, you must have an underlying collection (like an ObservableCollection) bound to it. Let's formalize this with a simple entity class that maps directly to your donation database table:

Public Class Donation
    ' Match these properties to your SQL Server table's fields exactly
    Public Property DonationId As Integer
    Public Property DonorName As String
    Public Property DonationAmount As Decimal
    Public Property DonationDate As DateTime
    Public Property DonorEmail As String ' Add any other fields you need
End Class

Then in your window code-behind, initialize this collection and link it to your CollectionViewSource:

Private _donationList As New ObservableCollection(Of Donation)()

Public Sub New()
    InitializeComponent()
    ' Load existing data into _donationList here if needed
    Dim cvs = CType(Me.Resources("cvsDonations"), CollectionViewSource)
    cvs.Source = _donationList
End Sub

Next, create a method to iterate through your collection and save each record to SQL Server. We'll use parameterized queries here to avoid SQL injection and ensure data type safety:

Private Sub SaveDonationsToDB()
    ' Replace with your actual SQL Server connection string
    Dim connString As String = "Server=YOUR_SERVER_NAME;Database=YOUR_DATABASE_NAME;Integrated Security=True;"
    
    Try
        Using conn As New SqlConnection(connString)
            conn.Open()
            
            ' Loop through every donation in your collection
            For Each donation In _donationList
                ' Adjust this INSERT statement to match your table's columns
                Dim sql As String = "INSERT INTO Donations (DonorName, DonationAmount, DonationDate, DonorEmail) 
                                     VALUES (@DonorName, @DonationAmount, @DonationDate, @DonorEmail)"
                
                Using cmd As New SqlCommand(sql, conn)
                    ' Map your Donation properties to SQL parameters
                    cmd.Parameters.AddWithValue("@DonorName", donation.DonorName)
                    cmd.Parameters.AddWithValue("@DonationAmount", donation.DonationAmount)
                    cmd.Parameters.AddWithValue("@DonationDate", donation.DonationDate)
                    cmd.Parameters.AddWithValue("@DonorEmail", If(String.IsNullOrEmpty(donation.DonorEmail), DBNull.Value, donation.DonorEmail))
                    
                    ' Execute the query to save the record
                    cmd.ExecuteNonQuery()
                End Using
            Next
            
            MessageBox.Show("Donation data saved successfully!")
        End Using
    Catch ex As Exception
        MessageBox.Show($"Save failed: {ex.Message}", "Error", MessageBoxButton.OK, MessageBoxImage.Error)
    End Try
End Sub

Since you aren't using buttons, you can trigger the save action via other events—like when the window closes, or a keyboard shortcut. For example, here's how to save when the window is closing:

Private Sub Window_Closing(sender As Object, e As ComponentModel.CancelEventArgs) Handles Me.Closing
    SaveDonationsToDB()
End Sub

If you prefer a keyboard shortcut (like Ctrl+S), you can add a command binding in your XAML:

<Window.InputBindings>
    <KeyBinding Key="S" Modifiers="Control" Command="{x:Static local:WindowCommands.SaveCommand}"/>
</Window.InputBindings>

Then in code-behind, define the command and link it to your save method:

Public Shared SaveCommand As New RoutedCommand()

Public Sub New()
    InitializeComponent()
    ' ... existing code ...
    Dim saveBinding As New CommandBinding(SaveCommand, AddressOf SaveDonationsToDB)
    Me.CommandBindings.Add(saveBinding)
End Sub

If your DataGrid lets users edit existing records and add new ones, modify the Donation class to track changes:

Public Class Donation
    ' ... existing properties ...
    Public Property IsNewRecord As Boolean = True ' Mark new entries
    Public Property IsModified As Boolean = False ' Track edits
End Class

Then update the save method to handle inserts and updates:

Private Sub SaveDonationsToDB()
    Dim connString As String = "Server=YOUR_SERVER_NAME;Database=YOUR_DATABASE_NAME;Integrated Security=True;"
    
    Try
        Using conn As New SqlConnection(connString)
            conn.Open()
            
            For Each donation In _donationList
                If donation.IsNewRecord Then
                    ' Insert new record
                    Dim insertSql As String = "INSERT INTO Donations (DonorName, DonationAmount, DonationDate, DonorEmail) 
                                              VALUES (@DonorName, @DonationAmount, @DonationDate, @DonorEmail)"
                    Using cmd As New SqlCommand(insertSql, conn)
                        ' ... add parameters ...
                        cmd.ExecuteNonQuery()
                        donation.IsNewRecord = False ' Mark as saved
                    End Using
                ElseIf donation.IsModified Then
                    ' Update existing record (use DonationId as primary key)
                    Dim updateSql As String = "UPDATE Donations 
                                              SET DonorName=@DonorName, DonationAmount=@DonationAmount, DonationDate=@DonationDate, DonorEmail=@DonorEmail
                                              WHERE DonationId=@DonationId"
                    Using cmd As New SqlCommand(updateSql, conn)
                        cmd.Parameters.AddWithValue("@DonationId", donation.DonationId)
                        ' ... add other parameters ...
                        cmd.ExecuteNonQuery()
                        donation.IsModified = False ' Mark as updated
                    End Using
                End If
            Next
        End Using
    Catch ex As Exception
        MessageBox.Show($"Save failed: {ex.Message}", "Error", MessageBoxButton.OK, MessageBoxImage.Error)
    End Try
End Sub

To set the IsModified flag when a user edits a cell, add this event handler to your DataGrid:

Private Sub DataGrid_CellEditEnding(sender As Object, e As DataGridCellEditEndingEventArgs)
    If e.EditAction = DataGridEditAction.Commit Then
        Dim editedDonation = CType(e.Row.DataContext, Donation)
        editedDonation.IsModified = True
    End If
End Sub

• Double-check that your Donation property types match your SQL Server table's column types (e.g., Decimal in VB maps to decimal in SQL Server).
• Always use parameterized queries—never concatenate user input into SQL strings.
• If you're loading existing data from the database initially, set IsNewRecord = False for those records.

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