Sdílet prostřednictvím

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

  • Článek

To create and deploy a database that uses a SQL Server Common Language Run-time (SQL CLR) assembly, you create a database project and a SQL 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 CLR project and define a new data type.

  • Use the type that you defined in the SQL 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, point to New, and click Project.

    The New Project dialog box appears.

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

    Poznámka

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

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

  4. In Name, type SQLClrDatabaseSandbox.

    Důležité

    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 click OK.

    The empty database project appears in Solution Explorer.

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

Create a SQL CLR Project

To create a SQL CLR project

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

    The New Project dialog box appears.

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

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

  4. In Name, type SupportingSQLClrObjects.

  5. Click OK to accept the default location, close the dialog box, and create the project.

    The empty database project appears in Solution Explorer.

  6. 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 SQL CLR assembly.

To define a custom SQL CLR data type

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

    The Add New Item dialog box appears.

  2. In Name, type MyCustomType.cs.

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

    Poznámka

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

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

To configure and build the SQL CLR assembly

  1. On the Build menu, click 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. Click Close.

  4. In Solution Explorer, right-click the SupportingSQLClrObjects project, and click 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 CLR 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 SQL CLR assembly

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

    Poznámka

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

    The Add Reference dialog box opens.

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

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

  3. In Solution Explorer, expand the References node, click the SupportingSQLClrObjects node, and press 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, type dbo.

    You could specify a different owner.

  7. 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 SQL CLR project produces.

To define the new data type

  1. On the View menu, click Error List and Database Schema View.

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

  3. Right-click the Types node, point to Add, and click 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, type MyCustomType, and click 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, click 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. Right-click the Tables node, point to Add, and click Table.

    The Add New Item dialog box appears.

  3. In Templates, verify that Table is highlighted.

  4. In Name, type SimpleTable, and click 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, click 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, click SQLClrDatabaseSandbox.

  2. On the Project menu, click SQLClrDatabaseSandbox Properties.

  3. Click 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. Click the Deploy tab.

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

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

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

  5. In Target Database Settings, click 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 click OK.

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

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

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

  10. On the File menu, click Save All.

    Next, you build the database project.

To build and deploy the database project

  1. On the Build menu, click Build Solution.

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

  2. On the Build menu, click Deploy Solution.

    The database project and the SQL 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 CLR assembly. The SQL 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, click the SQLClrDatabaseSandbox node.

  2. On the File menu, point to Source Control, and click 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, click the server that hosts the team project to which you want to add your solution.

    Poznámka

    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, click the team project to which you want to add the database project, and click OK.

    The Add Solution SQLClrDatabaseSandbox to Source Control dialog box appears.

  5. Click OK 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, point to Other Windows, and click Pending Changes.

    The Pending Changes window appears.

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

  8. In the Pending Changes window, click 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