A few months ago, we have a blog talking about extending the web publishing pipeline to package database project deployed SQL file.  In this blog, I’ll show step by step example of packaging web application with SQL Server database upgrade SQL file using Visual Studio 2010.  This way, we can generate a web package with incremental SQL script to certain database version.  The scenario may help distributions of a web upgrade package which needs to be installed on many different locations, each with their own SQL server database.

1. Create a Web Application Project

2. Add a new “SQL Server 2008 Database Project” to the solution

3. Prepare a development database, such as my test1 database as the following:

4. Right click our database project in solution explorer, and select “Import Database Objects and Settings”, then select the development database as connection in the “Import Database Wizard” dialog.  Click Start then finish when import succeeded.

5. In database project property page, select “Deploy” tab, and edit the target database settings.  Let’s targeting the development database so that we can update to development database easily through Visual Studio deploy command.  Also, choose the deploy action to be “Create a deployment script (.sql) and deploy to the database”

6. Run deploy for the test database to check if it builds successfully.  Nothing should be deployed to the development database at this point since they should be identical.  Check the deployed SQL directory %database project dir%sql%configuration% making sure sql file is generated

7. In Web application project’s property page, tab “Package/Publish SQL’”, add a new database entry for debug configuration.

8. Select it, add the deployed database script (generated in step 6) to the script list

6. Add extension pipeline project target file <projectName>.wpp.targets file in the same directory as the web project file.  How <projectName>.wpp.targets works is explained in how to package registry blog. For example, in the same directory as test1.vbproj file, add test1.wpp.targets file.  Make its content as following :

<!--********************************************************************-->
<!-- Task dependent db project custom deploy before build -->
<!--********************************************************************-->
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
      <MyTargetConnectionString Condition="'$(MyTargetConnectionString)'==''">"Data Source=1p18-fwg36%3BIntegrated Security=True%3BPooling=False"</MyTargetConnectionString>
      <MyTargetDatabase Condition="'$(MyTargetDatabase)'==''">StageDatabase1</MyTargetDatabase>

      <AfterAddContentPathToSourceManifest Condition="'$(AfterAddContentPathToSourceManifest)'==''">
      $(AfterAddContentPathToSourceManifest);
      DeployDbProjBeforePackage;
    </AfterAddContentPathToSourceManifest>
  </PropertyGroup>

  <!-- Specifies each dependent database project here, its properties will be appended to the target msbuild Properties -->
  <ItemGroup>
    <MyDependentDBProjects Include="..Database1Database1.dbproj">
      <Properties>DeployScriptFilePath=..Database1sql$(Configuration)Database1.sql</Properties>
    </MyDependentDBProjects>
  </ItemGroup>

  <Target Name="DeployDbProjBeforePackage">
    <!-- Make sure the corresponding directory exists before build the DB projects, especially useful for team build -->
    <Message Text="Creating directory: %(MyDependentDBProjects.rootdir)%(MyDependentDBProjects.directory)sql$(Configuration)" />
    <MakeDir Directories="%(MyDependentDBProjects.rootdir)%(MyDependentDBProjects.directory)sql$(Configuration)" />

    <MSBuild Projects="@(MyDependentDBProjects)" Targets="Build;Deploy" Properties="Configuration=$(Configuration);TargetConnectionString=$(MyTargetConnectionString);TargetDatabase=$(MyTargetDatabase);DeployToDatabase=False">
    </MSBuild>
  </Target>

</Project>

Note:

• connection string needs to be escaped so that semicolon is represented by %3B

• When msbuild dbproject target, changed its property TargetConnectionString, TargetDatabase, and set DeployToDatabase=False so that it only generate the incremental SQL script

7. Save the project and make sure it builds.

8. Test the normal project package from command line, such as:  msbuild test1test1.csproj /t:package  (Note, if you want to test out in Visual Studio UI, please make sure to reload the project after each change in <projectname>.wpp.targets file.

For team build parameters, you can check the previous blog.  To deploy a web package, you can check this blog.  For database deployment, please check this MSDN example.

Xinyang Qiu | Web Platform And Tools