Click here to Skip to main content
Click here to Skip to main content

Tagged as

Versioning your Database on a Budget with C# and SMO

, 19 Aug 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
An alternate way of versioning your database using C# and SMO without buying expensive plugins.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

David_Wimbley
Software Developer
United States United States
No Biography provided

Comments and Discussions

 
QuestionA VS solution would be nice PinmemberHenrik Hoyer28-Aug-13 22:31 
AnswerRe: A VS solution would be nice PinprofessionalDavid_Wimbley29-Aug-13 6:07 
GeneralRe: A VS solution would be nice PinmemberHenrik Hoyer29-Aug-13 21:20 
AnswerRe: A VS solution would be nice PinmemberRadu Slavila4-Dec-13 21:25 
SuggestionSQL Server Data Tools schema compare PinmemberJonathan Little20-Aug-13 3:48 
GeneralRe: SQL Server Data Tools schema compare PinmemberRandalthor0520-Aug-13 4:24 
GeneralRe: SQL Server Data Tools schema compare PinprofessionalDavid_Wimbley20-Aug-13 5:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150123.1 | Last Updated 20 Aug 2013
Article Copyright 2013 by David_Wimbley
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid