{"id":3204,"date":"2010-03-24T17:07:05","date_gmt":"2010-03-24T17:07:05","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/webdev\/2010\/03\/24\/extending-the-web-publishing-pipeline-to-package-database-project-deployed-sql-file\/"},"modified":"2010-03-24T17:07:05","modified_gmt":"2010-03-24T17:07:05","slug":"extending-the-web-publishing-pipeline-to-package-database-project-deployed-sql-file","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/dotnet\/extending-the-web-publishing-pipeline-to-package-database-project-deployed-sql-file\/","title":{"rendered":"Extending the Web Publishing Pipeline to package database project deployed SQL file"},"content":{"rendered":"

A Visual Studio solution file may contain both database project and a web application project.  When deploying (not building) the database project, it can generate a .sql file containing the full sql file or an incremental upgrade file against a database.  This blogs walkthrough one way to package database project generated sql file into the web application project\u2019s package.  This solution works for team build scenario as well.<\/p>\n

 <\/p>\n

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

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

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

3. In each of the database project, add a sample table and make sure build and deploy are successful.  Check the deployed SQL directory %database project dir%sql%configuration% making sure sql file is generated<\/p>\n

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

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

5. Select it, and add the two database script files (generated in step 3) to the script list <\/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>-->\n<!-- <\/span>Task dependent db project custom deploy before build <\/span>-->\n<!--<\/span>********************************************************************<\/span>-->\n<<\/span>Project <\/span>xmlns<\/span>=<\/span>"http:\/\/schemas.microsoft.com\/developer\/msbuild\/2003<\/span>">\n  <<\/span>PropertyGroup<\/span>>\n    <<\/span>AfterAddContentPathToSourceManifest <\/span>Condition<\/span>=<\/span>"'$(AfterAddContentPathToSourceManifest)'==''<\/span>">\n      <\/span>$(AfterAddContentPathToSourceManifest);\n      DeployDbProjBeforePackage;\n    <\/<\/span>AfterAddContentPathToSourceManifest<\/span>>\n  <\/<\/span>PropertyGroup<\/span>>\n\n  <!-- <\/span>Specifies each dependent database project here, its properties will be appended to the target msbuild Properties <\/span>-->\n  <<\/span>ItemGroup<\/span>>\n    <<\/span>MyDependentDBProjects <\/span>Include<\/span>=<\/span>"..Database1Database1.dbproj<\/span>">\n      <<\/span>Properties<\/span>><\/span>DeployScriptFilePath=..Database1sql$(Configuration)Database1.sql<\/<\/span>Properties<\/span>>\n    <\/<\/span>MyDependentDBProjects<\/span>>\n\n    <<\/span>MyDependentDBProjects <\/span>Include<\/span>=<\/span>"..Database2Database2.dbproj<\/span>">\n      <<\/span>Properties<\/span>><\/span>DeployScriptFilePath=..Database2sql$(Configuration)Database2.sql<\/<\/span>Properties<\/span>>\n    <\/<\/span>MyDependentDBProjects<\/span>>\n  <\/<\/span>ItemGroup<\/span>>\n\n  <<\/span>Target <\/span>Name<\/span>=<\/span>"DeployDbProjBeforePackage<\/span>">\n    <!-- <\/span>Make sure the corresponding directory exists before build the DB projects, especially useful for team build <\/span>-->\n    <<\/span>Message <\/span>Text<\/span>=<\/span>"Creating directory: %(MyDependentDBProjects.rootdir)%(MyDependentDBProjects.directory)sql$(Configuration)<\/span>" \/>\n    <<\/span>MakeDir <\/span>Directories<\/span>=<\/span>"%(MyDependentDBProjects.rootdir)%(MyDependentDBProjects.directory)sql$(Configuration)<\/span>" \/>\n\n    <<\/span>MSBuild <\/span>Projects<\/span>=<\/span>"@(MyDependentDBProjects)<\/span>" Targets<\/span>=<\/span>"Build;Deploy<\/span>" Properties<\/span>=<\/span>"Configuration=$(Configuration);<\/span>">\n    <\/<\/span>MSBuild<\/span>>\n  <\/<\/span>Target<\/span>>\n\n<\/<\/span>Project<\/span>>\n\n<\/span><\/pre>\n
<\/a><\/p>\n
7. Save the project and make sure it builds.<\/p>\n
8. Test the normal project package from command line, such as:  msbuild test1test1.vbproj \/t:package<\/p>\n
9. Test the team build solution package from command line, such as:  msbuild test1.sln \/p:DeployOnBuild=true .  Note: DeployOnBuild parameter file is used exclusively for solution build, which asks web application project to package when build in the solution.<\/p>\n
10. Check in to a TFS server and define a new build definition and test it in real team build environment<\/p>\n
\"image\"<\/a> <\/a><\/p>\n
Note, the target file works for \u201cBuild Deployment Package\u201d command in Visual Studio UI as well.  Since VS caches the targets file for a project, if you make editions to the targets file, in order for VS UI to use it, you might need to reload the project.<\/p>\n
\n    
<\/span>Xinyang Qiu | Visual Web Developer<\/span><\/p>\n
<\/span><\/p>\n
<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
A Visual Studio solution file may contain both database project and a web application project.  When deploying (not building) the database project, it can generate a .sql file containing the full sql file or an incremental upgrade file against a database.  This blogs walkthrough one way to package database project generated sql file into the […]<\/p>\n","protected":false},"author":404,"featured_media":58792,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[7356,7352,7343,7353,7354,7357,7323,7329],"class_list":["post-3204","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-aspnet","tag-database-project","tag-package","tag-publish","tag-target-extension","tag-target-file","tag-team-build","tag-visual-studio-2010","tag-web-deployment"],"acf":[],"blog_post_summary":"
A Visual Studio solution file may contain both database project and a web application project.  When deploying (not building) the database project, it can generate a .sql file containing the full sql file or an incremental upgrade file against a database.  This blogs walkthrough one way to package database project generated sql file into the […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts\/3204","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/users\/404"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/comments?post=3204"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts\/3204\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/media\/58792"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/media?parent=3204"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/categories?post=3204"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/tags?post=3204"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}