How to: Post Changes to SQL Database

• Jun 07, 2019
• 2 minutes to read

The .Net data model implies that a control bound to a database using DbDataAdapter and DataTable objects does not immediately post changes after they have been made. Instead, changes are accumulated in the DataTable. You have to manually call a specific method to update the database. Changes you made while editing data (adding, deleting or modifying records) are saved in the grid’s data source (e.g. DataTable). To post these changes to the database, you should call the data adapter’s Update method.

Before calling this method, make sure that the grid control has saved all the changes made to the currently focused row. In this instance, you should call the DataViewBase.CommitEditing method.

This example shows how to post changes to a SQL database. Before data is posted to the database, the DataViewBase.CommitEditing method is called to validate the currently edited row (if any).

XAML

<Grid>
    <dxg:GridControl x:Name="grid" AutoPopulateColumns="True"
                     HorizontalAlignment="Stretch"
                     VerticalAlignment="Top">
        <dxg:GridControl.View>
            <dxg:TableView NavigationStyle="Cell"/>
        </dxg:GridControl.View>
    </dxg:GridControl>
    <Button HorizontalAlignment="Left"
            VerticalAlignment="Bottom"
            Click="Button_Click">
        Save Data
    </Button>
</Grid>

C#

using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

// ...
public partial class Window1 : Window {
    SqlDataAdapter sqlAdapter;
    public Window1() {
        InitializeComponent();
        InitSQLDataAdapter();
        grid.ItemsSource = GetData();
    }
    private void InitSQLDataAdapter() {
        SqlConnection sqlConn = new SqlConnection(
            "Data Source=SQLServerName;Initial Catalog=Northwind;Integrated Security=True");
        sqlAdapter = new SqlDataAdapter(
            "SELECT [RegionID], [RegionDescription] FROM [Region]", sqlConn);
        sqlAdapter.UpdateCommand = new SqlCommand(
            "UPDATE Region SET RegionDescription = @RegionDescription WHERE RegionID = @RegionID",
            sqlConn);
        sqlAdapter.UpdateCommand.Parameters.Add("@RegionID", SqlDbType.Int, 4, "RegionID");
        sqlAdapter.UpdateCommand.Parameters.Add("@RegionDescription", SqlDbType.VarChar,
            50, "RegionDescription");
    }
    private DataView GetData() {
        DataSet ds = new DataSet();
        sqlAdapter.Fill(ds);
        return ds.Tables[0].DefaultView;
    }
    private void Button_Click(object sender, RoutedEventArgs e) {
        grid.View.CommitEditing();
        sqlAdapter.Update(((DataView)grid.DataSource).Table);
    }
}