SQL-DMO VB.NET Events  

Home
Up
What's New
DTS
SQL-NS
XP's
SQL Agent
Misc. Tools
Events
FAQ
Links
About...

 

version: 1.0.0.0

last updated: 4 January 2003

You would expect regular VB.NET events to work when using SQL-DMO events.

The code that does not work

So you would expect the following VB.NET code to work as expected:

Imports System.Runtime.InteropServices
Imports SQLDMO

Module BackupWithEvents
    <MTAThread()> _
   
Sub Main()
       
Dim app As App
        app =
New App()
        app.Run()
   
End Sub
End
Module

Public Class App
   
Dim server As SQLDMO.SQLServer2Class
   
Dim database As SQLDMO.Database2
   
Dim backup As SQLDMO.Backup2
   
Dim WithEvents backupEvents As SQLDMO.Backup2

    Public Sub Run()
       
Try
           
server = New SQLDMO.SQLServer2Class()
            backup =
New SQLDMO.Backup2Class()
            backupEvents = backup

            server.LoginSecure = True
           
server.Connect("(local)\dev")
            Console.WriteLine("server {0}", server.Name)

            database = server.Databases.Item("pubs")
            Console.WriteLine("database {0}", database.Name)

            backup.Database = database.Name
            backup.Files = "c:\pubs.bak"
            backup.Database = database.Name
            backup.Files = "c:\pubs.bak"
            backup.SQLBackup(server)

            server.DisConnect()
            backupEvents =
Nothing
            backup = Nothing
           
server = Nothing

        Catch ex As System.Runtime.InteropServices.COMException
            Console.WriteLine(ex.Message)
       
Catch ex As Exception
            Console.WriteLine(ex.Message)
       
End Try
   
End Sub

    Private Overloads Sub backupEvents_Complete(ByVal Message As String) Handles backupEvents.Complete
        Console.WriteLine(Message)
   
End Sub

    Private Overloads Sub backupEvents_NextMedia(ByVal Message As String) Handles backupEvents.NextMedia
        Console.WriteLine(Message)
   
End Sub

    Private Overloads Sub backupEvents_PercentComplete(ByVal Message As String, ByVal Percent As Integer) _
       
Handles
backupEvents.PercentComplete
        Console.WriteLine("{0} {1}", Message, Percent)
   
End Sub
End
Class

Download BackupWithEvents.zip

The wrong results

However when you look at the results is shows like:
server (local)
database pubs
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE successfully processed 145 pages in 0.260 seconds (4.541 MB/sec).
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE successfully processed 145 pages in 0.260 seconds (4.541 MB/sec).
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE successfully processed 145 pages in 0.260 seconds (4.541 MB/sec).
 

As you can see there is something wrong, the CompleteEventHandler fires 3 times, the other ones never fires at all. If you would change the order of linking the delegates you will see that the first event handler that you register is the one and only one that fires.

The problem

This behavior is caused by the fact that TLBIMP.EXE, which generates the Runtime Callable Wrappers (RCW) for the SQL-DMO COM library, creates a separate event interface implementation for each delegate rather than having a single interface with multiple functions. It appears that for every event that you want to be advised on, a new “sinkhelper” object, is being created and advised. As a result if one of the events is *dependent* on return value of another event you get unexpected results.

The solution

Technically there are two solutions, the first is to create your own RCW which implements the events handlers correctly using a single interface with multiple functions. This however requires a lot of coding for just handling events. The second solution provides a workaround for the problem using the UCOM interfaces to Advise our own implementation of the sink interface. To properly handle the events, the Visual C# .NET application must implement the IConnectionPointContainer and IConnectionPoint interfaces. The .NET Framework provides managed definitions of these interfaces through the UCOMIConnectionPointContainer and UCOMIConnectionPoint interfaces. These interfaces are part of the System.Runtime.InteropServices namespace.

Using the workaround

When using the UCOM interfaces the code will look like this:

Imports System.Runtime.InteropServices
Imports SQLDMO

Module
App
    <MTAThread()> _
   
Sub Main()
       
Dim server As SQLDMO.SQLServer2Class
       
Dim database As SQLDMO.Database2
       
Dim backup As SQLDMO.Backup2

        Try
           
server = New SQLDMO.SQLServer2Class()
            backup =
New SQLDMO.Backup2Class()

            server.LoginSecure = True
           
server.Connect("(local)")
            Console.WriteLine("server {0}", server.Name)

            database = server.Databases.Item("pubs")
            Console.WriteLine("database {0}", database.Name)

            backup.Database = database.Name
            backup.Files = "c:\pubs.bak"

            'Begin - set up events sink
           
Dim cpContainer As UCOMIConnectionPointContainer
            cpContainer =
CType(backup, UCOMIConnectionPointContainer)
           
Dim cpPoint As UCOMIConnectionPoint
           
Dim PES As BackupSink = New BackupSink()
           
Dim guid As Guid = New Guid("10021F09-E260-11CF-AE68-00AA004A34D5")
            cpContainer.FindConnectionPoint(guid, cpPoint)
           
Dim intCookie As Integer
           
cpPoint.Advise(PES, intCookie)
           
'End - set up events sink

            backup.Database = database.Name
            backup.Files = "c:\pubs.bak"
            backup.SQLBackup(server)

            cpPoint.Unadvise(intCookie)
            cpPoint =
Nothing
           
cpContainer = Nothing
           
PES = Nothing

            server.DisConnect()
            backup =
Nothing
           
server = Nothing

        Catch ex As System.Runtime.InteropServices.COMException
            Console.WriteLine(ex.Message)
       
Catch ex As Exception
            Console.WriteLine(ex.Message)
       
Finally
           
Console.ReadLine()
       
End Try
   
End Sub
End
Module

Public Class BackupSink
   
Implements SQLDMO.BackupSink

    Public Sub Complete(ByVal Message As String) Implements SQLDMO.BackupSink.Complete
        Console.WriteLine(Message)
   
End Sub

    Public Sub NextMedia(ByVal Message As String) Implements SQLDMO.BackupSink.NextMedia
        Console.WriteLine(Message)
   
End Sub

    Public Sub PercentComplete(ByVal Message As String, ByVal Percent As Integer) Implements SQLDMO.BackupSink.PercentComplete
        Console.WriteLine("{0} {1}", Message, Percent)
   
End Sub
End
Class

Download BackupWithEventsFix.zip

The correct results

server (local)
database pubs
PercentComplete [Microsoft][ODBC SQL Server Driver][SQL Server]83 percent backedup. 83
PercentComplete [Microsoft][ODBC SQL Server Driver][SQL Server]99 percent backedup. 99
PercentComplete [Microsoft][ODBC SQL Server Driver][SQL Server]100 percent backed up. 100
Complete [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE successfully processed 145 pages in 0.248 seconds (4.760 MB/sec).

Other SQL-DMO event sink interfaces

If you want to apply the same technique for other SQL-DMO objects that emit events you need to know the GUID of the interface for the event sink, this is the list of objects and the interface UUID for the various event sink interfaces in SQL-DMO

Name UUID
BackupSink 10021F09-E260-11CF-AE68-00AA004A34D5
BulkCopySink    10021C09-E260-11CF-AE68-00AA004A34D5
ReplicationSink 10031009-E260-11CF-AE68-00AA004A34D5
RestoreSink 10023206-E260-11CF-AE68-00AA004A34D5
ServerSink 10020209-E260-11CF-AE68-00AA004A34D5
TransferSink 10021E09-E260-11CF-AE68-00AA004A34D5

In case you are wondering how to find this information yourself, use OLEVIEW to find the sink interfaces in the type library.

***

Questions or problems regarding this web site should be directed to webmaster@sqldev.net.
Copyright © 1991-2003 SQLDev.Net. All rights reserved.
Last modified: 07/06/03.