Walkthrough: Create and Deploy a Database Project That Uses a SQL Server CLR Object

  • Article

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

In this walkthrough, you will perform the following tasks:

  • Create a simple database project.

  • Create a SQL Server CLR project and define a new data type.

  • Use the type that you defined in the SQL Server CLR assembly.

  • Build and deploy the database project.

Prerequisites

To complete this walkthrough, you must have installed Visual Studio 2010 Premium. 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

  1. On the File menu, choose New, Project.

    The New Project dialog box appears.

  2. Under Installed Templates, expand the Database node, and choose the SQL Server node.

    Note

    If you are using Visual Studio 2010 Professional, you must also expand the Advanced node before you choose SQL Server.

  3. In the list of templates, choose SQL Server 2008 Database Project.

  4. In Name, enter SQLClrDatabaseSandbox.

    Important

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

  5. Select the Create directory for solution check box if it is not already selected.

  6. Clear the Add to Source Control check box if it is not already cleared, and choose the OK button.

    The empty database project appears in Solution Explorer.

    Next, you create a project for the SQL Server CLR assembly.

Create a SQL Server CLR Project

To create a SQL Server CLR project

  1. On the File menu, choose Add, Project.

    The New Project dialog box appears.

  2. In Project types, expand the Database node, and choose SQL Server.

  3. In the Templates list, choose either Visual Basic SQL CLR Database Project Server Project or Visual C# SQL CLR Database Project.

  4. Choose .NET Framework 3.5 in the list at the top of the dialog box.

    Important

    If you choose the default value, .NET Framework 4.0, and deploy to SQL Server 2008or SQL Server 2008 R2, deployment will fail.

  5. In Name, enter SupportingSQLClrObjects.

  6. Choose the OK button to accept the default location, close the dialog box, and create the project.

    The empty database project appears in Solution Explorer.

  7. In the Add Database Reference dialog box, choose Cancel.

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

    Next, you create a custom data type in the SQL Server CLR assembly.

To define a custom SQL Server CLR data type

  1. On the Project menu, choose Add User-Defined Type.

    The Add New Item dialog box appears.

  2. In Name, enter 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 SQL Server CLR objects, see this topic on the Microsoft web site: Using CLR Integration in SQL Server 2005.

  3. On the File menu, choose Save MyCustomType.cs.

To configure and build the SQL CLR assembly

  1. On the Build menu, choose Configuration Manager.

    The Configuration Manager dialog box appears.

  2. 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.

  3. Choose the Close button.

  4. In Solution Explorer, open the shortcut menu for the SupportingSQLClrObjects project, and choose Build.

    The assembly builds without any errors.

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

Use the Type Defined in the SQL Server CLR Assembly

To use the type that you defined in the SQL Server CLR 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 SQL Server CLR assembly

  1. In Solution Explorer, expand the SQLClrDatabaseSandbox project, open the shortcut menu for the References node, and choose Add Reference.

    Note

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

    The Add Reference dialog box opens.

  2. On the Projects tab, verify that the SupportingSQLClrObjects project is highlighted, and choose the OK button.

    A reference to the SQL Server CLR project is added to your database project.

  3. In Solution Explorer, expand the References node, choose the SupportingSQLClrObjects node, and choose F4.

    The properties for the reference appear.

  4. Verify that Copy Local is set to True and that Permission Level is set to Safe.

  5. In the Assembly Name property, replace SqlClassLibrary with SupportingSQLClrObjects.

  6. In the Owner property, enter dbo.

    You could specify a different owner.

  7. On the File menu, choose 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 SQL Server CLR project produces.

To define the new data type

  1. On the View menu, choose Error List, Database Schema View.

  2. Expand the SQLClrDatabaseSandbox node, expand the Schemas node, expand the dbo node, and expand the Programmability node.

  3. Open the shortcut menu for the Types node and choose Add, User-defined Type CLR.

    The Add New Item dialog box appears.

  4. In Templates, verify that User-defined Type (CLR) is highlighted.

  5. In Name, enter MyCustomType, and choose Add.

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

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

  1. In the Transact-SQL editor, update the code to match the following:

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

  2. On the File menu, choose Save MyCustomType.udtclr.sql.

    In the Error List window, the error disappears 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

  1. In Schema View, open the Schemas node, and open the dbo node.

  2. Open the shortcut menu for the Tables node and choose Add, Table.

    The Add New Item dialog box appears.

  3. In Templates, verify that Table is highlighted.

  4. In Name, enter SimpleTable, and choose Add.

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

  5. In the Transact-SQL editor, update the code to match the following:

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

  6. On the File menu, choose Save dbo.SimpleTable.table.sql.

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

Build and Deploy the Database Project

To configure build settings

  1. In Solution Explorer, choose SQLClrDatabaseSandbox.

  2. On the Project menu, choose SQLClrDatabaseSandbox Properties.

  3. Choose the Build tab, and review the default settings.

    In this walkthrough, you can use the default settings.

    Next, you configure deployment settings.

To configure deployment settings

  1. Choose the Deploy tab.

  2. In the Deploy Action list, choose Create a deployment script (.sql) and deploy to database.

  3. In the Configure deployment settings for list, choose 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.

  4. In Deployment script name, accept the default value.

  5. In Target Database Settings, choose Edit to specify a target connection.

  6. In the Connection Properties dialog box, specify a connection to the server and database where you want to deploy the database project, and then choose the OK button.

  7. In Target database name, accept the default value.

  8. In the Deployment configuration file list, choose 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.

  9. In the SQL command variables file list, choose Properties\Database.sqlcmdvars.

  10. On the File menu, choose Save All.

    Next, you build the database project.

To build and deploy the database project

  1. On the Build menu, choose Build Solution.

    The database project and the SQL Server CLR assembly project build successfully.

  2. On the Build menu, choose Deploy Solution.

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

    ========== Deploy: 1 succeeded, 0 failed, 1 skipped ==========.

    The database project was successfully deployed. When you deployed the database project, you also deployed the SQL Server CLR assembly. The SQL Server CLR assembly did not have to be deployed separately, which is why it was skipped when you deployed the solution.

Make the Project Available to the Team (optional)

To add your project to version control

  1. In Solution Explorer, choose the SQLClrDatabaseSandbox node.

  2. On the File menu, choose Source Control, Add Solution to Source Control.

    At this point, you interact with the installed version control software. This walkthrough provides steps to add your project to Visual Studio Team Foundation Server. If you want to use different version control software, substitute the equivalent steps. If you are using Team Foundation Server, the Connect to Team Foundation Server dialog box appears.

  3. In Connect to a Team Foundation Server, choose the server that hosts the team project to which you want to add your solution.

    Note

    If you do not have a team project to which you can add the database project, see Planning and Tracking Projects.

  4. In Team projects, choose the team project to which you want to add the database project, and choose the OK button.

    The Add Solution SQLClrDatabaseSandbox to Source Control dialog box appears.

  5. Choose the OK button to accept the default values.

    Your database project and the files that it contains are put under version control. Initially, they are still checked out. You must check them in before other team members can access them.

  6. On the View menu, choose Other Windows, Pending Changes.

    The Pending Changes window appears.

  7. In the Comment field, enter Initial database project creation.

  8. In the Pending Changes window, choose Check In on the toolbar.

    The Check In Progress dialog box appears as the database project and the files that it contains are checked in. In Solution Explorer, the icons change to show that the files are checked in to version control.

Next Steps

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

See Also

Concepts

Starting Team Development of Databases

Starting Team Development of Databases that Reference Other Databases

Starting Team Development of Databases that Reference SQLCLR Objects

Starting Team Development of Databases that Reference Shared Server Objects

Required Permissions for Database Features of Visual Studio

Change History

Date

History

Reason

August 2011

Specify .NET Framework version requirements.

Customer feedback.