Monday, November 17, 2008

The importance of SQL Check Constraints

Not long ago I developed a database that was missing the proper check constraints on the columns. During the development of the application this was not a problem because I was doing all of the validation in the application. For example, I was checking that phone numbers and zip codes all used the same formats. The application was base on Volunteers and Events so searches against the database were based on fields such as phone numbers and zip codes to find volunteers. After the application was implemented into production all was going well until they noticed that many volunteers with matching phone numbers and zip codes were not coming back in the searches. I found out later that backend imports were being performed against the database. The import program had phone numbers and zip codes that were formatted differently. When the SQL Query was executed it was looking for a phone number in the format of ‘(888)222-2222’. Although the volunteer existed in the database, the phone number was formatted such as this: 888-222-2222 and the records were not returned. If I would have put the proper check constraints on my phone number and zip code columns this would not have been a problem.

http://msdn.microsoft.com/en-us/library/ms179491.aspx

Wednesday, November 5, 2008

A fast and easy console shell - Part 2

In part one I talked about how to build a fast and easy console shell. In part two, I want to talk about how to handle parameters that are passed in the command (well not passed in). Using this type of shell, I see no reason why parameters need to be passed in with the command. For example, the command “Start service ‘servername’”. The runtime needs to find where the base command ends and where the parameters start. Also, there is that problem of parameter ordering. I found, that the best way to handle this, is do just use “Console.ReadLine()”. For example,

public void Exeucte()

{

     Console.WriteLine( “Service to start:”);

     string serviceName = Console.ReadLine();

     StartService( serviceName );

}

Pretty easy! This way also allows you to enter and display defaults in case the user does not enter a value or just wants to use the default.

Monday, October 27, 2008

A fast and easy console shell - Part 1

There are many times when I need to have human interaction with a service or some kind of business logic and do not want to go through the hassle of writing a GUI for it. In these cases I always use a console application. Console applications are easy to use and provide a quick and dirty way of getting things done quickly. However, if you have a lot of things that need to go into the application things can get ugly fast. When I say console applications, I’m talking about a console shell. An application that you start and has a command prompt where the user can type in a command to perform an action. Other types of console applications are created just for the purpose of performing an action by calling the .exe and passing command line parameters to it. A good example of a console shell application is NetSh.exe. As I said before things can get ugly fast. The other day I was working on just this type of solution where I did not need a user interface for the service I was writing just a way to monitor events or send the service commands. So, I started to write the console shell and it started to get bad—if statements, switch statements, all bad. I took a little time and focused on this (this is not something I would normally spend a lot of time thinking about because it’s just a tool) and came up with a good and fast way to implement a console shell and keep it clean.

1) The first step is to create a base command class:

public abstract class ConsoleCommand
{
public ConsoleCommand ( )
{

}

public void Execute( string parms )
{
ExecuteCore( parms );
}

protected abstract void ExecuteCore( string parms );
}

2) Create a class level attribute.

[AttributeUsage(AttributeTargets.Class)]
public class ConsoleCommandAttribute : Attribute
{
public ConsoleCommandAttribute( string commandName )
{
CommandName = commandName.ToUpper();
}

public string CommandName { get; set; }

}


3) Create a new console command.

[ConsoleCommandAttribute ("Run sample" )]
public class RunSampleCommand : ConsoleCommand
{
public RunSampleCommand ( )
{

}

protected override void ExecuteCore( string parms )
{
Console.WriteLine( "Command executed" );
}
}

4) The next step is to find and load all of the commands when the application starts up.

static void LoadCommands( )
{
Assembly ass = Assembly.GetEntryAssembly( );

var query = ( from t in ass.GetTypes( )
where
Attribute.IsDefined( t ,
typeof(ConsoleCommandAttribute) ,
false ) == true
select t ).ToList( );

foreach ( Type type in query )
{
ConsoleCommandAttribute att =
Attribute.GetCustomAttribute( type ,
typeof(ConsoleCommandAttribute) ,
false ) as ConsoleCommandAttribute;

// You could create the type here or wait until it is ask for. The latter is the better option.
ConsoleCommand cmd =
(ConsoleCommand)Activator.CreateInstance( type );

CommandList.Commands.Add( att.CommandName , cmd );
}
}
}

5) Next handle the command the user entered.

String cmd = Console.ReadLine();

cmd = cmd.ToUpper();

if ( CommandList.Commands.ContainsKey( cmd ) )
{
try
{
CommandList.Commands [ cmd ].Execute( string.Empty );
}
catch ( Exception ex )
{
Console.WriteLine( ex.Message );
}
}
else
{
Console.WriteLine( "Command not found." );
}
}
Once the command has finished, you will need to call back to the method that waits for the user to enter a new command.

In part two, I will talk about how to handle parameters without string parsing.