Monday, November 24, 2008

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.

Wednesday, November 19, 2008

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.

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.

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