Creating a Stored Procedure without Visual Studio

 

Creating a Stored Procedure without Visual Studio

Let's start by creating a small stored procedure using Notepad. This approach exposes everything that needs to be done to get the stored procedure to operate. Then we'll look at what Microsoft Visual Studio .NET brings to the table to simplify the process. These examples run on SQL Server with the Northwind sample database attached. They also run on all other versions of SQL Server 2005 if you change your connection string appropriately.

Enabling the SQLCLR

To minimize resource usage and maximize security, the SQLCLR is disabled by default. To use the SQLCLR, you must first enable this server-wide setting, as shown in the following SQL script.

Note 

All of the following SQL scripts can be executed from a New Query window. Although I prefer to use the Microsoft SQL Server Management Studio, you can use the Server Explorer in Visual Studio to open a New Query window by right-clicking your connection and clicking New Query. When using the Server Explorer, you can deselect the Show Diagram Pane and the Show Criteria Pane, and you are left with a window that has the SQL Pane and the Result Pane. With the Server Explorer, you cannot use the GO statement to separate batches, so you must execute the commands that are between each GO separately, without using GO.

image from book

SQL: Enabling the SQLCLR

EXEC sp_configure 'clr enabled', 1 RECONFIGURE WITH OVERRIDE GO 
image from book

To disable the SQLCLR, change the 1 to a 0 and execute the script. To view the status of the SQLCLR, simply execute the following SQL script.

image from book

SQL: Retrieving the SQLCLR State

EXEC sp_configure 'clr enabled' 
image from book

This returns the minimum, maximum, configured, and currently running values for this setting.

You might also want to get other information about the SQLCLR, such as the version number. You do this by executing the following SQL script.

image from book

SQL: Retrieving SQLCLR Properties

SELECT * FROM sys.dm_clr_properties 
image from book

Creating the Source Code

If you want to create a stored procedure in Visual Basic or C#, the class with the method that will become a stored procedure must be public. The method must be public as well. Also, the method must be static (shared in Visual Basic) because the SQLCLR makes no attempt to create an instance of the class.

Namespaces are allowed in your code but not required. I've seen many code samples that don't use namespaces, and although I question the value of using a namespace in this scenario, I have included one so you can see how it affects the registration of the stored procedure later.

The following code can be typed into Notepad or any other text editor and saved to a file called HiVbWorld.vb for Visual Basic or HiCsWorld.cs for C#.

image from book

Visual Basic: HiVbWorld.vb

imports Microsoft.SqlServer.Server namespace VbTestNamespace    public class VbTestClass       public shared sub SayHi( )          SqlContext.Pipe.Send("Hi VB World from SQL Server!")       end sub    end class end namespace 
image from book

image from book

C#: HiCsWorld.cs

using Microsoft.SqlServer.Server; namespace CsTestNamespace {    public class CsTestClass    {       public static void SayHi()       {          SqlContext.Pipe.Send("Hi C# World from SQL Server!");       }    } } 
image from book

Using the Context Object

Notice the use of SqlContext, which is commonly called the "context" object. This object gives you access to the environment where the SQLCLR has been activated. It also enables access to runtime information based on the type of SQLCLR object that is being executed. The SqlContext class is in the Microsoft.SqlServer.Server namespace, and the class is defined in the System.Data.dll file. You normally need to add a reference to the System.Data.dll file, but the Visual Basic and C# compilers add the reference for you when they find the SqlContext class in your code.

The Pipe property lets you send data back to the client. In SQL, the PRINT statement is the equivalent of the Pipe.Send method. In your application, you can access the sent data by retrieving data from the SqlConnection object's InfoMessages collection.

Compiling the Code

Open the Visual Studio 2005 Command Prompt. Next, change your default directory to the directory that contains your source file and compile the file by executing the following command.

image from book

Visual Basic

vbc /t:library HiVbWorld.vb 
image from book

image from book

C#

csc /t:library HiCsWorld.cs 
image from book

Nothing fancy happens here. The output of a compile is an assembly, and this command simply created an assembly that consists of either the HiVbWorld.dll or HiCsWorld.dll file.

Loading the Assembly

The next step is to install the assembly into SQL Server. Installing the assembly gives the assembly a logical name that is usable in SQL Server and sets the permissions. Note that the assembly installs into the database that you are currently connected to. If the assembly is installed and the database is backed up, the assembly is included in the backup. When the database is restored, the assembly is restored as well, even if you deleted the file that you used to install the assembly. The following SQL script installs the assembly into SQL Server. It can be executed in a query window, or you can use the SqlCmd.exe command-line utility. Be sure to verify the path to the assembly.

image from book

SQL: Install the Visual Basic Assembly

CREATE ASSEMBLY VbProcs FROM 'C:\Projects\Vb\Chapter09\HiVbWorld.dll' WITH PERMISSION_SET=SAFE GO 
image from book

image from book

SQL: Install the C# Assembly

CREATE ASSEMBLY CsProcs FROM 'C:\Projects\CS\Chapter09\HiCsWorld.dll' WITH PERMISSION_SET=SAFE GO 
image from book

The permission can be set at the time that the assembly is registered. By default, the assembly is installed with the permission set to SAFE. The SQLCLR has three levels of execution permissions that are enforced at the assembly level:

Changing the Execution Permission

Before you can set the execution permission to either EXTERNAL_ACCESS or UNSAFE, you must have permission to change this access permission, and the database must be configured as TRUSTWORTHY. If these items aren't addressed, you receive the following exception when you attempt to change the execution permission.

CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName'is no t authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when eith er of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permi ssion and the database has the TRUSTWORTHY database property on2.; or the assembly is sign ed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. 

You can correct this exception by executing the following SQL script to set the database TRUSTWORTHY property.

image from book

SQL: Enabling the Database as Trustworthy

USE master ALTER DATABASE Northwind SET TRUSTWORTHY ON GO 
image from book

This is all that you need to do if you are the SQL Server administrator, but if you aren't, you need to get permission to set the execution permission. This can be granted using the appropriate SQL script that follows for external access or unsafe execution permission.

image from book

SQL: Granting External Access Permissions

USE master GRANT EXTERNAL ACCESS ASSEMBLY TO [loginName] GO 
image from book

image from book

SQL: Granting Unsafe Permissions

USE master GRANT UNSAFE ASSEMBLY TO [loginName] GO 
image from book

Registering the Stored Procedure

Your method is not available as a stored procedure until you register it. You do this using the following command.

image from book

SQL: Register the Visual Basic Stored Procedure

CREATE PROCEDURE SayVbHi AS EXTERNAL NAME VbProcs.[VbTestNamespace.VbTestClass].SayHi GO 
image from book

image from book

SQL: Register the C# Stored Procedure

CREATE PROCEDURE SayCsHi AS EXTERNAL NAME CsProcs.[CsTestNamespace.CsTestClass].SayHi GO 
image from book

Notice that the external name is in assembly.class.method format. The class has been defined within a namespace, so the namespace must also be included, but including the namespace means additional dots in the format, and SQL Server gets confused. The use of the square brackets around the namespace.class tells SQL Server that the middle section represents the full path to the class.

Executing the Stored Procedure

The stored procedure can be executed like any other stored procedure from within a query window, from the SqlCmd.exe console utility, or from your application. Figure 9-1 shows the query window output after the assemblies have been installed in the Northwind database and the stored procedures have been executed.

image from book
Figure 9-1: Executing the stored procedures in a query window from within SQL Management Studio displays the output.

Refreshing the Assembly

If you need to make changes to your source code and recompile your assembly, you must refresh the SQL Server installation as follows.

image from book

SQL: Refresh the Visual Basic Assembly

ALTER ASSEMBLY VbProcs FROM 'C:\Projects\Vb\Chapter09\HiVbWorld.dll' GO 
image from book

image from book

SQL: Refresh the C# Assembly

ALTER ASSEMBLY CsProcs FROM 'C:\Projects\CS\Chapter09\HiCsWorld.dll' GO 
image from book

Note that if you execute this statement without first recompiling your assembly, you get the message that states that the command failed because the source has the same MVID (module ID) as the assembly that is currently registered.

Using the ALTER statement means you don't lose the current security setting.

Viewing Installed Assemblies and their Permissions

You can view the loaded assemblies and their permissions by executing the following SQL script. Remember that this script lists the assemblies that are loaded into the current database.

image from book

SQL: List Assemblies in Current Database

SELECT * FROM sys.assemblies 
image from book

Using Parameters to Transfer Data

The previous sample application shows the steps required for getting your code to operate in the SQLCLR, but when data is transferred between SQL Server and the client, it's typically done using parameter and rowsets. This section explores the use of parameters to pass data; the subsequent section examines rowset-based data movement.

To use parameters, you simply add a method to your assembly that accepts parameters and/ or returns a value. The return value can be either SqlInt32 System.Int32 or void. If you attempt to return a string, for example, you can compile and load the assembly, but when you attempt to register the stored procedure, an exception is thrown. If you need to return a string value to the client, you can pass parameters as reference parameters using the ref (C#) or ByRef (Visual Basic) keywords, or you can pass parameters as output parameters using the out (C# only) keyword. For example, we'll add the following method to the previous assembly.

image from book

Visual Basic

public shared Sub GetGreeting(name as string, _       ByRef greeting as string)    greeting = "Hello from VB, " + name End Sub 
image from book

image from book

C#

public static void GetCsGreeting(string name,       ref string greeting) {    greeting = "Hello from C#, " + name; } 
image from book

After saving the source code, recompile and refresh the assembly. Registering the stored procedure is a bit different because the parameters must be specified when the stored procedure is registered. You don't need to identify the return value as a parameter if you are planning to use the RETURN statement in your stored procedure, however. The following SQL snippet shows how the parameters are defined in SQL Server.

image from book

SQL: Registering the Visual Basic Stored Procedure with Parameters

 CREATE PROCEDURE GetVbGreeting  @name nvarchar(50),  @greeting nvarchar(100) OUTPUT AS EXTERNAL NAME VbProcs.[VbTestNamespace.VbTestClass].GetGreeting 
image from book

image from book

SQL: Registering the C# Stored Procedure with Parameters

CREATE PROCEDURE GetCsGreeting  @name nvarchar(50),  @greeting nvarchar(100) OUTPUT AS EXTERNAL NAME CsProcs.[CsTestNamespace.CsTestClass].GetGreeting 
image from book

After registering the stored procedure, you can execute it using the following SQL snippet.

image from book

SQL: Executing the Visual Basic Stored Procedure

DECLARE @result nvarchar(100) EXEC GetVbGreeting 'Glenn', @result OUTPUT PRINT @result 
image from book

image from book

SQL: Executing the C# Stored Procedure

DECLARE @result nvarchar(100) EXEC GetCsGreeting 'Glenn', @result OUTPUT PRINT @result 
image from book

The output is displayed as shown in Figure 9-2. Notice that an empty variable called @result is created to hold the greeting. After the store procedure is run, @result is printed and the greeting is displayed.

image from book
Figure 9-2: The output window when you use parameters to pass data between SQL Server and the client

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

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