This is the first in what I hope will be a series of posts about my attempts to make this particular project better.

A couple months ago, I took over a pretty cool C#/WinForms project that deals with RFID scanning.  I was given the green light to refactor the application as needed, which is awesome because the application really needs it.   I’ve been working on a new feature which consists of several data-entry screens and three reports.  The screens and reports are all hitting about a dozen new tables I added to the system.

One of the requirements of the application is that as new versions are installed at customer sites, it should be able to handle updating the local databases without the use of external scripts or setup applications.  Basically they want to be able to send an updated .exe and application .dll to the customer, have them run it and know that the local database schema has been updated (if needed).  While I have my own opinions on this, there are bigger fish to fry, so I decided to follow the existing method for database updates.

When I first dug into the code, I saw many, many methods that had hard-coded SQL in them for creating new database objects, altering existing objects, etc.  Ugh.  Here’s an example of what I’m talking about:

string sqlCmd = "create table Accounts (" +
    "AccountID varchar(50) NOT NULL PRIMARY KEY," +
    "ClientName varchar(50) NULL)";

SqlCommand dcmd;
int rows;

dcmd = new SqlCommand(sqlCmd, dc);

try
{
    rows = (int)dcmd.ExecuteNonQuery();
}
catch (Exception e)
{
    // log the exception
}

Ok, now there are obviously more problems here than the hard-coded SQL, but that’s not the point of this post.

When the application starts, it connects to the database and checks the “VerInfo” table.  It grabs the “current” database version from this table and then executes “upgrade” code (like the code above).  It is smart enough to perform all the upgrades it needs in order to become completely current.

private void CheckToUpgrade()
{
    int version = GetDbVersion();

    if (version <= 102)
    {
    DbTo103();
    SetDbVersion(103, DateTime.Parse("6-1-2007 12:00:00 PM"), "Added Accounts Table");
    version = 103;
    }

    if (version <= 103)
    {
    DbTo104();
    SetDbVersion(104, DateTime.Parse("6-19-2007 12:00:00 PM"), "Added AccountID/ClientName field to Synch Table");
    version = 104;
    }

    if (version <= 104)
    {
    DbTo105();
    SetDbVersion(105, DateTime.Parse("6-21-2007 12:00:00 PM"), "Added SyncLog table");
    version = 105;
    }

    if (version <= 105)
    {
    DbTo106();
    SetDbVersion(106, DateTime.Parse("9-16-2008 12:00:00 PM"), "Added several new tables table to support Admin station.");
    version = 106;
    }
}

In the whole scheme of things, NOT the way I would have approached this particular problem, BUT I also need to work within the constraints of this client (and their clients).  They do NOT want external scripts that have to be manually executed.  Ok, I’m fine with that.  The application also doesn’t have a setup because they want to easily do x-copy installs. 

Fine.  So, my solution to this particular problem (the hard-coding of SQL statements in the code) was to:

• Take my SQL scripts that I already generate for checking in to svn and add them to my project as embedded resources.  This gets me away from hard-coding the SQL (and worrying about proper escaping, quote matching ,etc.) and allows me to easily change the scripts (either by hand or by modifying the objects in the database and re-scripting from the database).  It also allows me to do more such as checking to see if the object exists within the script itself.  As one of my reviewers pointed out, this is still “hard-coding”, but honestly, isn’t this better than hard-coding the actual “create” and “alter” statements?
• I created a method named “ExecuteSqlUsingSMOFromEmbeddedResource(string resourceName) and used SMO to execute the embedded scripts.  I used SMO (which was already referenced in the project) so I wouldn’t have to worry about stripping “GO” statements from the scripts.  Yea, the method name is a bit verbose, but I’d rather see a long name like that than “execSQL”.  I wanted to make it clear exactly what I was doing to any other devs that look at this code.

private void ExecuteSqlUsingSMOFromResource(string resourceName)
{
    SqlConnection connection = this.dc;
    Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(new ServerConnection(connection));
    if (server != null)
    {
        using (StreamReader s = new StreamReader(this.GetType().Assembly.GetManifestResourceStream(resourceName)))
        {
             string createScript;
             createScript = s.ReadToEnd();
             server.ConnectionContext.ExecuteNonQuery(createScript);
        }
    }
}

I actually feel a lot more comfortable with this solution.  I have control over the scripts, and if/when a table/object change is needed, I can make them in one place, script them out and call it good.  As for dependencies…well, right now I’m simply making sure objects are created in a specific order and then applying all indexes, etc. at the end.  It works well for my very limited scenario.

So, what do you think?  Good idea?  Is there a better way?  One of my reviewers mentioned migrator.net as an alternative, so I might look into that for future revisions.  Another mentioned a tool from Red Gate.  As the title of this post suggests, my solution is a bit better than the original, but it can still be improved.  It is a baby step toward a better, more robust solution.  What that final solution is remains to be seen.