DataSet or DataReader???

[Stevie]

I have a system I wrote in VB6 / SQL 2000 which I have to re-write / re-design in .Net (my first .net app).

This app gets data from the db via stored procs, and updates the db via stored procs. MANY users use this system. All data displayed in the front end is disconnected. The changes made in the front end are sent to the database and updated, but not before checks are made to ensure other users have not updated the data in the mean time.

Now from what I can tell, I can pretty much do this by just using the DataReader.

If I want to use the DataAdapter and DataSet how can I ensure I only update the db with my DataSet if no other users have updated the same records in the db with their DataSet?

Hope I explained that ok.

[lschaeff1]

You cannot use a datareader - it is for readonly - forward only data operations. You need to use a Data Adapter and Dataset. You can use the Update method of a dataset to check for changes to the datasource and you can lock records using the concurrency settings. I recommend looking up optimistic concurrency in MSDN library for more info.

LS

[Stevie]

I know a DataReader is read only, that is what I was saying.

I can do updates the same way I do in VB6 via stored procs.

What I was asking was

If I want to use the DataAdapter and DataSet how can I ensure I only update the db with my DataSet if no other users have updated the same records in the db with their DataSet?

I'm pretty sure locking won't cover what I'm after, as I'm talking about the situation where you access a record (read) then another user accesses the same record, changes it and saves it. The record you are now looking at is the same record but has different data, so if you were to make changes and save it, the changes the other user made would be overwritten.

[nswan]

there is something a flag called dbConncurency you set on the dataset i think. It throws an error that you need to trap if the data you have in the dataset has been changed between u getting it and wanting to update it.

there was a thread on here a little while ago about it. Do a search.

Nick

[Stevie]

Cheers

I'll have a look

[VBCrazyCoder]

Yes, if you are using a dataset, and try to update data that has been changed since you initially retrieved it, a Concurrency error will be thrown. You can handle this in your error handling, and can notify the user, and even ask if they wish to proceed or not.

I remember MSDN having some articles specifically on Datasets and this issue, but unfortunately I don't remember any links.

[Stevie]

The way I have it working in the old app at the moment is that when somebody goes into a record they get a token and when they update a record it the token is altered.

This means that if you are in a record then go to update it (and somebody else has already updated the record) the token you initially got will not match the current token, thus the system knows the record has been changed and prevents the user making (possibly tonnes of) changes that might be lost, and also refreshes the record before the user can make any changes.

Nicer way than saying to the user "Do you wish to drop changes, or overwrite etc..".

This to me would indicate that the best method would be to use the DataReader.