Saving Data for a Horse

[Previous] [Next]

With ADO, you can save data in two different ways:

• You can put the data you want to save in an ADO recordset and then call either its Update method or its UpdateBatch method. This would take care of all three kinds of modifications: updates, inserts, and deletions.
• You can create an action query, using an ADO Connection object or an ADO Command object to send a command to the database. The command could be an UPDATE, an INSERT, or a DELETE statement. It could also be a stored procedure call.

We decided to use the first way for updates and inserts and the second way for deletions. The following code handles inserts as well as updates for the HorseTrSrvcs.Save operation:

Public Sub Save(rs As Recordset) Dim objADOSrvcs As RaceADOSrvcs ' Get new IDs Set objADOSrvcs = CreateObject("RaceDataAccess.RaceADOSrvcs") rs.MoveFirst While Not rs.EOF If rs.EditMode = adEditAdd Then rs!HorseId = objADOSrvcs.GetNewNumber("Horses") End If rs.MoveNext Wend rs.MoveFirst rs.ActiveConnection = strConn rs.UpdateBatch End Sub

You can see in the preceding code that the Save method uses the EditMode property of the recordset to find out whether any of the records in it are new and should be inserted in the database. For these records, the GetNewNumber method of a RaceADOSrvcs object allocates a unique ID before having the UpdateBatch method do the actual update. During the update operation, ADO will use the same EditMode property of each record to decide whether to issue an UPDATE command or an INSERT command to the database.

The following code shows how we implemented the GetNewNumber method:

Public Function GetNewNumber(strTable As String) As Integer Dim rs As Recordset, strSQL As String strSQL = "SELECT Tablename, LastIdIssued " & _ "FROM IdTable " & _ "WHERE TableName = '" & strTable & "'" Set rs = CreateObject("ADODB.Recordset") rs.LockType = adLockPessimistic rs.CursorLocation = adUseServer rs.CursorType = adOpenKeyset rs.Source = strSQL rs.ActiveConnection = strConn rs.Open rs!LastIdIssued = rs!LastIdIssued + 1 rs.Update GetNewNumber = rs!LastIdIssued rs.Close End Function

Admittedly, this code is somewhat oversimplified. It does its job, but you can improve it, as Microsoft demonstrates in its TakeANumber component in the Island Hopper News sample in the Microsoft Transaction Server (MTS) documentation. The TakeANumber component has a GetANumber method that makes use of the Shared Property Manager (SPM, pronounced SPAM) in COM+ and MTS to achieve the same result without having to visit the database as often as in our example.

Anyway, for a component running the Island Hopper GetANumber or the preceding GetNewNumber method, you should set the transactional attribute in COM+ or MTS to require a new transaction. This setting of the transactional attribute in MTS or COM+ results in shorter database locks. Locks will be kept only for the time needed to get the next available number from the database and then update the number table to reflect that one more number has been taken. Because the locks are being held for such a short time, other users can access the numbers table almost immediately rather than having to wait until the entire transaction is completed.

Normally this manner of getting running ID numbers is better than using identity columns in Microsoft SQL Server. We have several reasons for holding this opinion. The most important one is that ADO won't return the ID given to a database row unless you use a server-side cursor. In most cases, you definitely don't want to use server-side cursors because they don't let you disconnect their recordsets. And if you can't disconnect a recordset, there's no way to send it over the network to main business objects, facade objects, or clients. If you can't send a recordset and its data over the network, you must also keep the objects holding that recordset alive.

Other developers have objected to this view concerning identity columns. The most usual objection is about the need to have the ID returned from the database. In many cases, you don't need to have it back, and then you can use identity columns without also having to use a server-side cursor. We accept that objection, but we still maintain our view. Systems tend to grow over time, and what's not needed today will be tomorrow. We think that the combination of ADO and identity columns lacks in functionality, and we don't recommend using it.