Autoincrement Key values in Relationships

[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.

 

[David Sceppa]

George,

This is a very commonly discussed scenario in this
newsgroup. Many developers have been able to use this approach
frequently in the past.

I've included some code at the end of this post that I used
to demonstrate the approach. On my machine, I'm seeing that the
child row contains the newly fetched auto-increment value in its
foreign key column and still has a RowState of Added after the
call to ParentAdapter.Update. Let me know how the code behaves
on your machine. If you could change the code to reproduce the
behavior you're seeing, or supply similar code that would allow
me to reproduce that behavior, that would speed up the process.
Also, pointing out what version of the Framework and which .NET
Data Provider you're using may prove useful.

Though I mentioned Guids as another way of generating key
values, I don't discourage the use of auto-increment columns in
favor of Guids.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.


'Sample code
Dim ds As New DataSet()
Dim tblOrders As DataTable = ds.Tables.Add("Orders")
Dim tblDetails As DataTable = ds.Tables.Add("Order Details")
Dim colID As DataColumn

With tblOrders.Columns
colID = .Add("OrderID", GetType(Integer))
.Add("CustomerID", GetType(String))
.Add("EmployeeID", GetType(Integer))
.Add("OrderDate", GetType(DateTime))
End With
colID.AutoIncrement = True
colID.AutoIncrementSeed = -1
colID.AutoIncrementStep = -1
tblOrders.PrimaryKey = New DataColumn() {colID}

With tblDetails.Columns
.Add("OrderID", GetType(Integer))
.Add("ProductID", GetType(Integer))
.Add("Quantity", GetType(Integer))
.Add("UnitPrice", GetType(Decimal))
tblDetails.PrimaryKey = New DataColumn() {.Item("OrderID"), _
.Item("ProductID")}
End With

Dim rel As DataRelation
rel = ds.Relations.Add("Orders_Details", _
tblOrders.Columns("OrderID"), _
tblDetails.Columns("OrderID"))

Dim rowNewOrder As DataRow = tblOrders.NewRow
With rowNewOrder
.Item("CustomerID") = "ALFKI"
.Item("EmployeeID") = 1
.Item("OrderDate") = DateTime.Today
End With
tblOrders.Rows.Add(rowNewOrder)

Dim rowNewDetail As DataRow = tblDetails.NewRow
With rowNewDetail
.SetParentRow(rowNewOrder, rel)
.Item("ProductID") = 1
.Item("Quantity") = 1
.Item("UnitPrice") = 1
End With
tblDetails.Rows.Add(rowNewDetail)

Dim strConn As String
Dim strSQL As String

strConn = "Data Source=(local);" & _
"Initial Catalog=Northwind;" & _
"Trusted_Connection=Yes;"
Dim cn As New SqlConnection(strConn)
strSQL = "SELECT OrderID, CustomerID, EmployeeID, " & _
"OrderDate FROM Orders"
Dim daOrders As New SqlDataAdapter(strSQL, cn)
strSQL = "SELECT OrderID, ProductID, Quantity, " & _
"UnitPrice FROM [Order Details]"
Dim daDetails As New SqlDataAdapter(strSQL, cn)

strSQL = "INSERT INTO Orders (CustomerID, EmployeeID, " & _
"OrderDate) VALUES (@CustomerID, @EmployeeID, " & _
"@OrderDate);SET @OrderID = SCOPE_IDENTITY()"
Dim cmdInsertOrder As New SqlCommand(strSQL, cn)
With cmdInsertOrder.Parameters
.Add("@CustomerID", SqlDbType.NVarChar, 5, "CustomerID")
.Add("@EmployeeID", SqlDbType.Int, Nothing, "EmployeeID")
.Add("@OrderDate", SqlDbType.DateTime, Nothing, "OrderDate")
.Add("@OrderID", SqlDbType.Int, Nothing, "OrderID")
.Item("@OrderID").Direction = ParameterDirection.Output
End With
cmdInsertOrder.UpdatedRowSource = UpdateRowSource.OutputParameters
daOrders.InsertCommand = cmdInsertOrder

Console.WriteLine("Before daOrders.Update")
ShowOrder(rowNewOrder)
ShowDetail(rowNewDetail)
Console.WriteLine()

daOrders.Update(tblOrders)

Console.WriteLine("After daOrders.Update")
ShowOrder(rowNewOrder)
ShowDetail(rowNewDetail)

Private Sub ShowOrder(ByVal rowOrder As DataRow)
Console.WriteLine("RowState = {0}, OrderID = {1}", _
rowOrder.RowState, rowOrder("OrderID"))
End Sub

Private Sub ShowDetail(ByVal rowDetail As DataRow)
Console.WriteLine("RowState = {0}, OrderID = {1}", _
rowDetail.RowState, rowDetail("OrderID"))
End Sub

 

[George Gavaghan]

Mr. Sceppa,

Thank you for your speedy reply.

I have tested your sample, and as I would expect, it works just like it
should. I even changed the code to use my Database and Tables, and was
a little surprised when it too worked without a problem.

You will forgive me however if I am still a little doubtful about this
working so easily when applied to a somewhat larger-scale project. Where
as the sample is short-and-sweet, the application that exhibits the
problem I described spans multiple forms, uses at least 5 Currency
managers, as well as matching DataViews and DataRowViews. The Dataset
has been defined in it's own class file (2300+ lines), with the
Connection and Data Adapters being defined in another Class file (700+
lines). A third Class brings the first two together and adds functions
specific to this particular dataset (sequencing of table Update,
deletes, etc), allowing the actual application to create a single class
instance and have simplified access to 'the whole ball of wax'.

The approach you provide in the sample is where I started this
particular battle. Since I am not prepared to re-write the 3 base
classes in order to re-test it, I will simply concede that your method
works, and move on. If ever presented with the opportunity in the
future, I will investigate it further.

Lastly, with regard to the Guids, you are correct. In the book you only
mention them as an alternative. The reference I made was incorrectly
aimed at you, but was actually taken from one of the Chat room
transcripts. My apologies.

Thank you for your time and attention. I'm sure you will be hearing
from me again.

 

[David Sceppa]

George,

I understand that there's a big difference between a small
snippet of sample code and a large scale project. If you do find
the time to investigate the problem further and can provide more
information on the problem, please let me know.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.