Browse DevX
Sign up for e-mail newsletters from DevX


Writing Managed Stored Procedures in SQL Server 2005 : Page 2

Are you tired of struggling with T-SQL to encapsulate the logic your applications need to get data into and out of SQL Server? Now you can bypass SQL altogether, and write logic that runs directly within SQL Server in your favorite .NET language.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Compile the Class Into a .NET Assembly
Compile the completed class to produce a .NET assembly, which you can then register with SQL Server. Figure 1 shows how to compile the VB.NET class with the VB command line compiler to produce the .NET assembly.

Figure 1. Compiling the VB.NET Class: The figure shows a sample command line to compile the VB.NET AddressType class using the VB command line compiler
As shown in Figure 1, you need to reference the sqlaccess.dll because the code uses the classes contained in the System.Data.SqlServer namespaces. The code download for this article also contains the C# version of the complete code.

Register the Assembly with SQL Server
The deployment unit for managed code is called an assembly. An assembly is packaged as a DLL or executable (EXE) file. While an executable can run on its own, a DLL must be hosted in an existing application. SQL Server can load and host managed DLL assemblies. To load an assembly into SQL Server, you need to use the Create Assembly statement.

CREATE ASSEMBLY AddressType FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\' + 'MSSQL\Binn\Test\CLRProcedures\CSharp\AddressType.dll'

When you execute the above statement in the SQL Server Workbench, it will load the assembly into SQL Server. The FROM clause specifies the pathname of the assembly to load. This path can either be a UNC path or a physical file path that is local to the machine. The above statement will register the assembly with the SQL Server (the assembly name should be unique within a database). Running the Create Assembly statement causes SQL Server to load a copy of the assembly. Subsequently, if you want to change the assembly code, you need to first drop the assembly, then change the code, recompile the assembly, and finally, register the assembly with SQL Server again. To drop an assembly from SQL Server, you use the Drop Assembly statement. For example, to drop the assembly created earlier, use the following command.


Creating Stored Procedure Definitions
After loading the assembly into SQL Server, you need to associate a stored procedure to a specific method of the class contained in the assembly. You create a stored procedure using the Create Procedure statement. SQL Server 2005 supports a new clause named External Name that lets you reference a method in a registered assembly (an external method). Referencing an external method hooks the stored procedure to that method in the assembly.

CREATE PROCEDURE [dbo].[GetAddressTypeDetailsByID] @addressTypeID int AS EXTERNAL NAME [AddressType]:[AddressType]:: [GetAddressTypeDetailsByID]

The preceding code uses the GetAddressTypeDetailsByID method shown earlier in the AddressType class. The External Name clause uses the following syntax.

[Assembly Name]:[Class Name]::[Method Name]

After creating the stored procedure, you can test it using the following statement, passing an ID of 1 as the @addressTypeID parameter.

exec GetAddressTypeDetailsByID 1

Figure 2. Results of the Stored Procedure: The figure shows representative results from executing the GetAddressTypeDetailsByID stored procedure using SQL Server Workbench
Invoking Managed Stored Procedures from Windows Forms Clients
Here's a simple Windows Forms application to test the stored procedure created in the previous sections. Create a new project, add a command button and a data grid to the form. Name the command button btnGetByID, and name the data grid grdAddressType. Then modify the Click event of the command button to contain the following code.

Private Sub btnGetByID_Click( _ ByVal _ sender As System.Object, _ ByVal e As System.EventArgs) _ Handles _ btnGetByID.Click Dim connString as String = _ "server=localhost;uid=sa;" & _ "pwd=thiru;database=AdventureWorks;" Dim conn as New SqlConnection _(connString) Dim procName as String = _ _ "GetAddressTypeDetailsByID" Dim adapter as New _ SqlDataAdapter(procName,conn) adapter.SelectCommand.CommandType = _ __ CommandType.StoredProcedure Dim paramAddressTypeID as New _SqlParameter _ ("@addressTypeID", SqlDbType.Int) paramAddressTypeID.Direction = _ ParameterDirection.Input paramAddressTypeID.Value = 1 adapter.SelectCommand.Parameters. _Add( _ paramAddressTypeID) Dim addressTypeDataSet as New _DataSet() adapter.Fill(addressTypeDataSet) grdAddressType.DataSource _= _ addressTypeDataSet.Tables(0).DefaultView End Sub

Figure 3. Output of the Client Application: Executing the application results in the details of the AddressType being displayed in a DataGrid control
As you can see, the preceding code is straightforward—and identical to the way you invoke any other SQL Server stored procedure. The code declares a variable named connString and assigns the database connection string to it. Then it creates a new SqlConnection object, using the connection string as an argument to its constructor. Next it creates a SqlDataAdapter object and supplies the stored procedure name and the previously created SqlConnection object as its arguments. Because the stored procedure takes an addressTypeID as an argument, the code creates a SqlParameter object and then assigns appropriate values to its properties. Finally, it executes the stored procedure by invoking the Fill method of the SqlDataAdapter object. After getting the results in the form of a DataSet object, you then bind the results of the DataSet to the DataGrid control. If you run the application and click on the command button, you will get the output shown in Figure 3.

Thanks for your registration, follow us on our social networks to keep up-to-date