CODEWITHSUNDEEP
visual-studio-2010sqlclrdatabase-project
Ralph Shillington
Ralph Shillington

Reputation: 21098

How to add a custom SQLCLR assembly to a Visual Studio Database Project

I have Database project and a SQLCLR database project in the same solution. Both projects deploy code to a database, however I would like the database project to be responsible for the deployment of all the code, including the custom assembly.

We use the database project to deploy to our staging and production environments, what's unclear is how to get the assembly of the seperate project to be deployed as well.

Upvotes: 2

Views: 2469

Answers (2)

Atilla Ozgur
Atilla Ozgur

Reputation: 14701

This two project file are msbuild project files. Therefore you can use a msbuild script to drive both of them.

Database Project imports this file. $(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets

In my 64 bit machine

C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\TeamData

It has targets like DspBuild DspSetupDeploy DspDeploy

While SqlClr project imports 

C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets

It has targets like

SqlClrDeploy

A simple msbuild file which calls both of deploy target should be enough.

Or you can customize database project's file to call before build to other file's target. Which will be more complex.

Upvotes: 0

Ralph Shillington
Ralph Shillington

Reputation: 21098

As it turns out this is very simple. In the Database project add a reference to the SqlClr project. The database project will then automatically identify the assembly, and add it to it's set of scripts.

With the assembly added to to the database, it's a simple matter to reference it in the create scripts of the CLR types, functions, aggregates, etc.

Step by step to adda SQLCLR aggregate function: 1) right-click the database project -> Add Reference. Select the SQL CLR project from the list of projects in the solution

2) Switch to the database Schema (View -> Database Schema View) Verify that the Assembly appears in the Assemblies foler

3) Drill down to the Functions Folder (Schema -> dbo -> Programmability -> Functions) Right click and Add -> Function -> Aggregate Function

Edit the template accordingly to create the aggregate.

Now the database project is the a complete unit of deployment and the SQL CLR project is not needed as part of the deployment.

Upvotes: 2

Related Questions