How to sync database with dataset?

Wizard_TPG · Post by **Wizard_TPG** » Mon 26 Sep 2005 04:42

Hi All,

I am trying to develop an app that gets data from a central mysql server and can then be used offline and, at a later date, can syncronize with the server.
If the user is connected to the net, this would be at one minute intervals.

After reading up on ADO.NET I believe that using a mysqlDataAdapter to dump the tables to a dataset (whcih can also be then dumped to xml for offline usage) would be the best way to approach it.

I created a small test dbase containing one table, which has an id column (unique auto increment) and a datemask (showing last date that each row was updated. The system uses the fill method to get the data to the dataset and the update method to upload any changes back to the database with no problems at all.

Where I am encountering difficulties is when the database data changes. Not the rows that I am updating.... but other rows eg. Diff user adds a new record.

I cannot seem to find any information on how to bring these changes back down to the dataset. I am sure there must be a way to do this and I have just missed it.

Can anyone provide some ideas or info on how to do this? I have been searching and trying everything I can with no luck and I am totatlly stuck

Serious · Post by **Serious** » Tue 27 Sep 2005 06:49

Just call MySqlDataAdapter.Fill() method any time you want to synchronize your DataSet with database.
See DataSet demo project in the %ProgramFiles%\CoreLab\MySQLDirect.NET\Samples folder.

Jammerj2000 · Post by **Jammerj2000** » Tue 27 Sep 2005 14:12

Would that be a correct statement? You normally use fill to get data from the server. Wouldn't you loose any local changes? Wouldn't you need to run a .Update, then a .Fill?

Serious · Post by **Serious** » Tue 27 Sep 2005 14:54

In context of previous topics I advised to call Fill() method after Update() method.

Wizard_TPG · Post by **Wizard_TPG** » Wed 28 Sep 2005 22:07

Fill would not seem to work for me.

Even in the event of adding new rows into the database, they would not fill down to the dataset.

I ended up manually using connected methods to parse tables that had altered datestamps since my last sync.
It works fine.

Wizard_TPG · Post by **Wizard_TPG** » Fri 30 Sep 2005 00:15

ok, I am still having troubles with this.

I have a table called "contact" in my dbase which has a Primary auto incrementing key of "id" and an index datetime "datestamp"

My dataset has a DataTable of same name with a primary key of "id" which has a seed of 0 and a seed increment of -1. (So that the created id values do not cause issues with the server created id values.

I am using the following code to sync....

systemCache is my dataset. dbTableName is a string of the table name.

Code: Select all

myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand("SELECT * FROM contact WHERE id = :id", myConn);
myAdapter.SelectCommand.Parameters.Add("id", MySqlType.Int, 0, "id"); 
					
myAdapter.InsertCommand = new MySqlCommand("INSERT INTO contact (firstname, lastname, datestamp) values (:firstname, :lastname, now()); " + 
"SELECT * FROM contact ORDER BY id desc LIMIT 0,1", myConn);
myAdapter.InsertCommand.Parameters.Add("firstname",		MySqlType.VarChar, 20, "firstname");
myAdapter.InsertCommand.Parameters.Add("lastname",		MySqlType.VarChar, 30, "lastname");

myAdapter.UpdateCommand = new MySqlCommand("UPDATE contact SET " +
"firstname = :firstname, " +
"lastname = :lastname, " +
"datestamp = now() "+
"WHERE id = :id and datestamp = :datestamp;" + 
"SELECT * FROM contact WHERE id = :id;", myConn);
myAdapter.UpdateCommand.Parameters.Add("id", MySqlType.Int, 0, "id"); 
myAdapter.UpdateCommand.Parameters.Add("firstname",		MySqlType.VarChar, 20, "firstname");
myAdapter.UpdateCommand.Parameters.Add("lastname",		MySqlType.VarChar, 30, "lastname");

myAdapter.DeleteCommand = new MySqlCommand("DELETE from contact WHERE id = :id;", myConn);
myAdapter.DeleteCommand.Parameters.Add("id", MySqlType.Int, 0, "id"); 

myAdapter.RowUpdated += new MySqlRowUpdatedEventHandler(MYSQL_RowUpdated);
myAdapter.Update(systemCache, dbTableName );  
systemCache.Tables[dbTableName ].AcceptChanges();
myAdapter.Fill(systemCache, dbTableName );

Now, the update function is working fine and is returning the inserted ids and the updated datestamps as you would expect.

If however, I edit a row in the dbase, or add a new row into the dbase manually (or delete rows), these changes are not filtering back down to the dataset.

To me this means that I am not doing something correct as fill is obviouslly not working.

Any idea as to why this would not work?
This should work right?

Wizard_TPG · Post by **Wizard_TPG** » Fri 30 Sep 2005 00:18

This is how the dataset table is created initially.

Code: Select all

string commandString = "SELECT * FROM contact";
MySqlDataAdapter myAdapter = new MySqlDataAdapter(commandString, myConn); 
myAdapter.Fill(systemCache,"contact"); 

//ensure primary key is initialised
systemCache.Tables["contact"].Columns["id"].AutoIncrementSeed = 0;
systemCache.Tables["contact"].Columns["id"].AutoIncrementStep = -1;
systemCache.Tables["contact"].Columns["id"].AutoIncrement = true;
systemCache.Tables["contact"].Columns["id"].Unique = true;
DataColumn[] keys = new DataColumn[1];
keys[0] = systemCache.Tables["contact"].Columns["id"];
systemCache.Tables["contact"].PrimaryKey = keys;

Any help would be appreciated

Serious · Post by **Serious** » Fri 30 Sep 2005 08:10

In disconnected ADO .NET architecture the common way to synchronize DataSet with data source is to call IDataAdapter.Fill() method. We don't develop enterprise applications, so we do not examine more complex schemas (with timestamps or anything else).

Here is sample code:

Code: Select all

    static void PrintTable(DataTable table) {

      Console.WriteLine();

      foreach (DataColumn col in table.Columns)
        Console.Write(col.ColumnName + ",");

      Console.WriteLine();

      foreach (DataRow row in table.Rows) {
        foreach (object obj in row.ItemArray)
          Console.Write(obj + ",");
        Console.WriteLine();
      }

      Console.WriteLine();
    }

    static void Main(string[] args) {

      MySqlConnection connection = new MySqlConnection("host=server;port=3307;database=test;user id=root;password=root;");
      connection.Open();
      MySqlDataAdapter adapter = new MySqlDataAdapter("select * from dept", connection);
      MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter);
      DataSet dataSet = new DataSet();
      adapter.Fill(dataSet);

      PrintTable(dataSet.Tables[0]);

      dataSet.Tables[0].Rows[0]["dname"] = "test_dname";
      adapter.Update(dataSet);
// another code modifies data source
      MySqlCommand command = new MySqlCommand("insert into dept values (100, 'a','b')", connection);
      command.ExecuteNonQuery();

      dataSet.Clear(); // clean data in data set
      adapter.Fill(dataSet);

      PrintTable(dataSet.Tables[0]);
    }

Sample output:

Code: Select all

DEPTNO,DNAME,LOC,
10,ACCOUNTING,NEW YORK,
20,RESEARCH,DALLAS,
30,SALES,CHICAGO,
40,OPERATIONS,BOSTON,


DEPTNO,DNAME,LOC,
10,test_dname,NEW YORK, <-- row from DataSet
20,RESEARCH,DALLAS,
30,SALES,CHICAGO,
40,OPERATIONS,BOSTON,
100,a,b, <-- row inserted by external code

Wizard_TPG · Post by **Wizard_TPG** » Sun 02 Oct 2005 23:39

ok, lets forget about hte timestamp column because, if I can get this to work I can do away with that anyways.

What I do need to maintain is the primary key "id" column.
The MS Documentation said to do what I have done (ie. a primary key in the dataset that seeds at 0 and auto-increments with -1) so that the dataset key will never conflict with a key in the database table.

Given a table with 3 columns, "id", "firstname", "lastname" where id is a primary key column. What insert statements will the commandBuilder create?

Will it be?
1) insert into mytable ('id', 'firstname', 'lastname') values (:id, :firstname, :lastname)
or
2) insert into mytable ('firstname', 'lastname') values (:firstname, :lastname); select id from mytable;

Wavyx · Post by **Wavyx** » Mon 03 Oct 2005 05:48

Here is my opinion:
usually, when I use auto-generated fields (like auto-increment) I do NOT pass any argument for an insert. I let the DBMS handle it for me.
But about the commandbuilder, I'm not sure... Just try to config one and let him populate the command strings then look

Serious · Post by **Serious** » Mon 03 Oct 2005 11:43

The simplest way to check behavior of command builder is using MySqlDataAdapter editor. Design-time features of MySQLDirect .NET use command builder in the same way as in common applications.

Note that MySqlCommandBuilder behavior differs in .NET Framework 1.x and in .NET Framework 2 (Microsoft changed some concepts).

Command builder does not generate 'select' statements in Insert/Update/Delete commands. However you can edit these commands manually after generating them in designer.