G
George Gavaghan
ADO.NET and Autoincrement fields.
Recently, while attempting to use .NET to build an
interface for a new system, I came upon the issue of using
Autoincrement fields as Key values. In and of themselves,
they are not terribly troublesome, but when used as part
of a Key defining a Parent-Child relationship, things get
a little sticky.
Per the MicrosoftPress book, ADO.NET by David Sceppa,
handling Autoincrement values in Parent-Child
relationships is no bother at all. In chapter 11 -
Advanced Updating Scenarios : Section 'Working with
Autoincrement Values and Relational Data', Sceppa states,
(after fetching the new Autoincrement value for the Parent
record from the database)"...how do you apply the new
Autoincrement values to the pending new line items?
Actually, you don't. You let ADO.NET do the work for you
through the DataRelation.".
That statement is only half right. ADO.NET will in fact
update the values through the DataRelation, but in the
process, it changes the RowState of the (child) records
from 'Added' to 'Modified'. Since this property is read-
only, this effectively prevents the child records from
being INSERTed into the database (because the DataAdapter
now believes these records are NOT NEW, but simply
existing records that have been modified. Using this
methodology, your detail data will never get stored to the
Database. Ever.
In his book, Sceppa also discourages the use of
Autoincrement values in favor of the GUID. Since that
value can be generated by either the client or server, it
is much easier to handle since you do not need any special
code to retrieve the values from the Database after the
row has been added. Except for the fact that GUID values
CAN BE DUPLICATED, and the fact that they are four times
larger than an (SQL)Int, this would probably be a good
suggestion. Even if the uniqueness could be guaranteed,
who wants to use a 16-byte random 'string' as a key value
for lookups and cross referencing between tables!?!?
After fighting with this particular issue for several days
(and nights), and repeated attempts to find something
resembling an answer on Microsoft's many websites, I found
something that actually works, and is not terribly
difficult to achieve. I would like to say that the idea,
if not the code, came from Microsoft. It didn't.
Let's use the standard ORDERS and ORDERDETAILS tables to
illustrate....
ORDERS : PrimaryKey = (ORDER_ID)
---------------------------------
ORDER_ID : Autoincrement (-1, -1)
CUSTOMER_ID : Int
.....
ORDERDETAILS : PrimaryKey = (ORDER_ID + LINEITEM_ID)
----------------------------------------------------
ORDER_ID : Int
LINEITEM_ID : Int
.....
DataRelation
--------------------------------
Parent : ORDERS("ORDER_ID")
Child : ORDERDETAILS("ORDER_ID")
Let's say that a single new order has been added to the
DataSet and has been assigned an ORDER_ID of -1. the new
records would look something like this...
ORDERS.Rows(0)("ORDER_ID") = -1
ORDERS.Rows(0)("CUSTOMER_ID") = 7026
.....
ORDERS.Rows(0).RowState = Added
ORDERDETAILS.Rows(0)("ORDER_ID") = -1
ORDERDETAILS.Rows(0)("LINEITEM_ID") = 27
.....
ORDERDETAILS.Rows(0).RowState = Added
In order for the Database assigned Autoincrement value to
propagate to the child table in the relationship, you need
to use the Parent tables Select method, and NOT GetChanges
since GetChanges will not cascade the values to the
ORDERDETAILS DataTable. The call would look something
like this....
nEffectedRows = sdaORDERS.Update(ds.ORDERS.Select
("ORDER_ID < 0"))
----->Insert statement is as follows....
"INSERT INTO dbo.ORDERS(CUSTOMER_ID) "_
"VALUES (@CUSTOMER_ID) ; "_
"SELECT ORDER_ID, CUSTOMER_ID FROM dbo.ORDERS "_
"WHERE (@ORDER_ID = @@IDENTITY)"
This will add the new ORDER record to the Database and
return the new database assigned ORDER_ID, which in turn
is stored in the appropriate ORDERS datarow. If the
DataRelation is set up correctly and in effect, the
ORDER_ID values in the Child record(s) will also be
updated with the correct ORDER ID. This makes our records
look like this...
ORDERS.Rows(0)("ORDER_ID") = 99
ORDERS.Rows(0)("CUSTOMER_ID") = 7026
ORDERS.Rows(0).RowState = Unchanged
ORDERDETAILS.Rows(0)("ORDER_ID") = 99
ORDERDETAILS.Rows(0)("LINEITEM_ID") = 27
ORDERDETAILS.Rows(0).RowState = Modified
....
This brings us to the problem with using this method.
At this point, we have called the Update method for the
ORDERS DataAdapter, but NOT for the ORDERDETAILS
DataAdapter. If you look at the RowState for the
ORDERDETAILS row you will see that it has been changed
from 'Added' to 'Modified'. If you now try to send this
row to the DataAdapters Update method, it will attempt to
UPDATE it rather than INSERT it. Obviously this fails
since the row does not exist in the Database. What makes
this really fun to deal with is the fact that the RowState
property is READONLY.
Microsoft does not offer any solution to this. Sceppa is
obviously unaware of this problem since he goes on to
say, "Once the new rows in the order details DataTable
contain the appropriate values for the OrderID column, you
can successfully submit the pending new rows to your
database".
No biggie. It's not the first time Microsoft has left out
a detail or two.
Solution.
It took me about 5 days to find a combination of tactics
that actually worked. The sequence of events are the same
as what is described above, but with a couple of extra
steps tossed in. The solution itself isn't terribly
difficult to code, infact it's rather simplistic. What
made it difficult was trying to do it 'their way'.
The brief run-down is as follows....
<data has already been added - we are about to update the
database with our changes >
//------------------------------------------------------
//------------------------------------------------------
Dim xx, yy As Int16
Dim tOrdersAry() As DataRow
Dim tDetailsAry() As DataRow
' Get our array of (POINTERS to) the ADDED ROWS...
tOrdersAry = _
ds.ORDERS.Select("", "ORDER_ID", Added)
tDetailsAry = _
ds.ORDERDETAILS.Select("", "ORDER_ID", Added)
' Now, update the ORDERS Table with the NEW records...
xx = sdaORDERS.Update(tOrdersAry)
Dim tempTable As DataTable
tempTable = <dsClassDef>.ORDERS.Clone
' The ORDER_ID field is an Autoincrement column.
' TURN IT OFF!!!!!
tempTable.Columns("ORDER_ID").AutoIncrement = False
' Copy over all the data from the real (Details) DataTable
' (which includes the new, correct ORDER_ID value)...
Dim tmpRow, tmpRow2 As DataRow
For Each tmpRow In tDetailsAry
tmpRow2 = tempTable.NewRow
tmpRow2("ORDER_ID") = tmpRow("ORDER_ID")
tmpRow2("CUSTOMER_ID") = tmpRow("CUSTOMER_ID")
tempTable.Rows.Add(tmpRow2)
Next
' Update the Database with the 'Added' records...
yy = sdaOREDRDETAILS.Update(tempTable)
//------------------------------------------------------
//------------------------------------------------------
One detail left to do at this point is call the
AcceptChanges method for the -real- rows in the
ORDERDETAILS DataTable. Since this is dependant upon your
particular handling of the transaction, I do not include
that code in this example, though I would suggest handling
the INSERTS in a separate transaction and calling the
AcceptChanges method for each added (detail) row rather
than for the entire table after all updates, inserts, and
deletes.
In closing, I would just like to add that under normal
circumstances I would not have bothered to comment on this
problem. I have in this case because Microsoft has
documented this situation to a fair degree of detail, but
has done so incorrectly, ineffectively, and when
consulting more than one source, in a conflicting manner.
I paid good money to buy a MicrosoftPress book since they,
by all rights, should be the absolute experts on the
subject. I then added to my confusion by consulting the
Microsoft website to clarify, only to find more wrong
answers (though stated in a very matter-of-fact manner).
I freely admit that I have never, and I mean never, really
liked Microsoft. Mostly because of their business
practices, but also because of the quantity of bugs and
known problems within their software (which will be fixed
in the next release for only $xxx.xx!!). When the company
I work for decided to use .NET for a system re-write, I
began investigating it, and to my own surprise, I was very
pleased with what I found. For the first time in twelve
years, I had something NICE to say about Microsoft.
However, as with everything else from MS, once you get
down to the details, the documentation ALWAYS works better
than the actual software.
Recently, while attempting to use .NET to build an
interface for a new system, I came upon the issue of using
Autoincrement fields as Key values. In and of themselves,
they are not terribly troublesome, but when used as part
of a Key defining a Parent-Child relationship, things get
a little sticky.
Per the MicrosoftPress book, ADO.NET by David Sceppa,
handling Autoincrement values in Parent-Child
relationships is no bother at all. In chapter 11 -
Advanced Updating Scenarios : Section 'Working with
Autoincrement Values and Relational Data', Sceppa states,
(after fetching the new Autoincrement value for the Parent
record from the database)"...how do you apply the new
Autoincrement values to the pending new line items?
Actually, you don't. You let ADO.NET do the work for you
through the DataRelation.".
That statement is only half right. ADO.NET will in fact
update the values through the DataRelation, but in the
process, it changes the RowState of the (child) records
from 'Added' to 'Modified'. Since this property is read-
only, this effectively prevents the child records from
being INSERTed into the database (because the DataAdapter
now believes these records are NOT NEW, but simply
existing records that have been modified. Using this
methodology, your detail data will never get stored to the
Database. Ever.
In his book, Sceppa also discourages the use of
Autoincrement values in favor of the GUID. Since that
value can be generated by either the client or server, it
is much easier to handle since you do not need any special
code to retrieve the values from the Database after the
row has been added. Except for the fact that GUID values
CAN BE DUPLICATED, and the fact that they are four times
larger than an (SQL)Int, this would probably be a good
suggestion. Even if the uniqueness could be guaranteed,
who wants to use a 16-byte random 'string' as a key value
for lookups and cross referencing between tables!?!?
After fighting with this particular issue for several days
(and nights), and repeated attempts to find something
resembling an answer on Microsoft's many websites, I found
something that actually works, and is not terribly
difficult to achieve. I would like to say that the idea,
if not the code, came from Microsoft. It didn't.
Let's use the standard ORDERS and ORDERDETAILS tables to
illustrate....
ORDERS : PrimaryKey = (ORDER_ID)
---------------------------------
ORDER_ID : Autoincrement (-1, -1)
CUSTOMER_ID : Int
.....
ORDERDETAILS : PrimaryKey = (ORDER_ID + LINEITEM_ID)
----------------------------------------------------
ORDER_ID : Int
LINEITEM_ID : Int
.....
DataRelation
--------------------------------
Parent : ORDERS("ORDER_ID")
Child : ORDERDETAILS("ORDER_ID")
Let's say that a single new order has been added to the
DataSet and has been assigned an ORDER_ID of -1. the new
records would look something like this...
ORDERS.Rows(0)("ORDER_ID") = -1
ORDERS.Rows(0)("CUSTOMER_ID") = 7026
.....
ORDERS.Rows(0).RowState = Added
ORDERDETAILS.Rows(0)("ORDER_ID") = -1
ORDERDETAILS.Rows(0)("LINEITEM_ID") = 27
.....
ORDERDETAILS.Rows(0).RowState = Added
In order for the Database assigned Autoincrement value to
propagate to the child table in the relationship, you need
to use the Parent tables Select method, and NOT GetChanges
since GetChanges will not cascade the values to the
ORDERDETAILS DataTable. The call would look something
like this....
nEffectedRows = sdaORDERS.Update(ds.ORDERS.Select
("ORDER_ID < 0"))
----->Insert statement is as follows....
"INSERT INTO dbo.ORDERS(CUSTOMER_ID) "_
"VALUES (@CUSTOMER_ID) ; "_
"SELECT ORDER_ID, CUSTOMER_ID FROM dbo.ORDERS "_
"WHERE (@ORDER_ID = @@IDENTITY)"
This will add the new ORDER record to the Database and
return the new database assigned ORDER_ID, which in turn
is stored in the appropriate ORDERS datarow. If the
DataRelation is set up correctly and in effect, the
ORDER_ID values in the Child record(s) will also be
updated with the correct ORDER ID. This makes our records
look like this...
ORDERS.Rows(0)("ORDER_ID") = 99
ORDERS.Rows(0)("CUSTOMER_ID") = 7026
ORDERS.Rows(0).RowState = Unchanged
ORDERDETAILS.Rows(0)("ORDER_ID") = 99
ORDERDETAILS.Rows(0)("LINEITEM_ID") = 27
ORDERDETAILS.Rows(0).RowState = Modified
....
This brings us to the problem with using this method.
At this point, we have called the Update method for the
ORDERS DataAdapter, but NOT for the ORDERDETAILS
DataAdapter. If you look at the RowState for the
ORDERDETAILS row you will see that it has been changed
from 'Added' to 'Modified'. If you now try to send this
row to the DataAdapters Update method, it will attempt to
UPDATE it rather than INSERT it. Obviously this fails
since the row does not exist in the Database. What makes
this really fun to deal with is the fact that the RowState
property is READONLY.
Microsoft does not offer any solution to this. Sceppa is
obviously unaware of this problem since he goes on to
say, "Once the new rows in the order details DataTable
contain the appropriate values for the OrderID column, you
can successfully submit the pending new rows to your
database".
No biggie. It's not the first time Microsoft has left out
a detail or two.
Solution.
It took me about 5 days to find a combination of tactics
that actually worked. The sequence of events are the same
as what is described above, but with a couple of extra
steps tossed in. The solution itself isn't terribly
difficult to code, infact it's rather simplistic. What
made it difficult was trying to do it 'their way'.
The brief run-down is as follows....
<data has already been added - we are about to update the
database with our changes >
//------------------------------------------------------
//------------------------------------------------------
Dim xx, yy As Int16
Dim tOrdersAry() As DataRow
Dim tDetailsAry() As DataRow
' Get our array of (POINTERS to) the ADDED ROWS...
tOrdersAry = _
ds.ORDERS.Select("", "ORDER_ID", Added)
tDetailsAry = _
ds.ORDERDETAILS.Select("", "ORDER_ID", Added)
' Now, update the ORDERS Table with the NEW records...
xx = sdaORDERS.Update(tOrdersAry)
Dim tempTable As DataTable
tempTable = <dsClassDef>.ORDERS.Clone
' The ORDER_ID field is an Autoincrement column.
' TURN IT OFF!!!!!
tempTable.Columns("ORDER_ID").AutoIncrement = False
' Copy over all the data from the real (Details) DataTable
' (which includes the new, correct ORDER_ID value)...
Dim tmpRow, tmpRow2 As DataRow
For Each tmpRow In tDetailsAry
tmpRow2 = tempTable.NewRow
tmpRow2("ORDER_ID") = tmpRow("ORDER_ID")
tmpRow2("CUSTOMER_ID") = tmpRow("CUSTOMER_ID")
tempTable.Rows.Add(tmpRow2)
Next
' Update the Database with the 'Added' records...
yy = sdaOREDRDETAILS.Update(tempTable)
//------------------------------------------------------
//------------------------------------------------------
One detail left to do at this point is call the
AcceptChanges method for the -real- rows in the
ORDERDETAILS DataTable. Since this is dependant upon your
particular handling of the transaction, I do not include
that code in this example, though I would suggest handling
the INSERTS in a separate transaction and calling the
AcceptChanges method for each added (detail) row rather
than for the entire table after all updates, inserts, and
deletes.
In closing, I would just like to add that under normal
circumstances I would not have bothered to comment on this
problem. I have in this case because Microsoft has
documented this situation to a fair degree of detail, but
has done so incorrectly, ineffectively, and when
consulting more than one source, in a conflicting manner.
I paid good money to buy a MicrosoftPress book since they,
by all rights, should be the absolute experts on the
subject. I then added to my confusion by consulting the
Microsoft website to clarify, only to find more wrong
answers (though stated in a very matter-of-fact manner).
I freely admit that I have never, and I mean never, really
liked Microsoft. Mostly because of their business
practices, but also because of the quantity of bugs and
known problems within their software (which will be fixed
in the next release for only $xxx.xx!!). When the company
I work for decided to use .NET for a system re-write, I
began investigating it, and to my own surprise, I was very
pleased with what I found. For the first time in twelve
years, I had something NICE to say about Microsoft.
However, as with everything else from MS, once you get
down to the details, the documentation ALWAYS works better
than the actual software.