Section 4.5. Hello World Example

4.5. Hello World Example

This section shows how to create, configure, and use a SQL Server CLR routine by way of a sample CLR stored procedure that returns the text message "Hello world." This example is followed by an example that shows how to create the same .NET Framework assembly using a command-line compiler.

Follow these steps in Visual Studio 2005 to create the .NET Framework assembly containing the CLR stored procedure:

1. Select File New Project.

3. Because the stored procedure will not be accessing any data, click Cancel in the Add Database Reference dialog box, shown in Figure 4-2.

   Figure 4-2. Add Database Reference dialog box

   

4. In Solution Explorer, right-click the HelloWorld project and select Add Stored Procedure from the context menu, as shown in Figure 4-3.

   Figure 4-3. Add Stored Procedure menu item

   

5. In the Add New Item dialog box, shown in Figure 4-4, select the Stored Procedure template. Enter the name HelloWorldStoredProcedure.cs and click Add.

6. Add the following line of code to the HelloWorldStoredProcedure( ) method in HelloWorldStoredProcedure.cs:

        SqlContext.Pipe.Send("Hello world.\n"); 

   
   

   The complete code should now be as follows:

        using System;     using System.Data;     using System.Data.Sql;     using System.Data.SqlTypes;     using Microsoft.SqlServer.Server;     public partial class StoredProcedures     {         [SqlProcedure]         public static void HelloWorldStoredProcedure(  )         {             SqlContext.Pipe.Send("Hello world.\n");         }     }; 

   
   

   Figure 4-4. Add New Item dialog box

   

7. Build the solution by selecting Build Build Solution from the Visual Studio 2005 main menu, by clicking the Build Solution button on the Build toolbar, or by right-clicking the HelloWorld project in Solution Explorer and selecting Build from the context menu. The stored procedure is compiled into an assembly called HelloWorld.dll in the bin\Debug subdirectory.

Once the stored procedure is compiled, you need to register the assembly with SQL Server before you can access the CLR stored procedure. This walkthrough and many of the examples in this book use a database called ProgrammingSqlServer2005. Follow these steps to register the assembly with SQL Server:

1. Right-click the ProgrammingSqlServer2005 database in Object Explorer and select New Query from the context menu, as shown in Figure 4-5.

2. Register the assembly HelloWorld.dll with the SQL Server assembly name HelloWorld by executing the following T-SQL statement:

        USE ProgrammingSqlServer2005     GO     CREATE ASSEMBLY HelloWorld     FROM 'C:\PSS2005\HelloWorld\HelloWorld\bin\Debug\HelloWorld.dll' 

   
   

   Figure 4-5. New Query menu item

   

   The SQL Server assembly name and the .NET Framework assembly DLL name do not have to be the same. The SQL Server assembly name must be unique in the database.

   You can confirm that the assembly is registered by expanding the Databases ProgrammingSqlServer2005 Programmability Assemblies node in the Object Explorer tree view, as shown in Figure 4-6.

   Figure 4-6. Object Explorer Assemblies node

   

3. Create a CLR stored procedure called HelloWorldSP based on the HelloWorld StoredProcedure( ) static method in the HelloWorld.dll assembly registered in Step 2. Execute the following query:

     CREATE PROCEDURE HelloWorldSP     AS     EXTERNAL NAME HelloWorld.StoredProcedures.HelloWorldStoredProcedure 

The EXTERNAL NAME clause has three parts, separated by periods:

• The SQL Server registered assembly name (from Step 2)HelloWorld

• The class name in the .NET Framework assemblyStoredProcedures

• The name of the public static method implementing the stored procedureHelloWorldStoredProcedure( )

You can confirm that the stored procedure was created by expanding the Databases ProgrammingSqlServer2005 Stored Procedure node in the Object Explorer tree view, as shown in Figure 4-7.

Figure 4-7. Object Explorer Stored Procedures node

You can now use the stored procedure just as you would use any other stored procedure.

Execute the HelloWorldSP stored procedure with the following T-SQL statement:

     EXEC HelloWorldSP 

The results follow:

     Hello world. 

The results are exactly the same as they would be if you had created and executed the following T-SQL stored procedure:

     CREATE PROCEDURE HelloWorldSP2     AS         PRINT 'Hello world.' 

Once you have finished with the sample, you can remove the CLR stored procedure and registered .NET Framework assembly by executing the following statements:

     DROP PROCEDURE HelloWorldSP     DROP ASSEMBLY HelloWorld 

4.5.1. Command-Line Compiler

While the examples in this book use Visual Studio 2005, you can create the program files using any text editor and compile them using a .NET command-line compiler. SQL Server 2005 installs .NET Framework redistribution files, including command-line language compilersfor example csc.exe for C# and vbc.exe for VB.NET. The command-line compilers are installed in the directory C:\<windir>\Microsoft.NET\Framework\<version>, where:

<windir>

The directory in which your version of Windows is installedoften WINDOWS or WINNT

<version>

The .NET Framework version

To use the compiler, add the directory containing the compiler to your Path environment system variable defined in the System variables list box accessed through Control Panel System Advanced Environment Variables.

To use the command-line C# compiler to compile the HelloWorldStoredProcedure.cs file created in the previous section, execute the following command:

     csc /target:library /out:HelloWorld.dll HelloWorldStoredProcedure.cs 

The /target compiler flag instructs the compiler to build a DLL. The /out flag instructs the compiler to override the default DLL name HelloWorldStoredProcedure.dll with the name HelloWorld.dll. For more information about Visual Studio .NET compilers and compiler flags, consult the Microsoft Developer Network (MSDN).

Once you have compiled the .NET Framework assembly, you register it and CLR routines in the same way as if you had used the Visual Studio 2005 compiler.