Click here to Skip to main content
Click here to Skip to main content
Go to top

SQL Server Objects in SourceSafe using SMO and Visual SourceSafe Automation

, 3 Apr 2006
Rate this:
Please Sign up or sign in to vote.
Useful tool to script database objects using SMO and check in Visual Source Safe

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

Sample screenshot

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

  • 3rd April, 2006: Initial post

License

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

Share

About the Author

Levent Soyalp
Web Developer
Turkey Turkey
No Biography provided

Comments and Discussions

 
GeneralError when running PinmemberRoel Schreurs21-Jul-08 23:52 
GeneralRe: Error when running Pinmembergetabdul9-Sep-08 21:47 
AnswerRe: Error when running Pinmemberx3030-Sep-08 2:03 
GeneralSource code build failed PinmemberRoel Schreurs21-Jul-08 23:44 
GeneralA small bug PinmemberPhilipos Sakellaropoulos1-Mar-07 22:23 
Questioncan any one tell me the link to do all this work in c# Pinmembermian ghous23-Dec-06 2:37 
GeneralWe got a simpler way Pinmemberyonision14-Nov-06 15:53 
QuestionCompatibility with other SCC Pinmembernirisarri20-Apr-06 6:02 
AnswerRe: Compatibility with other SCC PinmemberLevent Soyalp30-Apr-06 21:17 
Generalwonderful work Pinmemberdenny.regehr@acxiom.com13-Apr-06 5:43 
GeneralRe: wonderful work PinmemberLevent Soyalp30-Apr-06 21:19 

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 | Mobile
Web02 | 2.8.140916.1 | Last Updated 3 Apr 2006
Article Copyright 2006 by Levent Soyalp
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid