Tool to script and store SQL Server objects in SourceSafe using SQL DMO and Visual SourceSafe automation

• Download source files - 11.8 Kb
• Download demo project - 284 Kb

Introduction

Working with SQL Server databases in a huge group or enterprise environments with developers stepping out of line once in a while to make ad-hoc changes to tables, stored procedures or adding jobs and DTS will be a nightmare for admins and developers alike. Tracking changes to database structure is one of the most important piece of database development. Although sincere efforts are made in team development, crunching timelines and lack of resources tend to catch up with the developers once in a while to make an ad-hoc change to the database. Visual Studio and other client tools offer integration of SQL Server database with Visual SourceSafe. But many projects may not be able to afford the license or training time on these new tools. The attached DB Script Safe tool scripts and stores changes into Visual SourceSafe using SQL DMO and Visual SourceSafe automation. (Important note: This tool cannot be a substitute for regular database backups which include data.) This article attempts to demo the process of scripting and storing in SourceSafe. The attached code can be used by the developer community to cater their needs.

Demo application

On startup, the application displays the above dialog box to configure parameters.

1. Press <Get Servers List> button to fetch the list of SQL Server database instances.
2. Give "blank" for database login and password to log into SQL Server using Windows login.
3. Press <Connect> to connect to the database. On successful connection, the list of non-system databases are displayed and the <Script and Checkin Selected Databases> button is enabled.
4. Browse and select a srcsafe.ini file to connect to Visual SourceSafe and give credentials and a project under which the scripts should be stored. Scripts will be stored according to the database and database object type, under <project>/<database>/<object type>/ directory inside SourceSafe.

It is required that the <project> directory exists in SourceSafe. Complete source code is included with the demo.

Using the code

SQLDMO and Visual SourceSafe automation library are added to the references of the project before using these objects. These are found under the COM tab of 'Add Reference' dialog box as 'Microsoft Source Safe 6.0 Type Library' and 'Microsoft SQLDMO Object Library'. This will create 2 namespaces SourceSafeTypeLib and SQLDMO which are used in the application. The SQLDMO.SQLServerClass is the top most class which is used to connect to a database instance, and once successfully connected is retained in the application as a member variable. The SQLServerClass.Databases collection is used to populate the list box.

    DBListView.Items.Clear();
    foreach (SQLDMO.Database db in m_sqlServer.Databases)
        if (! db.SystemObject)
            DBListView.Items.Add(db.Name);

The SQLDMO.Database class has a method called ScriptTransfer() which is at the heart of the script generation scheme. The signature of this method is:

ScriptTransfer(TransferClass transfer, 
    SQLDMO_XFRSCRIPTMODE_TYPE type, string path);

The TransferClass contains what objects are to be scripted and options when generating the script, like including headers or not. Most of the options are from SQLDMO_SCRIPT_TYPE enumeration. In the demo application code, this class is populated in ScriptEngine.ScriptDatabase(Database db) method. The TransferClass also has ScriptMessage and ScriptTransferPercentComplete events which are subscribed to by the DBScriptSafe class to display status messages.

    ScriptEngine.s_transfer.StatusMessage += 
        new TransferSink_StatusMessageEventHandler(s_transfer_StatusMessage);
    ScriptEngine.s_transfer.ScriptTransferPercentComplete +=
        new TransferSink_ScriptTransferPercentCompleteEventHandler(
                    s_transfer_ScriptTransferPercentComplete);

Coming to the Visual SourceSafe part, the project item which is root/project is either fetched, and if not found, is created by the following code:

    VSSItem VssDbItem = GetVssItem(vssPath, VSSItemType.VSSITEM_PROJECT);

Once this is established, the working folder for the SourceSafe project is determined by the type of objects being scripted which can be seen by the switch statement in the code.

    for (Db_Types dt = Db_Types.Defaults; dt<= Db_Types.Views; dt++)
    {
        workingFolder = m_workingFolder + @"\" + db.Name + @"\" + dt.ToString();
        System.IO.Directory.CreateDirectory(workingFolder);
        switch(dt)
        {
        case Db_Types.Defaults:
            s_transfer.CopyAllDefaults = true;
            GenerateScript(s_transfer, workingFolder, db);
            s_transfer.CopyAllDefaults = false;
            break;
        case Db_Types.Functions:
            s_transfer.CopyAllFunctions = true;
            GenerateScript(s_transfer, workingFolder, db);
            ....
            ....
        ...
        }
    }

All the script is generated by the following helper function in ScriptEngine class:

    private void GenerateScript(TransferClass tc, string folder, Database db)
    {
        db.ScriptTransfer(tc,
        SQLDMO_XFRSCRIPTMODE_TYPE.SQLDMOXfrFile_SingleFilePerObject,
                folder);
    }

The checkout, checkin and other operations on the SourceSafe are deliberately kept at the end of the sequence. Just in case the user tries to abort the scripting, we will have a chance to terminate the whole operation without touching the SourceSafe. Watch the flags and the sequence in the following code segment for the SourceSafe:

    VssDbItem.Checkout("DBScriptManager Automatic Checkout",
            workingFolder,
            (int)(VSSFlags.VSSFLAG_GETNO | VSSFlags.VSSFLAG_RECURSYES));

    VssDbItem.Checkin("DBScriptManager Automatic Checkin",
            workingFolder,
            (int)(VSSFlags.VSSFLAG_DELTAYES| VSSFlags.VSSFLAG_RECURSYES|
                    VSSFlags.VSSFLAG_DELYES));

    VssDbItem.UndoCheckout(workingFolder, 
            (int)(VSSFlags.VSSFLAG_GETNO | VSSFlags.VSSFLAG_DELYES| 
                VSSFlags.VSSFLAG_RECURSYES));

    VssDbItem.Add(workingFolder, "Created by DBScriptManager",
            (int) (VSSFlags.VSSFLAG_DELYES | VSSFlags.VSSFLAG_RECURSYES));

All the operations are done recursively from the project item. That is why the VSSFLAG_RECURYES is used in all the calls.

1. First, the existing scripts are checked out without getting the files.
2. All the changed scripts are checked in. This will check-in only new scripts into the SourceSafe.
3. The UndoCheckout call next will undo checkout of any script which is missing from the last check-in.
4. This leaves with adding all the new scripts which is done by the Add call at the end.

This simple 4 line code does all the work for us and demonstrates the power of Visual SourceSafe automation.

Enhancements suggested to demo project

Some of the enhancements to the demo project to make it more effective can be:

• Making the program command line driven so that it can be scheduled to run under Windows Scheduler for periodic backups
• Scripting the database itself
• Scripts for Jobs and DTS at server level.