VS.NET Automation Whitepaper Published

Busy busy in the VSS corral.  My friend and esteemed colleague Kemp Brown just released a whitepaper to MSDN entitled Frequently Asked Questions About Visual Studio .NET Automation that will, if you use Visual Studio .NET 2002 or 2003, become an invaluable reference as you become more and more acquainted with the mind-blowing extensibility that VS.NET supports.

Published 23 June 03 02:28 by KorbyP
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

# keith duncan said on July 4, 2003 5:24 AM:
We have used a data project for our internal builds and have come up with a reasonable way to allow nightly builds of our database from sourcesafe for our development system. Below is a breakdown of the technical description of our process.... Initial Creation of Control Mechanism Using a Enterprise Manager we started by scripting all the objects to separate ASCII files which are then stored into sub-folders for each type of object (view, sp,etc). Next Using VS.NET we create a database project, and drag the files into the project, this is then added to source safe. Finally a Stored Procedure was added to support Dependency Drop iterations of the source, and a management block for Full and Refresh builds Build Process 1. Generated SourceSafe Latest Version 2. Preprocess Scripts to add Dependency Management 3. Create Database - Assumes no database is created and will create a fresh instance 4. Create Base Tables - Base tables are created in a standard structure each time, variations will be applied as change script 5. Create Data - Constructed from SQL-Insert as a SQL script, this will deposit the TestDB data into the new database. 6. Run any change scripts - Apply any changes to the structure of the database in a versioned script, as each change is added a new change level will be set so changes are not repeated. 7. Create User Defined Function, View and Procedures - objects are created in the order listed 8. Repeat 7 to manage dependencies Build types Full Build – All steps in order 1 -> 8 Refresh Build – 1,2 then 6 onwards Object Build – Check Out , Run, Check In Description of sp_CheckVersion Then an object build is performed sp_CheckRebuild will be false and so the object will be dropped, prior to recreation in the caller. When a Refresh Build or Full Build are done a check is made to determine the presence of a “depbuild” extended property, first time through this will not exist and sp_ CheckRebuild will drop the object as before, on subsequent iteration of the script the “depbuild” property will stop the routine deleting existing objects. All this is performed within the management block which is placed around the full script. Source create PROC sp_CheckRebuild @objtype nvarchar(20), @objname nvarchar(100) as -- using ext prop to determine dependancy builds if not exists(SELECT * FROM ::fn_listextendedproperty('depbuild', NULL, NULL, NULL, NULL, NULL, NULL)) begin if exists(select * from sysobjects where type = @objtype and name = @objname) begin print 'dropping' if @objtype = 'FN' set @sqlstr = 'drop function ' + @objname if @objtype = 'V' set @sqlstr = 'drop view ' + @objname if @objtype = 'P' set @sqlstr = 'drop proc ' + @objname execute sp_ExecuteSQL @sqlstr end else print 'nothing to drop' end else begin print 'not dropping' end Usage exec sp_CheckRebuild 'v','dumbview' go --create test object create view dumbview as select 'hello' greeting Management Block -- Initial Run of code from finalscript -- Start Management Block MidSection go sp_addextendedproperty 'depbuild', '1' go -- End Management Block Footer -- Dependency Runs of code rrom finalscript (4 times) -- Start Management Block Footer go sp_dropextendedproperty 'depbuild' go -- End Management Block Footer Macro This macro can be added to the developers IDE to execute the steps needed to add the required header block to each object under source control in the database project. Sub InsertDataObjectBuildHeader() arPath = Split(DTE.ActiveDocument.Path, "\") strPath = "\" & arPath(UBound(arPath) - 1) & "\" Dim strActiveWindow As String = strPath & DTE.ActiveDocument.Name() & " (TestDB)" strActiveWindow = CType(strActiveWindow, String) DTE.ActiveDocument.Selection.LineUp(False, 1000) DTE.Find.FindWhat = "drop " DTE.Find.Target = vsFindTarget.vsFindTargetCurrentDocument DTE.Find.MatchCase = False DTE.Find.MatchWholeWord = False DTE.Find.Backwards = False DTE.Find.MatchInHiddenText = True DTE.Find.PatternSyntax = vsFindPatternSyntax.vsFindPatternSyntaxLiteral DTE.Find.Action = vsFindAction.vsFindActionFind DTE.Find.Execute() DTE.Windows.Item(Constants.vsWindowKindFindReplace).Close() DTE.ActiveDocument.Selection.LineDown(False, 2) DTE.ActiveDocument.Selection.StartOfDocument(True) DTE.ActiveDocument.Selection.Delete() DTE.ActiveDocument.Selection.LineUp(False, 1000) DTE.Find.FindWhat = "create " DTE.Find.Target = vsFindTarget.vsFindTargetCurrentDocument DTE.Find.MatchCase = False DTE.Find.MatchWholeWord = False DTE.Find.Backwards = False DTE.Find.MatchInHiddenText = True DTE.Find.PatternSyntax = vsFindPatternSyntax.vsFindPatternSyntaxLiteral DTE.Find.Action = vsFindAction.vsFindActionFind DTE.Find.Execute() 'Exit Sub DTE.Windows.Item(Constants.vsWindowKindFindReplace).Close() DTE.ActiveDocument.Selection.CharRight() DTE.ActiveDocument.Selection.EndOfLine(True) DTE.ActiveDocument.Selection.Copy() SetObjectType(DTE.ActiveDocument.Selection.Text) DTE.ActiveDocument.Selection.LineUp(False, 1000) DTE.ActiveDocument.Selection.Text = "exec sp_CheckRebuild '" DTE.ActiveDocument.Selection.Text = strObjectType & "', '" & strObjectName & "'" DTE.ActiveDocument.Selection.NewLine() DTE.ActiveDocument.Selection.Text = "Go" DTE.ActiveDocument.Selection.Text = "" strObjectType = "" strObjectName = "" End Sub The rebuild Batch File echo ******************************************************** echo Unattended install for TestDB is starting. echo ******************************************************** Set SSDIR=\\osprey\VSS Set BUILDDIR=d:\TestAutoBuild pause 1000 d: cd %BUILDDIR% echo updating files from sourcesafe... del storedprocedures\* /Q /F del functions\* /Q /F del views\* /Q /F del tables\* /Q /F ss Get $/TestDB -R -YUser -I- pause 1000 type %BUILDDIR%\Change Scripts\*.sql > %BUILDDIR%\build\ChangeScript.sql echo Combining scripts... type %BUILDDIR%\Functions\*.UDF > %BUILDDIR%\build\combinedscript.sql type %BUILDDIR%\views\*.VIW >> %BUILDDIR%\build\combinedscript.sql type %BUILDDIR%\StoredProcedures\*.PRC >> %BUILDDIR%\build\combinedscript.sql type %BUILDDIR%\build\combinedscript.sql > %BUILDDIR%\build\finalscript.sql type %BUILDDIR%\build\ManagmentBlockMidSection.txt >> %BUILDDIR%\build\finalscript.sql @rem - run creates 4 times to ensure dependencies ok type %BUILDDIR%\build\combinedscript.sql >> %BUILDDIR%\build\finalscript.sql type %BUILDDIR%\build\combinedscript.sql >> %BUILDDIR%\build\finalscript.sql type %BUILDDIR%\build\combinedscript.sql >> %BUILDDIR%\build\finalscript.sql type %BUILDDIR%\build\combinedscript.sql >> %BUILDDIR%\build\finalscript.sql type %BUILDDIR%\build\ManagmentBlockFooter.txt >> %BUILDDIR%\build\finalscript.sql echo ******************************************************** echo Update to TestDB data is starting. echo ******************************************************** pause 1000 echo Running scripts... >> %BUILDDIR%\build\BuildReportCombinedScript.txt osql -S ServerName -d TestDB -E -i %BUILDDIR%\sp_CheckRebuild.sql -o "%BUILDDIR%\build\BuildReportCheckRebuild.txt" osql -S ServerName -d TestDB -E -i %BUILDDIR%\build\ChangeScript.sql -o "%BUILDDIR%\build\BuildReportChangeScript.txt" pause 1000 date /t >> %BUILDDIR%\build\BuildReportChangeScript.txt time /t >> %BUILDDIR%\build\BuildReportChangeScript.txt osql -S osprey -d SouthshoreEaster -E -i %BUILDDIR%\build\finalscript.sql -o "%BUILDDIR%\build\BuildReportCombinedScript.txt" pause 1000 date /t >> %BUILDDIR%\build\BuildReportCombinedScript.txt time /t >> %BUILDDIR%\build\BuildReportCombinedScript.txt echo ...done >> %BUILDDIR%\build\BuildReportCombinedScript.txt We can then use a nightly build of the system to refresh from the sourcesafe Here is the Programmer Usage Procedure ---------------------------------------------------------- VS.Net Database Project All files have dbo. prefix and suffix depending on data object type: .VIW = View .UDF = Function .PRC = StoredProcedure When adding a new file ensure it has the following format: dbo.OBJECT_NAME.OBJECT_TYPE New files will be added to sourcesafe but it’s not possible to rename a file, so delete and recreate instead. Updating an Existing Script File 1. Check-out the relevant file in the VS.Net $/TestDB data project . This can be found on the sourcesafe 2. Make any changes within VS.Net or using QueryAnalyser 3. Ensure the sp_CheckRebuild statement is correctly included and that the main statement is a “create” (i.e. not an “alter”) before checking-in Adding a New Script File 1. Add new data object file into the relevant folder type in the VS.Net TestDB data project 2. Add Sql script directly within VS.Net or by using QueryAnalyser then right-click the data object in ServerExplorer and select “Generate Create Script” and paste the generated script into the new TestDB data project file 3. Before checking in, replace the drop statement with a sp_CheckRebuild statement, specify the object type and the object name. Example: exec sp_CheckRebuild 'v', 'vSomeView' Go create view vSomeView as begin --do something end Note: The sp_CheckRebuild statement must be followed by a “go” statement Alternatively, run InsertDataObjectBuildHeader() in macro DataObjectBuildHeader to automatically insert the correct header for the active document. See macro code in technical overview. Notes: Remove all unrequired double quotes from statements. Example: select @tempDate = dateadd("dd",7 * (@week -1),@TempDate) becomes.. select @tempDate = dateadd(dd,7 * (@week -1),@TempDate)
# Korby Parnell said on July 10, 2003 2:51 AM:
HOLY YOU GUYS ROCK!! I'm speechless. If you lived in the other Washington (WA west), I'd sign a dollar bill and ask you to redeem it for a job in my division. Wow. I look forward to testing this myself as soon as I get off jury duty. Darned lawsuits.
# althia said on June 24, 2006 4:58 AM:
perfect site good information, very nice news and etc... tnx
# aescleah said on July 4, 2006 7:32 PM:
Nice site. Thank to work...
# howland said on July 4, 2006 7:32 PM:
i try to find something at google.com and take it on your site...thanks
# alcott said on July 17, 2006 11:23 PM:
thank you for your work
# replicarolex said on September 16, 2006 7:50 AM:
[URL=http://http://replicarolexwatch.ir.pl]replica-rolex-watch[/URL]<a href="http://http://replicarolexwatch.ir.pl">replica rolex watch</a>

Leave a Comment

(required) 
(optional)
(required) 

Search

Go

This Blog

Syndication

Page view tracker