Compartilhar via

A Simple Walkthrough for deploying a SQLCLR Stored Procedure (by Brad)

  • Artigo

Walkthrough: Simple deployment of SQLCLR Visual Basic Stored Procedure

The Yukon release of SQL Server allows functions, procedures and triggers to be written in any of the .Net languages. User code can access data from the local or other SQL servers using the Sql Programming Model.

SQL Server Yukon includes a managed provider defined in System.Data.SqlServer that runs in the process space of the server and that users writing code in any of the .Net languages can use to access data.

This document describes a simple example of a Visual Basic SQL Project which contains a simple stored procedure using the data access managed provider that runs inside the server.

 

In order to complete this walkthrough, you will need:

  • Access to a SQL Server 2005 server with the AdventureWorks SQL Server sample database.
  • CREATE ASSEMBLY and CREATE FUNCTION permission on the server or an administrator login.

The walkthrough is split into a number of pieces:

  • Creating a Visual Basic SQL Project w/ a connection to the SQL Server 2005 database.
  • Add a Stored Procedure to the project.
  • Edit the code of the Stored Procedure to make use of the In-Proc Managed Provider
  • Deploy the function to the SQL Server 2005 database.
  • Execute the function from Visual Studio Server Explorer.

Creating the Project and Function

The first step is to create a VB SQL Server or Visual C# Project

 

To create the project and function

  1. If you already have a solution open, select Close Solution from the File menu.
  2. From the File menu, point to New, and then click Project.
  3. In the New Project dialog, choose Visual Basic or Visual C# Projects in the Project Types pane.
  4. In the Templates pane, choose the SQL Server Template. For Visual C++, choose SQL Server Class Library from the .Net node.
  5. Assign a unique name to the project, and click OK.

Visual Studio will create a new project and display a dialog to choose the SQL Server to which the Assembly will be deployed.

Note: If there hasn’t been a connection previously established to a SQL Server 2005 server, Visual Studio will first prompt the user w/ the Add Connection dialog.

In this dialog, the user will choose an existing connection to a SQL Server 2005 sever or create a new connection by selecting the Add Reference button.

  1. Select OK after highlighting the correct SQL Server connection

This completes the creation of the SQL Server project

Add a Stored Procedure to the project

Now that the project has been created, a stored procedure can be added to the project.

  1. Select Project from the Main Menu in the Visual Studio environment.
  2. From the Project menu, select Add Stored Procedure.
  3. The Add New Item dialog should now be visible. Choose the template Stored Procedure from the list of installed Visual Studio Templates.
  4. Type in a valid name for the procedure, and select Add.

The newly created code file will contain the following Visual Basic code.

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlServer

Imports System.Data.SqlTypes

Partial Public Class StoredProcedures

    <SqlProcedure()> _

    Public Shared Sub StoredProcedure1 ()

        ' Add your code here

    End Sub

End Class

The previous code snippet is the basic template from which a stored procdured can be created. Now, change the code in the stored procedure to the following:

    Public Shared Sub StoredProcedure1 ()

        Dim cmd As SqlCommand

        cmd = SqlContext.GetConnection.CreateCommand()

        cmd.CommandText = "UPDATE Person.Contact SET" & _

     "MiddleName = 'Jack' WHERE ContactID = 1"

        cmd.CommandType = CommandType.Text

        cmd.ExecuteNonQuery()

    End Sub

This example performs a simple update to the Contacts table in the AdventureWorks database. This is accomplished by creating a command object from the SqlContext.Connection object. In effect, what is happening is that the procedure is running in the “context” of the connection that it is being called from. The user is not required, nor allowed, to create a completely new connection to perform this operation.

Deploy the Stored Procedure to the SQL Server 2005 database

So far, the project with a connection to the SQL Server 2005 database is created, and the stored procedure is created. The project is now ready to be deployed to the server.

  1. Select the Build Menu from the Main menu in the Visual Studio environment.
  2. Now, select Deploy Solution from the Build menu.

The Build Output pain should contain information similar to the following indicating a successful deployment

------ Build started: Project: SqlServerProject1, Configuration: Debug Any CPU ------

------ Deploy started: Project: SqlServerProject1, Configuration: Debug Any CPU ------

Deploying file: SqlServerProject1.dll, Path: D:\school\SqlServerProject1\SqlServerProject1\obj\Debug\SqlServerProject1.dll ...

Deploying file: SqlServerProject1.pdb, Path: D:\school\SqlServerProject1\SqlServerProject1\obj\Debug\SqlServerProject1.pdb ...

Deploying file: StoredProcedure1.vb, Path: D:\school\SqlServerProject1\SqlServerProject1\StoredProcedure1.vb ...

Deploying file: My Project\MyResources.resx, Path: D:\school\SqlServerProject1\SqlServerProject1\My Project\MyResources.resx ...

Deploying file: SqlServerProject1.vbproj, Path: D:\school\SqlServerProject1\SqlServerProject1\SqlServerProject1.vbproj ...

========== Build: 1 succeeded, 0 failed, 0 up-to-date, 0 skipped ==========

========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

For another method of verification, the user may query the system tables to check for a successful deployment:

SELECT * FROM sys.assemblies

SELECT * FROM sys.assemby_modules

Execute the deployed Stored Procedure

Now that the procedure is deployed to the server, users can execute the procedure from Server Explorer to verify it is performing as desired.

  1. Select the connection to the SQL Server 2005 in the Server Explorer window in Visual Studio environment.  
  2. Expand the connection to the Stored Procedures node and view the procedure that was just deployed.

Note: The Stored Procedures node may need to be refreshed in order to display what was just deployed.

  1. Highlight StoredProcedure1, and select the Context Menu by “right-clicking” the mouse.
  2. Select Execute from the Context menu
  3. View the contents of the Output pane. In this case, the output will indicate that no records were affected. This is due to the output reported by the stored procedure.

Note: Ensure Database output is selected in the Output pane to view the results of the execution of the deployed function. There is a bug currently whereby the number of rows affected is not returned correctly. Verification of the operation can be accomplished by re-querying the database.

Summary

This walkthrough has illustrated the basic steps involved in creating, deploying, and executing a Visual Basic, Visual C# SQL Server Project. The user could make a number of enhancements to this project.

  • Modify the code in the function to make calculations on data in the user’s database.  
  • Reference other deployed Assemblies to use their functionality in this assembly.

Comments

  • Anonymous
    December 14, 2004
    What does the 'Deploy Solution' command do behind the scenes? Deploying from VS is great when developing, but in production I'll need a scripted approach of some kind.

    Also, which files must be deployed and which are optional?

    Thanks!
  • Anonymous
    December 14, 2004
    A great introductory article and I second Jason's questions.

    I also want to see a detailed discussion not on HOW you do this, but WHY. The code same you posted is traditionally part of the "data layer" of a web application. What are exactly the benefits and drawbacks to physically moving the code to the database server itself?
  • Anonymous
    December 15, 2004
    A Simple Walkthrough for deploying a SQLCLR Stored Procedure
  • Anonymous
    December 15, 2004
    The comment has been removed
  • Anonymous
    December 16, 2004
    thank you and what about parametrised SP
  • Anonymous
    December 16, 2004
    Walkthrough of building a SQLCLR stored proc with VB
  • Anonymous
    January 04, 2005
    The comment has been removed
  • Anonymous
    June 30, 2007
    Stuff I&#39;ve been intending to post a meaningful post about, but haven&#39;t: If you have ever wondered