ADO.NET - Best way to use the mighty SELECT
-
I´ve used the DataReader, DataSet, ExecuteScalar, etc. However Im not sure what of these is the best solution for the following problem: I want to use a simple SELECT and loop through the data, and update some of the data but make sure no two persons can UPDATE some of the selected transactions at the same time. I could loop through the data with ExecuteScalar or DataReader, but would it allow two users to update the same data at the same time? (without locking the database) Thanks in advance :) BinaryHead
-
I´ve used the DataReader, DataSet, ExecuteScalar, etc. However Im not sure what of these is the best solution for the following problem: I want to use a simple SELECT and loop through the data, and update some of the data but make sure no two persons can UPDATE some of the selected transactions at the same time. I could loop through the data with ExecuteScalar or DataReader, but would it allow two users to update the same data at the same time? (without locking the database) Thanks in advance :) BinaryHead
BinaryHead wrote: make sure no two persons can UPDATE some of the selected transactions at the same time. transactions? You mean rows, surely. Personally I'd create a stored procedure for this sort of thing (if possible) which has the added advantage of a single round trip to the database. Anyway, you can create a transaction and run multiple commands in the same transaction and then commit it. There is an example here on MSDN[^]
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
I´ve used the DataReader, DataSet, ExecuteScalar, etc. However Im not sure what of these is the best solution for the following problem: I want to use a simple SELECT and loop through the data, and update some of the data but make sure no two persons can UPDATE some of the selected transactions at the same time. I could loop through the data with ExecuteScalar or DataReader, but would it allow two users to update the same data at the same time? (without locking the database) Thanks in advance :) BinaryHead
There are many different ways of ensuring that the rows are not modified while you are accessing them. But, let me ask you first - is it feasable that the data in the db can be altered by means other than your application? Ensuring optimitic locking inside your application only works if everyone is using your application to access the data. If others might have access to the data outside of your application, you'll most likely want to push all your updates into a transaction and set the isolation level accordingingly.
-
There are many different ways of ensuring that the rows are not modified while you are accessing them. But, let me ask you first - is it feasable that the data in the db can be altered by means other than your application? Ensuring optimitic locking inside your application only works if everyone is using your application to access the data. If others might have access to the data outside of your application, you'll most likely want to push all your updates into a transaction and set the isolation level accordingingly.
There could be many people accessing the same data at the same time using this application (many instances/copies of my program) Im trying to build (but not accessing the data with some third party application). Im just scared of one thing: - If two different programs, A and B, are running at the same time(both using the same database) and A requests some rows with the DataReader (f.ex. SELECT... where some Boolean == false) and at the same time B updates some of the SAME rows (UPDATE...where ..SET Boolean == true). Is the DataREader in A smart enough to SELECT some rows, and then while looping through them, figure out that one of the rows has been changed (Boolean == true) so it should skip it in the loop? (or are the rows that the DataReader returns just copied into memory and then looped blindly like the DataSet with Adapter.Fill() / Adapter.Update()? ).