Click here to Skip to main content
13,502,677 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


54 bookmarked
Posted 19 Jun 2008

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.


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();

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!


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
    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.


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


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!

You may also be interested in...

Comments and Discussions

GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:10
mvpKanasz Robert24-Sep-12 6:10 
Question"{}" bug Pin
jasonangela20-Aug-12 16:26
memberjasonangela20-Aug-12 16:26 
QuestionReally Good. Pin
Member 82521736-Oct-11 10:24
memberMember 82521736-Oct-11 10:24 
GeneralSuperb Pin
psymon2515-Sep-10 4:58
memberpsymon2515-Sep-10 4:58 
GeneralThis doesnt work with large files Pin
CoderMan1219-Nov-09 6:56
memberCoderMan1219-Nov-09 6:56 
GeneralRe: This doesnt work with large files Pin
Matt Whitfield11-Nov-09 0:19
memberMatt Whitfield11-Nov-09 0:19 
GeneralRe: This doesnt work with large files Pin
CoderMan12118-Nov-09 4:57
memberCoderMan12118-Nov-09 4:57 
General1.sql and 2.sql Pin
cakirhal1-Jul-08 20:18
membercakirhal1-Jul-08 20:18 
GeneralRe: 1.sql and 2.sql Pin
Matt Whitfield1-Jul-08 22:13
memberMatt Whitfield1-Jul-08 22:13 
Questionhow about a snapshot? Pin
Unruled Boy20-Jun-08 17:53
memberUnruled Boy20-Jun-08 17:53 
GeneralNice Pin
Pete O'Hanlon20-Jun-08 8:48
mvpPete O'Hanlon20-Jun-08 8:48 
GeneralRe: Nice Pin
Bassam Saoud20-Jun-08 9:18
memberBassam Saoud20-Jun-08 9:18 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180417.1 | Last Updated 19 Jun 2008
Article Copyright 2008 by Matt Whitfield
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid