A thorny issue regarding composite keys in a typed dataset

[Guest]

Hello group:

Given a data table in a typed dataset with a composite key made up of 2
integer fields, what approaches have people taken with regard to editing
them? The following might help describe the situation better.

Situation:

I have a grid view displaying some customer configuration information about
which county they live in, product shipping cost adjustments, etc... The
grid view can be edited and is bound to an object data source which brokers
communication to a web service. The data table 's key is made up of a
customer id (cannot be edited by the grid obviously) and a county id. The
county id is edited as a dropdownlist when the grid goes into edit mode.
When the changes are pushed to the object data source, its update method
retrieves the row in memory that is being edited, changes the relevant
fields, including county id if it is changed, and pushes those changes to the
webservice as a diff gram.

The problem comes when the adapter on the web service goes to update the
changed row via the 'update' method. Since the composite key is made up of
both the customer id (no problems there) and the county id (which *might* be
changed depending on whether or not the user changed the county value),
there is a possibility the stored proc will never match the row being updated
on account of the fact that the PK is now different (e.g. - a different
county id).

Possible resolution:

Does anyone have any suggestions for dealing with this problem? Some things
that jump to mind for me:

1) Modify the sp and the datatable by adding a column to the table that is
the equivalent of old county id or something and adding a parameter to the sp
that uses the old id value to find the row to update.

2) Not handling this particular update via an adapter, but instead through
a series of synchronous calls as each client-side row is changed.

3) Add a surrogate key (autonumbered) in both the db and the typed dataset
and use this for additions/changes on the client

4) Add the data column from option 1 client-side thus making it uptyped,
and bind to the parameter in the webservice.

5) On an edit of the county id, mark the row being edited for deletion and
add a new row with the changed values then push the changes to the web
service.

Note that I can get the dba to modify whatever I need to be done to solve
this issue since the product isn't to market yet.

Although I am leaning toward option 1, there are some things I don't like
about it. For one, outside callers not going thru our ui might find this new
column confusing. Also, it seems like it shouldn't be necessary to address
something like this in this fashion. Option 2 doesn't appeal to me at all
since the customer config change is really only one of several possible
changes to the dataset and I'd really like to keep them together. Option 3
would seem to be the least invasive, but I will be bothering the data
architect with this one. Once again, I don't like option 4 because outside
callers will have a problem comprehending the meaning. Although option 5,
might be confusing, it seems the cleanest of my proposed ideas because we
could explain that to outside callers as 'you cannot edit the county id of a
config record, only delete it and re-add a new one...'

Anyone have any other suggestions?

Thanks all,

Drew

 

[David Browne]

Hello group:

Given a data table in a typed dataset with a composite key made up of 2
integer fields, what approaches have people taken with regard to editing
them? The following might help describe the situation better.

Situation:

I have a grid view displaying some customer configuration information
about
which county they live in, product shipping cost adjustments, etc... The
grid view can be edited and is bound to an object data source which
brokers
communication to a web service. The data table 's key is made up of a
customer id (cannot be edited by the grid obviously) and a county id. The
county id is edited as a dropdownlist when the grid goes into edit mode.
When the changes are pushed to the object data source, its update method
retrieves the row in memory that is being edited, changes the relevant
fields, including county id if it is changed, and pushes those changes to
the
webservice as a diff gram.

The problem comes when the adapter on the web service goes to update the
changed row via the 'update' method. Since the composite key is made up
of
both the customer id (no problems there) and the county id (which *might*
be
changed depending on whether or not the user changed the county value),
there is a possibility the stored proc will never match the row being
updated
on account of the fact that the PK is now different (e.g. - a different
county id).

Possible resolution:

Does anyone have any suggestions for dealing with this problem? Some
things
that jump to mind for me:

1) Modify the sp and the datatable by adding a column to the table that
is
the equivalent of old county id or something and adding a parameter to the
sp
that uses the old id value to find the row to update.

2) Not handling this particular update via an adapter, but instead
through
a series of synchronous calls as each client-side row is changed.

3) Add a surrogate key (autonumbered) in both the db and the typed
dataset
and use this for additions/changes on the client

4) Add the data column from option 1 client-side thus making it uptyped,
and bind to the parameter in the webservice.

5) On an edit of the county id, mark the row being edited for deletion
and
add a new row with the changed values then push the changes to the web
service.

Note that I can get the dba to modify whatever I need to be done to solve
this issue since the product isn't to market yet.

Although I am leaning toward option 1, there are some things I don't like
about it. For one, outside callers not going thru our ui might find this
new
column confusing. Also, it seems like it shouldn't be necessary to
address
something like this in this fashion. Option 2 doesn't appeal to me at all
since the customer config change is really only one of several possible
changes to the dataset and I'd really like to keep them together. Option
3
would seem to be the least invasive, but I will be bothering the data
architect with this one. Once again, I don't like option 4 because
outside
callers will have a problem comprehending the meaning. Although option 5,
might be confusing, it seems the cleanest of my proposed ideas because we
could explain that to outside callers as 'you cannot edit the county id of
a
config record, only delete it and re-add a new one...'

Anyone have any other suggestions?

If you find yourself needing to edit part of the primary key, you should
really ask yourself if you have the right relational model. What business
transaction does editing the key value acomplish?

I would go with Option 5. Editing a primary key value is conceptually the
same thing as deleting the row and adding a new one, so that seems like the
right thing to do.

David

 

[Guest]

David,

Yeah, I ended up going with option 5 and will probably stay with it despite
the downsides. (unless I hear a better approach )

With regard to the relational model, although I didn't design it, I agree
with the DA's interpretation of the model for the db. His thought process
was basically, when evaluating the possible candidate keys for this table,
the combination for customer id and county id are what the make the row
unique. This table is a child relation (1:n) to a customer info table. A
customer can have many different locations in many different counties, so the
composite was a good choice I believe, issues notwithstanding.

Thanks for your input,

Drew

 

[RobinS]

If you are keeping track of a list of objects, why not just add a property
for the original value, and fill it when you fill the values the first
time. Then when you issue the update, use the original value for the
parameter in the where clause and the new value for it to be set to. You
just need two parameters into your SP.

Assuming your SP looks something like this:

CREATE PROCEDURE dbo.CompanyCodesUpdate_sp
(
@CompanyCode nvarchar(2),
@CompanyCode_old nvarchar(2),
@CompanyDescr nvarchar(50)
)
AS
UPDATE CompanyCodes
SET CompanyDescr = @CompanyDescr
WHERE CompanyCode = @CompanyCode_old
/*SET NOCOUNT ON*/
RETURN

When setting up your parameters,

Dim cn As SqlConnection = New SqlConnection(connectionString)
cn.Open()
Dim cmd As SqlCommand = New SqlCommand()
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = storedProcedureName
cmd.Parameters.AddWithValue("@CompanyCode", myObj.CompanyCode)
cmd.Parameters.AddWithValue("@CompanyCode_old", myObj.CompanyCode_old)
cmd.Parameters.AddWithValue("@CompanyDescr", myObj.CompanyDesc)
cmd.ExecuteNonQuery()

Robin S.
--------------------