Creating SQL Server Objects in Managed Code

  • Article

Typically, when you define database objects, you use the Transact-SQL programming language. However, you can also use .NET Framework languages to define database objects, such as stored procedures and triggers, and to retrieve and update data for SQL Server databases. These objects are often referred to as SQL Server Common Language Run-time objects, or SQL CLR objects.

To create a database object, you create a SQL Server project, add the required items to the project, and add code to those items. You then build the project into an assembly and deploy it to the SQL Server.

Note

By default, the common language runtime (CLR) integration feature is turned off in SQL Server. To use SQL CLR project items, you must enable CLR integration. To do this, use the clr enabled option of the sp_configure stored procedure. For more information, see the clr enabled Option topic on the Microsoft Web site.

Building, Deploying, and Debugging

When you build your project, it is compiled into an assembly that can be deployed to the database that is referenced in the project and debugged.

Note

As an alternative to building, deploying, and debugging in three separate steps, you can use the Start command (F5) or the Start Without Debugging command (CTRL+F5) to build the assembly, deploy it to the database, and debug the database object. Your SQL CLR database project must be set as your startup project if you want to use F5 or CTRL+F5. For more information, see How to: Set Startup Projects.

You can optionally add Transact-SQL statements to the PreDeployScript.sql and PostDeployScript.sql. You might choose to do so if you need to perform actions on the database before or after you deploy the SQL CLR database project.

Before you can debug the database object, you must add Transact-SQL code to the Test.sql script or you can add the same Transact-SQL code to an instance of the Transact-SQL editor that is connected to the database to which you deployed the project. The script is important for debugging because it performs the actions in the database that are required to start and test the database object. If your SQL CLR database project contains more than one database object, the script should include Transact-SQL code to exercise each database object that you want to debug.

This debug script requirement differs from a project that creates and runs an independent executable program, such as a Windows Forms project. This is because database objects run only in response to actions or calls in the database. For example, a trigger might be activated when a new row is inserted into a table. Therefore, the script must insert a new row into a table to activate the trigger. The results of the trigger are displayed in the Output Window in Visual Studio so that you can determine whether the trigger is working correctly.

Important

You must have appropriate permissions to deploy or debug a SQL CLR assembly. For more information, see Required Permissions for Database Features of Visual Studio.

You can find sample code for each database object type in the topic about that type.

Debugging the Database Object

When you debug a database object, the assembly is built, deployed to the database, and debugged. When you debug a database object that has been built and deployed previously, the assembly is built again only if the project has changed since it was built previously. The assembly is always deleted from the database and copied again. For more information, see Debugging Database Objects That Use Managed Code (SQL Server CLR Integration Debugging).

To enable remote debugging

  1. In Solution Explorer, right-click the SQL CLR project for which you want to enable remote debugging and click Properties.

  2. Click the Deploy tab.

  3. Select Deploy Code.

  4. On the File menu, click Save Selected Items.

To debug the database object

  1. In Solution Explorer, click the solution.

  2. On the Debug menu, click Start.

  3. In the Output window, in the Show output from list, select Database Output to view the results.

Common Tasks

Common Tasks

Supporting Content

Learn more about SQL CLR database projects: Read about advantages of developing.NET Framework database objects for SQL Server by using managed code. Managed code has several advantages compared with Transact-SQL.

Get hands-on practice: Follow the walkthrough to become familiar with how to create, deploy, and test a project that defines a stored procedure by using managed code.

Create a SQL CLR database project: Create a project in Visual Basic or Visual C# that will contain the definitions for the database objects that you want to define.

Add items to the SQL CLR database project: When you first create a SQL Server project, it contains only references and assembly information. To create database objects, you must add items to the project and then add code to the items. You can add stored procedures, aggregates, triggers, user-defined functions, and user-defined types. An attribute must be applied to each SQL Server project and each object that it contains.

You can define multiple objects in a project. You might choose to use multiple projects if the objects need to be updated independently or if not all objects are used in every database to which you will deploy the resulting assembly.

Deploy the assembly to a SQL Server and test it: After you create the project, add items, and add code to those items, you must deploy the resulting assembly to a SQL Server before you can use the objects that you have defined. If your SQL Server project contains a reference to one or more objects in another SQL Server project, you cannot use the Deploy command on the first project to deploy the second (referenced) project. After you deploy the assembly, you can perform some simple tests by running a test script.

Debug deployed .NET Framework database objects: If your database object is not functioning as intended, you can debug that object to identify and correct any code defects.

Reference

  • System.Data
    Describes the System.Data namespace and provides a table of relevant classes.

  • System.Data.Sql
    Describes the System.Data.Sql namespace and provides a table of relevant classes.

  • System.Data.SqlTypes
    The System.Data.SqlTypes namespace provides classes for native data types within SQL Server 2005.

  • Microsoft.SqlServer.Server
    Describes the Microsoft.SqlServer.Server namespace and provides a table of relevant classes.

  • Developing databases in a team environment that reference.NET Framework database objects: You can use additional features of Visual Studio to reduce risks and help speed the development of database applications in a team environment. You can define your database schema in a database project, put your projects under version control, and create database unit tests to verify expected behavior.