I recently upgraded from Team Foundation Server 2008 to Team Foundation Server 2010. This broke many msbuild tasks, including one that generates database scripts.

One of the prerequisites of the TFS upgrade was upgrading the SQL Server from 2005 to 2008. I found that the msbuild task was using DMO to generate the scripts and had been marked as deprecated, so I went about replacing the old COM library with SQL Management Objects, or SMO.

Someone is bound to mention that DMO works… why get rid of it. Here are my reasons:

1) It’s deprecated and will go away.
2) I had trouble getting it on my system as it required installing old software that had conflicts.
3) SMO is more efficient.

To begin using SMO, I added references from the GAC to the following assemblies: Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk.Sfc, and Microsoft.SqlServer.Smo. I then added using statements for Microsoft.SqlServer.Management.Smo and Microsoft.SqlServer.Management.Common.

The first major difference comes in connecting to the server. This was the DMO code.

SQLServer2Class svr = new SQLServer2Class();                
svr.Connect(serverName, uid, pwd);
Database2 db = (Database2)svr.Databases.Item(m.Groups["db"].Value, null);

Here is the corresponding code in SMO.

SqlConnection sqlConnection = new SqlConnection(ConnectionString);
ServerConnection serverConnection = new ServerConnection(sqlConnection);
Server server = new Server(serverConnection);
Database database = server.Databases[match.Groups["db"].Value];

One key difference in SMO is that it only pulls what you request by default. In other words, it’s rather lazy. You will have to set it up to make the request eager for the fields you need. Here are the ones I used.

server.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
server.SetDefaultInitFields(typeof(StoredProcedure), "IsEncrypted");
server.SetDefaultInitFields(typeof(StoredProcedure), "Owner");
server.SetDefaultInitFields(typeof(StoredProcedure), "Text");

One key point I need to make is the parameter “Text” in the statement above. There is no Text property on StoredProcedure. This initializes TextHeader and TextBody. If you pass either of those in as a parameter, you will receive an error (at least against the database version I’m running). I see this mistake in many blog postings.

The DMO version looped on db.StoredProcedures, had several nested if statements before finally generating the script file from the database.

sp.Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops | SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default,
    Path.Combine(outputDirectory, sp.Name + ".sql"),
    SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);

I changed the loop/ifs to a LINQ statement. StoredProcedures is IEnumerable, but not IEnumerable<T>. This made the query expression syntax cleaner than method syntax, so I used it.

var storedProcedures = (from StoredProcedure sp in database.StoredProcedures
                        where !sp.IsSystemObject && !sp.IsEncrypted 
                            && IsScriptedSchema(sp.Owner)
                            && sp.TextHeader.IndexOf("isGenerated=\"true\"") > 0
                        select sp).ToArray();

We were logging the number of scripts generated, so it was essential to call ToArray() so that Count() will get the count from memory (via the ICollection.Count property).

The script generation actually turned out to be a bit of a challenge. The options on DMO generates the DROP statements along with the CREATE statements. There is nothing similar in SMO. The system was expecting scripts similar to the DMO scripts from before, so I wanted to keep them the same. I created an extension method called ScriptDropAndCreate() to hide the details.

public static IEnumerable<string> ScriptDropAndCreate(this StoredProcedure sp)
{
    ScriptingOptions options = new ScriptingOptions
    {
        ScriptDrops = true,
        IncludeIfNotExists = true
    };

    var dropScript = sp.Script(options);
    var createScript = sp.Script();
    var script = dropScript.OfType<string>().Concat(createScript.OfType<string>()).ToArray();

    return script.AppendGoStatements();
}

private static IEnumerable<string> AppendGoStatements(this IEnumerable<string> sql)
{
    foreach (var statement in sql)
    {
        yield return statement + "\nGO\n";
    }
}

The script ends up containing four sections. At the top is the “IF EXISTS/DROP” section. The next two sections are SET statements, and the final section is the CREATE. It is important to append GO to these as the CREATE must be the first statement in a batch.

Yes, I’m amused that I have a private extension method too.