Tuesday, February 25, 2014

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

One my friends wanted to call a WCF Service from a Stored Procedure in Microsoft SQL Server 2012. So this is how you can do it.

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

When wring functionalities 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 use of 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 with T-SQL. The best example for such task would be the task that I am 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.

In my development environment, I have Microsoft Visual Studio 2013 and Microsoft SQL Server 2012 installed.

First let me start off with with creating a WCF Service which will be consumed by the Stored Procedure. I am creating a WCF Service application,  targeting .NET Framework 4.5.1. If you are thinking since my SQL Server version is 2012 and the CLR version for SQL Server 2012 is 4.0, I 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.

Picture1
Create WCF Service Application
Here I have the following WCF Service with a method “SayHello” which accepts a string. So I will be calling this method from my Stored Procedure.

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.

Picture2
WCF Service Up and Running
Now I am done with creating the WCF Service Application. Now before moving into next steps let me briefly explain the approach I am going to follow.

I will need to have a SQL CLR project created using Visual Studio 2013. There I 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 I should have an intermediate WCF client. So I will be creating a DLL which will acts as the WCF Client here. There I will be exposing a public static method which in turn call the WCF Service. Then in my SQL CLR project, I will be adding a reference to that particular DLL, and then I can call it’s public static method which will in turn call the WCF Service. Hope you all got a clear idea on my approach here.

Now coming back in to the action, I will now create a Class Library Project.

Picture3
Create Class Library
Now first thing I am going to do is adding a reference to my WCF Service.

Untitled4
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).

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

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

Picture7
Add Reference
Now I 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 I am done with creating the DLL, Next part is creating the SQL Project using CLR. In Visual Studio 2013, under installed templates go to Other Languages section. There inside SQL Server category, I can find the SQL Server Database Project.

Picture8
Create SQL Server Database Project
Now I am adding a New Item to the project.

Untitled1
Add New Item
Then In Following window, I am selecting “SQL CLR C# Stored Procedure”.

Picture9
Create SQL CLR C# Stored Procedure
Now I am adding a reference to my above created DLL by right clicking on Reference, clicking on Add Reference and browsing for the DLL.

Picture10
Reference Added
Then I am modifying my 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 I am calling the static method inside my DLL and will send the result directly to the client or current output consumer.

Now I am all done with Visual Studio. Let’s move into the SQL Server Management Studio. I have an empty database created there named “MyDemoDB”.

First I am going to register my SQL CLR Project as an object in my instance of SQL Server.

For that I am running 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
I am thrown with this error.

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 I am running the following query.
alter database [MyDemoDB]
set trustworthy on;
go
Command got completed successfully. Then I am running the previous query back again. And this time it got completed successfully.

Now I am creating 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 my DLL which in turn call the WCF Service.

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

Picture11
Stored Procedure Created
Now I am executing 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 I am running the following query.
USE [MyDemoDB]
EXEC sp_configure 'clr enabled', '1';
RECONFIGURE;
It got completed and again I am executing the “spWcfCall” stored procedure. And this time no errors. My Stored Procedure has successfully called the WCF Service and resulted string is as follows.

Picture12
Result
I am attaching the full sample to my SkyDrive.


Happy Coding.

Regards,
Jaliya

Monday, February 24, 2014

WCF Client using a Proxy Class

I have seen some confusions in my fellow developers when creating WCF clients to consume from WCF SOAP Service (Here why I have explicitly mentioned SOAP Service is if you are using a WCF REST Service and created a Proxy class for WCF Rest service using svcutil.exe or by Adding Service Reference, it doesn’t work. Here is a link to a great post on describing why).

Some seem to be bit confused in the term Proxy class. This is kind of like a basic topic and everyone should have a solid understanding about the Proxy class. So in this post I am going to explain about Proxy class in respect to WCF clients.

A WCF client and a WCF SOAP Service can communicate using SOAP messages, which encapsulate the input and output parameters as XML. The Proxy class maps parameters to XML elements and then sends the SOAP messages over a network. There are two ways to add a proxy class to your client project using the Microsoft .NET Framework: with the svcutil.exeServiceModel Metadata Utility Tool (Svcutil.exe) in the .NET Framework, or by adding a Service reference in Microsoft Visual Studio.

For the demonstration, I will be using following WCF Service.

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);
    }
}

ServiceModel Metadata Utility Tool (Svcutil.exe)

The ServiceModel Metadata Utility Tool (svcutil.exe) can be found at the Windows SDK installation location, specifically,
C:\Program Files\Microsoft SDKs\Windows\”yourversion”\bin
Now let’s see how to use svcutil.exe.

I am opening the Command Prompt, navigating to the above folder and issuing the following command.
svcutil.exe /d:D:\Temp http://localhost:64435/Service1.svc
Here /d parameter for mentioning the directory to create files in. Default will be the current directory. Since I don’t want my files to be created in current directory, I have given a path of a temp folder. (When you are using svcutil.exe, there are a lot of parameters you can pass in. It's up to you to learn those). 

Picture1
Running svcitul.exe 
Now when I examine my temp folder, I can see two files created. One is a config file and the other, the proxy class.

Now I am creating a Console Application and copy and pasting the content in config file into my projects’ app.config file. Then I am adding the proxy class to the project by right clicking on the project and Add->Existing Item.

Then adding a reference to System.ServiceModel.

Picture2
Add Reference to System.ServiceModel
Now building project. It gets completed. Now I can write the code for consuming the service methods.
using System; 

namespace MyWcfClientUsingSvcUtil
{
    class Program
    {
        static void Main(string[] args)
        {
            using (Service1Client client = new Service1Client())
            {
                Console.WriteLine(client.SayHello("Jaliya"));
            }
        }
    }
}
Output will be as follows.

Picture3
Result

Add Service Reference in Microsoft Visual Studio

Why take all the steps above when you have Visual Studio. You can just use Visual Studio add to Add Service Reference. Here I have created another Console Application and after project is created, I am right clicking on References and clicking on Add Service Reference.

Picture4
Add Service Reference
After entering the Service address and providing the namespace name, I am clicking on OK. Now I am ready to write the code.
using MyWcfClientUsingAddSerRef.svcService1;
using System;
 
namespace MyWcfClientUsingAddSerRef
{
    class Program
    {
        static void Main(string[] args)
        {
            using (Service1Client client = new Service1Client())
            {
                Console.WriteLine(client.SayHello("Jaliya"));
            }
        }
    }
}
The output is same as above.

Picture3
Result
Here is a link to one of my previous posts which shows how to create WCF clients without using a Proxy class.
   Calling WCF Service using ChannelFactory from a Silverlight Application

I am uploading the sample to my SkyDrive.


Happy Coding.

Regards,
Jaliya

Tuesday, February 18, 2014

Visual C# Technical Guru - January 2014

Became the Visual C# Technical Guru for the month of January, 2014. Feeling so happy as I am the first Visual C# Technical Guru in the TechNet Wiki Guru Competition for the year 2014.

The TechNet Guru Awards celebrate the technical articles on Microsoft TechNet.

Post in WikiNinjas Official Blog,
http://blogs.technet.com/b/wikininjas/archive/2014/02/16/technet-guru-awards-january-2014.aspx

Visual C# Technical Guru - January 2013
Happy Coding.

Regards,
Jaliya