D
Dan
I'm having strange errors with a C# app using form controls bound to a typed
dataset, which holds a parent-table with a single record and a number of
children tables with many records. As I'm new to ADO.NET, first of all I'd
like to know if I'm doing something wrong in my general approach. Here's a
simplified scenario, it's a bit long but I hope it can be useful to newbies
like me, facing the identity problems when updating a dataset. Thank you
very much for your patience!
(a) I use MSDE and the corresponding SqlClient namespace objects. All the
identity values in my tables are autonumber; in the dataset, autonumber seed
and step are set to -1 after their construction, so that the records added
in the "in memory" dataset will be granted to have a different identity from
any database record.
(b) All the insert commands use an output parameter (@newID) set to the new
identity value, e.g. the T-SQL code looks like:
ALTER PROCEDURE AnInsertCommand
(
....
@newID int OUTPUT
)
AS
INSERT INTO ... ;
SET @newID = SCOPE_IDENTITY()
The corresponding command objects include this @newID in their params
collection and map it to the table primary key field, so that when the
command is executed the dataset table will be updated to hold the "real"
autonumber value assigned by database (thus replacing the negative value
used in-memory).
(c) After generating the dataset, I have manually added the required
required foreign-key relations to it using the VS dataset designer, so that
the parent/child relationship are set up correctly: each primary key in a
child table is linked to the corresponding foreign key in the parent table.
(d) Let's say the parent table in dataset is "Customer", and the child table
is "Country". Customer has a countryID acting as the foreign key to the
Country child table. In my application, the user selects a single customer
to edit, or wants to enter a new customer; the form shows his data in bound
controls, and the data for child tables are typically shown in combo boxes
(e.g. a list of countries the user can select from). Thus, the dataset will
always contain a single parent record (=1 customer) and all the children
records (=countries list). The user can also add new countries when editing
a single customer record.
(e) When data are to be stored into database, the update policy is:
1) call BindingContext EndCurrentEdit for each table in the dataset, e.g.:
BindingContext[myDataSet, "Table Name"].EndCurrentEdit();
2) open the connection to database and begin a transaction
3) call Update for each CHILD table for NEW records only, e.g.:
countryDataAdapter.Update(myDataSet.TCountry.Select("","",DataViewRowState.A
dded));
this inserts the newly added countries only and updates the dataset Country
table so that the autonumber values reflect the "real" (non-negative) values
assigned by database; thus, a countryID = -10 becomes e.g. 67; also, the
relationships I have setup in the dataset cascade this update to the linked
parent table, so that the foreign key -10 becomes 67 too.
4) call Update for the PARENT table for NEW or MODIFIED records only
(deleted records will be processed later);
5) call Update for each CHILD table for all other records (e.g. MODIFIED or
DELETED);
6) call Update for the PARENT table for DELETED records.
7) commit the transaction and call AcceptChanges for the whole dataset.
8) close the connection anyway (in a finally {} block; in the catch{} block
the transaction is rolled back).
Now, this seems to work fine: I edit and add records, and autonumber values
are treated as expected and their changes propagated to the parent as
required. Here's the problem: when I'm editing an existing parent record and
add a new child record (e.g. a new country) and then store data, all works
fine; when I create a NEW parent record and do the same, the Update method
for the child table raises an "Object reference not set to instance of an
object" exception: as far as I can check with the debugger, all the values
in the dataset are as expected: the parent record has a negative ID, as for
the newly added child record, and both are linked correctly
(parent.countryID = child.countryID). As everything works when updating an
existing parent record, I'd expect the code to work for a new record too, as
the only difference is that a new record has a negative ID in the parent
table (which anyway should not matter for updating the child table, which is
linked via a foreign key).
Could anyone tell me if anything is "philosophically" wrong with this
approach? I cannot understand the reason for such errors...
dataset, which holds a parent-table with a single record and a number of
children tables with many records. As I'm new to ADO.NET, first of all I'd
like to know if I'm doing something wrong in my general approach. Here's a
simplified scenario, it's a bit long but I hope it can be useful to newbies
like me, facing the identity problems when updating a dataset. Thank you
very much for your patience!
(a) I use MSDE and the corresponding SqlClient namespace objects. All the
identity values in my tables are autonumber; in the dataset, autonumber seed
and step are set to -1 after their construction, so that the records added
in the "in memory" dataset will be granted to have a different identity from
any database record.
(b) All the insert commands use an output parameter (@newID) set to the new
identity value, e.g. the T-SQL code looks like:
ALTER PROCEDURE AnInsertCommand
(
....
@newID int OUTPUT
)
AS
INSERT INTO ... ;
SET @newID = SCOPE_IDENTITY()
The corresponding command objects include this @newID in their params
collection and map it to the table primary key field, so that when the
command is executed the dataset table will be updated to hold the "real"
autonumber value assigned by database (thus replacing the negative value
used in-memory).
(c) After generating the dataset, I have manually added the required
required foreign-key relations to it using the VS dataset designer, so that
the parent/child relationship are set up correctly: each primary key in a
child table is linked to the corresponding foreign key in the parent table.
(d) Let's say the parent table in dataset is "Customer", and the child table
is "Country". Customer has a countryID acting as the foreign key to the
Country child table. In my application, the user selects a single customer
to edit, or wants to enter a new customer; the form shows his data in bound
controls, and the data for child tables are typically shown in combo boxes
(e.g. a list of countries the user can select from). Thus, the dataset will
always contain a single parent record (=1 customer) and all the children
records (=countries list). The user can also add new countries when editing
a single customer record.
(e) When data are to be stored into database, the update policy is:
1) call BindingContext EndCurrentEdit for each table in the dataset, e.g.:
BindingContext[myDataSet, "Table Name"].EndCurrentEdit();
2) open the connection to database and begin a transaction
3) call Update for each CHILD table for NEW records only, e.g.:
countryDataAdapter.Update(myDataSet.TCountry.Select("","",DataViewRowState.A
dded));
this inserts the newly added countries only and updates the dataset Country
table so that the autonumber values reflect the "real" (non-negative) values
assigned by database; thus, a countryID = -10 becomes e.g. 67; also, the
relationships I have setup in the dataset cascade this update to the linked
parent table, so that the foreign key -10 becomes 67 too.
4) call Update for the PARENT table for NEW or MODIFIED records only
(deleted records will be processed later);
5) call Update for each CHILD table for all other records (e.g. MODIFIED or
DELETED);
6) call Update for the PARENT table for DELETED records.
7) commit the transaction and call AcceptChanges for the whole dataset.
8) close the connection anyway (in a finally {} block; in the catch{} block
the transaction is rolled back).
Now, this seems to work fine: I edit and add records, and autonumber values
are treated as expected and their changes propagated to the parent as
required. Here's the problem: when I'm editing an existing parent record and
add a new child record (e.g. a new country) and then store data, all works
fine; when I create a NEW parent record and do the same, the Update method
for the child table raises an "Object reference not set to instance of an
object" exception: as far as I can check with the debugger, all the values
in the dataset are as expected: the parent record has a negative ID, as for
the newly added child record, and both are linked correctly
(parent.countryID = child.countryID). As everything works when updating an
existing parent record, I'd expect the code to work for a new record too, as
the only difference is that a new record has a negative ID in the parent
table (which anyway should not matter for updating the child table, which is
linked via a foreign key).
Could anyone tell me if anything is "philosophically" wrong with this
approach? I cannot understand the reason for such errors...
article on identity values, but