Over the last number of years I have seen a lot of code which has been used to generate lots of dynamic sql. There has always been one thing in common with the way that I have seen this be done. Typically the approach is to have a string and just directly append everything to the end of the string during a button press or some such from the interface. I have seen cases where the c# code to produce a dynamic query have been over 200 lines. It would typically resemble something along the lines of this.

void MyEvent_Click() {
	string sql = string.Empty;
	sql += "ALTER INDEX [" + IndexName + "] "
	sql += "ON [" + TableName + "] " + Action;
}

The above is a simple case of making some dynamic sql. It is quite common to have something like above buy using dynamic joins and multiple optional conditions. Other bad things also start to happen where the code is then copied from place to place. It becomes difficult to maintain and almost impossible to test all the posisble combinations of options in the dynamic query. Not the mention that the code becomes more complex when you start to add join's and where clauses at the same time it causes problem because they both need to be added to different locations to the string.

Here is an alternative method. Simply come up with a class which sole purpose is to generate the sql for the query. Then the usage in the user interface code becomes significatly cleaner because it is only moving options / choices into the structure that will eventually build the query. This does also work with any other sql statement. The basic principle is to store all the required information in non sql form until the sql is actually required to be executed. This has a number of benifit's. It will also allow you to modified any part of the sql statement at any time in your code.

Here is a simple example that I pulled out of the index rebuild tool I made.

public class IndexWorkItem
{
    public string TableName = null;
    public string IndexName = null;
    public IndexWorkItemAction Action = IndexWorkItemAction.None;

    public SqlCommand ToSqlCommand()
    {
        if (Action == IndexWorkItemAction.None)
            return;

        SqlCommand sql = new SqlCommand(string.Format(@"
            ALTER INDEX [{1}] ON [{0}] {2}
        ", TableName, IndexName, ActionToSQL(Action)));

        return sql;
    }

    private string ActionToSQL(IndexWorkItemAction Action)
    {
        switch (Action)
        {
            case IndexWorkItemAction.Rebuild:
                return "REBUILD";
            case IndexWorkItemAction.Reorganize:
                return "REORGANIZE";
            default:
                throw (new NotImplementedException());
        }
    }
}

public enum IndexWorkItemAction
{
    None = 0,
    Reorganize = 1,
    Rebuild = 2
}

The main benifits of doing this are quiet clear.

1. Easy debugging. You can seperate the dynamic query away from the interface code.
2. Easy to test. Unit tests can be created to test almost every possible combination of the query.
3. Testing has a major advantage because you can ensure backwards compatibility from existing code if the class changes.
4. Cleaner / easy to maintain code.
5. Possible to support multiple database engine's by adding additional functions. ToSqlCommand / ToMySql / ToOracle / etc..
6. Easy to expand to support more options.

Something that seems quite easy to do is to parse a url. This is actually very easy in c#. However if you search on google you will see all sorts of solutions to it. Using regular expressions and various other ways to do it. Most of which I have always found really ugly. If your going for this method you really have to think about the fact that it has been a problem now for over 20 years and there must be a more common solution.

Some of these extreme methods may include something like this.

Protected Function ExtractDomainFromURL(ByVal sURL As String) As String
	Dim rg As New Regex("://(?<host>([a-z\d][-a-z\d]*[a-z\d]\.)*[a-z][-a-z\d]+[a-z])")
 
	If rg.IsMatch(sURL) Then
        	Return rg.Match(sURL).Result("${host}")
	Else
		Return String.Empty
	End If
End Function

Something like that is may work but. Can you read it again in six months time?

What about the path? What about correctly decoding the path? What about the paramaters?

You can do this in c# by adding a reference to System.Web and using the following code.

class Program
{
    static void Main(string[] args)
    {
        Uri tmp = new Uri("http://www.google.co.uk/search?hl=en&q=parsing+a+url+in+c%23&aq=f&aqi=g1g-j9&aql=&oq=");

        Console.WriteLine("Protocol: {0}", tmp.Scheme);
        Console.WriteLine("Host: {0}", tmp.Host);
        Console.WriteLine("Path: {0}", HttpUtility.UrlDecode(tmp.AbsolutePath));
        Console.WriteLine("Query: {0}", tmp.Query);
        NameValueCollection Parms = HttpUtility.ParseQueryString(tmp.Query);
        Console.WriteLine("Parms: {0}", Parms.Count);
        foreach (string x in Parms.AllKeys)
            Console.WriteLine("\tParm: {0} = {1}", x, Parms[x]);

        Console.ReadLine();
    }
}

The program will produce the following output. With a correctly decoded url and access to the query string.

Protocol: http
Host: www.google.co.uk
Path: /search
Query: ?hl=en&q=parsing+a+url+in+c%23&aq=f&aqi=g1g-j9&aql=&oq=
Parms: 6
        Parm: hl = en
        Parm: q = parsing a url in c#
        Parm: aq = f
        Parm: aqi = g1g-j9
        Parm: aql =
        Parm: oq =

Enjoy 

Somethnig that I wanted to do the other day was to parse out the different parts of and email address. The user and the domain part. However the data I was using was quite poor. I also wanted to be able to use this information in a computed column.

First of all I tried it this way.

User part

left([EMailAddress],charindex('@',[EMailAddress])-(1))

Domain part

right([EMailAddress],len([EMailAddress])-charindex('@',[EMailAddress]))

Which of course would crash on poor data. So they very quickly turned into functions so they were easyier to change and also decided that they should return null if there was no '@' in the data.

CREATE FUNCTION EMailExtractDomain
      (@EMail VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
      DECLARE @idx INT
      DECLARE @domain VARCHAR(MAX)

      SELECT @idx = CHARINDEX('@', @EMail)
      IF @idx = 0
            RETURN NULL

      SELECT @domain = SUBSTRING(@EMail, @idx + 1, LEN(@EMail))
      RETURN @domain
END
GO

CREATE FUNCTION EMailExtractUser
      (@EMail VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
      DECLARE @idx INT
      DECLARE @domain VARCHAR(MAX)

      SELECT @idx = CHARINDEX('@', @EMail)
      IF @idx = 0
            RETURN NULL

      SELECT @domain = SUBSTRING(@EMail, 0, @idx)
      RETURN @domain
END
GO

I also ran them though a set of quick tests.

SELECT dbo.EMailExtractUser('nobody@example.com'), dbo.EMailExtractDomain('nobody@example.com')
SELECT dbo.EMailExtractUser('example.com'), dbo.EMailExtractDomain('example.com')
SELECT dbo.EMailExtractUser(''), dbo.EMailExtractDomain('')
SELECT dbo.EMailExtractUser(NULL), dbo.EMailExtractDomain(NULL)

Enjoy 

Many people are not aware of this. It is possible since .NET 3.5 to extend any class in c# to make it appear that extra functions have been added to a class. Here is a short example of being able to add an extra function to the bool class / type that exists in c#

public static class ExtenderBool
{
    public static string ToHuman(this bool b)
    {
        if (b)
            return "Yes";
        return "No";
    }

}

You can then use this in c# so long as the namespace is in scope from the calling function. Here is a quick example of a program to test it.

class Program
    {
        static void Main(string[] args)
        {
            bool tmp = true;

            Console.WriteLine("Value: {0}", tmp.ToHuman());
            Console.ReadLine();
        }
    }

The above works because the compiler will figure out that you want it to be able to use the function with that class as a argument to the static function though It does not actually add it to the class it self.

What is GLBP?

GLBP is a protocol for load balancing routers but make them appear to be a single router to other devices on a network. GLBP stands for gateway load balancing protocol. An example of a situation where this could be used is for balancing between two broadband connections for a small to medium office. As far I as am aware it will only work with cisco routers and nobody else support the GLBP protocol. A full description can be found on the cisco web site for the command reference.

How does it work?

It works the same way as other redundant gateway protocols work. Like HSRP or VRRP. However in GLBP instead of one or other router being the active gateway both routers share an ip address to make them both active in the setup. So as an added benefit of load balancing you can also have the added benefit of redundancy for free. It is possible for the device to share a single ip address because it will only ever advertise one mac / arp address to each host that requests the gateway.

Where is the benefit?

What it will not be able to do is to double an internet connection bandwidth to a single machine when nat is involved. What it will do is spread the load from multiple machines into two or more internet connections. So it is best suited to growing offices where there are too many people for a single internet connection but not yet enough to consider getting a costly leased line.

More...