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 href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vstechart/html/vstchFAQAboutVSNETAutomation.asp">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.

Comments (10)

  1. keith duncan says:

    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=\ospreyVSS
    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%buildChangeScript.sql

    echo Combining scripts…
    type %BUILDDIR%Functions*.UDF > %BUILDDIR%buildcombinedscript.sql
    type %BUILDDIR%views*.VIW >> %BUILDDIR%buildcombinedscript.sql
    type %BUILDDIR%StoredProcedures*.PRC >> %BUILDDIR%buildcombinedscript.sql

    type %BUILDDIR%buildcombinedscript.sql > %BUILDDIR%buildfinalscript.sql
    type %BUILDDIR%buildManagmentBlockMidSection.txt >> %BUILDDIR%buildfinalscript.sql

    @rem – run creates 4 times to ensure dependencies ok
    type %BUILDDIR%buildcombinedscript.sql >> %BUILDDIR%buildfinalscript.sql
    type %BUILDDIR%buildcombinedscript.sql >> %BUILDDIR%buildfinalscript.sql
    type %BUILDDIR%buildcombinedscript.sql >> %BUILDDIR%buildfinalscript.sql
    type %BUILDDIR%buildcombinedscript.sql >> %BUILDDIR%buildfinalscript.sql

    type %BUILDDIR%buildManagmentBlockFooter.txt >> %BUILDDIR%buildfinalscript.sql

    echo ********************************************************
    echo Update to TestDB data is starting.
    echo ********************************************************
    pause 1000

    echo Running scripts… >> %BUILDDIR%buildBuildReportCombinedScript.txt

    osql -S ServerName -d TestDB -E -i %BUILDDIR%sp_CheckRebuild.sql
    -o "%BUILDDIR%buildBuildReportCheckRebuild.txt"

    osql -S ServerName -d TestDB -E -i %BUILDDIR%buildChangeScript.sql -o "%BUILDDIR%buildBuildReportChangeScript.txt"
    pause 1000

    date /t >> %BUILDDIR%buildBuildReportChangeScript.txt
    time /t >> %BUILDDIR%buildBuildReportChangeScript.txt

    osql -S osprey -d SouthshoreEaster -E -i %BUILDDIR%buildfinalscript.sql -o "%BUILDDIR%buildBuildReportCombinedScript.txt"
    pause 1000

    date /t >> %BUILDDIR%buildBuildReportCombinedScript.txt
    time /t >> %BUILDDIR%buildBuildReportCombinedScript.txt
    echo …done >> %BUILDDIR%buildBuildReportCombinedScript.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)

  2. 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.

  3. althia says:

    perfect site good information, very nice news and etc… tnx

  4. aescleah says:

    Nice site. Thank to work…

  5. howland says:

    i try to find something at google.com and take it on your site…thanks

  6. alcott says:

    thank you for your work