Tuesday, December 8, 2009

Maintaining Referential Integrity in a Database

It can be tricky sometimes to maintain referential integrity in a database that has a number of related tables.  Here’s an approach I’ve found that seems to work well:

  1. Use foreign key constraints in the database to maintain referential integrity between two related tables.
  2. Assign a default value to each table to avoid getting foreign key constraint errors if the value is not specified.   (This makes it easier to create new records without specifying all the field values to satisfy the foreign key constraints when the record is created).
  3. When specifying foreign key constraints, there are two methods that are particularly critical to specify correctly:
    • How the database propagates updates
    • How the database propagates deletions
  4. For updates, set the foreign key constraints to automatically Cascade updates in all cases
  5. For deletions, there are two ways to handle them depending on the nature of the relationship between the two tables:
    • There is a "parent-child" relationship between the tables (For example, the "Orders" table is the parent of the "Order Line Items" table). In this situation, set the foreign key constraint between the two tables to Cascade the deletions. For example, deleting an order should also delete all the order line items associated with that order and not leave them behind as "orphans".
    • There is a "type" or a modifier relationship between the tables rather than an ownership relationship (For example, the "Records" table has a "Record Storage Location" column that is joined to the Record Storage Locations table to specify where the record is stored). In this situation, set the foreign key constraint between the two tables to "Set Default" on deletion. For example, if the a given record storage location is deleted, you wouldn't necessarily delete all the records that were stored in that location. They should have been moved to another location prior to this if, in fact they are to continue to exist, but if there are stragglers in the database, you would probably want to know about it. By setting the default value for this field which would typically be an unassigned value, you can easily spot these "stragglers" and determine if they need to be reassigned.
I hope that is helpful to others...it seems self-evident, but I assure you it wasn't until I took some time to think through it. Chuck

.Net Remoting versus COM

I’ve been looking into .Net Remoting as an alternative to COM for being able to use objects across application boundaries (See my separate article on ROTHook).

.Net Remoting has the potential to do most or perhaps all of the things we’ve done in the past with COM, but it would not seem to be a good use of that technology to do that.  Here’s how I see it:

  • COM is designed around objects
  • .Net Remoting seems to be designed around a service-oriented environment

To illustrate the difference, here’s a little analogy that seems to be appropriate.  Suppose you want to hear a dog bark:

  • Using COM you would tell it to create a “dog” object and COM would give you back the entire “dog” object.  At that point, you could call the “bark” method on the “dog” object to get it to bark.  While you had the “dog” object, you could also call any other methods you wanted to “wag tail”, etc.
  • With the services approach that .Net Remoting seems to be designed around, you would simply call the “dog bark” service and it would bark for you.  If you want to call other methods like “wag tail”, that would generally be a different service.

That’s how I see the difference between the two approaches.  I hope that helps others see it…it took me a little while to get there.

Chuck

ADO.Net Synchronization

The ADO.Net Sync Framework has some very nice capabilities for synchronizing a variety of data sources.  It even has capabilities for synchronizing files or if you were ambitious enough, you could develop a custom data source provider to work with sync services.

The design of the application I’m working on is based on a local SQL Server Compact Edition database on the client machine and a more centralized SQL Server Express database that aggregates data from each client machine.  Sync services will do the job of synchronizing each SQL Server CE database with the SQL Server Express database, but I was wondering if it would also do the job of synching the data in the DataSet with SQL Server CE.  It seems to be a logical capability for it to provide since it is able to synchronize a number of different types of data sources and a DataSet schema looks just like the database it is derived from.

I found out that ADO.Net synchronization will not provide that capability; however, I found another way of accomplishing that objective that’s very nice and very easy to implement.  The tricky part about database updates comes into play when you have related tables with foreign key constraints and they have to be updated in the proper sequence to avoid a foreign key violation.  In the original design of my application, I had some custom ADO.Net code that did that, but I wanted to find a way to simplify and standardize that code as much as possible.  That was one of the key things I was hoping Sync Services would do for me.

The technique I wound up using is a TableAdapterManager.  The TAM is a relatively new capability and it does exactly what I was looking for synch services to do to synch the DataSet with the SQL CE database.  You create a TableAdapterManager to consist of one or more TableAdapters for all the tables in a relationship, and simply use the “UpdateAll” method on the TAM and it will update all of the related tables in the proper sequence to avoid foreign key constraint errors.  The TAM is a very nice capability!

Chuck

VSTO Deployment

I’ve been working over the past few months to convert a Visio Add-in I originally developed about 5-6 years ago to migrate it from VB.Net to C# and also to Visual Studio 2008, .Net Framework 3.5, and Visual Studio Tools for Office (VSTO).

The original application design used a small stub of VBA code inside the Visio document to launch the COM Add-in.  This is a somewhat crude approach and it wasn’t a true Visio Add-in, but it has worked well for a number of years.  However, much better capabilities are available now through VSTO to make it very easy to create a true Visio Add-in (or an Add-in for any other Office Application).

Deployment of the completed VSTO Add-in can be difficult – especially on Vista and Windows 7 systems that have tightened up code access security to control what an application is allowed to do (and is not allowed to do).  Here are a few notes on what needs to be done to successfully deploy a VSTO Add-in:

  1. How the application stores and accesses files on the target system needs to be carefully thought out.  In the past, many applications simply put all files (both application files and data files) in the C:\Program Files location.  This approach no longer works on Vista systems because Vista will not allow an application to write or modify files in that area at runtime unless it is running with Administrator privileges.  My recommended strategy is this:
    • Use the Program Files directory only for application files that are only written or modified when the application is installed.
    • Create a folder in the user’s “My Documents” area for any documents that the user might create or modify while he/she is using the application.
    • Create a folder in the LocalApplicationData area for any other files that are written or modified by the application at run-time that do not need to be easily accessible to the user (for example configuration files and application data files).  On Vista systems this folder is located at C:\Users\<UserName>\AppData\Local
  2. The Office Add-in must be granted full trust in order for the VSTO loader to load it at run-time.  Problems with the VSTO loader not loading an application can be elusive to track down because the VSTO loader doesn’t seem to report all errors (even if you have error logging turned on).  Sometimes it just refuses to load the Add-in and doesn’t provide an error message or any indication that the Add-in hasn’t been loaded.  In order to grant full trust to your Add-in, you must include an installer project that creates a code access security policy to grant full-trust to the application.   I recommend going beyond that and assigning full-trust to the entire installation directory, especially if you have other DLL’s in the installation directory that also must be trusted for the application to run.  Here are some references for more details on how to implement this:
  3. Make sure that all the required prerequisites are included in the installer package. The obvious ones are:
    • .Net Framework 3.5 (or whatever other version you used)
    • VSTO Runtime
    One that is not so obvious is the ".Net 3.5 Framework Client Profile". The "Framework Client Profile" is supposed to be a subset of the full .Net Framework, so it seems redundant to include it and and the full .Net Framework, but it does seem to be necessary.

Chuck

Friday, December 4, 2009

COM and the Run-time Object Table (ROT)

You would think that it would be relatively easy for one managed code assembly to get a reference to another managed code assembly at runtime that might be running under a different application domain, but it's not. In order to do that you have to do several things:
  1. Make the server .Net Assembly Com-visible and Com-Compatible
  2. Register the server assembly in the RunTime Object Table (ROT) so that the client assembly can find it at run time

The technique for exposing a .Net assembly to COM is not too difficult and is covered in a number of locations on the web.  The task of registering a COM object in the ROT and retrieving a reference to that COM object from another application is a bit tricky.  Here’s some code that I’ve found works for that:

Option Strict On
Option Explicit On

Imports System.Runtime.InteropServices
Imports System.Runtime.InteropServices.ComTypes
Imports System.Runtime.InteropServices.Marshal

Public Class ROTHook

Private Const ROTFLAGS_REGISTRATIONKEEPSALIVE As Integer = 1
Private Const ROTFLAGS_ALLOWANYCLIENT As Integer = 2

<DllImport("ole32.dll", ExactSpelling:=True, PreserveSig:=False)> _
Private Shared Function GetRunningObjectTable(ByVal reserved As Int32) As IRunningObjectTable
End Function

<DllImport("ole32.dll", CharSet:=CharSet.Unicode, ExactSpelling:=True, PreserveSig:=False)> _
Private Shared Function CreateItemMoniker(ByVal lpszDelim As String, ByVal lpszItem As String) As IMoniker
End Function

<DllImport("ole32.dll", ExactSpelling:=True, PreserveSig:=False)> _
Private Shared Function CreateBindCtx(ByVal reserved As Integer) As IBindCtx
End Function

Public Shared Function AddToROT(ByVal obj As Object, ByVal classID As String) As Integer
'----------------------------------------------------------------------------------
' AddToROT
'
' Abstract - Adds a Reference to This Object to the Runtime Object Table
'
' Parameters
'               obj Object to register
'               classID Class ID of object to register
'
' Return Value  cookie to revoke ROT registration
'----------------------------------------------------------------------------------
Dim cookieValue As Integer
Dim rot As IRunningObjectTable = Nothing
Dim moniker As IMoniker = Nothing

Try
'---- Get the ROT -------------------------------------------------------------
rot = GetRunningObjectTable(0)

'--- Create a moniker ---------------------------------------------------------
Dim objName As String = "{" + classID + "}"
moniker = CreateItemMoniker("!", objName)

'--- Registers the object in the running object table -------------------------
cookieValue = rot.Register(ROTFLAGS_REGISTRATIONKEEPSALIVE, obj, moniker)

Return cookieValue

Catch Exc As Exception
Throw Exc
Finally

'--- Releases the COM objects -----------------------------------------------
If Not (moniker Is Nothing) Then
Try
While (Marshal.ReleaseComObject(moniker) > 0)
End While
Catch ex As Exception
Exit Try
End Try
End If
If Not (rot Is Nothing) Then
Try
While (Marshal.ReleaseComObject(rot) > 0)
End While
Catch ex As Exception
Exit Try
End Try
End If
End Try

End Function

Public Shared Function GetObjectByClassName(ByVal objClassName As String) As Object
Return GetActiveObject("!" + objClassName)
End Function

Public Shared Function GetObjectByClassID(ByVal objClassID As String) As Object
Return GetActiveObject("!{" + objClassID + "}")
End Function

Public Shared Function GetActiveObject(ByVal objName As String) _
As Object
'----------------------------------------------------------------------------------
' GetActiveObject
'
' Abstract - Gets an Instance of this Object from the Runtime Object Table
'
' Parameters
'
' Return Value  Object found or Nothing if Not Found
'----------------------------------------------------------------------------------
Try

Dim ROTObject As Object = Nothing
Dim runningObjectTable As IRunningObjectTable
Dim monikerEnumerator As IEnumMoniker = Nothing
Dim monikers(1) As IMoniker

runningObjectTable = GetRunningObjectTable(0)
runningObjectTable.EnumRunning(monikerEnumerator)
monikerEnumerator.Reset()

Dim numFetched As IntPtr = New IntPtr()
While (monikerEnumerator.Next(1, monikers, numFetched) = 0)
Dim ctx As IBindCtx
ctx = CreateBindCtx(0)

Dim runningObjectName As String = ""
monikers(0).GetDisplayName(ctx, Nothing, runningObjectName)
runningObjectName = runningObjectName.ToUpper
If (runningObjectName.StartsWith(objName.ToUpper)) Then
Dim runningObjectVal As Object = Nothing
runningObjectTable.GetObject(monikers(0), runningObjectVal)
ROTObject = CType(runningObjectVal, Object)
Return ROTObject
End If
End While

Return ROTObject

Catch Exc As Exception
Throw Exc
End Try

End Function

Public Shared Sub RemoveFromROT(ByVal myCookie As Integer)
'----------------------------------------------------------------------------------
' RemoveFromROT
'
' Abstract - Removes a Reference to This Object From the Runtime Object Table
'
' Parameters
'
' Return Value  
'----------------------------------------------------------------------------------

If (myCookie = 0) Then Exit Sub
Dim rot As IRunningObjectTable = Nothing

Try

'--- Get the running object table and revoke the cookie ----------------
rot = GetRunningObjectTable(0)
rot.Revoke(myCookie)
myCookie = 0

Catch Exc As Exception
Throw Exc
Finally
If Not (rot Is Nothing) Then
While (Marshal.ReleaseComObject(rot) > 0)
End While
End If
End Try

End Sub

End Class

You can use the “AddToROT” method to register an object in the ROT by classID – that method will return a cookie that can be used later to remove the object.  The “GetObjectByClassID” can be used to retrieve the object from another process.

Chuck

Correctly Displaying Windows Forms

If you have an application that launches a number of different forms and the forms can be launched in different ways by different parent forms or different parts of the application, displaying the form correctly relative to the parent form can be tricky.  The way I originally did this was to pass the parent handle of the parent form to the child form.  That’s a little cumbersome because you have to pass the parent handle as an argument every time you launch a child form.  I’ve found a much better way to do that.
I’ve written a small amount of code that calls a Windows API to get the windows handle of the active window and I use that to launch all forms.  It works great.  Here’s the code:

Imports System.Drawing
Imports System.Windows.Forms

Public Class WindowManager

Private Sub New()

End Sub

Public Shared ReadOnly Property ActiveWindowPointer() As IntPtr
Get
Try
Return WindowsAPI.GetActiveWindow()
Catch
Return 0
End Try
End Get
End Property

Public Shared ReadOnly Property ForegroundWindowPointer() As IntPtr
Get
Try
Return WindowsAPI.GetForegroundWindow()
Catch
Return 0
End Try
End Get
End Property

Public Shared ReadOnly Property ForegroundWindow() As IWin32Window
Get
Return New WindowWrapper(ForegroundWindowPointer)
End Get
End Property

Public Shared ReadOnly Property ActiveWindowBounds() As Rectangle
Get
Dim r As WindowsAPI.RECT = New WindowsAPI.RECT(0, 0, 0, 0)
Dim found As Boolean = WindowsAPI.GetWindowRect(ActiveWindowPointer, r)
If found Then
Return r.Rectangle
Else
Return Screen.PrimaryScreen.Bounds
End If
End Get
End Property

Public Shared Sub CenterFormToActiveWindow(ByVal form As Form)
Try

Dim point As Point = New Point()
Dim formSize As Size = form.Size
Dim workingArea As Rectangle = ActiveWindowBounds
point.X = (workingArea.X + (workingArea.Width / 2)) - (formSize.Width / 2)
point.Y = (workingArea.Y + (workingArea.Height / 2)) - (formSize.Height / 2)
form.Location = point

Catch

End Try

End Sub

End Class

Public Class WindowWrapper
Implements IWin32Window

Dim _hwnd As IntPtr

Public Sub New(ByVal handle As IntPtr)
_hwnd = handle
End Sub

Public ReadOnly Property Handle() As IntPtr Implements IWin32Window.Handle
Get
Return _hwnd
End Get

End Property

End Class


Declare Function GetActiveWindow Lib "user32" Alias "GetActiveWindow" () As IntPtr
Declare Function GetForegroundWindow Lib "user32" () As IntPtr
Public Declare Function GetWindowRect Lib "user32" (ByVal HWnd As IntPtr, _
ByRef lpRect As RECT) As Boolean


The way to use this is simple:
  • If you’re launching a dialog form, just use the following line of code:


    frm.ShowDialog(WindowManager.ForegroundWindow)

    and it will launch the form relative to the currently active Window.

  • That approach works for all dialog forms - if you’re launching a non-dialog form, you have to set the parent form inside the form constructor before you launch the form.
This approach has saved a lot of time and resulted in much cleaner code.

Chuck