How do I commit the changes I have made to a DataTable to the Table from which I grabbed it?

Question

I'm working on a minimalist database interface that grabs all relevant information from all relevant tables and stores that information in data tables locally so that the information can be modified, changed, updated, and what not, as needed without maintaining a constant connection to the database.

The method works perfectly, all changes are reflected and stored in the table, and that's great.

Now I need to know how to go about reflecting the changes made locally in the data tables to the database that I grabbed the tables from.

Everything I've read seems to me to say "You need to maintain a connection to do that" but because it may take some amount of time to enact the changes to the datatables, and I've heard it's best practice to just establish a connection, do what you need to, and then get out, I'm seeing a kind of conflict here.

The articles I'm referencing are this one, and this one.

This is the code for getting the table(s) :

public static DataTable GetTable( string Table ) {
    string Query = "SELECT * FROM " + Table;
    return SQLLib.GetDataTable( Query, null );
}

private static DataTable GetDataTable( string CMD, object[] Params ) {
    DataSet DS = new DataSet( );
    using ( MySqlConnection MSQCon = new MySqlConnection( SQLLib.sqlconstr ) ) {
        try { MSQCon.Open( ); } catch ( MySqlException ) {
            Console.WriteLine( "Failed to open SQL Connection" );
        }
        MySqlDataAdapter MSQDA = new MySqlDataAdapter( );
        MySqlCommand MSQCom = new MySqlCommand( CMD, MSQCon );

        if (Params != null) for ( int c = 0; c < Params.Length; c++ )
            MSQCom.Parameters.AddWithValue( "@param_val_" + ( c + 1 ), Params[c] );
        MSQCom.CommandType = CommandType.Text;
        MSQDA.SelectCommand = MSQCom;
        MSQDA.Fill( DS );
        MSQCon.Close( );
    } try { return DS.Tables[0];
    } catch ( IndexOutOfRangeException ) { return SQLLib.GetDataTable( CMD, Params ); }
}

So, now, is there a method through which I can update the source table with the DataTable I keep locally until it's ready to be committed?

CularBytes · Accepted Answer · 2014-12-23T16:09:28.897

It is not about keeping the connection open, you just have to use the Command Builder, it's the same with MySql I believe.

private MySqlDataAdapter adapt;
private DataSet someDataSet;
someDataSet = new DataSet();

    public DataSet GetCustomerData(int customerId)
    {
        using(MySqlConnection connect = new MySqlConnection(ConnString))
        {
            connect.Open();
            MySqlCommand comm = new MySqlCommand("SELECT * FROM customers WHERE Id = @0", connect);
            someDataSet.Tables.Add("CustomersTable");
            comm.Parameters.AddWithValue("@0", customerId);
            adapt.SelectCommand = comm;
            adapt.Fill(someDataSet.Tables["CustomersTable"]);
        }

        return someDataSet;
   }

Now for the updating: you could use a new adapter as well, but then you have to give it a select command, based on that the commandbuilder will make the Insert,Update and Delete commands.

    public void UpdateTable(DataTable table, int customerId)
    {
        using (MySqlConnection connect = new MySqlConnection(ConnString))
        {
            connect.Open();
            MySqlCommandBuilder commbuilder = new MySqlCommandBuilder(adapt);
            adapt.SelectCommand = new MySqlCommand("SELECT * FROM customers WHERE Id = "+customerId, connect); //or use parameters.addwithvalue
            adapt.Update(table);
        }
    }

anytime, are there any errors in my code? didn't really test it since I have no MySql connection — CularBytes, Dec 20 '14 at 19:09
I don't know but the whole point of MySqlCommandBuilder. That has the method .GetUpdateCommand() which I can set as the Adapters .Update command and then when I call .Update over the entire dataset.tables collection, it should do the job. I still need to test it but it feels like the right path. — Will, Dec 20 '14 at 19:15
In your code you have a string 'Table' for the table name? If this doesn't work then you can provide the table name when updating. `adapt.Update(table,tableName);` it has to be static I believe — CularBytes, Dec 20 '14 at 19:56
I do a for-each loop for every table in the dataset. when I finish getting the tables I assign each one in the set its proper name, so it should be simple enough to just say Update(Table, Table.Name) — Will, Dec 20 '14 at 20:13

score 0 · Answer 2 · answered Dec 20 '14 at 18:08

0

You should create a MySqlCommand for updating your database and set it as your MySqlDataAdapter's UpdateCommand property value. Exactly as you do this for the SelectCommand.

When you are ready to commit, you just call the Update() method of your MySqlDataAdapter.

answered Dec 20 '14 at 18:08

dymanoid

14,771
4
36
64

Okay, but how is that going to work with the connection closed? – Will Dec 20 '14 at 18:10
Obviously, you should first open it, as you do in your `GetDataTable()` method. Create a new `UpdateDataTable()` method for this, or store your connection instance somewhere keeping it open while you need it. – dymanoid Dec 20 '14 at 18:13

How do I commit the changes I have made to a DataTable to the Table from which I grabbed it?

2 Answers2

Linked