Share via


Calling WCF Service from a Stored Procedure in Microsoft SQL Server 2012


Introduction

This wiki explains how to call a WCF Service from a Stored Procedure in Microsoft SQL Server 2012.

Before starting off with coding, there are some things that we all need to know and understand well.

When writing SQL Server code such as Stored Procedures, Triggers etc. for a database which sits inside Microsoft SQL Server, there are two things we can use.

  • T-SQL
  • SQL CLR (SQL Common Language Runtime)

You can either write your operation using T-SQL or you can use SQL CLR. Since you all are familiar with T-SQL, let’s see what SQL CLR is.

SQL CLR is using the Microsoft .NET Common Language Runtime within SQL Server. SQL CLR is very useful when you want to accomplish a task which is not possible or hard with T-SQL. The best example for such task would be the task that we are going to do right now.

You can’t use only T-SQL to consume a WCF Service. But SQL CLR gives the ability to call and consume a WCF Service. So what we can do is write a Stored Procedure with the use of SQL CLR which will call and consume from the WCF Service. Then we can write a T-SQL Stored Procedure to call the SQL CLR Stored Procedure.

Different SQL Server versions uses different CLR versions. SQL Server 2012 uses version 4.0 of the CLR although previous versions of SQL Server (from SQL Server 2005) uses version 2.0 of the CLR.

Keeping that in mind let’s jump into the action.

Example

In the development environment, we have Microsoft Visual Studio 2013 and Microsoft SQL Server 2012 installed.

First we will start with creating a WCF Service which will be consumed by the Stored Procedure. Then create a WCF Service application,  targeting .NET Framework 4.5.1. If you are thinking since the SQL Server version here is 2012 and the CLR version for SQL Server 2012 is 4.0, that we should be using  .NET framework 4.0 instead of 4.5.1. Don’t worry, the .NET Framework 4.0, 4.5, and 4.5.1 include CLR 4.0, the .NET Framework 2.0, 3.0, and 3.5 include CLR 2.0 as there was no version 3 of the CLR.

WCF Service Application

http://lh3.ggpht.com/-lI-2mt5ElG4/UwcVFY1iqlI/AAAAAAAACLo/PxKqZKgs6NA/Picture1_thumb%25255B4%25255D.png?imgmax=800
Create WCF Service Application

Here we have the following WCF Service with a method “SayHello” which accepts a string. The Stored Procedure will be calling this particular method.

IService1.cs

[ServiceContract]
public interface IService1
{
    [OperationContract]
    string SayHello(string name);
}

Service.cs

public class Service1 : IService1
{
    public string SayHello(string name)
    {
        return string.Format("Hello {0}!", name);
    }
}

Now make sure your WCF Service is up and running.

http://lh4.ggpht.com/-AU8aB3jV8Kw/UwcVHue-3oI/AAAAAAAACL4/13hLSSjWDcA/Picture2_thumb%25255B2%25255D.png?imgmax=800
WCF Service Up and Running

Now we are done with creating the WCF Service Application. Now before moving into next steps here is a brief explanation on the approach that we are going to follow.

Idea

We will need to have a SQL CLR project created using Visual Studio 2013. There we will have a Stored Procedure created. But when you are creating SQL project using Visual Studio 2013 and if you think about calling WCF Service directly from SQL project by adding a web reference, you can’t do that. From SQL Projects you can’t add web references.

Because of that we should have an intermediate WCF client. So we will be creating a DLL which will act as the WCF client here. There we will be exposing a public static method which in turn call the WCF Service. Then in our SQL CLR project, we will be adding a reference to that particular DLL, and then we can call it’s public static method which will in turn call the WCF Service. Hope you all got a clear idea on our approach here.

Now coming back in to the action, let's create a Class Library Project.

Class Library Project

http://lh3.ggpht.com/-fwurAvHwHL8/UwcVJSoO6EI/AAAAAAAACMI/pm5SdiWSSNI/Picture3_thumb%25255B2%25255D.png?imgmax=800
Create Class Library

Now the first thing what we will be doing is adding a reference to WCF Service.

http://lh4.ggpht.com/-ChTUzf9D5iE/UwcVKhS6c0I/AAAAAAAACMU/AAcEA_g7qOI/Untitled4_thumb%25255B2%25255D.png?imgmax=800
Add Service Reference

From the next window, click on Advance (That’s because we should add the reference as a Web Reference, and not as a WCF Service Reference. The WCF Client is not supported in SQL CLR, and in SQL 2012 it doesn't work).

http://lh5.ggpht.com/-XKHbr_0RGLQ/UwcVMZfsRtI/AAAAAAAACMo/PxuJFqD1Wc4/Picture5_thumb%25255B3%25255D.png?imgmax=800
Advanced

In the following window click on “Add Web Reference”.

http://lh5.ggpht.com/-z7sjK9AUkEY/UwcVOn5TINI/AAAAAAAACM4/izpdtThWhf8/Picture6_thumb%25255B3%25255D.png?imgmax=800
Add Web Reference

Now enter the WCF Service URL, provide a Web reference name and click on Add Reference.

http://lh4.ggpht.com/-UUPafPAApgk/UwcVYOTMfaI/AAAAAAAACNI/91NuKBB5kFg/Picture7_thumb%25255B3%25255D.png?imgmax=800
Add Reference

Now we have the following class “MyClass” with the following public static method “CallWcfService”.

using MyClassLibrary.svcService;
namespace MyClassLibrary
{
    public class MyClass
    {
        public static string CallWcfService(string name)
        {
            string result = string.Empty;
            using (Service1 client = new Service1())
            {
                result = client.SayHello(name);
            }
            return result;
        }
    }
}

Above method accepts a string and will call the “SayHello” method in my WCF Service and return the result from the “SayHello” method.

Now we are done with creating the DLL, Next part is creating the SQL Project using CLR.

SQL Server Database Project

In Visual Studio 2013, under installed templates go to Other Languages section. There inside SQL Server category, you can find the SQL Server Database Project.

http://lh3.ggpht.com/-UudHCpzTYs0/UwcVbmQ5dCI/AAAAAAAACNY/xcoNarkqdCE/Picture8_thumb%25255B3%25255D.png?imgmax=800
Create SQL Server Database Project

Now let's add a New Item to the project.

http://lh3.ggpht.com/-X5jBAuEnhIo/UwcVdnpNu7I/AAAAAAAACNo/XCkXwBmG_ck/Untitled1_thumb%25255B4%25255D.png?imgmax=800
Add New Item

Then In Following window, let's select “SQL CLR C# Stored Procedure”.

http://lh4.ggpht.com/-MeO7loE729Y/UwcVfZfbAqI/AAAAAAAACN4/_vtGijMEjVc/Picture9_thumb%25255B6%25255D.png?imgmax=800
Create SQL CLR C# Stored Procedure

Now let's add a reference to above created DLL by right clicking on Reference, clicking on Add Reference and browsing for the DLL.

 

http://lh5.ggpht.com/-Guo83UoLvc0/UwcVghoj_-I/AAAAAAAACOI/Ua3koOHBleI/Picture10_thumb%25255B2%25255D.png?imgmax=800
Reference Added

Then let's modify the created Stored Procedure as follows.

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void spCallWcfService(string name)
    {
        try
        {
            SqlPipe sqlPipe = SqlContext.Pipe;
            string result = MyClassLibrary.MyClass.CallWcfService(name);
            SqlContext.Pipe.Send(result);
        }
        catch (Exception ex)
        {
            SqlContext.Pipe.Send(ex.Message);
        }
    }
}

Now the modified Stored Procedure will accepts a string. Then let's call the static method inside the DLL and will send the result directly to the client or current output consumer.

Now we are all done with Visual Studio. Let’s move into the SQL Server Management Studio. Let's create an empty database there named “MyDemoDB”.

 

First we need to register the SQL CLR Project as an object in the instance of SQL Server.

For that let's run the following query.

IF EXISTS (
    SELECT [name] 
    FROM sys.assemblies 
    WHERE [name] = N'MySQLCLRProject')
 
    BEGIN
        DROP ASSEMBLY MySQLCLRProject
        ALTER ASSEMBLY MySQLCLRProject
        FROM 'D:\...\MySQLCLRProject.dll'
        WITH PERMISSION_SET = UNSAFE ;
    END
ELSE
    BEGIN
        CREATE ASSEMBLY MySQLCLRProject
        FROM 'D:\...\MySQLCLRProject.dll'
        WITH PERMISSION_SET = UNSAFE;
    END

There following error will be thrown.

CREATE ASSEMBLY for assembly 'MySQLCLRProject' failed because assembly 'MySQLCLRProject' is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

For that we need to run the following query.

alter database [MyDemoDB]
set trustworthy on;
go

Command got completed successfully. Then let's run the previous query back again. And this time it got completed successfully.

Now let's create a T-SQL Stored Procedure inside “MyDemoDB” which calls the CLR Stored Procedure.

CREATE PROCEDURE [dbo].[spWcfCall]
      @parameter1 NVARCHAR(MAX)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME MySQLCLRProject.StoredProcedures.spCallWcfService

spWcfCall” will be accepting a string. It will be then passed to to the CLR Stored Procedure. It will be again get passed to public static method in DLL which in turn call the WCF Service.

 

After executing the query you can see that T-SQL Procedure inside Stored Procedures in “MyDemoDB

http://lh4.ggpht.com/-XQE8zkQs65Q/UwcViETISUI/AAAAAAAACOY/6LGv-4Ncdxk/Picture11_thumb%25255B9%25255D.png?imgmax=800
Stored Procedure Created

Now let's execute the “spWcfCall” stored procedure with a parameter hoping to see the result returned by the service.

EXEC spWcfCall "Jaliya"

Again an error.

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

For that let's run the following query.

USE [MyDemoDB]
EXEC sp_configure 'clr enabled', '1';
RECONFIGURE;

It got completed and again let's execute the “spWcfCall” stored procedure. And this time no errors. Our Stored Procedure has successfully called the WCF Service and resulted string is as follows.

http://lh3.ggpht.com/-hVb8HK_BevQ/UwcVjIA_MHI/AAAAAAAACOk/hgIIKauEeE8/Picture12_thumb%25255B3%25255D.png?imgmax=800
Result

See Also

Please have a look at all the Visual C# Resources on the TechNet Wiki.

Download Sample

Full sample is uploaded to MSDN Code Gallery.
   Download Sample

Happy Coding.