SQL Server Objects in SourceSafe using SMO and Visual SourceSafe Automation

• Download source files - 41.61 KB
• Download demo project - 1.46 MB

Introduction

It is important to backup database for reliability and continuity of database administration. In addition to that, change management of schemas of database should not be an ignorable event in administration tasks. Generally, there are two types of databases, these are production and development. Both of them can be controlled changing schemas of structure. Change management is the basic foundation for security administration. Finally, to do work, I used SQL Server 2005 and Visual Source Safe. I also use SMO for scripting database. I found some work on DMO and Visual Source Safe. However, no work is made for VSS using SMO. I wrote some extra code for adaptation of new features. This article helped to do my study.

Demo Application

Requirements

• SQL Server 2005
• Visual Source Safe
• .NET 2.0

On startup,

• Left panel contains SQL connection parameters.
• Using NT Integrated security or SQL user to connect database.
• To get server list in your network, press “getserver” button.
• After writing the name database server, press “connect” button to connect SQL Server.
• After successful connection, the right panel would be active with the database list.
• After filling VSS parameters, select database that is being scripted and sent to VSS.
• Finally, press “Script and checkin selected databases” button to check in source to VSS.

Using the Code

SMO and Visual Source Safe automation library are added to references of the project before using these objects.

Add the below DLLs to references of the project:

• Microsoft.SqlServer.ConnectionInfo;
• Microsoft.SqlServer.Smo;
• Microsoft.SqlServer.SmoEnum;
• Microsoft.SqlServer.SqlEnum;
• Microsoft.VisualStudio.SourceSafe.Interop;

and deletion temp folder, add:

• System.Management

After script button,

if (m_dbList.Count > 0)
            {
                m_scriptEngine.DatabaseName = (string)m_dbList[0];
                m_dbList.RemoveAt(0);
                backgroundWorker1.RunWorkerAsync();
                System.Windows.Forms.Application.DoEvents();
            }

Program runs the backgroundworker to call script:

m_scriptEngine.Script();

backgroundworker component is used to script table because scripting table is a slow process.

In ScriptEngine class,

ReadandWriteObjectsFromDatabasetoFile(db, workingFolder);

To script file,

foreach (Rule rule in db.Rules)
            {
                filename = rule.Name;
                oname.Text = "_Progressing....:" + filename;
                FileStream file = new FileStream(workingFolder + filename +
		"_rules.sql", FileMode.CreateNew, FileAccess.Write);
                StreamWriter sw = new StreamWriter(file);
                StringCollection sc = rule.Script();
                foreach (string s in sc)
                    sw.WriteLine(s);
                sw.Close();
                file.Close();
            }

calls to every object of database to script temp folder. After locating script to temp folder, every files are sent to the specified VSS.

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));

Delete temp folder that includes script files.

Finally

According to new features of the SMO, code can be improved. For the development environment, scheduled task could be used with proper changes of the program.

string dirObject = String.Format("win32_Directory.Name='{0}'", targetDirectory);
            using (ManagementObject dir = new ManagementObject(dirObject))
            {
                dir.Get();
                ManagementBaseObject outParams = dir.InvokeMethod("Delete", null,
                null);
                // ReturnValue should be 0, else failure
                if (Convert.ToInt32(outParams.Properties["ReturnValue"].Value) != 0)
                {
                }
            }

History

• 3^rd April, 2006: Initial post