Monthly Archives: April 2010

Windiff

Recently, I had to compare 2 xml files. They were massive, and the changes were subtle. This is not something which should be done with the naked eye.

A guy at work suggested I use a utility which is one of the Visual Studio tools called Windiff. This is easily fired up by typing the command Windiff at the Visual Studio 2008 command prompt.

It’s pretty intuitive once it has opened up. Upon opening both files, you’ll see the following. As you can see, the application is telling us that there is at least 1 difference between the two files:
windiff pic

If you double click on that red text (or click on it, then click the expand button), then you’ll see something like:

windiff pic2

There, you can clearly see the difference between the two files. That is really handy if the files have thousands of lines of text and are completely identical, but for 1 line.

Using a From Clause in an Update Statement

So, lets say you have a table of data as follows:

And lets say you have a destination table with the same schema. But the data is not necessarily in the same order:
We’re told that the combination of first name and last name are unique in this schema. Now, the idea is to copy all of the primary keys in the top table to fill the nulls in the bottom table. Of course, this is easily done with a handful of update statements here. But what if both tables have thousands of rows? That’s where we can use SQL Server’s UPDATE statement with a FROM clause.

UPDATE ContactTableNew
SET id = cs.id
FROM ContactTableNew cn INNER JOIN ContactTableSource cs ON cs.fname = cn.fname and cs.lname = cn.lname

OK. So exactly what have we done? It’s kinda like a join. Well, it is a join. We have taken data from one table and updated the new table with that data.

Using Enums to Help Build a Search Query

In my last post, I talked about a search and separating quoted objects from free-standing words. As part of that search code, I wrote a method which helps build the search query, depending upon whether an AND radio button is selected or an OR radio button. As can be seen, we have a finite set of options here – AND and OR. Smells like an enum to me. So I created an enum:

private enum SearchType { AND, OR };

Then, a snippet of code from the method with uses that enum, which is passed in as a parameter:

if (nonQuotedSearchWords != null)
{
        foreach (string searchTermWord in nonQuotedSearchWords)
        {
             queryBuilder.Append(" CONTAINS(ALL, '" + searchTermWord + "') " + Enum.GetName(typeof(SearchType), typeOfSearch));
        }
}

typeOfSearch is the actual enum of type SearchType (the enum which I defined above).

Separating Quoted Words from Other Search Terms

I wrote a bit of code to perform a search using Sharepoint search functionality. The first thing I implemented was the keywords AND and OR – the normal boolean flags for searches. The next thing I implemented needed a bit more consideration. I wanted any words which were surrounded by quotes to be considered as 1 block of text. For example, “this is a quote” would be a search for the words this is a quote, in that order and with each word required. So the problem that I needed to solve was to separate out those blocks of text from the other words in the search.

My approach was to populate a generic dictionary with two kinds of objects – quoted blocks of text and separate words. The separate words could easily be rounded up into a string array. The quoted blocks needed to be separated out. I did that using a regular expression. Once I had identified those blocks of text, I added each one to the dictionary, with a key that had a sequential numbering scheme:

        protected Dictionary<string, object> ParseSearchTerms(string searchTerm)
        {
            string[] words = null;
            Dictionary<string, object> items = new Dictionary<string, object>(StringComparer.Ordinal);

            //  If there's no quoted text, just add a string array to the dictionary.
            if (searchTerm.IndexOf('"') < 0)
            {
                words = searchTerm.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
                items.Add("nonQuotedWords", words);
            }
            else
            {
                //  Identify quoted text and add it to the dictionary with the key "quotedText".
                //  Remove such text from the string to be left with a bunch of words (or nothing)
                //  which will be added as a simple string array, with the key "nonQuotedWords".
                Regex regex = new Regex("\"[^\"]*\\w*\"");
                MatchCollection matches = regex.Matches(searchTerm);
                int counter = 1;
                foreach (Match match in matches)
                {
                    items.Add("quotedText" + counter++, match.Value);
                    searchTerm = searchTerm.Replace(match.Value, string.Empty);
                }

                if (searchTerm.Length > 0)
                {
                    words = searchTerm.ToString().Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
                    items.Add("nonQuotedWords", words);
                }
            }

            return items;
        }