Side by Side SQL Comparer in C#

• Download source code - 26.8 KB

Introduction

I have written some database tools, one of which is a schema comparison tool for SQL Server. I needed a component which would let me compare elements of the schema in two databases, highlighting any lines that had differences. Unfortunately, I couldn't find anything that did exactly what I wanted. I did, however, find three articles on CodeProject which I knew would get me most of the way there:

• StringTokenizer by Andrew Deren
• A Generic, Reusable Diff Algorithm in C# - II by Michael Potter
• Syntax highlighting textbox written in C# by Uri Guy

I hope these guys are happy for me to extend their work - I didn't have time to check before submitting the article...

So, I had something to tokenize strings (the basis of syntax highlighting), a diff algorithm which would find the differences in the text for me, and a syntax highlighting text box (which I actually just used as a reference for the RTF control codes).

Using the Code

This component is pretty much simplicity itself. There are a couple of limitations, which I will probably address at some point:

• The font cannot be changed, neither can its size
• The number of digits that make up a line number cannot be changed
• The DiffEngine is locked into 'SlowPerfect' mode which might not work well for larger diffs
• The highlighting colours cannot be changed
• The SQL parsing is very basic and doesn't really go the full monty

To use the component, all you need to do is the following:

using (TextReader tr = new StreamReader(@"c:\1.sql"))
{
    sideBySideRichTextBox1.LeftText = tr.ReadToEnd();
}
using (TextReader tr = new StreamReader(@"c:\2.sql"))
{
    sideBySideRichTextBox1.RightText = tr.ReadToEnd();
}
sideBySideRichTextBox1.CompareText();

And, that's it. You simply load the left text and the right text, and then call the CompareText method. I haven't shipped the code with any SQL to compare - so you'll have to dig out your own!

Methodology

The CompareText function really goes through three steps - first, on construction, it loads the keywords and function names that it recognises into a hash table. Then, when calling CompareText(), it performs the other two steps, which are using the DiffEngine to get a diff of the two sides, and then performing the syntax highlighting and output.

The syntax highlighting is always performed using the colour of the text, and the difference highlighting is always performed using the colour of the background.

Each side has its own state object which remembers whether we are in a persistent parsing state (for which, I had to expand the StringTokenizer a bit), what the last colour and highlight we used was, and what the RTF text is.

There are a few helper functions in there which aid with this last step:

• getSQLFormattedString() - takes a line, tokenizes it, and then applies the syntax highlighting.
• SetColour() / SetHighlight() - emits the RTF to set the colour and highlight (if needed) and stores the current colour in the state object.
• CheckTokenListAndOutput() - checks to see if the current token exists in the hash table, and highlights it appropriately - plain text, if not.
• AppendLineNumber() - adds the line number to the start of each line.

Points of Interest

The rich text boxes on the left and right are scroll-locked together, meaning that as you scroll one, the other scrolls with it. This is handled by the ApplyScroll function:

private void ApplyScroll(RichTextBox Source, RichTextBox Target, ScrollBarDirection Direction)
{
    // unhook target from relevant event, otherwise we end up in an infinite loop!
    switch (Direction)
    {
        case ScrollBarDirection.SB_VERT:
        {
            Target.VScroll -= rtb_VScroll;
        } break;
        case ScrollBarDirection.SB_HORZ:
        {
            Target.HScroll -= rtb_HScroll;
        } break;
    }

    IntPtr ptrLparam = new IntPtr(0);
    IntPtr ptrWparam;

    // Prepare scroll info struct
    SCROLLINFO si = new SCROLLINFO();
    si.cbSize = (uint)Marshal.SizeOf(si);
    si.fMask = (uint)ScrollInfoMask.SIF_ALL;

    // Get current scroller posion
    GetScrollInfo(Source.Handle, (int)Direction, ref si);

    // if we're tracking, set target to current track position
    if ((si.nTrackPos > 0) || ((si.nTrackPos == 0) && (si.nPos != 0)))
    {
        si.nPos = si.nTrackPos;
    }

    // Reposition scroller
    SetScrollInfo(Target.Handle, (int)Direction, ref si, true);
    ptrWparam = new IntPtr(SB_THUMBTRACK + 0x10000 * si.nPos);

    // send the relevant message to the target control, and rehook the event
    switch (Direction)
    {
        case ScrollBarDirection.SB_VERT:
        {
            SendMessage(Target.Handle, WM_VSCROLL, ptrWparam, ptrLparam);
            Target.VScroll += new EventHandler(this.rtb_VScroll);
        } break;
        case ScrollBarDirection.SB_HORZ:
        {
            SendMessage(Target.Handle, WM_HSCROLL, ptrWparam, ptrLparam);
            Target.HScroll += new EventHandler(this.rtb_HScroll);
        } break;
    }
}

What this does is apply the scroll position from one text box to the other. While setting the scroll position, you need to unhook from the scroll event on the target text box, otherwise, you end up in an infinite loop (so left applies to right, right gets event, right applies to left, left gets event, left applies to right...).

Another gotcha here is setting the scroll position while the user is tracking (scrolling with the mouse held down over the scroll bar). The bit of code that handles this is:

// if we're tracking, set target to current track position
if ((si.nTrackPos > 0) || ((si.nTrackPos == 0) && (si.nPos != 0)))
{
    si.nPos = si.nTrackPos;
}

This basically says that if the track position is set, or the track position is 0 and the previous resting position is not 0, then set the target position to the track position from the source.

History

This is the initial release of this component, no history as yet!