tableadapters, where is my concurrency exception?

  • Thread starter Thread starter troy
  • Start date Start date
T

troy

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?ID=3405596d-4556-4aa8-be12-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
 
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

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?ID=3405596d-4556-4aa8-be12-d7c12bbb3726
Click to expand...

[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
 
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
 
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

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


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.
Click to expand...
Click to expand...
 
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
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

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


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.
Click to expand...
Click to expand...
Click to expand...
 
Than show your code where you are updating.

Cor

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
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

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

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.
Click to expand...
Click to expand...
Click to expand...
 
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:

Than show your code where you are updating.

Cor

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
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

<[email protected]> schreef in bericht
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

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.
Click to expand...
Click to expand...
Click to expand...
 
oops. I accidentally posted that without the testdataset.designer.cs
snippet. Here it is:

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

this._adapter.UpdateCommand.CommandText = "UPDATE [dbo].[Test] SET
[count] = @count WHERE (([TestId] = @Original_TestId) AND" +
" ([count] = @Original_count));\r\nSELECT TestId, count FROM Test
WHERE (TestId = @" +
"TestId)";
 
With your update statement you won't get an exception;
Your where:
WHERE (TestId = @Original_TestId) AND (count = @Original_count)

So, the update only takes place on the row when testid didn't change and
count didn't change.

So, when you encounter a concurrency problem (when one or both have
changed) the count of rows that are updated will be 0 -> no row has been
updated.

When another value in the row has changed (assumed that the table
contains more than 2 cols), your update will succeed, again without a
concurrency exception.

This is because sqlclient works with disconnected recordsets (the reason
you need an update statement); to work with optimistic concurrency with
real concurrency checks, you need a connected recordset, so your updates
can use the rowversion.

An article explaning this:
http://www.ftponline.com/vsm/2003_10/magazine/columns/databasedesign

But, you can check your concurrency based on the columnvalues; if there
is no row containing those values, a concurrency error is raised (I did
not test this):

IF NOT EXISTS(SELECT * FROM TEST WHERE (TestId = @Original_TestId) AND
(count = @Original_count))
BEGIN
RAISERROR(16934,10,16)
--Optimistic concurrency check failed.
--The row was modified outside of this cursor.
END
ELSE
BEGIN
UPDATE Test
SET count = @count
WHERE (TestId = @Original_TestId) AND (count = @Original_count)
END



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
Click to expand...
 
Troy,

Something crazy is happening, I will probably search for this tomorrow.

There are no update commands anymore generated on my computer.

(Maybe am I blind now, so maybe it goes tomorrow better)

I did it with other databases, with VB and with C#, every time the same.

Cor
 
Did you remember to set your primary key? I heard that without it the
update does not get generated.

Troy
Troy,

Something crazy is happening, I will probably search for this tomorrow.

There are no update commands anymore generated on my computer.
Click to expand...
[snip]
 
Troy,

That I tried this morning, I had forget it, but I added it and in the table
and in the dataset but had the same result, I will go on. But probably it
takes more time, I am almost sure that it almost was going before.

Cor

Did you remember to set your primary key? I heard that without it the
update does not get generated.

Troy
Troy,

Something crazy is happening, I will probably search for this tomorrow.

There are no update commands anymore generated on my computer.
Click to expand...
[snip]
Click to expand...
 
Troy,

I see the update command again, but I have to go soon, so probably you would
have to wait a while.

The strange thing is that I had installed yesterday temporally VB6, I have
deinstalled and as far as I can see now everythings goes fine. There should
not be a connection however maybe to watch for in this newsgroup. I have
more seen messages "there is no correct update command" from which I thought
it was impossible.

Cor

Did you remember to set your primary key? I heard that without it the
update does not get generated.

Troy
Troy,

Something crazy is happening, I will probably search for this tomorrow.

There are no update commands anymore generated on my computer.
Click to expand...
[snip]
Click to expand...
 
Troy ,

I don't think so,

If I was using as you had showed, (what was fine for me, I never did it that
way), than the update was *not* processed. Because I always use a datatable
attached to the Server I have attached that created one.

In that case when I set a breakpoint before the update, changed it as you
wrote and went on, I got a concurrency error (nd when I did not change it
not)

"Inbreuk op gelijktijdige uitvoering: 0 van de verwachte 1 records zijn
beïnvloed door de UpdateCommand."

If you don't believe me, ask somebody who speaks Dutch

To summarize with the not connected mdf file the update was completely not
processed.

I did it now in a windowform, but that did it either with a standalone mdf
as I have tested this.
I have not tested it with a consoleapplication but I expect the same
results.

I hope you get the same result.

Cor
 
Hi,

I have the sent the bug to the feedback center.

Cor

Cor Ligthert said:
Troy ,

I don't think so,

If I was using as you had showed, (what was fine for me, I never did it
that way), than the update was *not* processed. Because I always use a
datatable attached to the Server I have attached that created one.

In that case when I set a breakpoint before the update, changed it as you
wrote and went on, I got a concurrency error (nd when I did not change it
not)

"Inbreuk op gelijktijdige uitvoering: 0 van de verwachte 1 records zijn
beïnvloed door de UpdateCommand."

If you don't believe me, ask somebody who speaks Dutch

To summarize with the not connected mdf file the update was completely not
processed.

I did it now in a windowform, but that did it either with a standalone mdf
as I have tested this.
I have not tested it with a consoleapplication but I expect the same
results.

I hope you get the same result.

Cor

I think I found the problem. See:
http://search.support.microsoft.com/kb/915880/en-us?spid=8291&sid=200
I haven't been able to get the hotfix yet to try it though.

[snip]
Click to expand...
Click to expand...
 
Hi Cor, I just updated my computer with the hotfix 915880 from
Microsoft and you are correct, It doesn't work. FYI, I am Dutch!
Although I was born in Canada and only speak english :(

Troy
Troy ,

I don't think so,

If I was using as you had showed, (what was fine for me, I never did it that
way), than the update was *not* processed. Because I always use a datatable
attached to the Server I have attached that created one.

In that case when I set a breakpoint before the update, changed it as you
wrote and went on, I got a concurrency error (nd when I did not change it
not)

"Inbreuk op gelijktijdige uitvoering: 0 van de verwachte 1 records zijn
beïnvloed door de UpdateCommand."

If you don't believe me, ask somebody who speaks Dutch

To summarize with the not connected mdf file the update was completely not
processed.

I did it now in a windowform, but that did it either with a standalone mdf
as I have tested this.
I have not tested it with a consoleapplication but I expect the same
results.

I hope you get the same result.

Cor

I think I found the problem. See:
http://search.support.microsoft.com/kb/915880/en-us?spid=8291&sid=200
I haven't been able to get the hotfix yet to try it though.

[snip]
Click to expand...
Click to expand...
 
Back
Top