Walkthrough: Create and Deploy a Database Project that Uses a SQLCLR Object

Article
08/23/2010

To create and deploy a database that uses a SQL common language runtime (CLR) assembly, you create a database project and a SQLCLR project. When you build and deploy the database project, you automatically deploy the SQLCLR assembly that it references at the same time.

In this walkthrough, you will perform the following tasks:

Create a simple database project.
Create a SQLCLR project and define a new data type.
Use the type that you defined in the SQLCLR assembly.
Build and deploy the database project.

Prerequisites

To complete this walkthrough, you must have installed the general distribution release (GDR) of Visual Studio Team System 2008 Database Edition. You must have access to an instance of SQL Server 2005 or SQL Server 2008. You must have installed the code samples for SQL Server from the CodePlex Web site. You must have permissions to deploy a database to your database server.

Create a Database Project

To create a database project

On the File menu, point to New, and click Project.

The New Project dialog box appears.
In Project types, expand the Database Projects node, and click Microsoft SQL Server 2005 or Microsoft SQL Server 2008.
In the Templates list, click SQL Server 2005 Database Project or SQL Server 2008 Database Project.
In Name, type SQLClrDatabaseSandbox.

Important Note:

In Location and Solution Name, do not change the default values.
Select the Create directory for solution check box if it is not already selected.
Clear the Add to Source Control check box if it is not already cleared, and click OK.

The empty database project appears in Solution Explorer. Next you create a project for the SQLCLR assembly.

Important Note:
In Location and Solution Name, do not change the default values.

Create a SQLCLR Project

To create a SQLCLR project

On the File menu, point to Add, and click Project.

The New Project dialog box appears.
In Project types, expand the Database Projects node, and click SQL CLR.
In the Templates list, click SQL Server Project next to the C# icon.
In Name, type SupportingSQLClrObjects.
Click OK to accept the default location, close the dialog box, and create the project.

The empty database project appears in Solution Explorer.
In the Add Database Reference dialog box, click Cancel.

By clicking Cancel, you enable the database project to determine where the assembly will be deployed.

Next, you create a custom data type in the SQLCLR assembly.

To define a custom SQLCLR data type

On the Project menu, click Add User-Defined Type.

The Add New Item dialog box appears.
In Name, type MyCustomType.cs.

The code editor opens and displays the contents of MyCustomType.cs.

Note

At this point, you should modify the default user-defined type to provide an implementation that meets your requirements. For this walkthrough, you will accept the default implementation. For more information about how to define custom types and other SQLCLR objects, see "Using CLR Integration in SQL Server 2005" on the Microsoft Web site.
On the File menu, click Save MyCustomType.cs.

To configure and build the SQLCLR assembly

On the Build menu, click Configuration Manager.

The Configuration Manager dialog box appears.
In Project contexts, clear the Deploy check box in the row for the SupportingSQLClrObjects assembly.

By clearing the check box, you prevent the assembly from being deployed when you deploy the solution. Because the assembly will be deployed with the database project, you do not want the assembly to be deployed two times.
Click Close.
In Solution Explorer, right-click the SupportingSQLClrObjects project, and click Build.

The assembly builds without any errors.

Next, you will reference the new assembly and use the type that you defined in that assembly.

Use the Type Defined in the SQLCLR Assembly

To use the type that you defined in the SQCLR assembly, you must add a reference from your database project to that assembly. You then define the data type in your database project so that you can use that new data type in object definitions.

To add a reference to the SQLCLR assembly

In Solution Explorer, expand the SQLClrDatabaseSandbox project, right-click the References node, and click Add Reference.

Note

You can also click the SQLClrDatabaseSandbox project in Solution Explorer, open the Project menu, and click Add Reference.

The Add Reference dialog box opens.
On the Projects tab, verify that the SupportingSQLClrObjects project is highlighted, and click OK.

A reference to the SQLCLR project is added to your database project.
In Solution Explorer, expand the References node, click the SupportingSQLClrObjects node, press F4.

The properties for the reference appear.
Verify that Copy Local is set to true and that Permission Level is set to Safe.
In the Assembly Name property, replace SqlClassLibrary with SupportingSQLClrObjects.
On the File menu, click Save All.

The properties that you specified control the CREATE ASSEMBLY statement that is created when you build the project.

Next, you define a data type that uses the assembly that the SQLCLR project produces.

To define the new data type

On the View menu, click Error List.
On the View menu, click Database Schema View.
Expand the SQLClrDatabaseSandbox node, expand the Schemas node, expand the dbo node, and expand the Programmability node.
Right-click the Types node, point to Add, and click User-defined Type CLR.

The Add New Item dialog box appears.
In Templates, verify that User-defined Type (CLR) is highlighted.
In Name, type MyCustomType, and click Add.

The Transact-SQL (T-SQL) editor appears and displays the definition for the type. In the Error List, the following error appears:

SR0029 : Microsoft.Validation : User Defined Type: [dbo].[MyCustomType] has an unresolved reference to Assembly [assembly_name]

In the T-SQL editor, update the code to match the following:

CREATE TYPE [dbo].[MyCustomType]
EXTERNAL NAME [SupportingSQLClrObjects].[MyCustomType]

On the File menu, click Save MyCustomType.udtclr.sql.

The error disappears from the Error List because you have updated the type definition to refer to a valid assembly and class.

Next, you define a simple table that uses the new data type.

To use the new data type in a table definition

In Schema View, locate the Tables node by looking under the Schemas node and then under the dbo node.
Right-click the Tables node, point to Add, and click Table.

The Add New Item dialog box appears.
In Templates, verify that Table is highlighted.
In Name, type SimpleTable, and click Add.

The T-SQL editor appears and displays the definition for the table.

In the T-SQL editor, update the code to match the following:

CREATE TABLE [dbo].[SimpleTable]
(
column_1 int NOT NULL, 
column_2 [dbo].[MyCustomType] NULL
)

On the File menu, click Save dbo.SimpleTable.table.sql.

Next, you will configure the database project and then deploy it to an isolated development environment.

Build and Deploy the Database Project

To configure build settings

In Solution Explorer, click SQLClrDatabaseSandbox.
On the Project menu, click SQLClrDatabaseSandbox Properties.
Click the Build tab, and review the default settings.

In this walkthrough, you can use the default settings.

Next, you will configure deployment settings.

To configure deployment settings

Click the Deploy tab.
In the Deploy Action list, click Create a deployment script (.sql) and deploy to database.
In the Configure deployment settings for list, click My isolated development environment.

By specifying this setting, you configure values that affect only your isolated development environment. You will not change the settings for the database project.
In Deployment script name, accept the default value.
In Target Database Settings, click Edit to specify a target connection.
In the Connection Properties dialog box, specify a connection to the server and database where you want to deploy the database project, and then click OK.
In Target database name, accept the default value.
In the Deployment configuration file list, click Properties\Database.deploymentconfig.

By default, your isolated development environment is not associated with a set of detailed deployment settings. When you specify this file, you will use the deployment settings that are associated with the database project. You could also create a separate configuration file and modify the settings for your isolated development environment.
In the SQL command variables file list, click Properties\Database.sqlcmdvars.
On the File menu, click Save All.

Next, you will build the database project.

To build and deploy the database project

On the Build menu, click Build Solution.

The database project and the SQLCLR assembly project build successfully.
On the Build menu, click Deploy Solution.

The database project and the SQLCLR assembly are deployed to the target server and database that you specified in the deployment settings. In addition, the following message appears in the Output window:

========== Deploy: 1 succeeded, 0 failed, 1 skipped ==========. The database project was successfully deployed. When you deployed the database project, you also deployed the SQLCLR assembly. The SQLCLR assembly did not have to be deployed separately, which is why it was skipped when you deployed the solution.

Next Steps

You might next create unit tests and a data generation plan to test the assembly. For more information, see Configuring Database Projects and Performing a Test Deployment.

Share via