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