SQLCLRProject

SQLCLRProject is a tool that allows you to deploy .NET assemblies to SQL Server (version 2005 and later). Under the covers it uses MSBUILD tasks. It deploys the assembly(ies) to the database as well as creating the T-SQL procedures/functions/triggers/etc. In addition, it also creates T-SQL scripts during the deployment process.

The tool can be used both from command-line as well as by using a GUI and also as an add-in to Visual Studio 2005/2008 (with it’s own project type and item templates).

The tool started out as a very, very basic command line tool, but it evolved over time and consists now of four parts:

• yukondeploy.dll - this is the underlying dll that contains the various MSBUILD tasks and does all the heavy lifting. You can use yukondeploy together with build scripts from the command-line directly.
• DeployProperties.exe - a stand-alone UI executable which allows you to generate (and execute) build scripts from a GUI.
• VSTemplates - Visual Studio (2005 and later) C-Sharp and VB.NET project- and item-templates to create .NET assemblies that can be deployed to SQL Server.
• DeployAddIn - an add-in for Visual Studio, which allows you to generate the necessary build files and do deployment from inside VS based on the VS projects you have created off the templates above.

Features

Here follows some of the major features of the SQLCLRProject tool.

• Automatically deploy an assembly to SQL Server and create the T-SQL methods from the .NET functions. This can be done from the command line, the DeployProperties GUI executable as well as from inside the Visual Studio IDE.
• Automatic creation of T-SQL deployment scripts.
• Automatic creation of T-SQL DML scripts to test the generated T-SQL methods.
• Ability to alter an assembly and only deploy newly added methods from the assembly.
• Ability to create objects in a non default schema: “schemaname.objectname”.
• Assigning of default values to parameters in the T-SQL generated objects.
• When re-deploying a UDT, instead of dropping the whole table - the deploy task can create a new column in the table in question and transfer all the data from the original column over to the new column, and then drop the original column.
• Debug facilities from inside the VS IDE.

Visual Studio

By now, especially the ones of you that are using Visual Studio, you may wonder what the differences are between the SQLCLRProject and the VS built-in project type for SQL Server; SQL Server Project. After all, the SQL Server Project is built in into VS. Well, quite a lot:

Feature	SQLCLRProject	VS built-in
Specific VS project type and item templates	Yes	Yes
Automatic deployment of assemblies and creation of T-SQL objects	Yes	Yes
Debugging support	Yes	Yes
Automatic creation of T-SQL deployment scripts	Yes	-
Automatic creation of T-SQL DML scripts for testing of the created objects	Yes	-
Alter an assembly and only deploy newly added methods	Yes	-
Create objects in a non-default schema	Yes	-
Define parameters in the .NET methods to have default values in T-SQL	Yes	-
Define parameters in .NET to be created with different names in T-SQL	Yes	-
Redeploying an UDT without manually dropping tables/columns depending on the UDT	Yes	-
Deployment of methods with nullable value-types as parameters (SQL 2008)	Yes	-
Support for SQL Server 2008's new spatial data-types and HierarchyId	Yes	-

In addition to the above, the SQLCLRProject works both against SQL Server 2005 as well as against SQL Server 2008, whereas in Visual Studio 2005 the VS built-in project does not work against SQL Server 2008.

Support

When installing the tool, documentation are installed as well. The documentation covers all the different parts of SQLCLRProject.

If you:

• need more help
• come across things that don’t work as you think they should
• you come across bugs (as unlikely as that might be - yeah right :-) )
• you have ideas how to evolve SQLCLRProject,

please post a comment on this page or write me an email. If it is a feature request or a bug report, you can use the "Issue Tracker" tab.