{"id":2914,"date":"2010-08-09T11:07:23","date_gmt":"2010-08-09T11:07:23","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/webdev\/2010\/08\/09\/an-example-of-packaging-web-application-containing-database-upgrade-sql-file\/"},"modified":"2022-08-16T01:34:40","modified_gmt":"2022-08-16T08:34:40","slug":"an-example-of-packaging-web-application-containing-database-upgrade-sql-file","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/dotnet\/an-example-of-packaging-web-application-containing-database-upgrade-sql-file\/","title":{"rendered":"An example of packaging web application containing database upgrade SQL file"},"content":{"rendered":"

A few months ago, we have a blog<\/a> talking about extending the web publishing pipeline to package database project deployed SQL file.  In this blog, I\u2019ll 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.<\/p>\n

 <\/p>\n

1. Create a Web Application Project<\/p>\n

2. Add a new \u201cSQL Server 2008 Database Project\u201d to the solution<\/p>\n

\"image_2[1]\"<\/a> <\/p>\n

3. Prepare a development database, such as my test1 database as the following:<\/p>\n

\"image\"<\/a> <\/p>\n

4. Right click our database project in solution explorer, and select \u201cImport Database Objects and Settings\u201d, then select the development database as connection in the \u201cImport Database Wizard\u201d dialog.  Click Start then finish when import succeeded.<\/p>\n<\/p>\n

\"image\"<\/a> <\/p>\n

\"image\"<\/a> <\/p>\n<\/p>\n

5. In database project property page, select \u201cDeploy\u201d tab, and edit the target database settings.  Let\u2019s 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 \u201cCreate a deployment script (.sql) and deploy to the database\u201d<\/p>\n

\"image\"<\/a> <\/p>\n

 <\/p>\n

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<\/p>\n

\"image\"<\/a> <\/p>\n

7. In Web application project\u2019s property page, tab \u201cPackage\/Publish SQL’\u201d, add a new database entry for debug configuration.<\/p>\n

\"image_4[1]\"<\/a> <\/p>\n

8. Select it, add the deployed database script (generated in step 6) to the script list<\/p>\n<\/p>\n<\/p>\n

\"image\"<\/a> <\/p>\n

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<\/a>. For example, in the same directory as test1.vbproj file, add test1.wpp.targets file.  Make its content as following :<\/p>\n

<!--<\/span>********************************************************************<\/span>-->\r\n<!-- <\/span>Task dependent db project custom deploy before build <\/span>-->\r\n<!--<\/span>********************************************************************<\/span>-->\r\n<<\/span>Project <\/span>xmlns<\/span>=<\/span>"http:\/\/schemas.microsoft.com\/developer\/msbuild\/2003<\/span>">\r\n  <<\/span>PropertyGroup<\/span>>\r\n      <<\/span>MyTargetConnectionString <\/span>Condition<\/span>=<\/span>"'$(MyTargetConnectionString)'==''<\/span>"><\/span>"Data Source=1p18-fwg36%3BIntegrated Security=True%3BPooling=False"<\/<\/span>MyTargetConnectionString<\/span>>\r\n      <<\/span>MyTargetDatabase <\/span>Condition<\/span>=<\/span>"'$(MyTargetDatabase)'==''<\/span>"><\/span>StageDatabase1<\/<\/span>MyTargetDatabase<\/span>>\r\n\r\n      <<\/span>AfterAddContentPathToSourceManifest <\/span>Condition<\/span>=<\/span>"'$(AfterAddContentPathToSourceManifest)'==''<\/span>">\r\n      <\/span>$(AfterAddContentPathToSourceManifest);\r\n      DeployDbProjBeforePackage;\r\n    <\/<\/span>AfterAddContentPathToSourceManifest<\/span>>\r\n  <\/<\/span>PropertyGroup<\/span>>\r\n\r\n  <!-- <\/span>Specifies each dependent database project here, its properties will be appended to the target msbuild Properties <\/span>-->\r\n  <<\/span>ItemGroup<\/span>>\r\n    <<\/span>MyDependentDBProjects <\/span>Include<\/span>=<\/span>"..Database1Database1.dbproj<\/span>">\r\n      <<\/span>Properties<\/span>><\/span>DeployScriptFilePath=..Database1sql$(Configuration)Database1.sql<\/<\/span>Properties<\/span>>\r\n    <\/<\/span>MyDependentDBProjects<\/span>>\r\n  <\/<\/span>ItemGroup<\/span>>\r\n\r\n  <<\/span>Target <\/span>Name<\/span>=<\/span>"DeployDbProjBeforePackage<\/span>">\r\n    <!-- <\/span>Make sure the corresponding directory exists before build the DB projects, especially useful for team build <\/span>-->\r\n    <<\/span>Message <\/span>Text<\/span>=<\/span>"Creating directory: %(MyDependentDBProjects.rootdir)%(MyDependentDBProjects.directory)sql$(Configuration)<\/span>" \/>\r\n    <<\/span>MakeDir <\/span>Directories<\/span>=<\/span>"%(MyDependentDBProjects.rootdir)%(MyDependentDBProjects.directory)sql$(Configuration)<\/span>" \/>\r\n\r\n    <<\/span>MSBuild <\/span>Projects<\/span>=<\/span>"@(MyDependentDBProjects)<\/span>" Targets<\/span>=<\/span>"Build;Deploy<\/span>" Properties<\/span>=<\/span>"Configuration=$(Configuration);TargetConnectionString=$(MyTargetConnectionString);TargetDatabase=$(MyTargetDatabase);DeployToDatabase=False<\/span>">\r\n    <\/<\/span>MSBuild<\/span>>\r\n  <\/<\/span>Target<\/span>>\r\n\r\n<\/<\/span>Project<\/span>>\r\n<\/span><\/pre>\n
Note:<\/pre>\n