Update changed DataGrid to the database?

Question

Okay guys,

After many readings and finding solutions down here I just need to ask this. I already searched for it but it didn't help me as good as it should to sort this out. Also, excuse me for my grammatical mistakes if any...

What's the problem? I load up a WPF form with a combobox in. This combobox gets all the names from all the tables in my database. I choose a table name and press a button to fill up a DataGrid (WPF) with the chosen table. This all works perfectly. But, then when I changed a cell or added/deleted a row or column I have to update this to the database. This is where I'm stuck. I got it working trough a not so optimal way. So that's why I ask if there is a better solution.

//fill the datagrid with data from chosen table in combobox!            
selectedTable = cboxTables.SelectedItem.ToString();
dataset = db.getDataFromTable(selectedTable);
datatable = dataset.Tables[selectedTable];
datagridAdmin.ItemsSource = datatable.DefaultView;

And when the selection in the DataGrid has changed, I set the 'Submit' button as active wich calls this code:

db.updateTable(selectedTable, datatable);

Note that 'db' is a instance off my databaseclass. The method is as following:

public bool updateTable(String tableName, DataTable datatable)
{
    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(thisConnection.ConnectionString, SqlBulkCopyOptions.CheckConstraints))
    {
        //Set destination table name
        //to table previously created.
        bulkcopy.DestinationTableName = tableName;

        try
        {
            thisCommand = new SqlCommand("DELETE FROM " + tableName, thisConnection);
            thisCommand.ExecuteNonQuery();
            bulkcopy.WriteToServer(datatable);
        }
        catch (Exception ex)
        {
            logger.WriteLine(applicationName, ex.Message);
        }
    }
}

But the problem is, the first column is a auto increment ID wich keeps raising every time I submit the changed DataGrid. Isn't there a better way to do this?

Thanks!

PS: I'm coding in Visual Studio 2010 with C# in WPF.

GarethD GarethD · Accepted Answer · 2012-01-31T14:47:16

In short for the method you have given you would be better off using the Truncate table method before calling your bulk copy, this will reset the Identity column back to 0:

thisCommand = new SqlCommand("TRUNCATE TABLE " + tableName, thisConnection);

However either method is going to cause problems with foreign keys in your database. I am not sure how you are retrieving your data in your database class, but I would look at using the SqlCommandBuilder to update your database rather than deleting and re-inserting all the data with each update.

EDIT

To further explain my suggestion of the SqlCommandBuilder.

A ViewModel such as the following should allow use of the SqlCommandBuilder (Note: this is hugely cut down and in real terms this requires better validation, exception and event handling, but the rough idea is there):

public class YourViewModel
{
    private SqlDataAdapter adapter;
    private DataTable table;
    private SqlCommandBuilder commandBuilder;
    public DataTable Table { get { return table; } set { table = value; } }
    public void LoadTable(string connectionString, string tableName, int[] primaryKeyColumns)
    {
        adapter = new SqlDataAdapter(string.Format("SELECT * FROM {0}", tableName), connectionString);
        table = new DataTable();
        adapter.Fill(table);
        List<DataColumn> primaryKeys = new List<DataColumn>();
        for (int i = 0; i < primaryKeyColumns.Length; i++) 
        {
             primaryKeys.Add(table.Columns[primaryKeyColumns[i]]);
        }
        table.PrimaryKey = primaryKeys.ToArray();
        commandBuilder = new SqlCommandBuilder(adapter);
        commandBuilder.GetUpdateCommand();
    }
    public int Update()
    {
        if (table == null || table.Rows.Count == 0 || adapter == null) 
        {
             return 0;
        }
        if (table.PrimaryKey.Length == 0)
        {
            throw new Exception("Primary Keys must be defined before an update of this nature can be performed");
        }
        else
        {
            return adapter.Update(table);
        }
    }
}

Bind the Table property to the grid view, then call the Update method when required. You can even bind the update to the table's rowchanged events etc to automatically update the db. The Code Project has a fairly good article on WPF, Data grids and database integration.

Update changed DataGrid to the database?

1 Answers