Hi, I wrote a very simple program to test concurrency with sql server
2005. I used the VS 2005 dataset designer wizard to create my dataset
and and tableadapter.

When I ran the program, I used the debugger to stop the code before the
tableadapter.update(row) method was called. I then ran the server
explorer and modified the data row and then continued with the program.
To my surprise, no error was thrown! The row was not updated(as
expected) because the where clause did not match.

How do I check for concurrency?

My test data table has two columns. the first column is an auto
incremented integer primary key. The second column is just and integer
field.

Here is my program:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ConcurrencyTest {
class Program {
static void Main(string[] args)
{
TestDataSet testDS = new TestDataSet();

TestDataSetTableAdapters.TestTableAdapter testTA = new
TestDataSetTableAdapters.TestTableAdapter();

TestDataSet.TestDataTable testDT = testTA.GetDataBy(1000);
TestDataSet.TestRow testRow = testDT[0];
testRow.count++;
try {
testTA.Update(testRow);
} catch (DBConcurrencyException e) {
String data = e.Message;
} catch (Exception e) {
String data = e.Message;
}
}
}
}

here is what the update looks like:

UPDATE Test
SET count = @count
WHERE (TestId = @Original_TestId) AND (count = @Original_count)

Troy

Troy,

To test the concurrency you have to use the original row matching the
current row in the database.

Have a look for that in the cmd.update in this sample. It is VB but that
does not change anything in a selectcommand.

http://www.vb-tips.com/dbpages.aspx?...2-d7c12bbb3726

In VS 2005 there are more methods because there is as well added a method to
check the timestamp.

But here you see the basic principle. Check if the original row was not
changed.

I hope this gives an idea,

Cor


<troy (AT) makaro (DOT) com> schreef in bericht
news:1156745465.452583.16210 (AT) m79g2000cwm (DOT) googlegroups.com...

Not sure I follow you, Isn't that what I am doing with the update
command:

UPDATE Test
SET count = @count
WHERE (TestId = @Original_TestId) AND (count = @Original_count)

Troy


Cor Ligthert [MVP] wrote:
[snip]

No one else has had this problem? Or can someone at least tell me if
they have tried this and it works for them?

Concurreny is pretty important stuff so its hard to believe I have come
across something with vs2005 datasets that no one else has run into.

Troy

Did you look at the update command on the page I have showed you?

I am sorry but I don't see any select in your sqlstring.

Cor

<troy (AT) makaro (DOT) com> schreef in bericht
news:1156823549.754342.112220 (AT) b28g2000cwb (DOT) googlegroups.com...

oh you mean the select after the update statement. That is in the xml
dataset file(remeber I am using the designer):

UPDATE [dbo].[Test] SET [count] = @count WHERE (([TestId] =
@Original_TestId) AND ([count] = @Original_count));
SELECT TestId, count FROM Test WHERE (TestId = @TestId)

I also tried:
UPDATE [dbo].[Test] SET [count] = @count WHERE (([TestId] =
@Original_TestId) AND ([count] = @Original_count));
SELECT TestId, count FROM Test WHERE (TestId = @TestId) and ([count] =
@count)

which didn't work either
I thought the select was only to return back fields that may have been
modied by the server such as auto increment fields like the primary
key?

Troy



Cor Ligthert [MVP] wrote:

Troy,

You mean that the designer has made for you an update string without
checking the original readed value to the current version in the database.
What designer did you use?

Cor

<troy (AT) makaro (DOT) com> schreef in bericht
news:1156834315.196322.43060 (AT) b28g2000cwb (DOT) googlegroups.com...

I'm using the vs2005 dataset designer. Just add a dataset to your
project and then drag a table onto the designer. If you click on your
primary table adapter and look at the updatecommand property you will
notice the update statement without the added select statement.
However, if you look at the xml generated you will see both the update
command followed by the ';select' command. I think this is fine.

All optimistic concurrency does is add more to the where clause. So
instead of saying 'where TestId = @original_TestId' it says 'where
TestId = @orignal_TestId and [count] = @original_count'. The select
command after the update command is for retrieving field changes such
as auto increment fields. I don't think the select command has anything
to do with optimistic concurrency. At any rate, the select command is
there and it still doesn't work.

Its important to note that optimistic concurrency is partially working
in that it does not overwrite the other persons changes. It just
doesn't throw a concurrency exception.

Troy

Cor Ligthert [MVP] wrote:

Than show your code where you are updating.

Cor

<troy (AT) makaro (DOT) com> schreef in bericht
news:1156836420.153694.234480 (AT) m79g2000cwm (DOT) googlegroups.com...

Hi Cor, my first post shows the code completely. Also, sorry about the
steps to recreate the problem but that is the simplest way. the
testdataset.xsd file is quite large and the generated designer code is
even larger.

Here it is again:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ConcurrencyTest {
class Program {
static void Main(string[] args)
{
TestDataSet testDS = new TestDataSet();

TestDataSetTableAdapters.TestTableAdapter testTA = new
TestDataSetTableAdapters.TestTableAdapter();

TestDataSet.TestDataTable testDT = testTA.GetDataBy(1000);
TestDataSet.TestRow testRow = testDT[0];
testRow.count++;
try {
int a = testTA.Update(testRow);
} catch (SqlException e) {
String data = e.Message;
} catch (DBConcurrencyException e) {
String data = e.Message;
} catch (Exception e) {
String data = e.Message;
}
}
}
}
Here are the exact steps I used to create my test in visual studion
2005:
1) create a new windows console program
2) go ito add> new item and choose 'sql database'
3) create a 'Test' table with two columns:
int TestId as primary key, auto increment with a seed of 1000.
no nulls.
int count no nulls
4) right click the new table in the server explorer and choose 'show
table data'. Use that to create one row and set count to say '10'
5) go into add> new item and choose dataset. You will get a dataset
designer.
6) go into server explorer and drag your table onto the dataset
designer. This will create your default table adapter with the proper
update statement. in the dataset designer if you right click on the
TestTableAdapter you will be able to see the update command inthe
properties window. Also, if you look at the testdataset.xsd file with
an xml editor you will see the updatecommand followed by the select
command.
7) right click on the testTableAdapter and choose 'add query'. enter
the sql for the update:
SELECT TestId, count FROM dbo.Test where TestId = @testId
This generates the gGetDataBy method seen in my code example.

8) testing... use the debugger to stop before the 'int a =
testTA.Update(testRow)'
9) use the server explorer to modify the row and change the count to
something else.
10) step through the 'int a = testTA.Update(testRow)'. No concurrency
error is thrown!

FYI: a snippet of the testdataset.designer.cs:


Cor Ligthert [MVP] wrote: