Saturday, September 30, 2017

Caching In Web API

Download

In this article we will are going to learn how we can use caching in Web API. Normally caching is the process of storing data somewhere for the future requests, in our case we can avoid the unwanted hit to database to get the data if we cache the data somewhere, this way we can make sure that the data is served in a faster manner. Here we are going to see the caching in our Web API controller. If you are new to caching, I have written an article about caching in MVC, please read it here: Caching In MVC. We will explain caching with the help of System.Runtime.Caching which is given by Microsoft. This DLL is not available in the default references, you may need to add that separately. I will show you how. I am creating this application in Visual Studio 2015. You can always get the tips/tricks/blogs about these mentioned technologies from the links given below.

  • MVC Tips, Tricks, Blogs
  • Web API Tips, Tricks, Blogs

    Now we will go and create our application. I hope you will like this.

    Background

    For the past few days I am working with Web API.Here we are going to see a demo of how to use Caching in Web API in our MVC application. We are going to use the DLL System.Runtime.Caching.dll, which you need to add as reference.

    Create a MVC application

    Click File-> New-> Project then select MVC application. From the following pop up we will select the template as empty and select the core references and folders for MVC.

    Empty Template With MVC And Web API Folders

    Empty Template With MVC And Web API Folders

    Once you click OK, a project with MVC like folder structure with core references will be created for you.

    Folder Structure And References For Empty MVC Project

    Folder Structure And References For Empty MVC Project

    Once your application is ready, we can add the reference for System.Runtime.Caching

    Add reference for System.Runtime.Caching

    To add the reference, right click on References and click Add reference.

    Add_References

    Add_References

    Now click on the browse button, and search for System.Runtime.Caching

    References_Found_

    References_Found_

    And the click OK, the DLL will be added to your references now.

    Using the code

    We will set up our database first so that we can create Entity Model for our application later.

    Create a database

    The following query can be used to create a database in your SQL Server.

    SQL
    Edit|Remove
    USE [master]   GO       /****** Object:  Database [TrialsDB]      CREATE DATABASE [TrialsDB]    CONTAINMENT = NONE    ON  PRIMARY   ( NAME = N'TrialsDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TrialsDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )    LOG ON   ( NAME = N'TrialsDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TrialsDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)   GO       ALTER DATABASE [TrialsDB] SET COMPATIBILITY_LEVEL = 110   GO       IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))   begin   EXEC [TrialsDB].[dbo].[sp_fulltext_database] @action = 'enable'   end   GO       ALTER DATABASE [TrialsDB] SET ANSI_NULL_DEFAULT OFF   GO       ALTER DATABASE [TrialsDB] SET ANSI_NULLS OFF   GO       ALTER DATABASE [TrialsDB] SET ANSI_PADDING OFF   GO       ALTER DATABASE [TrialsDB] SET ANSI_WARNINGS OFF   GO       ALTER DATABASE [TrialsDB] SET ARITHABORT OFF   GO       ALTER DATABASE [TrialsDB] SET AUTO_CLOSE OFF   GO       ALTER DATABASE [TrialsDB] SET AUTO_CREATE_STATISTICS ON   GO       ALTER DATABASE [TrialsDB] SET AUTO_SHRINK OFF   GO       ALTER DATABASE [TrialsDB] SET AUTO_UPDATE_STATISTICS ON   GO       ALTER DATABASE [TrialsDB] SET CURSOR_CLOSE_ON_COMMIT OFF   GO       ALTER DATABASE [TrialsDB] SET CURSOR_DEFAULT  GLOBAL   GO       ALTER DATABASE [TrialsDB] SET CONCAT_NULL_YIELDS_NULL OFF   GO       ALTER DATABASE [TrialsDB] SET NUMERIC_ROUNDABORT OFF   GO       ALTER DATABASE [TrialsDB] SET QUOTED_IDENTIFIER OFF   GO       ALTER DATABASE [TrialsDB] SET RECURSIVE_TRIGGERS OFF   GO       ALTER DATABASE [TrialsDB] SET  DISABLE_BROKER   GO       ALTER DATABASE [TrialsDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF   GO       ALTER DATABASE [TrialsDB] SET DATE_CORRELATION_OPTIMIZATION OFF   GO       ALTER DATABASE [TrialsDB] SET TRUSTWORTHY OFF   GO       ALTER DATABASE [TrialsDB] SET ALLOW_SNAPSHOT_ISOLATION OFF   GO       ALTER DATABASE [TrialsDB] SET PARAMETERIZATION SIMPLE   GO       ALTER DATABASE [TrialsDB] SET READ_COMMITTED_SNAPSHOT OFF   GO       ALTER DATABASE [TrialsDB] SET HONOR_BROKER_PRIORITY OFF   GO       ALTER DATABASE [TrialsDB] SET RECOVERY FULL   GO       ALTER DATABASE [TrialsDB] SET  MULTI_USER   GO       ALTER DATABASE [TrialsDB] SET PAGE_VERIFY CHECKSUM    GO       ALTER DATABASE [TrialsDB] SET DB_CHAINING OFF   GO       ALTER DATABASE [TrialsDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )   GO       ALTER DATABASE [TrialsDB] SET TARGET_RECOVERY_TIME = 0 SECONDS   GO       ALTER DATABASE [TrialsDB] SET  READ_WRITE   GO
     

    Now we will create the table we needed. As of now I am going to create the table tblTags

    Create tables in database

    Below is the query to create the table tblTags.

    SQL
    Edit|Remove
    USE [TrialsDB]   GO       /****** Object:  Table [dbo].[tblTags]    Script Date: 23-Mar-16 5:01:22 PM ******/   SET ANSI_NULLS ON   GO       SET QUOTED_IDENTIFIER ON   GO       CREATE TABLE [dbo].[tblTags](       [tagId] [int] IDENTITY(1,1) NOT NULL,       [tagName] [nvarchar](50) NOT NULL,       [tagDescription] [nvarchar](max) NULL,    CONSTRAINT [PK_tblTags] PRIMARY KEY CLUSTERED   (       [tagId] ASC   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]       GO
     

    Can we insert some data to the tables now?

    Insert data to table

    You can use the below query to insert the data to the table tblTags

    SQL
    Edit|Remove
    USE [TrialsDB]   GO       INSERT INTO [dbo].[tblTags]              ([tagName]              ,[tagDescription])        VALUES              (<tagName, nvarchar(50),>              ,<tagDescription, nvarchar(max),>)   GO
     

    Next thing we are going to do is creating a ADO.NET Entity Data Model.

    Create Entity Data Model

    Right click on your model folder and click new, select ADO.NET Entity Data Model. Follow the steps given. Once you have done the processes, you can see the edmx file and other files in your model folder. Here I gave Dashboard for our Entity data model name. Now you can see a file with edmx extension have been created.
    Now will create our Web API controller.

    Create Web API Controller

    To create a Web API controller, just right click on your controller folder and click Add -> Controller -> Select Web API 2 controller with actions, using Entity Framework.

    Web API 2 Controller With Actions Using Entity Framework

    Web API 2 Controller With Actions Using Entity Framework

    Now select tblTag (CachingInWebAPI.Models) as our Model class and TrialsDBEntities (CachingInWebAPI.Models) as data context class.

    As you can see It has been given the name of our controller as tblTags. Here I am not going to change that, if you wish to change, you can do that.

    Now you will be given the following codes in our new Web API controller.

    C#
    Edit|Remove
    using System;   using System.Collections.Generic;   using System.Data;   using System.Data.Entity;   using System.Data.Entity.Infrastructure;   using System.Linq;   using System.Net;   using System.Net.Http;   using System.Web.Http;   using System.Web.Http.Description;   using CachingInWebAPI.Models;       namespace CachingInWebAPI.Controllers   {       public class tblTagsController : ApiController       {           private TrialsDBEntities db = new TrialsDBEntities();               // GET: api/tblTags           public IQueryable<tblTag> GettblTags()           {               return db.tblTags;           }               // GET: api/tblTags/5           [ResponseType(typeof(tblTag))]           public IHttpActionResult GettblTag(int id)           {               tblTag tblTag = db.tblTags.Find(id);               if (tblTag == null)               {                   return NotFound();               }                   return Ok(tblTag);           }               // PUT: api/tblTags/5           [ResponseType(typeof(void))]           public IHttpActionResult PuttblTag(int id, tblTag tblTag)           {               if (!ModelState.IsValid)               {                   return BadRequest(ModelState);               }                   if (id != tblTag.tagId)               {                   return BadRequest();               }                   db.Entry(tblTag).State = EntityState.Modified;                   try               {                   db.SaveChanges();               }               catch (DbUpdateConcurrencyException)               {                   if (!tblTagExists(id))                   {                       return NotFound();                   }                   else                   {                       throw;                   }               }                   return StatusCode(HttpStatusCode.NoContent);           }               // POST: api/tblTags           [ResponseType(typeof(tblTag))]           public IHttpActionResult PosttblTag(tblTag tblTag)           {               if (!ModelState.IsValid)               {                   return BadRequest(ModelState);               }                   db.tblTags.Add(tblTag);               db.SaveChanges();                   return CreatedAtRoute("DefaultApi"new { id = tblTag.tagId }, tblTag);           }               // DELETE: api/tblTags/5           [ResponseType(typeof(tblTag))]           public IHttpActionResult DeletetblTag(int id)           {               tblTag tblTag = db.tblTags.Find(id);               if (tblTag == null)               {                   return NotFound();               }                   db.tblTags.Remove(tblTag);               db.SaveChanges();                   return Ok(tblTag);           }               protected override void Dispose(bool disposing)           {               if (disposing)               {                   db.Dispose();               }               base.Dispose(disposing);           }               private bool tblTagExists(int id)           {               return db.tblTags.Count(e => e.tagId == id) > 0;           }       }   }
     

    As we are not going to use only read operation, you can remove other functionalities and keep only Get methods for now.

    C#
    Edit|Remove
    // GET: api/tblTags          public IQueryable<tblTag> GettblTags()          {              return db.tblTags;          }
     

    So the coding part to fetch the data from database is ready, now we need to check whether our Web API is ready for action!. To check that, you just need to run the URL http://localhost:4832/api/tblTags. HeretblTags is our Web API controller name. I hope you get the data as a result.

    Web_API_Result

    Web_API_Result

    Now we will start testing our caching. For that please import the namespace System.Runtime.Caching;




    C#
    Edit|Remove
     

    Next we will create an instance of MemoryCache class.

    MemoryCache_Tooltip_

    MemoryCache_Tooltip_

    As you can see there are four possible settings we can set in MemoryCache class.

    MemoryCache_Settings

    MemoryCache_Settings

    Now we can add MemoryCache as follows.

    C#
    Edit|Remove
    public IQueryable<tblTag> GettblTags()           {               var ca = db.tblTags;               memCache.Add("tag", ca, DateTimeOffset.UtcNow.AddMinutes(5));               return db.tblTags;           }

    MemoryCache_Add

    MemoryCache_Add

    Here “tag” is my key and “ca” is my values and DateTimeOffset.UtcNow.AddMinutes(5) is for setting the cache for five minutes from now.

    Shall we check how it works now? To check whether the content has been added to the cache we need to use the Get method, please see the code block below.

    C#
    Edit|Remove
    var res = memCache.Get("tag");               if (res != null)               {                   return res;               }               else {                   var ca = db.tblTags;                   memCache.Add("tag", ca, DateTimeOffset.UtcNow.AddMinutes(5));                   return db.tblTags;               }
     

    We will get the cache values in the variable res, remember this values will be there only for five minutes. You can always change that as per need. If the value is not null, we will just return it and do the manipulation and if it is null we will go ahead and fetch the data from database and add the value to cache. Now please run your API by running the URL http://localhost:4832/api/tblTags.

    Cache_Value_Null

    Cache_Value_Null

    You can see that we are setting the value to memCache as the memCache.Get(“tag”) is null.

    Cache_Value_Not_Null

    Cache_Value_Not_Null

    So we just tried to load the same Web API URL within five minutes, so we gets values frommemCache.Get(“tag”). Sounds good?

    There is an option to remove our cache too, we will see that now. We will use the Remove function for the same. First we will check whether the key is available in the MemoryCache, if it is available we will remove that.

    C#
    Edit|Remove
    //This is to remove the MemoryCache - start                   if (memCache.Contains("tag"))                   {                       memCache.Remove("tag");                   }                   //This is to remove the MemoryCache - end   
     

    MemoryCache_Remove

    MemoryCache_Remove

    We have done everything!. That’s fantastic right? Have a happy coding.

  • Calendar Web Control Sample

    Download


    Figure1:Calendar Control

    Introduction

    # The introduction of a basic sample of "Calendar".
    Because it is a sample that I serve as study, thank you for your understanding for movement.
    because it may become unstable.

    Building the Sample

    # You will need to run this sample on VisualStudio 2015 or higher.

    Description

    # I made this sample in VisualBasic.Net.
    It is one of WebControl in VisualBasic.Net , a sample of "Calendar".

    # Calendar control is control to display a calendar . I display a calendar for one month and , on 1st, can choose 1-month indication for one week and can acquire data.

    Customization

    # Main property
    "SelectionMode"---I appoint it wherther "a day" , "a week", "the moon" are selectable.





    ***********************************************************************************************
    ***********************************************************************************************
    ***********************************************************************************************

    ysato1500 , Japan

    ***********************************************************************************************
    ***********************************************************************************************
    ***********************************************************************************************

    Calling a SSIS Package

    Download
    Problem

    Calling a SQL Server Integration Services (SSIS) package from s stored procedure should be an easy task however Microsoft has provided no direct method to do so.
     
    A quick search on Google found this CodePlex example which uses the xp_cmdshell extended stored procedure to execute dtexec via the Windows Command Shell and this blog post (Rick Weyenberg) which describes how to use a stored procedure to create a SQL Server Job which executes a SSIS package via a Job Step.

    Although the above examples work quite well, I wasn’t too keen on using xp_cmdshell and I wanted a solution that provided the ability to:
    • Notify of execution failure
    • Use dynamic package declaration
    • Use dynamic variable assignment
    Solution

    This solution is based on the SQL Server Job example provided by Rick Weyenberg however it uses XML to implement my additional requirements.

    It has been been tested on SQL Server 2005 – 2008(R2) and is configured to execute SSIS packages that reside on the same server as the executing stored procedure.

    XML Structure

    The structure of the XML used in this solution is as follows:

    XML
    Edit|Remove
    <package name="PackageName" notifyMethod="Method" notifyOperator="Operator"            owner="Owner" runAs="RunAs">        <variable name="Variable1" value="Value"/>        <variable name="Variable2" value="Value"/>    </package>
     
    Package Element
    • name: Name of the SSIS package to execute.  You need to specify the full package name and path i.e. if a package called SSISTest resides in a folder called Test within MSDB, the package name would be \Test\SSISTest
    • notifyMethod: The method used to notify of job failure.  The value for this attribute corresponds with SQL Server Agent notification types (Except for “Automatically Delete Job” which is always set to “When the job completes” and “Write to the Windows Application Event Log” which is always set to “When the job fails”) .  Leave blank for no notification.
    • notifyOperator: The name of the SQL Server Operator to notify when notifyMethod is provided.
    • owner: An account in SQL Server that has sufficient permissions to execute jobs
    • runAs: An proxy account that has sufficient permissions to execute any tasks within the SSIS package.  Leave blank to run under the SQL Server Agent Service Account.
    I have only added the owner and runAs attributes to demonstrate that this can be done.  I recommend that you do not allow the caller of the stored procedure to nominate which accounts to use.

    Variable Element
    • name: Name of the SSIS variable you want to assign a value to
    • value: Value you want to assign to the variable
    Stored Procedure

    The stored procedure used to implement this solution is quite lengthy so I’ll break it down into chunks and provide explanations where required.

    1) Create the stored procedure shell which accepts a parameter of type XML and implements some basic error handling.
    SQL
    Edit|Remove
    CREATE PROCEDURE [dbo].[usp_ExecuteSSISPackage]    (      @xVar XML    )    AS      BEGIN        -- Step 2 code goes here            BEGIN TRY          -- Step 3 code goes here              -- Step 4 code goes here              -- Step 5 code goes here              -- Step 6 code goes here              -- Step 7 code goes here              -- Return successfull job creation          RETURN 1        END TRY        BEGIN CATCH          -- Retrieve error details          SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;              -- Return failed job creation          RETURN 0        END CATCH      END    
     
    The error handling implemented here only relates to job creation. Any errors that occur during job execution will be logged in the Windows Application Event Log.

    2) Create some local variables.

    @
    VariableTable is used to hold the shredded XML variable data (@xVar parameter)

    3) Shred the XML contained in the @xVar parameter and store the resulting data in local variables

    SQL
    Edit|Remove
          -- Get package attributes          SELECT @PackageName = nref.value('@name[1]','varchar(255)'),                 @NotifyMethod = nref.value('@notifyMethod[1]''varchar(10)'),                 @NotifyOperator = nref.value('@notifyOperator[1]''varchar(255)'),                 @Owner = nref.value('@owner[1]','varchar(100)'),                 @RunAsAccount = nref.value('@runAs[1]''varchar(100)')          FROM   @xVar.nodes('/package') AS R(nref)              -- Store SSIS variable details in local table variable          INSERT INTO @VariableTable          SELECT nref.value('@name[1]''varchar(50)'),                 nref.value('@value[1]''varchar(50)')          FROM   @xVar.nodes('/package/variable') AS R(nref)
     
     
    4) Create the job.

    The job name (@JobName) is made up of the combination of the package name and a GUID.  This enables multiple copies of the same package to be executed at the same time.

    @delete_level = 3 causes the job to be deleted regardless of whether job execution succeeded

    SQL
    Edit|Remove
    -- Get the current server name          SET @Server = CONVERT(sysname, SERVERPROPERTY(N'servername'))              -- Create a unique job name          SET @JobName = @PackageName + '_' + CAST(NEWID() AS CHAR(36))              -- Create the job          IF @NotifyMethod = 'E-mail'            BEGIN              EXEC  msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1,                @notify_level_email = 2, -- When the job fails                @notify_email_operator_name = @NotifyOperator,                @notify_level_eventlog = 2, -- When the job fails                @Owner_login_name = @Owner,                @delete_level = 3, -- Delete job regardless of execution state                @job_id = @JobId OUTPUT            END          ELSE IF @NotifyMethod = 'Page'            BEGIN              EXEC  msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1,                @notify_level_page = 2, -- When the job fails                @notify_page_operator_name = @NotifyOperator,                @notify_level_eventlog = 2, -- When the job fails                @Owner_login_name = @Owner,                @delete_level = 3, -- Delete job regardless of executionstate,                @job_id = @JobId OUTPUT            END          ELSE IF @NotifyMethod = 'Net send'            BEGIN              EXEC  msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1,                @notify_level_netsend = 2, -- When the job fails                @notify_netsend_operator_name = @NotifyOperator,                @notify_level_eventlog = 2, -- When the job fails                @Owner_login_name = @Owner,                @delete_level = 3, -- Delete job regardless of execution state,                @job_id = @JobId OUTPUT            END          ELSE            BEGIN              EXEC  msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1,                @notify_level_eventlog = 2, -- When the job fails                @Owner_login_name = @Owner,                @delete_level = 3, -- Delete job regardless of execution state,                @job_id = @JobId OUTPUT            END              -- Add the job to the server          EXEC msdb.dbo.sp_add_jobserver @job_name = @JobName, @server_name = @Server    
      
    5) Get the package variables and their associated values.

    This code portion loops through the rows in the @VariableTable and builds a string which is used to add the variables and their associated values to the job step.  The syntax used to set package variables is:

    SQL
    Edit|Remove
    /SET \Package.Variables[User::VariableName].Value;VariableValue
     
    e.g.

    SQL
    Edit|Remove
    /SET \Package.Variables[User::ProjectID].Value;666
      
    Actual stored procedure code.

     
    SQL
    Edit|Remove
          -- Build the SSIS variable assignment list          SELECT @MaxID = MAX(ID) FROM @VariableTable          SET @VariableList = ''         SET @CurrentID = 0                    WHILE (@CurrentID <= @MaxID)            BEGIN              SELECT @VariableList = @VariableList                + ' /SET \Package.Variables[User::' + Name + '].Value;' + Value              FROM @VariableTable              WHERE ID = @CurrentID                  SET @CurrentID = @CurrentID + 1            END
     
    6) Create the job step

    SQL
    Edit|Remove
            -- Create the job step            SET @StepCmd = '/SQL "' + @PackageName + '" /SERVER '              + @Server + ' /CHECKPOINTING OFF ' + @VariableList + ' /REPORTING E'                EXEC msdb.dbo.sp_add_jobstep @job_name = @JobName,              @step_name = N'SSISExecuteStep', @step_id = 1,              @cmdexec_success_code = 0, @on_success_action = 1,              @on_fail_action = 2, @retry_attempts = 0,              @retry_interval = 0, @os_run_priority = 0,              @subsystem = N'SSIS', @command = @StepCmd,              @database_name = N'master', @flags=0, @proxy_name=@RunAsAccount
      
    7) Start the job

    SQL
    Edit|Remove
            -- Start job            EXEC msdb.dbo.sp_start_job @job_id = @JobId
      
    That’s it.  To execute the stored procedure in SSMS use:

    SQL
    Edit|Remove
    DECLARE @XML XML   SET @XML =   '<package name="\SSISTest\SSISTest" notifyMethod="E-mail" notifyOperator="ITSupport"           owner="Machine\Administrator" runAs="">       <variable name="TestID" value="2"/>       <variable name="ModifiedBy" value="Jeff"/>   </package>'      exec [dbo].[usp_ExecuteSSISPackage] @XML
     
    To call the stored procedure from .Net is very straight forward.

    I’m using the Microsoft Enterprise Library 5.0 as it handles the majority of the database code required to connect to SQL Server and execute the stored procedure.

    C#
    Edit|Remove
    using Microsoft.Practices.EnterpriseLibrary.Data;   using Microsoft.Practices.EnterpriseLibrary.Data.Sql;   using System.Data.Common;   using System;   using System.Data;      class SSIS   {       public int ExecuteSSISPackage()       {           SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();              // This is the stored procedure we created earlier           DbCommand dbCommand =               db.GetStoredProcCommand("[dbo].[usp_ExecuteSSISPackage]");              // Add a paramter of type XML and assign the           String xmlData = "<Your XML Data Goes Here>";                 db.AddInParameter(dbCommand, "@xVar", DbType.Xml, xmlData);           // Add a ReturnValue paramter of type INT for error checking           db.AddParameter(dbCommand, "@RETURN_VALUE", DbType.Int16, 0,               ParameterDirection.ReturnValue, false, 00"",               DataRowVersion.Current, 0);              // Execute the stored procedure           DataSet ds = db.ExecuteQuery(dbCommand);              //Get error indicator           int erroCode =               Convert.ToInt32(db.GetParameterValue(dbCommand, "@RETURN_VALUE"));              return erroCode;       }   }   
     
    Conclusion

    In this post I have demonstrated how to call a SSIS package from a SQL Server stored procedure.

    Calling WCF Service from a

    Download
    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