Walkthrough: Update a Database That References SQLCLR Objects

Visual Studio 2010

You can deploy updates to a database that refers to objects from a SQL common language runtime (CLR) assembly without updating or deploying the assembly itself. For example, you might have a database such as the one described in Walkthrough: Create and Deploy a Database Project That Uses a SQL Server CLR Object.

In this walkthrough, you will follow these steps:

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 permissions to deploy a database to your database server. You must have completed Walkthrough: Create and Deploy a Database Project That Uses a SQL Server CLR Object. As part of that walkthrough, you created and deployed the SQLCLR assembly and database that you will import in this walkthrough.

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, expand the SQL Server node, and click Advanced.

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

  4. In Name, type ImportedSQLCLRSandbox.

    Important noteImportant

    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 import the database schema and referenced SQLCLR assembly.

To import the database schema

  1. In Solution Explorer, expand the ImportedSQLCLRSandbox project node, and expand the References node.

    The project contains one database reference to Microsoft.SqlTypes.dbschema.

  2. Right-click the ImportedSQLCLRSandbox project node, and click Import Database Objects and Settings.

    The Import Database Wizard dialog box appears.

  3. In Source database connection, click the connection that corresponds to the SQLClrDatabaseSandbox database that you created in Walkthrough: Create and Deploy a Database Project That Uses a SQL Server CLR Object.

    If the connection does not appear in the list, click New Connection to specify a connection to that database.

  4. Accept the default values for the other settings, and click Start.

    The database schema and the referenced SQLCLR assembly are imported into your database project.

  5. When the schema has been imported, click Finish.

  6. In Solution Explorer, open the References folder, and click SqlClassLibrary.

  7. On the View menu, click Properties Window.

    The value of the Assembly Name property is SupportingSQLClrObjects, and the value of the Assembly Owner property is dbo.

    Next, you will add a table to the database project. The table will use the user-defined type that is defined in the SQLCLR assembly.

To add a table to your database project

  1. On the View menu, click Database Schema View.

  2. Expand the ImportedSQLCLRSandbox node, expand the Schemas node, expand the dbo node, and expand the Tables node.

  3. Right-click the Tables node, point to Add, and click Table.

    The Add New Item dialog box opens.

  4. In Name, type SecondTable, and click Add.

    The new table appears in Schema View, and the Transact-SQL editor opens, displaying the definition for the table.

  5. In the Transact-SQL editor, modify the table definition to match the following:

    CREATE TABLE [dbo].[SecondTable]
        [ID] INT NOT NULL,
        [Name] NVARCHAR(30) NOT NULL,
        [Type] [dbo].[MyCustomType] NULL
  6. On the File menu, click Save SecondTable.table.sql.

    Next you will configure, build, and deploy your project.

To configure build settings

  1. In Solution Explorer, click ImportedSQLCLRSandbox.

  2. On the Project menu, click ImportedSQLCLRSandbox 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 same server and database from which you imported database objects and settings earlier in this walkthrough, 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. As an alternative, you could 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 builds successfully.

  2. On the Build menu, click Deploy Solution.

    The database project is deployed to the target server and database that you specified in the deployment settings. Because you are referring to an existing SQLCLR assembly, the assembly is not deployed with the database project.

    In the Output window, the following message appears:

------ Deploy started: Project: ImportedSQLCLRSandbox, Configuration: Debug Any CPU ------
    Deployment script generated to:

    Creating dbo.SecondTable...
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

The database project is successfully deployed.

To add your project to version control

  1. In Solution Explorer, click the ImportedSQLCLRSandbox 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.


    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 ImportedSQLCLRSandbox 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 Comment, type Initial database project creation and schema import.

  8. On the Source Control - Team Foundation toolbar, click Check In.

    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.

At this point, you might create or update database unit tests to verify new or changed behavior of your database. You might also create or update a data generation plan to provide test data. For more information, see Configuring Database Projects and Performing a Test Deployment.

Community Additions