Versioning your Database on a Budget with C# and SMO

Introduction

A problem that I've run into when trying to maintain our database is being able to version our SQL scripts, one way around this is using SQL Management Objects to programmatically generate your schema into individual files. Then through your desired source control (svn/git/mercurial/cvs) API, you could then include the ability to auto-checkin your SQL scripts to create a history of changes without shelling out big bucks for SQL Server Management Studio Plugins.

By generating each object from SQL to an individual file, I am able to pinpoint changes that happen throughout the day should any new bugs be introduced by accident and easily revert those changes back as necessary rather than running going "what happened?!"

What I've chosen to script to file in this example are Create Table Scripts, Stored Procedures, and User Defined Functions. You can also script other SQL objects such as views should you have any.

Requirements

1. .NET 4 or greater
2. SQL Management Studio installed on your dev box (you'll need it to grab the SQL Management Object DLLs)
3. Add the following DLLs to your project
   • Microsoft.SqlServer.ConnectionInfo
   • Microsoft.SqlServer.Management.Sdk.Sfc
   • Microsoft.SqlServer.Smo
   • Microsoft.SqlServer.SmoExtended
4. The SMO DLLs are located at C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\

Using the Code

Initialize Folders For Scripts

string dbServerFolder = Path.Combine(scriptsDir, dbServer.Replace(".",""));
string dbFolder = Path.Combine(dbServerFolder, dbName);
string dbTablesFolder = Path.Combine(dbFolder, "Tables");
string dbStoredProcFolder = Path.Combine(dbFolder, "StoredProcedures");
string dbUserFunctionsFolder = Path.Combine(dbFolder, "Functions");

Directory.CreateDirectory(dbServerFolder);
Directory.CreateDirectory(dbFolder);
Directory.CreateDirectory(dbTablesFolder);
Directory.CreateDirectory(dbUserFunctionsFolder);
Directory.CreateDirectory(dbStoredProcFolder); 

Initialize Scripter Object

Scripter scrp = new Scripter(srv);
scrp.Options.ScriptDrops = false;
scrp.Options.WithDependencies = true;
scrp.Options.Indexes = true;
scrp.Options.DriAllConstraints = true;
scrp.Options.NoCommandTerminator = true;
scrp.Options.AllowSystemObjects = true;
scrp.Options.Permissions = true;
scrp.Options.DriAllConstraints = true;
scrp.Options.SchemaQualify = true;
scrp.Options.AnsiFile = true;
scrp.Options.DriIndexes = true;
scrp.Options.DriClustered = true;
scrp.Options.DriNonClustered = true;
scrp.Options.NonClusteredIndexes = true;
scrp.Options.ClusteredIndexes = true;
scrp.Options.FullTextIndexes = true;
scrp.Options.EnforceScriptingOptions = true; 

The scripter object contains the same settings as the Advanced Scripting Options window when you go through and manually choose to generate scripts. Simply set your desired options to true/false based on your need.

Generate Scripts and Save To File

foreach (Table tb in db.Tables)
{
    if (tb.IsSystemObject == false)
    {
        StringCollection scriptCollection = scrp.Script(new Urn[] { tb.Urn });
        List<string> dbscripts = scriptCollection.Cast<string>().ToList();
        File.WriteAllLines(Path.Combine(dbTablesFolder, 
        string.Format("{0}.sql", tb.Name)), dbscripts);
    }
}

foreach (StoredProcedure storedProc in db.StoredProcedures)
{
    if (storedProc.IsSystemObject == false)
    {
        StringCollection scriptCollection = scrp.Script(new Urn[] { storedProc.Urn });
        List<string> dbscripts = scriptCollection.Cast<string>().ToList();
        File.WriteAllLines(Path.Combine(dbStoredProcFolder, 
        string.Format("{0}.sql", storedProc.Name)), dbscripts);
    }
}

foreach (UserDefinedFunction function in db.UserDefinedFunctions)
{
    if (function.IsSystemObject == false)
    {
        StringCollection scriptCollection = scrp.Script(new Urn[] { function.Urn });
        List<string> dbscripts = scriptCollection.Cast<string>().ToList();
        File.WriteAllLines(Path.Combine(dbUserFunctionsFolder, 
        string.Format("{0}.sql", function.Name)), dbscripts);
    }
}

Based on the objects you end up wanting to collect via script, you'll need to loop over them invdivudally in order to save to file. The easiest way to write to a file in this case is using File.WriteAllLines, however since the scripts are returned as a StringCollection you will need to convert that to either an array of string or something that implements the IEnumberable Interface...in this case, I chose List<string>.

Usage

using InsuranceBlog.Console.Examples;

namespace InsuranceBlog.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            string server = args[0];
            string dbName = args[1];
            string scriptsDir = args[2];

            SmoExample.GenerateScripts(dbName, server, scriptsDir);
        }
    }
}

The usage for this is in a simple command line utility that needs you to specify the database server, database name and the directory for your scripts to output to. This could be improved to provide error handling or usage information should you not enter the correct number of arguments for this to run. This is simply a basic example to get started.

Full Code

using InsuranceBlog.Console.Examples;

namespace InsuranceBlog.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            string server = args[0];
            string dbName = args[1];
            string scriptsDir = args[2];

            SmoExample.GenerateScripts(dbName, server, scriptsDir);
        }
    }
}

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Smo;
using System.IO;

namespace InsuranceBlog.Console.Examples
{
    public static class SmoExample
    {
        public static void GenerateScripts(string dbName, string dbServer, string scriptsDir)
        {
            string dbServerFolder = Path.Combine
            (scriptsDir, dbServer.Replace(".",""));
            string dbFolder = Path.Combine(dbServerFolder, dbName);
            string dbTablesFolder = Path.Combine(dbFolder, "Tables");
            string dbStoredProcFolder = Path.Combine(dbFolder, "StoredProcedures");
            string dbUserFunctionsFolder = Path.Combine(dbFolder, "Functions");

            Directory.CreateDirectory(dbServerFolder);
            Directory.CreateDirectory(dbFolder);
            Directory.CreateDirectory(dbTablesFolder);
            Directory.CreateDirectory(dbUserFunctionsFolder);
            Directory.CreateDirectory(dbStoredProcFolder);
            
            Server srv = new Server(dbServer);
            Database db = srv.Databases[dbName];

            Scripter scrp = new Scripter(srv);
            scrp.Options.ScriptDrops = false;
            scrp.Options.WithDependencies = true;
            scrp.Options.Indexes = true;
            scrp.Options.DriAllConstraints = true;
            scrp.Options.NoCommandTerminator = true;
            scrp.Options.AllowSystemObjects = true;
            scrp.Options.Permissions = true;
            scrp.Options.DriAllConstraints = true;
            scrp.Options.SchemaQualify = true;
            scrp.Options.AnsiFile = true;
            scrp.Options.DriIndexes = true;
            scrp.Options.DriClustered = true;
            scrp.Options.DriNonClustered = true;
            scrp.Options.NonClusteredIndexes = true;
            scrp.Options.ClusteredIndexes = true;
            scrp.Options.FullTextIndexes = true;
            scrp.Options.EnforceScriptingOptions = true;

            foreach (Table tb in db.Tables)
            {
                if (tb.IsSystemObject == false)
                {
                    StringCollection scriptCollection = scrp.Script(new Urn[] { tb.Urn });
                    List<string> dbscripts = scriptCollection.Cast<string>().ToList();
                    File.WriteAllLines(Path.Combine(dbTablesFolder, 
                    string.Format("{0}.sql", tb.Name)), dbscripts);
                }
            }

            foreach (StoredProcedure storedProc in db.StoredProcedures)
            {
                if (storedProc.IsSystemObject == false)
                {
                    StringCollection scriptCollection = scrp.Script(new Urn[] { storedProc.Urn });
                    List<string> dbscripts = scriptCollection.Cast<string>().ToList();
                    File.WriteAllLines(Path.Combine(dbStoredProcFolder, 
                    string.Format("{0}.sql", storedProc.Name)), dbscripts);
                }
            }

            foreach (UserDefinedFunction function in db.UserDefinedFunctions)
            {
                if (function.IsSystemObject == false)
                {
                    StringCollection scriptCollection = scrp.Script(new Urn[] { function.Urn });
                    List<string> dbscripts = scriptCollection.Cast<string>().ToList();
                    File.WriteAllLines(Path.Combine(dbUserFunctionsFolder, 
                    string.Format("{0}.sql", function.Name)), dbscripts);
                }
            }
        }
    }
}

Points of Interest

More information about SQL Management Objects can be found here.

History

• First version of article - 8/19/2013