Click here to Skip to main content
11,720,545 members (70,491 online)
Click here to Skip to main content

SQL Server Objects in SourceSafe using SMO and Visual SourceSafe Automation

, 3 Apr 2006 CPOL 53.5K 1.1K 42
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

You may also be interested in...

Comments and Discussions

 
GeneralError when running Pin
Roel Schreurs21-Jul-08 23:52
memberRoel Schreurs21-Jul-08 23:52 
GeneralRe: Error when running Pin
getabdul9-Sep-08 21:47
membergetabdul9-Sep-08 21:47 
AnswerRe: Error when running Pin
x3030-Sep-08 2:03
memberx3030-Sep-08 2:03 
GeneralSource code build failed Pin
Roel Schreurs21-Jul-08 23:44
memberRoel Schreurs21-Jul-08 23:44 
GeneralA small bug Pin
Philipos Sakellaropoulos1-Mar-07 22:23
memberPhilipos Sakellaropoulos1-Mar-07 22:23 
Questioncan any one tell me the link to do all this work in c# Pin
mian ghous23-Dec-06 2:37
membermian ghous23-Dec-06 2:37 
GeneralWe got a simpler way Pin
yonision14-Nov-06 15:53
memberyonision14-Nov-06 15:53 
QuestionCompatibility with other SCC Pin
nirisarri20-Apr-06 6:02
membernirisarri20-Apr-06 6:02 
AnswerRe: Compatibility with other SCC Pin
Levent Soyalp30-Apr-06 21:17
memberLevent Soyalp30-Apr-06 21:17 
No, it supports only VSS.
Generalwonderful work Pin
denny.regehr@acxiom.com13-Apr-06 5:43
memberdenny.regehr@acxiom.com13-Apr-06 5:43 
GeneralRe: wonderful work Pin
Levent Soyalp30-Apr-06 21:19
memberLevent 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 | Terms of Use | Mobile
Web02 | 2.8.150901.1 | Last Updated 3 Apr 2006
Article Copyright 2006 by Levent Soyalp
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid