Working with SQL Server databases in a huge group or enterprise environments with developers stepping out of line once in a while to make ad-hoc changes to tables, stored procedures or adding jobs and DTS will be a nightmare for admins and developers alike. Tracking changes to database structure is one of the most important piece of database development. Although sincere efforts are made in team development, crunching timelines and lack of resources tend to catch up with the developers once in a while to make an ad-hoc change to the database. Visual Studio and other client tools offer integration of SQL Server database with Visual SourceSafe. But many projects may not be able to afford the license or training time on these new tools. The attached DB Script Safe tool scripts and stores changes into Visual SourceSafe using SQL DMO and Visual SourceSafe automation. (Important note: This tool cannot be a substitute for regular database backups which include data.) This article attempts to demo the process of scripting and storing in SourceSafe. The attached code can be used by the developer community to cater their needs.
On startup, the application displays the above dialog box to configure parameters.
It is required that the <project> directory exists in SourceSafe. Complete source code is included with the demo.
SQLDMO and Visual SourceSafe automation library are added to the references of the project before using these objects. These are found under the COM tab of 'Add Reference' dialog box as 'Microsoft Source Safe 6.0 Type Library' and 'Microsoft SQLDMO Object Library'. This will create 2 namespaces SourceSafeTypeLib and SQLDMO which are used in the application. The SQLDMO.SQLServerClass is the top most class which is used to connect to a database instance, and once successfully connected is retained in the application as a member variable. The SQLServerClass.Databases collection is used to populate the list box.
DBListView.Items.Clear();
foreach (SQLDMO.Database db in m_sqlServer.Databases)
if (! db.SystemObject)
DBListView.Items.Add(db.Name);
The SQLDMO.Database class has a method called ScriptTransfer() which is at the heart of the script generation scheme. The signature of this method is:
ScriptTransfer(TransferClass transfer,
SQLDMO_XFRSCRIPTMODE_TYPE type, string path);
The TransferClass contains what objects are to be scripted and options when generating the script, like including headers or not. Most of the options are from SQLDMO_SCRIPT_TYPE enumeration. In the demo application code, this class is populated in ScriptEngine.ScriptDatabase(Database db) method. The TransferClass also has ScriptMessage and ScriptTransferPercentComplete events which are subscribed to by the DBScriptSafe class to display status messages.
ScriptEngine.s_transfer.StatusMessage +=
new TransferSink_StatusMessageEventHandler(s_transfer_StatusMessage);
ScriptEngine.s_transfer.ScriptTransferPercentComplete +=
new TransferSink_ScriptTransferPercentCompleteEventHandler(
s_transfer_ScriptTransferPercentComplete);
Coming to the Visual SourceSafe part, the project item which is root/project is either fetched, and if not found, is created by the following code:
VSSItem VssDbItem = GetVssItem(vssPath, VSSItemType.VSSITEM_PROJECT);
Once this is established, the working folder for the SourceSafe project is determined by the type of objects being scripted which can be seen by the switch statement in the code.
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);
....
....
...
}
}
All the script is generated by the following helper function in ScriptEngine class:
private void GenerateScript(TransferClass tc, string folder, Database db)
{
db.ScriptTransfer(tc,
SQLDMO_XFRSCRIPTMODE_TYPE.SQLDMOXfrFile_SingleFilePerObject,
folder);
}
The checkout, checkin and other operations on the SourceSafe are deliberately kept at the end of the sequence. Just in case the user tries to abort the scripting, we will have a chance to terminate the whole operation without touching the SourceSafe. Watch the flags and the sequence in the following code segment for the SourceSafe:
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));
All the operations are done recursively from the project item. That is why the VSSFLAG_RECURYES is used in all the calls.
UndoCheckout call next will undo checkout of any script which is missing from the last check-in.
Add call at the end. This simple 4 line code does all the work for us and demonstrates the power of Visual SourceSafe automation.
Some of the enhancements to the demo project to make it more effective can be:
| You must Sign In to use this message board. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Any chance of sharing it? 