StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » GUID's As Primary Keys

GUID's As Primary Keys

Ben Kim — Tue, 30 Jan 2007 10:57:41 GMT

The vast majority of our table schemas use GUID's (uniqueidentifiers) as their primary keys. I am not able to save new entries back to the test table I am using and updates sometimes go through sometimes do not! I have a simple form based on the Maintenance Form. Attached is the debug log I captured.

I did try to add code int he BO_BeforeAddNew proc that primes the GUID of the file:

Private Sub IncdTypeBO_BeforeAddNew(ByVal e As MicroFour.StrataFrame.Business.BeforeAddNewEventArgs) Handles Me.BeforeAddNew

Me.SQLGUID = New System.Guid()

End Sub

Which just brought up this most recent error as shown in the log. Also I never heard back on the Radio button issue I posted yesterday. The combo box appears to work now. But for the life of me I cannot make the RadioButtonGrp/Options work with the BO.

Ben

RE: GUID's As Primary Keys

Paul Chase — Tue, 30 Jan 2007 10:57:41 GMT

Just wanted to share this function as I use guid's as well. Here is a good article on using guids as Primary Keys. These function's are somewhere in that article in C# but I just made a vb version of it.

http://www.informit.com/articles/article.asp?p=25862&rl=1

Public Shared Function NewSeqGuid() As Guid

Dim laGuid() As Byte = System.Guid.NewGuid.ToByteArray

Dim ldBaseDate As DateTime = New DateTime(1900, 1, 1)

Dim ldNow As DateTime = DateTime.Now

' Get the days and milliseconds which will be used to build the byte string

Dim strucdays As TimeSpan = New TimeSpan((ldNow.Ticks - ldBaseDate.Ticks))

Dim strucmsecs As TimeSpan = New TimeSpan((ldNow.Ticks _

- (New DateTime(ldNow.Year, ldNow.Month, ldNow.Day).Ticks)))

' Convert to a byte array

' Note that SQL Server is accurate to 1/300th of a millisecond so we divide by 3.333333

Dim laDays() As Byte = BitConverter.GetBytes(strucdays.Days)

Dim laSecs() As Byte = BitConverter.GetBytes(CType((strucmsecs.TotalMilliseconds / 3.333333), Long))

' Reverse the bytes to match SQL Servers ordering

Array.Reverse(laDays)

Array.Reverse(laSecs)

' Copy the bytes into the guid

Array.Copy(laDays, (laDays.Length - 2), laGuid, (laGuid.Length - 6), 2)

Array.Copy(laSecs, (laSecs.Length - 4), laGuid, (laGuid.Length - 4), 4)

Return New System.Guid(laGuid)

End Function

Public Shared Function GetDateFromSeqGuid(ByVal guid As System.Guid) As DateTime

Dim baseDate As DateTime = New DateTime(1900, 1, 1)

Dim daysArray() As Byte = New Byte((4) - 1) {}

Dim msecsArray() As Byte = New Byte((4) - 1) {}

Dim guidArray() As Byte = guid.ToByteArray

' Copy the date parts of the guid to the respective byte arrays.

Array.Copy(guidArray, (guidArray.Length - 6), daysArray, 2, 2)

Array.Copy(guidArray, (guidArray.Length - 4), msecsArray, 0, 4)

' Reverse the arrays to put them into the appropriate order

Array.Reverse(daysArray)

Array.Reverse(msecsArray)

' Convert the bytes to ints

Dim days As Integer = BitConverter.ToInt32(daysArray, 0)

Dim msecs As Integer = BitConverter.ToInt32(msecsArray, 0)

Dim lddate As DateTime = baseDate.AddDays(days)

lddate = lddate.AddMilliseconds((msecs * 3.333333))

Return lddate

End Function

RE: GUID's As Primary Keys

StrataFrame Team — Thu, 25 Jan 2007 09:10:15 GMT

There is a BeforeSave event on the business object...

If you want to exclude fields from the concurrency checking, you'll need to implement row versioning. Add an extra column to the table that is an integer. You can call it Version, RowVersion, prefix_Version, whatever, it just has to be an integer. Then, change the UpdateConcurrencyType to OptimisticRowVersion and set the RowVersionOrTimestamp column to the name of that column. The business object will handle the rest. The trigger can then just ignore that column and the business object won't think there is a concurrency issue.

You can also use a Timestamp column for concurrency, but in this case, the trigger would also modify that field since Timestamp columns are updated automatically by the system.

RE: GUID's As Primary Keys

Ben Kim — Thu, 25 Jan 2007 08:24:21 GMT

Apparently this has something to do with a trigger that is firing on the backend that our DBA established. He is automatically updating the "SQLLASTUPDATED" and several other field values in the table.

If I add

Me.SQLLastUpdated = Now

To the BO (only place that made sense was _CheckRulesOnCurrentRow - Shouldn't there be a BeforeSave in the BO?), it started working but the SQLLastUpdated column now always causes a "collison". Is there anyway to ignore specific fields such as the SQLLastUpdated column when using concurrency checking? IE: I need all fields BUT the ones handle by the DBA's trigger.

Ben

RE: GUID's As Primary Keys

Ben Kim — Thu, 25 Jan 2007 08:13:59 GMT

Attached find the debug log. Maybe that will help.

Ben

RE: GUID's As Primary Keys

Ben Kim — Wed, 24 Jan 2007 15:30:07 GMT

OK I have moved the code. Now it allows me to save. But when I update the record I just saved, I get the following error message:

BusinessLayerException
An error occurred while saving an the data to the server.
DataLayerSavingException
Arithmetic operation resulted in an overflow.
OverflowException
Arithmetic operation resulted in an overflow.

Source : MicroFour StrataFrame Business

Stack Trace:
   at MicroFour.StrataFrame.Data.DataBasics.CompareDateTime(DateTime DateTime1, DateTime DateTime2)
   at MicroFour.StrataFrame.Data.DataLayer.BuildFieldCollisions(DataRow ServerRow, DataRow LocalRow)
   at MicroFour.StrataFrame.Data.DataLayer.HandleConcurrencyException(DataRow LocalRow)
   at MicroFour.StrataFrame.Data.SqlDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler, Boolean RecreateCommand)
   at MicroFour.StrataFrame.Data.DbDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler)
   at MicroFour.StrataFrame.Data.DataLayer.UpdateDataTableThread(Object ThreadParams)
   at MicroFour.StrataFrame.Data.DataLayer.SaveByForm(DataTable TableToSave, Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.Business.BusinessLayer.SaveByForm(Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Save(Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Save()
   at MicroFour.StrataFrame.UI.Windows.Forms.MaintenanceFormToolStrip.cmdSave_Click(Object sender, EventArgs e)
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ToolStrip.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Ideas?

Ben

RE: GUID's As Primary Keys

StrataFrame Team — Wed, 24 Jan 2007 10:04:02 GMT

When you set the primary key for the record within the BeforeAddNew(), the record has not been added to the business object, yet. You want to move that code to the SetDefaultValues() event handler (there should already be a method stub for it in the BO if it was created by the template). The event sequence goes like this:

RaiseEvent BeforeAddNew

If Not e.Cancel Then

' Add new row

RaiseEvent SetDefaultValues

RaiseEvent AfterAddNew

End If

Try changing that and see if that fixes it.