Multiuser database

[Vanessa]

Hi!

I started working with .NET and I have some doubts about how to work with
multiuser database. I am using VS2008 with MySQL through ODBC for Windows
Form.

1. Is it correct to create a DataSet with all tables in the form load? And
how can I be notified if some other modifies the database, to update the
DataSet?
2. What is better: to update the database via DataSet or via SQL command?
3. How can I treat concurrency, i.e., update on the same record by 2 users?

The application works on conected and disconected way.

Thank you!
Vanessa

 

[Nicholas Paldino [.NET/C# MVP]]

Vanessa,

1. You have to handle that yourself. The DataSet is a disconnected set of
data, there is no tie to the underlying database. If someone changes a
value in the database, you are responsible for notifying your application of
the change. As for whether or not it is correct to do so in the form load,
that is up to you, and the semantics of your program.

2. Well, DataSets can't do any updates. They are merely data containers.
When you pass the DataSet to a DataAdapter, you set the SelectCommand,
InsertCommand, DeleteCommand, and UpdateCommand to command parameters that
represent statements (in this case, SQL) or stored procedures which will be
called on for every change that was made in the DataSet.

3. Given the nature of the DataSet (disconnected), you are going to want to
go with some sort of optimistic concurrency, such as a timestamp, a GUID, or
a date. Basically, when you load the record, you make sure this value is
loaded as well. Then, when you write the record back, you check to make
sure that the value is the same. If it is, then you can change the record,
if not, then you know someone else has changed it, and you should abort the
operation (and rollback the transaction, if there is one). Of course, after
you make the change, you make sure that the timestamp is updated (if it is a
guid or date, then you have to do this yourself, timestamp columns should
auto-update on change).

 

[Vanessa]

Thank you Nicholas!

About item 1, what technique should I use to be notified about changes in
the database?

Vanessa

Vanessa,

1. You have to handle that yourself. The DataSet is a disconnected set of
data, there is no tie to the underlying database. If someone changes a
value in the database, you are responsible for notifying your application of
the change. As for whether or not it is correct to do so in the form load,
that is up to you, and the semantics of your program.

2. Well, DataSets can't do any updates. They are merely data containers.
When you pass the DataSet to a DataAdapter, you set the SelectCommand,
InsertCommand, DeleteCommand, and UpdateCommand to command parameters that
represent statements (in this case, SQL) or stored procedures which will be
called on for every change that was made in the DataSet.

3. Given the nature of the DataSet (disconnected), you are going to want to
go with some sort of optimistic concurrency, such as a timestamp, a GUID, or
a date. Basically, when you load the record, you make sure this value is
loaded as well. Then, when you write the record back, you check to make
sure that the value is the same. If it is, then you can change the record,
if not, then you know someone else has changed it, and you should abort the
operation (and rollback the transaction, if there is one). Of course, after
you make the change, you make sure that the timestamp is updated (if it is a
guid or date, then you have to do this yourself, timestamp columns should
auto-update on change).


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi!

I started working with .NET and I have some doubts about how to work with
multiuser database. I am using VS2008 with MySQL through ODBC for Windows
Form.

1. Is it correct to create a DataSet with all tables in the form load? And
how can I be notified if some other modifies the database, to update the
DataSet?
2. What is better: to update the database via DataSet or via SQL command?
3. How can I treat concurrency, i.e., update on the same record by 2
users?

The application works on conected and disconected way.

Thank you!
Vanessa