Click here to Skip to main content
Click here to Skip to main content

Side by Side SQL Comparer in C#

, 19 Jun 2008
Rate this:
Please Sign up or sign in to vote.
A side by side text comparison control with T-SQL syntax highlighting.

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:

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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Matt Whitfield
Architect HTEC Ltd / Atlantis Interactive
United Kingdom United Kingdom
I started using computers when I was 6. It was all downhill from there!

Comments and Discussions

 
GeneralMy vote of 5 PinmvpKanasz Robert24-Sep-12 6:10 
Question"{}" bug Pinmemberjasonangela20-Aug-12 16:26 
QuestionReally Good. PinmemberMember 82521736-Oct-11 10:24 
GeneralSuperb Pinmemberpsymon2515-Sep-10 4:58 
GeneralThis doesnt work with large files PinmemberCoderMan1219-Nov-09 6:56 
GeneralRe: This doesnt work with large files PinmemberMatt Whitfield11-Nov-09 0:19 
GeneralRe: This doesnt work with large files PinmemberCoderMan12118-Nov-09 4:57 
General1.sql and 2.sql Pinmembercakirhal1-Jul-08 20:18 
GeneralRe: 1.sql and 2.sql PinmemberMatt Whitfield1-Jul-08 22:13 
Questionhow about a snapshot? PinmemberUnruled Boy20-Jun-08 17:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 19 Jun 2008
Article Copyright 2008 by Matt Whitfield
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid