Using Data adapter's Update method

  • Thread starter Thread starter Marina
  • Start date Start date
M

Marina

Hi, consider the following scenario: A new row gets inserted into a
datatable. Some of the fields are filled in, others are left as NULL. Then
Update gets called using the SqlCommandBuilder to build the commands. The
database table this insert is going into has default values defined for
fields. So the fields that were not explicitly set, get actual values. This
same row is later edited and Update is called again - this time to update
the row. This of course fails, since the database row has the default
values for some fields, but the code's DataRow still has NULL's.

So it is clear why the problem occurs. What isn't clear, is a way to avoid
it.

The SqlCommandBuilder creates a WHERE clause that includes the original
values for every single column - not just the primary key. Shouldn't there
be an option to have the WHERE clause be generated based on primary key
columns only? This would be a really easy way to solve the problem -
unfortunately it isn't there.

Ok, so decided to try using MissingShemaAction.AddWithKey to get all the
primary key information, to maybe re-do the WHERE clause manually. BUT,
this downloads the fact that some columns are NOT NULL in the database.
However, it does not download what their default value is (even though
DataColumn has a DefaultValue property - it isn't being populated). So now,
you get an error even before a trip to the database is made, since the .NET
classes are checking the integrity of this field. Now why would one piece
of information be retrieved and not the other? (This is documented as such,
but doesn't make sense).

So really, the solution seems to have to manually define
update/insert/delete commands for each table. We are trying to create a
generic component that is capable of performing update of what could be one
of hundreds of tables, used in a variety of ways. Coding the update to work
with hundreds of tables, and keeping track of which table has what primary
key columns, and then manually creating all the statements seems a bit much?

Any thoughts or other suggestions?
 
Hi Marina,

A couple of ideas:
1. if you know the table's default values, why not just add them to the
appropriate columns in the newrow? Then your datatable would be in sync
with the back end;
2. refetch the datatable

I don't love either of these, but I think it will solve your problem.

HTH,

Bernie Yaeger
 
1) This defeats the purpose of having database defaults.
Additionally, some tables may have a large number of columns - many of which
are fine with default values.

2) Refetching the data is going to cause performance degradation, as the
database may be remote, thus every trip costly.

I was hoping someone would know of a method that was built in.

It feels like there is a minor detail missing, that if added in the
framework could have been very useful and time saving. Instead we are stuck
with a development/maintainence/performance nightmare?
 
I am usually not a fan of using the Wizards that come
with VS.NET for data access, but a few weeks ago I saw
somebody using them and noticed a couple of things that
you might find interesting for the problem that you are
having.

For example, when you define a SQL Data Adapter with the
Wizard there is an option to: "Use Optimistic
Concurrency" that I believe defines what the WHERE clause
for INSERT and UPDATE looks like.

There is also an option to "Refresh the Dataset" after an
INSERT or UPDATE. The option in the Wizard indicates that
this is good to retrieve identity column values *and*
default values. When you select this option, the INSERT
and UPDATE statements are suffixed with a SELECT
statement that brings back all the fields. Which I am not
sure is much better than a roundtrip, but it might be
better than the alternative.

Hector Correa




You probably don't want to use the Wizard, but take a
look at the properties t

-----Original Message-----
Hi, consider the following scenario: A new row gets inserted into a
datatable. Some of the fields are filled in, others are left as NULL. Then
Update gets called using the SqlCommandBuilder to build the commands. The
database table this insert is going into has default values defined for
fields. So the fields that were not explicitly set, get actual values. This
same row is later edited and Update is called again - this time to update
the row. This of course fails, since the database row has the default
values for some fields, but the code's DataRow still has NULL's.

So it is clear why the problem occurs. What isn't clear, is a way to avoid
it.

The SqlCommandBuilder creates a WHERE clause that includes the original
values for every single column - not just the primary key. Shouldn't there
be an option to have the WHERE clause be generated based on primary key
columns only? This would be a really easy way to solve the problem -
unfortunately it isn't there.

Ok, so decided to try using
Click to expand...
MissingShemaAction.AddWithKey to get all the
 
Hi Hector,

I have used the wizard often, especially for datagrid development, where I
use Component One's True DB Grid, and I always have those options checked,
but I never paid attention to them! You are so right! Thanks for the
observation.

Bernie Yaeger
 
Again, what we are working on is a generic component to perform updates on
what could be one of a number of tables.
Not only that, but whenever a table schema changes, we would have to go
through the wizard and re-generate everything.

These 2 factors would make using the wizard a maintenance nightmare.

The other thing is, it is only in some cases that a row in inserted, then
the same row is updated as i described. In other cases, rows are just
inserted. To do a re-retrieve on all of these, would be a huge performance
loss, since their values are not examined again. So for the 5% of cases
that are a problem, a whole lot of extra database work would be done.

I feel like all the suggestions people give are prefixed with 'this isn't
really a great solution'. It feels like something major has been left out.

Simply an option to create the WHERE clause on the primary keys only, would
solve this problem - hard to believe it's been left out!
 
Back
Top