version: 1.0.0.0

last updated: 4 January 2003

You would expect regular delegates to work when using SQL-DMO events in C#.

The code that does not work

So you would expect the following C# code to work as expected:

using System;
using System.Runtime.InteropServices;
using SQLDMO;

namespace BackupWithEvents
{
    /// <summary>
    /// Class that drives from SQLDMO.BackupSink to implement event handlers
    /// </summary>
    class App : SQLDMO.BackupSink
    {
        [MTAThread]
        static void Main(string[] args)
        {
            App app = new App();
            app.Backup();
        }

        public void Backup()
        {
            SQLServer2Class server = new SQLServer2Class();
            Databases databases;
            Database2 database;

            Backup2Class backup = new Backup2Class();

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

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

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

            SQLDMO.BackupSink_CompleteEventHandler ceh = new SQLDMO.BackupSink_CompleteEventHandler(Complete);
            backup.Complete += ceh;

            SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(PercentComplete);
            backup.PercentComplete += pceh;

            SQLDMO.BackupSink_NextMediaEventHandler nmeh = new SQLDMO.BackupSink_NextMediaEventHandler(NextMedia);
            backup.NextMedia += nmeh;

            backup.SQLBackup(server);

            server.DisConnect();
        }

#region Implementation of BackupSink
        public void Complete(string Message)
        {
            Console.WriteLine("Complete {0}", Message);
        }

        public void PercentComplete(string Message, int Percent)
        {
            Console.WriteLine("PercentComplete {0} {1}", Message, Percent);
        }

        public void NextMedia(string Message)
        {
            Console.WriteLine("NextMedia {0}", Message);
        }
#endregion
    }
}

Download BackupWithEvents.zip

The wrong results

However when you look at the results is shows like:
server (local)
database pubs
Complete [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE successfully processed 145 pages in 0.245 seconds (4.819 MB/sec).
Complete [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE successfully processed 145 pages in 0.245 seconds (4.819 MB/sec).
Complete [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE successfully processed 145 pages in 0.245 seconds (4.819 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:

using System;
using System.Runtime.InteropServices;
using SQLDMO;

namespace BackupWithEvents
{
    /// <summary>
    /// SQL-DMO events using UCOMIConnectionPoint*
    /// </summary>
    class App
    {
        [MTAThread]
        static void Main(string[] args)
        {
            App app = new App();
            app.Backup();
        }

        public void Backup()
        {
            try
            {
                SQLServer2Class server = new SQLServer2Class();
                Databases databases;
                Database2 database;
                Backup2Class backup = new Backup2Class();

                server.LoginSecure = true;
                server.Connect("(local)", null, null);
                Console.WriteLine("server {0}", server.Name);
                databases = server.Databases;
                database = (Database2) databases.Item("pubs", null);
                Console.WriteLine("database {0}", database.Name);

                UCOMIConnectionPointContainer CnnctPtCont = (UCOMIConnectionPointContainer) backup;
                UCOMIConnectionPoint CnnctPt;
                BackupSink bs = new BackupSink();
                Guid guid = new Guid("10021F09-E260-11CF-AE68-00AA004A34D5"); // UUID of SQLDMO Backup Event Sink
                CnnctPtCont.FindConnectionPoint(ref guid, out CnnctPt);
                int iCookie;
                CnnctPt.Advise(bs, out iCookie);

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

                CnnctPt.Unadvise(iCookie);

                server.DisConnect();
                backup = null;
                server = null;
            }
            catch(System.Runtime.InteropServices.COMException ex)
            {
                Console.WriteLine(ex);
            }
            catch(System.Exception ex)
            {
                Console.WriteLine(ex);
            }
        } // public void Backup()

    } // class App

#region Implementation of BackupSink
    class BackupSink : SQLDMO.BackupSink
    {
        public void Complete(string Message)
        {
            Console.WriteLine("Complete {0}", Message);
        }

        public void PercentComplete(string Message, int Percent)
        {
            Console.WriteLine("PercentComplete {0} {1}", Message, Percent);
        }

        public void NextMedia(string Message)
        {
            Console.WriteLine("NextMedia {0}", Message);
        }
    } // class BackupSink
#endregion

} // namespace BackupWithEvents

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

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 Web Master.
Copyright © 1991-2005 SQLDev.Net. All rights reserved.
Last modified: 04/06/05.