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

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