SMO Tutorial 3 of n - Scripting

• Download source - 16.1 KB
• Download demo - 18.9 KB

Introduction

This article is part 3 of a series of articles about programming Server Management Objects. In the first article, I have described what Server Management Objects are. We also saw how to work with database storage objects. In the second article, I have described how to use classes not related to database storage objects. In this article, I will show how to use the scripting abilities of SMOs.

Background

SQL Server client tools have very good functionality for creating database object scripts, but they don't always provide enough functionality to generate scripts the way programmers would like. These customized scripts could be used for database documentation. For that reason, database administrators and developers must code custom script generators. These generators usually generate scripts into text files that could be stored and versioned in a source control system. The custom generators could be created by using SMO objects. Scripting in SMO is controlled by the Scripter object and its child objects. A script is generated with the specified list and scripting options. The result is returned as a StringCollection system object.

To get started with SMO, first you must add a reference to Visual Studio. In the Add Reference window, select:

• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.SmoExtended
• Microsoft.SqlServer.Management.Sdk.Sfc
• Mircorost.SqlServer.ConnectionInfo

When these references are added, you must add two using statements for these two namespaces:

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

SMO classes for scripting operations

Using the code

This simple project consists of a form called Form1. For connection to SQL Server 2005 (or later versions), it is important to use the Server class. One of the overloaded constructors of this class accepts a ServerConnection object. This object represents a connection to a server. ServerConnection has a constructor with three parameters (serverInstance, userName, and password). All parameters are loaded from the app.config file.

string serverstr = ConfigurationManager.AppSettings["Server"];
string user = ConfigurationManager.AppSettings["User"];
string password = ConfigurationManager.AppSettings["Password"];
ServerConnection conn = new ServerConnection(serverstr, user, password);
try
{
    Server server = new Server(conn);
    foreach (Database database in server.Databases)
    {
        cboDatabase.Items.Add(database.Name);
    }
    cboDatabase.SelectedIndex = 0;
}
catch (Exception err)
{
    MessageBox.Show(err.Message, "Error", 
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
}

Using this code, you can connect to an instance of a SQL Server using credentials specified in the mentioned app.config file. When the connection is successful, dboDatabase is populated by a list of databases.

Before script generation, it is important to choose which types of objects are going to be scripted. This selection is provided in the Objects panel. You can choose from four object types (Stored Procedures, User Defined Functions, Views, and Tables). The scripting options can by chosen in the Scripting Option panel.

All properties of ScriptingOprions can be found here.

Script generation starts after clicking on the Script button. The BackgroundWorker object is initialized and the RunWorkerAsync() method is called. The BackgroundWorker class executes operations on a separate thread. I decided to use this approach because the scripting of multiple objects could cause the user interface to stop responding while the operation is running. I have added the scripting code into the DoWork event hander.

private void btnScript_Click(object sender, EventArgs e)
{
    if (backgroundWorker1.IsBusy != true)
    {
        // Start the asynchronous operation.
        backgroundWorker1.RunWorkerAsync();
    }
}

private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
    BackgroundWorker worker = sender as BackgroundWorker;

    this.Invoke(new MethodInvoker(delegate
    {
        richTextScript.Text = "";
        txtProgress.Text = "";
    }));

    string serverstr = ConfigurationManager.AppSettings["Server"];
    string user = ConfigurationManager.AppSettings["User"];
    string password = ConfigurationManager.AppSettings["Password"];
    ServerConnection conn = new ServerConnection(serverstr, user, password);
    try
    {
        Server server = new Server(conn);

        string dbname = "";

        this.Invoke(new MethodInvoker(delegate
        {
            dbname = cboDatabase.SelectedItem.ToString();
        }));

        Database db = server.Databases[dbname];
        Scripter scripter = new Scripter(server);
        scripter.ScriptingProgress += 
          new ProgressReportEventHandler(ScriptingProgressEventHandler);

        ScriptingOptions so = new ScriptingOptions();
        so.IncludeIfNotExists = chkScriptIfNotExists.Checked;
        so.IncludeHeaders = chkScriptHeaders.Checked;
        so.Permissions = chkScriptPermissions.Checked;
        so.ExtendedProperties = chkScriptExtendedProperties.Checked;
        so.ScriptDrops = chkScriptDrop.Checked;
        so.IncludeDatabaseContext = chkDBContext.Checked;
        so.NoCollation = chkNoCollation.Checked;
        so.NoFileGroup = chkNoFileGroups.Checked;
        so.NoIdentities = chkNoIdentities.Checked;

        StringBuilder sbScript = new StringBuilder();

        int version = 0;

        this.Invoke(new MethodInvoker(delegate
        {
            version = cboServerVersion.SelectedIndex;
        }));

        switch (version)
        {
            case 0:
                so.TargetServerVersion = SqlServerVersion.Version80;
                break;

            case 1:
                so.TargetServerVersion = SqlServerVersion.Version90;
                break;
            case 2:
                so.TargetServerVersion = SqlServerVersion.Version100;
                break;
        }

        scripter.Options = so;

        if (chkScriptDatabase.Checked)
        {
            sbScript.Append(ScriptObject(new Urn[] { db.Urn }, scripter));
        }

        if (chkTables.Checked)
        {
            server.SetDefaultInitFields(typeof(Table), "IsSystemObject");
            foreach (Table tb in db.Tables)
            {
                if (!tb.IsSystemObject)
                {
                    sbScript.Append(ScriptObject(new Urn[] { tb.Urn }, scripter));
                }
            }
        }

        if (chkViews.Checked)
        {
            server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), 
                                        "IsSystemObject");
            foreach (Microsoft.SqlServer.Management.Smo.View v in db.Views)
            {
                if (!v.IsSystemObject)
                {
                    sbScript.Append(ScriptObject(new Urn[] { v.Urn }, scripter));
                }
            }
        }

        if (chkUserDefinedFunctions.Checked)
        {
            server.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject");
            foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
            {
                if (!udf.IsSystemObject)
                {
                    sbScript.Append(ScriptObject(new Urn[] { udf.Urn }, scripter));
                }
            }
        }

        if (chkStoredProcedures.Checked)
        {
            server.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");

            foreach (StoredProcedure sp in db.StoredProcedures)
            {
                if (!sp.IsSystemObject)
                {
                    sbScript.Append(ScriptObject(new Urn[] { sp.Urn }, scripter));
                }
            }
        }

        this.Invoke(new MethodInvoker(delegate
        {
            richTextScript.Text = sbScript.ToString();
        }));

        Parse();
        conn.Disconnect();
    }
    catch (Exception err)
    {
        MessageBox.Show(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

The above code demonstrated script generation. Before scripting, a connection on the server is made by the Server object using the credentials specified in the app.config file. Next, the Scripter and ScriptingOptions objects are created. A StringBuilder object sbScript is then created. Into this object, the scripts are stored, and when all the scripts are created, text from sbScript is displayed in the richTextScript object.

private string ScriptObject(Urn[] urns, Scripter scripter)
{
    StringCollection sc = scripter.Script(urns);
    StringBuilder sb = new StringBuilder();

    foreach (string str in sc)
    {
        sb.Append(str + Environment.NewLine + "GO" +
          Environment.NewLine + Environment.NewLine);
    }

    return sb.ToString();
}

The Script( ) method of the Scripter class generates T-SQL that can be used to create SQL Server objects identified by either a SqlSmoObject array, Urn array, or UrnCollection object passed as an argument to the constructor. The Script( ) method returns the T-SQL as a StringCollection object. The Options property exposes a ScriptingOptions object that lets you control scripting operations.

After all script are successfully generated, they are parsed and highlighted by the Parse() method. Keywords to be highlighted are stored in a SQL.txt file. For more information about syntax highlighting, go to:

• Syntax Highlighting in Rich TextBox Control - Part 1
• Syntax Highlighting in Rich TextBox Control - Part 2

History

• 27 Feb 2011 - Article created.