Click here to Skip to main content
12,511,335 members (102,728 online)
Click here to Skip to main content
Articles » Database » Database » Utilities » Downloads

Stats

135.8K views
2.1K downloads
65 bookmarked
Posted

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

, 5 May 2004
This useful tool demonstrates the power of SQL DMO to generate scripts for tables, stored procedures and other database objects, and how to integrate with Visual SourceSafe using automation.
DBScriptSafe
DBScriptSafe.ico
DBScriptSafe.exe
Interop.SQLDMO.dll
Interop.SourceSafeTypeLib.dll
using System;
using SQLDMO;
using SourceSafeTypeLib;
using System.Collections;

// Project : DBScriptSafe
// File : ScriptEngine.cs
// Author : Sriram Chitturi (c) 2004
// Date : May 06, 2004

namespace DBScriptSafe
{
	public delegate void ScriptingHandler();

	/// <summary>
	/// Summary description for ScriptEngine.
	/// </summary>
	public class ScriptEngine
	{
		public event ScriptingHandler ScriptDone;

		// member variables
		SQLServerClass	m_sqlserver;
		string			m_dbName;
		VSSDatabaseClass	m_vssDatabase;
		string			m_vssRootPath;
		VSSItem			m_vssRoot;
		string			m_workingFolder;
		public static	TransferClass s_transfer = new TransferClass();

		enum Db_Types {Defaults, Functions, Rules, SPs, Tables, Triggers, UDTs, Views};

		// constructor
		public ScriptEngine(SQLServerClass sqlserver, // sql server
			VSSDatabaseClass vssDb, // VSS database
			string vssRootPath // root in VSS under which items are created
			)
		{
			m_sqlserver = sqlserver;
			m_vssDatabase = vssDb;
			m_vssRootPath = vssRootPath;
			m_vssRoot = m_vssDatabase.get_VSSItem(vssRootPath, false);

			m_workingFolder = Environment.GetEnvironmentVariable("TEMP");
			m_workingFolder += "\\DBScriptManager";
			System.IO.Directory.CreateDirectory(m_workingFolder);
		}

		public string DatabaseName 
		{
			get { return m_dbName; }
			set { m_dbName = value; }
		}

		public void Script()
		{
			try
			{
				if (m_dbName == null)
					return; // there is no DB to script
				foreach (Database db in m_sqlserver.Databases)
					if (m_dbName == db.Name)
					{
						System.IO.Directory.CreateDirectory(m_workingFolder);
						ScriptDatabase(db);
						System.IO.Directory.Delete(m_workingFolder, true);
					}
			} 
			catch (System.Threading.ThreadAbortException e)
			{ //end up here in case of abort, ignore !
			}
		}

		private void ScriptDatabase(Database db)
		{
			string workingFolder = m_workingFolder + @"\" + db.Name;
			System.IO.Directory.CreateDirectory(workingFolder);

			// TransferClass tc = new TransferClass();
			s_transfer.ScriptType = SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_All |
							SQLDMO_SCRIPT_TYPE.SQLDMOScript_TransferDefault;
			s_transfer.ScriptType = s_transfer.ScriptType &
							(~ SQLDMO_SCRIPT_TYPE.SQLDMOScript_IncludeHeaders);
			s_transfer.DropDestObjectsFirst = true;
			s_transfer.CopySchema = true;
			s_transfer.CopyAllObjects = false; // first stop all objects

			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);
						s_transfer.CopyAllFunctions = false;
						break;
					case Db_Types.Rules:
						s_transfer.CopyAllRules = true;
						GenerateScript(s_transfer, workingFolder, db);
						s_transfer.CopyAllRules = false;
						break;
					case Db_Types.SPs:
						s_transfer.CopyAllStoredProcedures = true;
						GenerateScript(s_transfer, workingFolder, db);
						s_transfer.CopyAllStoredProcedures = false;
						break;
					case Db_Types.Tables:
						s_transfer.CopyAllTables = true;
						GenerateScript(s_transfer, workingFolder, db);
						s_transfer.CopyAllTables = false;
						break;
					case Db_Types.Triggers:
						s_transfer.CopyAllTriggers = true;
						GenerateScript(s_transfer, workingFolder, db);
						s_transfer.CopyAllTriggers = false;
						break;
					case Db_Types.UDTs:
						s_transfer.CopyAllUserDefinedDatatypes = true;
						GenerateScript(s_transfer, workingFolder, db);
						s_transfer.CopyAllUserDefinedDatatypes = false;
						break;
					case Db_Types.Views:
						s_transfer.CopyAllViews = true;
						GenerateScript(s_transfer, workingFolder, db);
						s_transfer.CopyAllViews = false;
						break;
				}
				s_transfer.RemoveAllObjects();
			}

			string vssPath = m_vssRootPath + "/" + db.Name;
			VSSItem VssDbItem = GetVssItem(vssPath, VSSItemType.VSSITEM_PROJECT);
			workingFolder = m_workingFolder + @"\" + db.Name;
			VssDbItem.LocalSpec = workingFolder;

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

			m_dbName = null;
			if (ScriptDone != null)
				ScriptDone();
		}

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

		// try to get the path and if not create it
		private VSSItem GetVssItem(string path, VSSItemType type)
		{
			VSSItem item = null;
			try 
			{
				item = m_vssDatabase.get_VSSItem(path, false);
			} 
			catch(Exception ex1)
			{
				try 
				{
					if (type == VSSItemType.VSSITEM_PROJECT)
						item = m_vssRoot.NewSubproject(path, "Created by DBScriptManager");
				} 
				catch(Exception ex2) 
				{
				}
			}
			return item;
		}
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Sriram Chitturi
Architect
United States United States
No Biography provided

You may also be interested in...

Pro
Pro
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160929.1 | Last Updated 6 May 2004
Article Copyright 2004 by Sriram Chitturi
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid