Building Your First CLR Executable


Let's get right down to it. Assuming you've decided that you have a routine that you think can be implemented more efficiently by calling a CLR executable, you're ready to step through the process of implementing it using Visual Studio. This example walk-through builds a pair of CLR functions: one to convert Fahrenheit temperature values to Celsius and another to convert back to Celsius from Fahrenheit. Sure, these routines can be built with T-SQL. The purpose of this exercise is to show you how much performance can be gained (or lost) by using a CLR function.

IMHO

Overheard in the hallway: "I'm not a DBA, but I play one at work...."


1.

Before you get started wizarding and coding, you'll need to ask permission from your DBA to enable CLR executables on the target SQL Server. If you're acting as the DBA, you need to ask yourself the important security questions all DBAs ask. I listed many of these earlier in this chapter. If you get permission, start the SQL Server Surface Area Configuration utility that ships with SQL Server 2005. You should see an initial dialog that looks like Figure 13.1.



Figure 13.1. Choose the target server with the SQL Server Surface Area Configuration tool.


The process of enabling CLR executables can also be done via SQL commands, as shown in Figure 13.2. Regardless of the technique you use, this setting applies to the server instancenot just to a specific database. Once it's enabled and your developers start implementing CLR executable functionality, you'll find it's tough to go back and unwind all of the functionality these executables expose.

Figure 13.2. Using a SQL Server Management Studio batch to enable CLR executables.


2.

Next, click on "Surface Area Configuration for Features". This populates a tree view for each SQL Server instance running on the target machine, as shown in Figure 13.3. Select "CLR Integration" and make sure the check box is selected. Click OK to continueit's off by default. If you're working with a team, it's important to leave this feature on once enabled, to permit other developers' CLR executables to continue working.

Figure 13.3. Enabling CLR integration on the target SQL Server instance.


3.

Next, fire up Visual Studio, start a new project, and choose your favorite language. Drill down into the "Database" project types. Nope, while one might think so, this template does not come from the list of "Business Intelligence" types. You should see one (and only one) project type here: "SQL Server Project", as shown in Figure 13.4.



Figure 13.4. Creating a new CLR executable project and solution.


Note that I set the project name to have a "prj" prefix and the solution name to have an "sln" prefix. This naming convention helps when it comes time to see how these are deployed to SQL Server. Click OK to continue.

4.

Next, Visual Studio exposes a dialog to permit you to choose a "Database Reference" from one of the registered Data Connections (as managed by the Server Explorer). Basically, all you're doing here is setting a ConnectionString to address the SQL Server instance and database that's intended to host the CLR executable. If the desired Data Connection is not in the list (as shown in Figure 13.5), you can click on "Add New Reference..." to create another Server Explorer-managed Data Connection.

Figure 13.5. Choose one of the exposed Data Connections.


It's this Database Reference (Data Connection) that's accessed when it comes time to deploy the stored procedure and to address the host (a specific SQL Server instance database) when debugging. It can be changed later (as when switching from testing to production) by changing the Connection String setting in the project properties (Database tab). I discuss this project property and others a bit later.

5.

If you haven't done so already, Visual Studio warns you that the specified connection must support SQL/CLR debugging if you want to use this feature. Yes, this is different than supporting the ability to host a CLR executable, as I discussed earlier. It's a good idea to click "Yes" on this dialog if you intend to debug your code.

While you're debugging your SQL Server CLR executable, all (not just your) CLR executables (managed threads) are stopped. This means if you're testing on a production server, the DBA might come and stand in your office eyeing your network cable (or your neck) with an axe.

6.

To add a CLR executable class template to your project (regardless of the type of executable you want to build), right-click the project in the Project Explorer and choose "Add", which exposes another dialog (shown in Figure 13.6). Pick any one of the CLR executable templatesit makes no difference at this pointyou're just going to be taken to another selection dialog where you can make your final choice.

Figure 13.6. Choosing a CLR executable template.


7.

Since I'm building a CLR Function to convert Fahrenheit to Celsius (and back), I choose "User-Defined Function...". This opens the "Add New Item" dialog, where you're prompted to choose one of the Visual Studio installed templates (including templates of your own) and name the new Function. Note that I named the Function generically (as shown in Figure 13.7). That is, this clsTempConversions.vb class is designed to contain several "conversion" functions whose names are determined by code within the class. Let's use a unique prefix here as well, so I can (again) track how this code is deployed to SQL Server.



Figure 13.7. Choose the appropriate class file Template and name.


8.

Each of the templates creates a unique block of code to initialize your CLR executable classspecially configured to correctly interface to SQL Server. Let's take a look at the generated (and unchanged) function template (as shown in Figure 13.8), as it's the easiest to understand.



Figure 13.8. The unmolested CLR Function template.


Note that the default CLR function template (you'll certainly want to create your own) expects no input parameters and returns a SqlString. Note the attribute <Microsoft.SqlServer.Server.SqlFunction>, which tells the compiler that you're building a CLR function. To save time, I'm going to go ahead and modify the generated code to implement the two functions I need for my temperature conversion class.

IMHO

Save often and exit Visual Studio occasionally to preserve your work.

9.

When you use the "function" template, the Public Class created is (always) given the name "UserDefinedFunctions". This class name is not exposed when invoking or addressing the functions in T-SQL, but it is referenced behind the scenes by Visual Studio and the other tools that deploy your application to the target server. No, you don't need to change it unless you plan to have more than one class that defines one or more User-Defined (CLR) Functions.

However, you do need to set the CLR function name and define the function's input parameters. The code shown in Figure 13.9 first defines the function "signature" and is referenced by the applications that call it. Once a function is deployed to the database and developers begin to use it, you won't be able to change this signature without causing more folks to come to your office carrying edged weapons. This is no different than any T-SQL function, stored procedure, or class definition, for that matter.

Figure 13.9. This code defines two CLR functions to convert temperatures.


Note that both of the publicly exposed functions (fnFahrenheitToCelsius and fnCelsiusToFahrenheit) are also prefixed with an XML attribute[5] that tells the compiler and deployment routines which functions in this class are to be made visible to SQL Server. Sure, the public class UserDefinedFunctions can have any number of other functions or subroutines, but only those properly designated will be exposed to SQL Server. As I step through the other types of CLR executables, you'll see how this attribute is used to define how the executable is accessed, serialized (if necessary), and interfaced.

[5] These compiler-directive "attributes" are called "decorations" in some references.

As with other classes I've written, this class includes a few constants that are visible throughout the class scope. When working with CLR functions, you won't be able to define class-global uninstantiated variables. This means you'll be able to declare Const (in Visual Basic .NET) variables but not other "New" instance variables that require code to run outside the context of the called CLR functions. That's because there's no "New" method for the functions that are called when they're first invoked. Sure, you can declare global variables; you'll just have to initialize them using code within one of the functions. One approach might be to have a "setup" or initialization function that must be called before the others, but that assumes that SQL Server maintains the state of these variables between invocations. It doesn't. What you can do, however, is have the function itself create an instance of a class and invoke it. You still won't be able to retain state between calls.




Hitchhiker's Guide to Visual Studio and SQL Server(c) Best Practice Architectures and Examples
Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)
ISBN: 0321243625
EAN: 2147483647
Year: 2006
Pages: 227

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net