Monday, November 24, 2008

Making MFC controls available for WinForms through MFC subclassing

When trying to figure out how to use some of my MFC controls in a WinForms application, I came across this article by Rama Krishna Vavilala. As his article was targetting the .NET 1.1 framework, I decided to rework it for .NET 2.0. The main difference is the switch from using Managed Extension for C++ to using C++/CLI.

Static Win32 library for C3DMeterCtrl

As is the case in Rama's article, I will also use Mark C. Malburg's Analog Meter Control. So first, create a Win32 static library project with support for MFC and precompiled headers called "ControlS" (S stands for static). This will contain the MFC code for the existing 3DMeterCtrl control. Place the files 3DMeterCtrl.cpp, 3DMeterCtrl.h and MemDC.h in the "ControlS" project. Modify the 3DMeterCtrl.cpp file to remove the line #include "MeterTestForm.h".

The .NET designer and runtime will call functions that try to talk to your MFC control even before its window handle is created. In case of the C3DMeterCtrl, I needed to add this function call at the beginning of the "UpdateNeedle" and "ReconstructControl" functions:

if (!GetSafeHwnd())
  return;

MFC library for the managed 'ThreeDMeter' control

To bridge the gap between MFC and .NET I'm going to use C++/CLI. This allows me to create a managed wrapper object around the MFC control.

Add an "MFC DLL" project, called "control". Go to the project properties and enable the common language runtime support (/clr). Using the "Add Class" wizard add a new control and call it "ThreeDMeter". Make these changes to the ThreeDMeter.h file:

  • #include the header file of the MFC control "..\ControlS\3DMeterCtrl.h"
  • Change the inheritance of the control to public System::Windows::Forms::Control
  • Add a private instance of C3DMeterCtrl to the class. Create it in the constructor and delete it in the finalizer. In "OnHandleCreated", call its "SubclassWindow" method using the .NET controls window handle.
The file "ThreeDMeter.h" should now look like this:
#pragma once

using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Windows::Forms;
using namespace System::Data;
using namespace System::Drawing;

#include "..\ControlS\3DMeterCtrl.h"

namespace Control {

public ref class ThreeDMeter : public System::Windows::Forms::Control
{
public:
ThreeDMeter(void)
{
  InitializeComponent();
  m_pCtrl = new C3DMeterCtrl();
}

protected:
//Finalizer
!ThreeDMeter()
{
  if (m_pCtrl != NULL)
  {
    delete m_pCtrl;
    m_pCtrl = NULL;
  }
}

//Destructor
~ThreeDMeter()
{
  if (components)
  {
    delete components;
  }

  //call finalizer to release unmanaged resources.
  this->!ThreeDMeter();
}

virtual void OnHandleCreated(EventArgs^ e) override
{
  System::Diagnostics::Debug::Assert(m_pCtrl->GetSafeHwnd() == NULL);

  m_pCtrl->SubclassWindow((HWND)Handle.ToPointer());

  Control::OnHandleCreated(e);
}

private:
System::ComponentModel::Container ^components;
C3DMeterCtrl* m_pCtrl;
};
In order to expose the properties of the MFC control in .NET, you need to implement them yourself. Add following code in the public section of the ThreeDMeter wrapper class.
event EventHandler^ OnValueChanged;

[property: System::ComponentModel::CategoryAttribute("Meter")]
property Color NeedleColor
{
  Color get()
  {
    if( m_pCtrl == NULL )
      throw gcnew ObjectDisposedException(ThreeDMeter::GetType()->ToString());
  
    return System::Drawing::ColorTranslator::FromWin32(m_pCtrl->m_colorNeedle);
  }
  
  void set(Color clr)
  {
    if (!m_pCtrl)
      throw gcnew ObjectDisposedException(ThreeDMeter::GetType()->ToString());
  
    AFX_MANAGE_STATE(AfxGetStaticModuleState());
  
    m_pCtrl->SetNeedleColor(ColorTranslator::ToWin32(clr));
  }
}

[property: System::ComponentModel::CategoryAttribute("Meter")]
property String^ Units
{
  void set(String^ units)
  {
    if (!m_pCtrl)
      throw gcnew ObjectDisposedException(ThreeDMeter::GetType()->ToString());
  
    AFX_MANAGE_STATE(AfxGetStaticModuleState());
  
    CString strUnits(units);
  
    m_pCtrl->SetUnits(strUnits);
  }
  
  String^ get()
  {
    if (!m_pCtrl)
      throw gcnew ObjectDisposedException(ThreeDMeter::GetType()->ToString());
  
    LPCTSTR szUnits = (m_pCtrl->m_strUnits);
  
    return gcnew String(szUnits);
  }
}

[property: System::ComponentModel::CategoryAttribute("Meter")]
property double Value
{
  double get()
  {
    if (!m_pCtrl)
      throw gcnew ObjectDisposedException(ThreeDMeter::GetType()->ToString());
  
    return m_pCtrl->m_dCurrentValue;
  }
  
  void set(double d)
  {
    if (!m_pCtrl)
      throw gcnew ObjectDisposedException(ThreeDMeter::GetType()->ToString());
  
    AFX_MANAGE_STATE(AfxGetStaticModuleState());
  
    m_pCtrl->UpdateNeedle(d);
  
    OnValueChanged(this, EventArgs::Empty);
  }
}

.NET Test application

In order to test the managed ThreeDMeter control, add a .NET "Windows application" project to the solution in your favorite language. Put the Three3Meter control on the form and add a timer control
Public Class Form1
  Private Sub Timer1_Tick(ByVal sender As System.Object, 
                          ByVal e As system.EventArgs) Handles Timer1.Tick

    If Me.ThreeDMeter1.Value <= 4 Then
      Me.ThreeDMeter1.Value += (Me.Timer1.Interval / 1000)
    Else
      Me.ThreeDMeter1.Value = -5
    End If
  End Sub
End Class

This will make the meter move from left to right.

Wednesday, November 19, 2008

Deleting absent, updating existing and inserting new rows in a table

Synchronizing a table based on a flat files is one of the more common tasks when dealing with databases. It should be a straight forward task to delete removed records, update changed records and insert new records but I often encounter horrible ways of achieving this goal.

I recently came across an implementation using a DTS package with a data-driven task in SQL Server 2000. For each line in the file it would launch several queries to detect if it was a new, updated or unchanged record (all glued together with VBScript) and then launch the appropriate query to update the database.

Searching a bit on the internet I came across this simple solution. The best way of doing this fast and reliably is by using the JOIN and LEFT JOIN clauses in your UPDATE, INSERT and DELETE commands to determine the status of the record. I would like to show my implementation of slightly more complicated requirements.

Preparation

First start by bulk inserting your data file into a staging table that has no constraints or indexes. This is the fastest way to get your data into the database. For the synchronisation queries (see below), you might want to consider using transactions if there is a risk of data corruption. You could also gain some speed by disabling the indexes while running the insert query.

In the following examples I will try to synchronize the accounts table using the tmp_accounts table as the staging table.

Deleting removed rows

DELETE accounts 
FROM accounts acc LEFT JOIN tmp_accounts tmp
ON  acc.category = tmp.category
AND acc.code     = tmp.code
WHERE acc.code IS NULL
   OR acc.category IS NULL

Fields code and category together uniquely define an account.
Using the LEFT JOIN with the FROM clause will ensure that all records from the accounts table are selected, even if they don't exist in tmp_accounts. The records that don't exist in tmp_accounts will have a NULL value for every field. So all records that exist in the accounts table but have NULL values in their tmp_accounts fields, need to be deleted.

Updating changed rows

UPDATE accounts SET
  description = tmp.description,
  type        = tmp.type,
  section     = tmp.section,
  audit_date  = tmp.audit_date,
  audit_user  = tmp.audit_user
FROM accounts     acc
JOIN tmp_accounts tmp
ON  acc.category = tmp.category
AND acc.code     = tmp.code
WHERE acc.description <> tmp.description
   OR acc.type        <> tmp.type
   OR acc.section     <> tmp.section

By using the JOIN clause, we are sure to work only on records that exist in both the accounts table and the tmp_accounts table. Accounts need to be update in case their description, type or section has changed.

Audit_user and audit_date are automatically filled in the tmp_accounts table using a DEFAULT and are always kept up-to-date in the accounts table.

Inserting new rows

INSERT INTO accounts
( category,
  code,
  description, 
  type,
  section,
  title_only, 
  division,
  override,
  allow_manual_entry, 
  audit_date, 
  audit_user
)
SELECT
  tmp.category,
  tmp.code, 
  tmp.description,
  tmp.type,
  tmp.section,
  tmp.title_only, 
  tmp.division,
  tmp.override,
  tmp.allow_manual_entry, 
  tmp.audit_date,
  tmp.audit_user
FROM tmp_accounts  tmp
LEFT JOIN accounts acc
ON  tmp.category = acc.category
AND tmp.code     = acc.code
WHERE acc.code IS NULL
   OR acc.category IS NULL

Using a LEFT JOIN makes sure we are working with all records in the tmp_accounts table, even if they don't exist in the accounts table. Records that don't exist in the accounts table will have NULL values for all of their fields. So all records that exist in tmp_accounts and have NULL values for the accounts fields, have to be inserted.

Audit_user and audit_date are automatically filled in the tmp_accounts table using a DEFAULT and are always kept up-to-date in the accounts table

Friday, September 5, 2008

Scripting with SQL Server Management Objects

When I needed to move about 28500 stored procedures from a SQL Server 2000 to a SQL Server 2005, I turned to the SQL Server Management Studio. It didn't offer any option to move the stored procedures but it did allow to script them to a text file. I launched the "Generate Scripts" task and noticed that after 8 hours of processing only 3500 stored procedures had been scripted. The process had also eaten all the RAM on my workstation.

It would takes ages at this rate so I cancelled the task and turned to the SQL Server Management Objects that are installed together with SQL Server Management Studio. According to MSDN, SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

The following code shows the C# application that I wrote to quickly generate scripts for all stored procedures in one database. In order to make it compile, you will need to add references to these assemblies:

  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoEnum
  • Microsoft.SqlServer.ConnectionInfo
What the application does in general is:
  • connect to the database
  • loops over the collection of all stored procedures
  • script it to a text file if it is not a system stored procedure

using System;
using System.IO;
using System.Text;
using System.Collections.Specialized;

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace SProcScripter
{
  class Program
  {
    static void Main(string[] args)
    {
        int success = 0;
        int failure = 0;

        StreamWriter sw = new StreamWriter("d:\\SProcs.sql");

        // Connect to server and database
        Server srv = new Server("myServerName");
        Database db = srv.Databases["myDatabaseName"];
        Scripter scrp = new Scripter(srv);

        scrp.Options.ScriptDrops = false;
        scrp.Options.WithDependencies = false;

        foreach (StoredProcedure sp in db.StoredProcedures)
        {
            try
            {
                Urn[] smoObjects;
                smoObjects = new Urn[1];
                smoObjects[0] = sp.Urn;

                if (!sp.IsSystemObject)
                {
                    Console.Write("Scripting " + sp.Name);

                    StringCollection sc = scrp.Script(smoObjects);
                    StringBuilder sb = new StringBuilder();

                    foreach (String st in sc)
                    {
                        sb.AppendLine(st);
                        sb.AppendLine("GO");
                    }

                    sw.WriteLine(sb.ToString());

                    success++;
                    Console.WriteLine(" done.");
                }
            }
            catch (Exception ex)
            {
                failure++;
                Console.WriteLine(" failed with exception: " + ex.Message);
            } // try - catch
        } // foreach stored procedure

        sw.Close();

        Console.WriteLine("Success: " + success.ToString());
        Console.WriteLine("Failure: " + failure.ToString());
        Console.WriteLine("Press any key");
        Console.ReadKey();

    } // void Main
  }
}

All stored procedures were generated within 30 minutes! I've tried to make this work faster using a ThreadPool but the SMO components are not thread-safe and all sort of unexpected errors and race conditions popped up. I needed to introduce several locks to keep the object out of each others way. I succeeded in making it work but due to these locks it wasn't really faster than the single threaded version above.

Thursday, September 4, 2008

Stand-Alone DTS Designer on SQL Server 2005

This article describes how to open the DTS Designer for SQL Server 2005 programmatically. It eliminates the need to open the SQL Server Management Studio if you just need to edit a DTS that was saved as a structured storage file. I’m building a command line utility in C# that takes the full path to a dts package as a parameter and opens it in the designer. You can find its VB.NET code here.

In my previous post, I already created a similar utility in VB.NET that opened the DTS Designer on PCs with SQL Server Enterprise Manager installed, which is the client application for SQL Server 2000. The source code is exactly the same. The only differences are that you'll need to install the DTS Designer Components from the Feature Pack for Microsoft SQL Server 2005 and that your project will reference the Microsoft.SQLServer.Dts80.dll assembly from the GAC

Visual Studio 2005 doesn't allow you to reference this assembly directly from the GAC. If you would browse to the GAC's folder (by default in the %windir%\assembly folder), the Assembly Cache Viewer (SHFusion.dll) shell extension will be actived which doesn't have an option to copy assemblies. You could disable this shell extension but it is just as easy to copy the assembly to a diffent folder using the command prompt:

  1. Click Start
  2. Click Run...
  3. Type 'cmd.exe' and press <enter>. The command prompt appears
  4. Type the following command:
copy %windir%\assembly\GAC_MSIL\Microsoft.SqlServer.Dts80\9.0.242.0__89845dcd8080cc91\Microsoft.SqlServer.Dts80.dll c:\data\

This will put a copy of the Microsoft.SqlServer.Dts80.dll to c:\data\ from where you can reference it in Visual Studio 2005. As soon as the project is finished and compiles you don't need this file anymore as your executable will use the one in the GAC.

In order to get started, open Visual Studio and create a new Windows application in C#. Put a reference to the Microsoft.SqlServer.Dts80.dll that you have copied to c:\data\

Create a ShowPkgDesigner function that takes the full pathname of an existing DTS package:

private static void ShowPkgDesigner(string sPackagePathName)
{
  DTS.CDTSLegacyDesigner designer = null;
  String packageName = String.Empty;
  String packageID = String.Empty;
  String packageVersionID = String.Empty;

  //* Remove quotes from packagePath
  sPackagePathName = sPackagePathName.Replace("\"", "");

  if (sPackagePathName == String.Empty ||
      ShowPkgBrowser(ref packageName, ref packageID,
                     ref packageVersionID, sPackagePathName))
{
  try
  {
    designer = new DTS.CDTSLegacyDesigner();

    designer.Initialize();
    designer.Location = 2;
    designer.PackageID = packageID;
    designer.PackageName = packageName;
    designer.ServerName = sPackagePathName;
    designer.VersionID = packageVersionID;

    //* Launch designer
    designer.ShowDesigner();
  }
  finally
  {
    if (designer != null)
    {
      designer.Dispose();
      designer = null;
    }
  }
}
}

Multiple DTS packages and versions can be safed in one structured storage file. The ShowPckBrowser function allows a user to select a specific version of a DTS package:

private static bool ShowPkgBrowser(ref String sPackageName,
                                  ref String sPackageID,
                                  ref String sPackageVersionID,
                                  String sPackagePathName)
{
 DTS.Package package = null;
 DTS.SavedPackageInfos packageInfoColl = null;
 DTS.SavedPackageInfo packageInfo = null;

 int selectedPackage = 0;

 package = new DTS.Package();
 packageInfoColl = package.GetSavedPackageInfos(sPackagePathName);

 if (packageInfoColl.Count == 1)
   selectedPackage = 1;
 else
 {
   PackageBrowser pckBrowser = new PackageBrowser();
  
   for (int i = 1; i <= packageInfoColl.Count; i++)
   {
     packageInfo = packageInfoColl.Item(i);
     pckBrowser.AddPackageVersion(
     packageInfo.VersionSaveDate.ToString(),
     packageInfo.PackageName);
   }
  
   if (pckBrowser.ShowDialog() == DialogResult.OK)
     selectedPackage = pckBrowser.SelectedPackage;
 }
  
 if (selectedPackage > 0)
 {
   packageInfo = packageInfoColl.Item(selectedPackage);
   sPackageName = packageInfo.PackageName;
   sPackageID = packageInfo.PackageID;
   sPackageVersionID = packageInfo.VersionID;
  
   return true;
 }
 else
 {
   return false;
 }
}

The code above uses a custom form that only contains a listbox. In the for loop, all versions and package names are added to that listbox. As soon as a user double-clicks on the desired version, the 'PackageBrowser.SelectedPackage' contains the index of that version. This is then used to retrieve the package name, id and versionId. I'll leave it up to the reader to implement the PackageBrowser component.

All that's left to do is to call the ShowPkgDesigner function from the main function. I want my executable to take the full path to the DTS package so that I can link it to "*.dts" files in Windows Explorers' "Open With..." functionality:

static int Main(string[] args)
{
  try
  {
    String packagePath = String.Empty;
    
    // Full path to the DTS package is the first command line option
    if (args.Length > 0)
    {
      if ((new System.IO.FileInfo(args[0])).Exists)
        packagePath = args[0];
      else
      {
        OpenFileDialog openFiledlg = new OpenFileDialog();
        
        openFiledlg.CheckFileExists = true;
        openFiledlg.Filter =
        "DTS Packages(*.dts)*.dtsAll Files(*.*)*.*";
        
        if (openFiledlg.ShowDialog() == DialogResult.OK)
        packagePath = openFiledlg.FileName;
      }
    }
    
    ShowPkgDesigner(packagePath);
    
    return 0;
  }
  
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message, "DTSDesigner90 Error",
    MessageBoxButtons.OK, MessageBoxIcon.Error);
    return -1;
  }
}

If no path is provided to the command line, an empty designer sheet is opened. If an invalid pathname is passed, the user will be prompted to browse to an existing file.

Having this command line utility has really improved my productivity when I was constantly editing and comparing DTS packages that were scattered all over the companies network. Being able to open a package by just double-clicking it is just that little bit more efficient than opening them through the SQL Server Management Studio.

Wednesday, August 13, 2008

Stand-Alone DTS Designer on SQL Server 2000

This article describes how to open the DTS Designer for SQL Server 2000 programmatically. It eliminates the need to open the SQL Server Enterprise Manager if you just need to edit a DTS that was saved as a structured storage file. In this article I'm building a command line utility in VB.NET that takes the full path to a dts package as a parameter and opens it in the designer. You can find its VB.NET code here.

I recently started working on a project involving the creation and modification of DTS packages that were scattered around on many different systems and file shares. What really annoyed me was the fact that you are forced to go via the SQL Server Enterprise Manager and connect to a random SQL Server, even if you wanted to open a DTS package that was saved to the file system as a structured storage file.

After some searching on the internet for a DTS API, I came across the MSDN article on Creating DTS Packages in Visual Basic. No explanation however was given on how to open the designer programmatically although the library (dtspck.dll) included an object called CDTSLegacyDesigner, which clearly was the object I was after.

The following VB.NET code shows how to load the designer with a empty design surface. You will have to add a reference to the COM component dtspck.dll first, which is installed together with the SQL Server 2000 Client Tools (such as Enterprise Manager, Query Analyzer...)

Try
  designer = New DTS.CDTSLegacyDesigner
  designer.Initialize()
  designer.ShowDesigner()
Finally     
  If Not designer Is Nothing Then
    designer.Dispose()
    Marshal.ReleaseComObject(designer)
    designer = Nothing
  End If
End Try

DTS packages are saved as a structured storage file. A single file can contain multiple DTS packages and multiple package versions. The following simplified code shows how to retrieve the necessary information about last package version stored in the DTS. You should loop over the packageInfoColl collection and allow users to select the version they wish to open.

Dim sPackageName As String  
Dim sPackageID As String  
Dim sPackageVersionID As String  
Dim sPackagePathName As String 'Full pathname to DTS package   
 
Try    
  Dim package As DTS.Package = Nothing  
  Dim packageInfoColl As DTS.SavedPackageInfos = Nothing  
  Dim packageInfo As DTS.SavedPackageInfo = Nothing  
  package = New DTS.Package 
  packageInfoColl = package.GetSavedPackageInfos(sPackagePathName) 
 
  '** Example: select the last saved version 
  packageInfo = packageInfoColl.Item(packageInfoColl.Count) 
  sPackageName = packageInfo.PackageName 
  sPackageID = packageInfo.PackageID 
  sPackageVersionID = packageInfo.VersionID
 
Finally 
  If Not package Is Nothing Then Marshal.ReleaseComObject(package) 
  If Not packageInfo Is Nothing Then Marshal.ReleaseComObject(packageInfo) 
  If Not packageInfoColl Is Nothing Then 
    Marshal.ReleaseComObject(packageInfoColl) 
  End If
End Try

When you have retrieved the packageName, packageID and packageVersion you can pass these as properties to your instance of the CDTSLegacyDesigner class as shown in the following code. The location property indicates that the DTS package should be loaded from the file system.

Try
  designer = New DTS.CDTSLegacyDesigner
  With designer
    .Initialize()
    .Location = 2
    .PackageID = packageID
    .PackageName = packageName
    .ServerName = packagePathName
    .VersionID = packageVersionID
  End With
 
  '** Launch designer
  designer.ShowDesigner()
 
Finally  
  If Not designer Is Nothing Then    
    designer.Dispose()
    Marshal.ReleaseComObject(designer)
    designer = Nothing 
  End If
End Try

Putting these code snippets together into a function application still takes some work but you can find my try on my googlepage.