Creating CLR database objects

Get an overview of the process to manually create a CLR database object. Then go further and learn how to use the Visual Studio 2005 Professional, Enterprise, and Team System Editions, which all have tools that help create CLR database objects as well as deploy and debug them.

This Content Component encountered an error

After the SQL Server assembly is created, you can then use SQL Server Management Studio to execute a T-SQL CREATE PROCEDURE, CREATE TRIGGER, CREATE FUNCTION, CREATE TYPE, or CREATE AGGREGATE statement that uses the EXTERNAL NAME clause to point to the assembly that you created earlier.

When the assembly is created, the DLL is copied into the target SQL Server database and the assembly is registered. The following code illustrates creating the MyCLRProc stored procedure that uses the MyCLRDLL assembly:

 

CREATE PROCEDURE MyCLRProc
AS EXTERNAL NAME
MyCLRDLL.StoredProcedures.MyCLRProc

The EXTERNAL NAME clause is new to SQL Server 2005. Here the EXTERNAL NAME clause specifies that the stored procedure MyCLRProc will be created using a .SQL Server assembly. The DLL that is encapsulated in the SQL Server assembly can contain multiple classes and methods; the EXTERNAL NAME statement uses the following syntax to identify the correct class and method to use from the assembly:

 

Assembly Name.ClassName.MethodName

In the case of the preceding example, the registered assembly is named MyCLRDLL. The class within the assembly is StoredProcedures, and the method within that class that will be executed is MyCLRProc.

Specific examples showing how you actually go about creating a new managed code project with Visual Studio 2005 are presented in the next section.

Creating CLR database objects

The preceding section presented an overview of the process along with some example manual CLR database object creation steps to help you better understand the creation and deployment process for CLR database objects. However, while it's possible to create CLR database objects manually, that's definitely not the most productive method. The Visual Studio 2005 Professional, Enterprise, and Team System Editions all have tools that help create CLR database objects as well as deploy and debug them. In the next part of this chapter you'll see how to create each of the new CLR database objects using Visual Studio 2005.

NOTE: The creation of SQL Server projects is supported in Visual Studio 2005 Professional Edition and higher. It is not present in Visual Studio Standard Edition or the earlier releases of Visual Studio.

The previous tip is from "Developing CLR database objects: 10 tips in 10 minutes," excerpted from Chapter 3 of the book "Microsoft SQL Server 2005: A Developer's Guide" written by Michael Otey and Denielle Otey, courtesy of McGraw-Hill Publishing. 

Developing CLR database objects

This was first published in May 2007

Dig deeper on Microsoft SQL Server 2005

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close