How to use RowVersion with TableAdapters

Zero-G. · Post by **Zero-G.** » Wed 02 May 2007 09:00

Hey

I do use the new v4 of mySQL.NET drivers. - After some trying around, I decided to use tableAdapters with DataSets & the DataSet wizzard.

So, I create a network application and now want to know, how I can use a rowversion (timestamp) to see, if the Data has been changed since the client got it on his screen.

Hope you can help me - THX

Serious · Post by **Serious** » Thu 03 May 2007 09:51

You should create table with TIMESTAMP field, then in DataSet Wizard configure update command in data adapter (press 'Configure' button at the 'Setup Individual Tables' page to show 'Table Adapter Editor' dialog) and select the key field and timestamp field as updating fields ('Command generator' tab).

Table example:

Code: Select all

CREATE TABLE row_version (
  id INT(11) AUTO_INCREMENT,
  value VARCHAR(20) DEFAULT NULL,
  version TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
)

Generated update command example:

Code: Select all

UPDATE `row_version` SET `id` = :id, `value` = :value, `version` = :version WHERE ((`id` = :Original_id) AND (`version` = :Original_version))

Zero-G. · Post by **Zero-G.** » Thu 03 May 2007 11:06

Hey

This sounds great! - And how can I examine, if the data has been changed?
Do you have an code example based on the example from your last post!?

THX

Serious · Post by **Serious** » Thu 03 May 2007 12:16

My code provides the optimistic locking example. When trying to update/delete a row that has been changed by another user you'll get a System.Data.DBConcurrencyException.

Zero-G. · Post by **Zero-G.** » Thu 03 May 2007 12:41

Hey

THX! - This sounds great

I have installed Vista on my PC. - Now I have the problem, that after using the DataSet Wizzard and try to drag/drop a database to my form, NO TableAdapter & No code is generated. - I also can not find a TableAdapter. What can be the problem? AND where to find the TableAdapter to set by manual?

Serious · Post by **Serious** » Thu 03 May 2007 12:55

DataSet Wizard uses MySqlDataSetGenerator custom tool by default (see 'Use provider-specific code generator' option on the 'Select DataSet' page). This tool generates configured MySqlDataSet and MySqlDataTable descendants, not TableAdapters.

To change this behaviour, uncheck option I've pointed or manually set the 'Custom Tool' option of .xsd file to 'MSDataSetGenerator' value. Classes will be automatically regenerated and TableAdapters will be created. Note that in this case you will not be able to use MySqlDataSet and MySqlDataTable features.

Zero-G. · Post by **Zero-G.** » Thu 03 May 2007 14:45

Hey

OK - I got it. - It works really fine

One more question:

When I use the DataAdapter, how do I have to handle parameters?

In the TableAdapter, I only had to input the parameters with the fill method.

THX

Serious · Post by **Serious** » Thu 03 May 2007 15:57

DataAdapter updates database depending on DataTable row state, it cannot handle separate rows as TableAdapter does. In this case parameters are generated automatically.

Zero-G. · Post by **Zero-G.** » Fri 04 May 2007 06:35

Hey

When I have a SQL Statement like
Select * from Test where ID = :Number

how can I handle the :Number in the code?

THX

Serious · Post by **Serious** » Fri 04 May 2007 09:54

When using MySqlDataAdapter, you can access select command parameters through MySqlDataAdapter.SelectCommand property.