DataAdapter call ExecuteReader instead of ExecuteNonQuery

[Lionel LASKE]

I'm working on my own implementation of an ADO.NET Data Provider
derived from IDbXXXX classes and MSDN sample
(http://msdn.microsoft.com/library/d...ide/html/cpconImplementingNETDataProvider.asp).
All is fine when I execute simple Command and DataReader but I got an
error with DataAdapter.
Running of SelectCmd and InsertCmd work fine but each UpdateCmd or
DeleteCmd thrown a DBConcurrencyException.
It seems that problem is that DataAdapter call ExecuteReader instead
of ExecuteNonQuery on InsertCmd, UpdateCmd and DeleteCmd. So, number
of row affected is probably incorrect.
Do I miss something or is it an usual way of working for a DataAdapter
?
Why ExecuteReader is called for an Update and a Delete without any
result expected ?
Thanks in advance for your help or advices.

Lionel.

 

[Miha Markic [MVP C#]]

Hi Lionel,

I guess the reader part is because of concurrency issues (to load timestamp
or autoinc key).
See help on
IDbCommand.UpdatedRowSource Property

 

[Lionel LASKE]

Thanks for your answer but my DataSet don't have any autoinc or timestamp
key and I have just one access on my table (local database).
I tried all enum value in System.Data.UpdateRowSource and I have the same
exception thrown: DBConcurrencyException.
I'm really wonder why for an UPDATE command DbDataAdapter.Update calls my
command with ExecuteReader instead of ExecuteNonQuery. Here is my source
code:


// ... On init

dataAdapter = new MyADOProvider.MyDataAdapter(selectCmd);
dataAdapter.UpdateCommand = new MyADOProvider.MyCommand("MyProc
@action='UPDATE',", cnx);
dataAdapter.UpdateCommand.CommandType = CommandType.StoredProcedure;
param = dataAdapter.UpdateCommand.CreateParameter();
param.DbType = DbType.Int32;
param.ParameterName = "@code";
dataAdapter.UpdateCommand.Parameters.Add(param);
param = dataAdapter.UpdateCommand.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = "@name";
dataAdapter.UpdateCommand.Parameters.Add(param);

dataSet = new DataSet();
dataAdapter.Fill(dataSet, "results");


//... On Update button

dataAdapter.Update(dataSet, "results");

My guess is that with a ExecuteNonQuery, you can have a "Number of rows
affected" but you can't have this number with a ExecuteReader.
Is someone know the source code of DbDataAdapter.Update ?

Lionel.

Hi Lionel,

I guess the reader part is because of concurrency issues (to load
timestamp or autoinc key).
See help on
IDbCommand.UpdatedRowSource Property

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

I'm working on my own implementation of an ADO.NET Data Provider
derived from IDbXXXX classes and MSDN sample
(http://msdn.microsoft.com/library/d...ide/html/cpconImplementingNETDataProvider.asp).
All is fine when I execute simple Command and DataReader but I got an
error with DataAdapter.
Running of SelectCmd and InsertCmd work fine but each UpdateCmd or
DeleteCmd thrown a DBConcurrencyException.
It seems that problem is that DataAdapter call ExecuteReader instead
of ExecuteNonQuery on InsertCmd, UpdateCmd and DeleteCmd. So, number
of row affected is probably incorrect.
Do I miss something or is it an usual way of working for a DataAdapter
?
Why ExecuteReader is called for an Update and a Delete without any
result expected ?
Thanks in advance for your help or advices.

Lionel.

 

[Miha Markic [MVP C#]]

Thanks for your answer but my DataSet don't have any autoinc or timestamp
key and I have just one access on my table (local database).
I tried all enum value in System.Data.UpdateRowSource and I have the same
exception thrown: DBConcurrencyException.
I'm really wonder why for an UPDATE command DbDataAdapter.Update calls my
command with ExecuteReader instead of ExecuteNonQuery. Here is my source
code:

Hmm, I don't see any UpdateRowSource setting in your code.

// ... On init

dataAdapter = new MyADOProvider.MyDataAdapter(selectCmd);
dataAdapter.UpdateCommand = new MyADOProvider.MyCommand("MyProc
@action='UPDATE',", cnx);
dataAdapter.UpdateCommand.CommandType = CommandType.StoredProcedure;
param = dataAdapter.UpdateCommand.CreateParameter();
param.DbType = DbType.Int32;
param.ParameterName = "@code";
dataAdapter.UpdateCommand.Parameters.Add(param);
param = dataAdapter.UpdateCommand.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = "@name";
dataAdapter.UpdateCommand.Parameters.Add(param);

dataSet = new DataSet();
dataAdapter.Fill(dataSet, "results");


//... On Update button

dataAdapter.Update(dataSet, "results");

My guess is that with a ExecuteNonQuery, you can have a "Number of rows
affected" but you can't have this number with a ExecuteReader.
Is someone know the source code of DbDataAdapter.Update ?

Sure, try with Reflector.
http://www.aisto.com/roeder/dotnet/

 

[Lionel LASKE]

Hmm, I don't see any UpdateRowSource setting in your code.

Of course, I deleted it before publishing here. You can believe me : I made
the test

Sure, try with Reflector.
http://www.aisto.com/roeder/dotnet/

Waaooo!!! Reflector is really a great tool but my first look on source code
for System.Data.Common.DbDataAdapter.Update give me more new questions than
anwser... :-(

 

[Miha Markic [MVP C#]]

Waaooo!!! Reflector is really a great tool

Indeed

but my first look on source code

for System.Data.Common.DbDataAdapter.Update give me more new questions
than anwser... :-(

Not trivial, eh-