Insert, Update, and Delete rows directly in DataGrid and save changes in database

Viewed 1062

I have a ListView with a list of tables from my SQL Server database. I also have a Selected event for my list view items, which populate my DataGrid with the table in the database.

I would like to have the user be able to INSERT, UPDATE, & DELETE rows in the selected table and save those changes in the database.

Here is my code that populates the DataGrid when the ListView item is selected. I removed some to not make this too long:

    public partial class TableManagement : UserControl
{
    public TableManagement()
    {
        InitializeComponent();
    }


      private void CustomerContact_Selected(object sender, RoutedEventArgs e)
    {
        try
        {
            string connectionString = ("Data Source=WINDOWS-B1AT5HC\\SQLEXPRESS;Initial Catalog=CustomerRelations;Integrated Security=True;");
            DataTable dt = new DataTable();
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataAdapter cmd = new SqlDataAdapter("SELECT [TXT_CUST_CNT], [Inactive] FROM [hb_CustCntct] ", connection);
            cmd.Fill(dt);
            dt.AcceptChanges();
            dt_TableManagement.DataContext = dt;

            dt_TableManagement.SelectedIndex = 0;

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

Here is my XMAL code for the listVeiw and DataGrid

            <!--Grid 1-->
        <Grid x:Name="Grid1" Grid.Row="1" Grid.Column="0" Margin="15">
            <ListView>
                <ListViewItem x:Name="CustomerContact" Content="Customer Contact" Selected="CustomerContact_Selected"/>
                <ListViewItem x:Name="CsrNames" Content="Customer Service Representative Names" Selected="CsrNames_Selected"/>
                <ListViewItem x:Name="DisputeClassification" Content="Dispute Classification" Selected="DisputeClassification_Selected"/>
                <ListViewItem x:Name="DisputeStatus" Content="Dispute Status" Selected="DisputeStatus_Selected"/>
                <ListViewItem x:Name="FinancialAdjustment" Content="Financial Adjustment" Selected="FinancialAdjustment_Selected"/>
                <ListViewItem x:Name="InitialCallAnalysis" Content="Initial Call Analysis" Selected="InitialCallAnalysis_Selected"/>
                <ListViewItem x:Name="InitialCallReason" Content="Initial Call Reason" Selected="InitialCallReason_Selected"/>
                <ListViewItem x:Name="MeterIssue" Content="Meter Issue" Selected="MeterIssue_Selected"/>
                <ListViewItem x:Name="OpenInError" Content="Open In Error" Selected="OpenInError_Selected"/>
                <ListViewItem x:Name="PrimaryCause" Content="Primary Cause" Selected="PrimaryCause_Selected"/>
                <ListViewItem x:Name="RequestedWork" Content="Requested Work" Selected="RequestedWork_Selected"/>
                <ListViewItem x:Name="RevenueClass" Content="Revenue Class" Selected="RevenueClass_Selected"/>
                <ListViewItem x:Name="ServiceOrderDetails" Content="Service Order Details" Selected="ServiceOrderDetails_Selected"/>
                <ListViewItem x:Name="ServiceOrderType" Content="Service Order Type" Selected="ServiceOrderType_Selected"/>
                <ListViewItem x:Name="ServiceOrderAdjustment" Content="Service Order Adjusted" Selected="ServiceOrderAdjustment_Selected"/>
                <ListViewItem x:Name="UnderlyingCause" Content="Underlying Cause" Selected="UnderlyingCause_Selected"/>
                <ListViewItem x:Name="UtilityReportType" Content="Utility Report Type" Selected="UtilityReportType_Selected"/>
                <ListViewItem x:Name="WFMIssuedBy" Content="WFM Issued By" Selected="WFMIssuedBy_Selected"/>
            </ListView> 
        </Grid>

        <!--Grid 2-->
        <Grid x:Name="Grid2" Grid.Row="1" Grid.Column="2" Margin="15">
            <DataGrid x:Name="dt_TableManagement"
              materialDesign:ListBoxAssist.IsToggle="True"
              Background="White"
              EnableColumnVirtualization="True"
              EnableRowVirtualization="True"
              ItemsSource="{Binding}">
            </DataGrid>
        </Grid>
1 Answers

To make it very easy, you could use a DataAdapter where you can set your SELECT, INSERT, UPDATE, DELETE commands.

So, after DataAdapter will populate your DataSet / DataTable instead of using command directly, it will be able to automatically detect changed entries changes and use appropriate method (INSERT/UPDATE/DELETE) when you only do a call to DataAdapter's Update method.

DataAdapter has Fill method, that use "SELECT" command and Update command that use insert/update/delete basing on RowState property of the DataRow. So when you, delete / add / modify row, it just change it's state. State of the rows will be "Changed" in some way until you AcceptChanges - which Update method of the DataAdapter does actually. For example, you populated some datatable using DataAdapter, then you delete some DataRow - the row still exists in the DataTable but with "Removed" state. Now when you perform "Update" method of the dataadapter with the DataTable parameter you just modified - DataAdapter looks for rows modified in some way, and executes proper SQL commands for the rows (in the case, DeleteCommand).

To use all of the commands, you have to set proper properties of SqlDataAdapter

  • UpdateCommand
  • InsertCommand
  • DeleteCommand
Related