The Visual Studio Team System 2008 Database Edition GDR release introduces two new ways on how to share information between projects, partial projects and composite projects. This blog post will go in to how to use partial projects, composite projects will be discussed in a later post.

When we started talking to existing users of the current database project system and the use cases involved it became clear that many had a need to share actual implementation between different database projects. One common scenario we were confronted was is what I will call the "baseline" project. All projects derive from a common set of objects inside a base project. Some users solved this by copying the implementation files; some used clever source code control mapping tricks, none provided an easy answer to the problem, hence the idea of "Partial Projects" was born.

Partial projects allow code sharing between projects by including files from different project. The "base" project exports a list of files that can be included by the "derived" project. This list is create by the "base" project and materialized as a .files file.

image

The "derived" project includes the .files file. The result is that the code exported by the base project is included as-is in the derived project. The source code control ownership remains with the parent (base) project. So the files are included in a read-only fashion. This new feature promotes code re-use while maintaining single sourcing of artifacts and resulting in a single deployment where the derived projects includes the base project artifacts.

 

Partial Projects Example

Lets walk through a simple but typical example. We will create a new Visual Studio Solution, named PartialDBProj, to which we will add two new Database Projects, BaseDB and DerivedDB.

Step 1: Create PartialDBProh solution file

  • File->New Project->Other Project Types->Visual Studio Solutions
  • Name: PartialDBProj

Step 2: Add Database Projects to Solution

  • File->Add New Project->Database Projects->SQL Server 2008->SQL Server 2008 Database Project
  • Name: BaseDB
  • File->Add New Project->Database Projects->SQL Server 2008->SQL Server 2008 Database Project
  • Name: DerivedDB

The end result should like something like this:
image

Step 3: Add Tables to BaseDB project

  • Add 3 tables to the BaseDB project, either using Add Item in Solution Explorer or through the Schema View, just use the default names and definition [dbo].[Table1], [dbo].[Table2] and [dbo].[Table3]

Step 4: Export two table from the BaseDB project

  • In Solution Explorer, select the files you want to share/export. In this example select Table1 and Table3, right click and choose the "Export As Partial Project..." option

image image image

  • You will be asked where to save the .files file, the default location is inside the root of the exporting project. You will also be asked if you want to add the .files file to the exporting project file, if you choose to do so your Solution Explorer looks something like this:

image

Step 5: Open the .files file

  • Right click on the BaseDB.files files and choose Open. This will open the file and will show the XML contents of the file.
   1: <?xml version="1.0" encoding="utf-16"?>
   2: <Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
   3:   <!--
   4:       Before you import this file, you must define the 'BaseDBBasePath_5_10_2008_05_12_18' property to point to the directory where this file is located. 
   5:       Failure to define this property will result in a build error.
   6: -->
   7:   <ItemGroup>
   8:     <Build Include="$(BaseDBBasePath_5_10_2008_05_12_18)\Schema Objects\Schemas\dbo\Tables\Table1.table.sql">
   9:       <Link>BaseDBBasePath_5_10_2008_05_12_18\Table1.table.sql</Link>
  10:       <SubType>Code</SubType>
  11:     </Build>
  12:     <Build Include="$(BaseDBBasePath_5_10_2008_05_12_18)\Schema Objects\Schemas\dbo\Tables\Table3.table.sql">
  13:       <Link>BaseDBBasePath_5_10_2008_05_12_18\Table3.table.sql</Link>
  14:       <SubType>Code</SubType>
  15:     </Build>
  16:   </ItemGroup>
  17: </Project>

As you can see the .files file contains MSBuild links.

Step 6: Add Views to DerivedDB project

  • Add 2 views to the DerivedDB project, either using Add Item in Solution Explorer or through the Schema View.
  • Add [dbo].[View1] using the following definition: 
    •    1: CREATE VIEW [dbo].[View1]
       2: AS
       3: SELECT  [T1].[column_1],
       4:         [T1].[column_2]
       5: FROM    [dbo].[Table1] AS [T1]
     
  • Add [dbo].[View3] using the following definition: 
    •    1: CREATE VIEW [dbo].[View3]
       2: AS 
       3: SELECT  [T3].[column_1],
       4:         [T3].[column_2]
       5: FROM    [dbo].[Table3] AS [T3]

Now that you added the views to the DerivedDB project, you will notice these views are in an error state

image

Step 7: Import the Partial Project

  • Now it is time to import the partial project in to the DerivedDB project. In Solution Explorer, select the DerivedDB project node, go to the Project menu and choose "Import Partial Project...". You can also right click on the project node and choose "Import Partial Project.
  • You first get a notification that adding or removing partial project will cause project unload - load to occur, to add the MSBuild includes to the project
    image
  • If you answered Yes to the previous question you will get a file selection dialog, which you use to select the BaseDB.files file
    image
  • When you select the file, the DerivedDB project will be unloaded, we add the link to the BaseDB.files file and reload the DerivedDB project. After the reload is completed you will see the included objects in Solution Explorer. The icon with the arrow indicates the is a linked object.
    image
  • Inside "Schema View" you will now find Table1 and Table3, just as if you added them to the project yourself.
    image

Step 8: Build the DerivedDB project

  • If you now Build and Deploy the DerivedDB project you will find that Table1 and Table3 are part of the build output (DerivedDB.dbschema) and therefore included in the deployment.

Including objects through a partial project have the same effect as adding the objects to the project directly. Partial projects are effectively includes of sets of files, where a set can contain 1 to N number of files.

Step 9: Remove the partial project

  • The last step is to remove the partial project from the DeviredDB project, by selecting the project node inside Solution Explorer and choosing the "Remove Partial Project..." option from the Project menu or by right clicking on the project node inside Solution Explorer and selecting the "Remove Partial Project..." options.
  • You will receive the same question when you added the project project, asking you to confirm if it is OK to unload and reload the project file.
    image
  • Since you can have more then one Partial Project imported you need to select which one to unload, by selecting the ones to unload.
     image
  • When you select OK, the DerivedDB project will be unloaded, we remove the link and reload the project, after which the Table1 and Table3 objects are no longer part of the DerivedDB project and the views will be in an error state again.

That concludes the example. We have seen how to:

  • Export a Partial Project
  • Import a Partial Project
  • Remove a Partial Project

Conclusion

Partial Projects are a simple mechanism to share implementation between project, without having to transfer the source code control ownership between the projects and resulting in a single deployment unit from Build. Conceptually "Partial Projects" are comparable with C/C++ #include files.

I hope this post gives you enough information to start using this great new feature.

-GertD