Calling C# code from Sql procedure

CLR is abbreviation of Common Language Runtime. In SQL Server 2005 and later version of it database objects can be created which are created in CLR. Stored Procedures, Functions, Triggers can be coded in CLR. CLR is faster than T-SQL in many cases. CLR is mainly used to accomplish task which are not possible by T-SQL or can use lots of resources. CLR can be usually implemented where there is intense string operation, thread management or iteration methods which can be complicated for T-SQL. Implementing CLR provides more security to Extended Stored Procedure.

Let us create one very simple CLR where we will print current system datetime.

1) Open Microsoft Visual Studio >> Click New Project >> Select Visual C# >> Database >> SQL Server Project

2) Either choose from existing database connection as reference or click on Add New Reference. In my example I have selected Add New Reference.

3) If you have selected existing reference skip to next step or add database reference as displayed in image.

4) Once database reference is added following project will be displayed in Solution Explorer. Right click on Solution Explorer >> Click on Add >> Stored Procedure.

5) Add new stored procedure template from following screen.

6) Once template added it will look like following image.

7) Now where it suggest to //Put your code here. Replace it with code displayed in the image. Once the code is complete do following two steps.
a) Click on menu bar >> Build >> Build ProjectName
b) Click on menu bar >> Build >> Deploy ProjectName
Building and Deploying project should give successful message.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure] public static void CLRSPTest()
{
SqlPipe sp;
sp = SqlContext.Pipe;
String strCurrentTime = “Current System DateTime is: “
+ System.DateTime.Now.ToString();
sp.Send(strCurrentTime);
}
};
8) Now open SQL Server Management Studio and run following script in Query Editor. It should return current system datetime. Running it again the time will change.

USE AdventureWorks
GOEXEC dbo.CLRSPTest
GO

Event based throttler test

    using Microsoft.VisualStudio.TestTools.UnitTesting;
    using System;
    using System.Collections.Generic;
    using System.Threading;
    using System.Threading.Tasks;
    using CommonUtilities.Schedulers;

    [TestClass]
    public sealed class TimeboundThrottlerTest
    {
        [TestMethod]
        public void Test_TimeboundThrottler_ctor_Success()
        {
            TimeboundThrottler validThrottler = new TimeboundThrottler(10);
            Assert.IsNotNull(validThrottler);
        }

        [TestMethod]
        [ExpectedException(typeof(ArgumentOutOfRangeException))]
        public void Test_TimeboundThrottler_ctor_Failure()
        {
            TimeboundThrottler invalidThrottler = new TimeboundThrottler(0);
        }

        [TestMethod]
        public void Test_TimeboundThrottler_Enqueue()
        {
            TimeboundThrottler throttler = new TimeboundThrottler(3);
            int runningTasks = 0;
            int completedTasks = 0;

            List<Task> tasks = new List<Task>();
            List<CancellationTokenSource> cancellationTokens = new List<CancellationTokenSource>();

            for (int i = 0; i < 15; i++)
            {
                var cts = new CancellationTokenSource();
                ChangeNotifier notifier = new ChangeNotifier();
                notifier.StartStateChanged += (s, e) => { lock (this) { runningTasks++; } };
                notifier.CompletedStateChanged += (s, e) => { lock (this) { completedTasks++; } };
                cancellationTokens.Add(cts);
                tasks.Add(throttler.Enqueue(() => TestFuncWithCancellationAsync(cts, notifier)));
            }

            // Initially verify that only 3 tasks are picked and rest of the tasks are waiting
            Assert.AreEqual(3, runningTasks);
            Assert.AreEqual(0, completedTasks);

            Thread.Sleep(1100);
            // Initially verify that 3 new tasks are picked and rest of the tasks are waiting
            Assert.AreEqual(6, runningTasks);
            Assert.AreEqual(0, completedTasks);

            // Complete one task, still verify that only 3 tasks are picked in next 1sec
            cancellationTokens[0].Cancel();
            Thread.Sleep(1100);
            Assert.AreEqual(9, runningTasks);
            Assert.AreEqual(1, completedTasks);
        }

        [TestMethod]
        public void Test_TimeboundThrottler_Enqueue_TaskwithResult()
        {
            TimeboundThrottler throttler = new TimeboundThrottler(3);
            int runningTasks = 0;
            int completedTasks = 0;

            List<Task<CancellationToken>> tasks = new List<Task<CancellationToken>>();
            List<CancellationTokenSource> cancellationTokens = new List<CancellationTokenSource>();

            for (int i = 0; i < 15; i++)
            {
                var cts = new CancellationTokenSource();
                ChangeNotifier notifier = new ChangeNotifier();
                notifier.StartStateChanged += (s, e) => { lock (this) { runningTasks++; } };
                notifier.CompletedStateChanged += (s, e) => { lock (this) { completedTasks++; } };
                cancellationTokens.Add(cts);
                tasks.Add(throttler.Enqueue(() => TestFuncWithCancellation2Async(cts, notifier)));
            }

            // Initially verify that only 3 tasks are picked and rest of the tasks are waiting
            Assert.AreEqual(3, runningTasks);
            Assert.AreEqual(0, completedTasks);

            Thread.Sleep(1100);
            // Initially verify that 3 new tasks are picked and rest of the tasks are waiting
            Assert.AreEqual(6, runningTasks);
            Assert.AreEqual(0, completedTasks);

            // Complete one task, still verify that only 3 tasks are picked in next 1sec
            cancellationTokens[0].Cancel();
            Thread.Sleep(1100);
            Assert.AreEqual(9, runningTasks);
            Assert.AreEqual(1, completedTasks);
            Assert.AreEqual(tasks[0].Result, cancellationTokens[0].Token);
        }


        private async Task TestFuncWithCancellationAsync(CancellationTokenSource cts, ChangeNotifier notifier)
        {
            notifier.IsStarted = true;
            while (true)
            {
                if (cts.Token.IsCancellationRequested)
                {
                    break;
                }
                await Task.Delay(1000);
            }
            notifier.IsCompleted = true;
        }

        private async Task<CancellationToken> TestFuncWithCancellation2Async(CancellationTokenSource cts, ChangeNotifier notifier)
        {
            notifier.IsStarted = true;
            while (true)
            {
                if (cts.Token.IsCancellationRequested)
                {
                    break;
                }
                await Task.Delay(1000);
            }
            notifier.IsCompleted = true;
            return cts.Token;
        }

        public class ChangeNotifier
        {
            // Local data
            private bool isStarted = false;
            private bool isCompleted = false;

            // Ctor to assign data
            public ChangeNotifier() { this.isStarted = false; this.isCompleted = false; }
           
            // The event that can be subscribed to
            public event EventHandler StartStateChanged;
            public event EventHandler CompletedStateChanged;
           
            public bool IsStarted
            {
                get { return this.isStarted; }
                set
                {
                    // If the value has changed...
                    if (this.isStarted != value)
                    {
                        // Assign the new value to private storage
                        this.isStarted = value;

                        // And raise the event
                        if (this.StartStateChanged != null)
                            this.StartStateChanged(this, EventArgs.Empty);
                    }
                }
            }
           
            public bool IsCompleted
            {
                get { return this.isCompleted; }
                set
                {
                    // If the value has changed...
                    if (this.isCompleted != value)
                    {
                        // Assign the new value to private storage
                        this.isCompleted = value;

                        // And raise the event
                        if (this.CompletedStateChanged != null)
                            this.CompletedStateChanged(this, EventArgs.Empty);
                    }
                }
            }
        }
    }

Concurrent throttler

    using System;
    using System.Threading.Tasks;
    using Interfaces;

    public class ConcurrentThrottler : IThrottler
    {
        private TaskQueue queue;
        private int _concurrentRequests;

        public int ConcurrentOperations
        {
            get
            {
                return _concurrentRequests;
            }
        }

        public ConcurrentThrottler(int concurrentRequests)
        {
            Validator.IsPositive(concurrentRequests, CallerDetailsExtensions.GetMemberName(() => concurrentRequests));

            this._concurrentRequests = concurrentRequests;
            queue = new TaskQueue(concurrentRequests);
        }

        public Task<T> Enqueue<T>(Func<Task<T>> taskGenerator)
        {
            return queue.Enqueue(() => taskGenerator());
        }

        public Task Enqueue(Func<Task> taskGenerator)
        {
            return queue.Enqueue(() => taskGenerator());
        }
    }

Time based Throttler

Time based throttler;


    using System;
    using System.Threading.Tasks;
    using Interfaces;

    public class TimeboundThrottler : IThrottler
    {
        private TaskQueue queue;
        private int _requestsPerSecond;

        public int ConcurrentOperations
        {
            get
            {
                return _requestsPerSecond;
            }
        }

        public TimeboundThrottler(int requestsPerSecond)
        {
            Validator.IsPositive(requestsPerSecond, CallerDetailsExtensions.GetMemberName(() => requestsPerSecond));

            this._requestsPerSecond = requestsPerSecond;
            queue = new TaskQueue(requestsPerSecond);
        }

        public Task<T> Enqueue<T>(Func<Task<T>> taskGenerator)
        {
            TaskCompletionSource<T> tcs = new TaskCompletionSource<T>();
            var unused = queue.Enqueue(() =>
            {
                tcs.Match(taskGenerator());
                return Task.Delay(TimeSpan.FromSeconds(1));
            });
            return tcs.Task;
        }

        public Task Enqueue(Func<Task> taskGenerator)
        {
//TaskCompletionSource will attach source of Task to the edstination. So TaskCompletionSource is matched to the taskGenerator.
//when the function comes out of the queue, TaskCompletionSource will be responsible for calling the function taskGenerator which will enerate Task
            TaskCompletionSource<bool> tcs = new TaskCompletionSource<bool>();
            var unused = queue.Enqueue(() =>
            {
                tcs.Match(taskGenerator());
                return Task.Delay(TimeSpan.FromSeconds(1));
            });
            return tcs.Task;
        }
    }

Delimiter handling in sql procedure and ISOLATION LEVEL SERIALIZABLE

-- Drop Procedure View_Creation_Procedure
DECLARE @procedureExists BIT
EXEC Procedure_Exists 'View_Creation_Procedure', @exists=@procedureExists OUTPUT

IF (@procedureExists = 0)
BEGIN
-- Create a basic procedure
EXEC ('CREATE PROCEDURE View_Creation_Procedure as SELECT 1')
END

EXEC dbo.sp_executesql @statement = N'
ALTER PROCEDURE View_Creation_Procedure
  @scopeList VARCHAR(MAX),
  @entityName VARCHAR(50)
AS

DECLARE @viewExists BIT
DECLARE @viewName varchar(50)
SET @viewName = @entityName + ''Summary''
EXEC View_Exists @viewName= @viewName, @exists=@viewExists OUTPUT
-- Check if view exists or not
IF (@viewExists = 0)
    -- View does not exists
    BEGIN
  DECLARE @viewCreationStatement nvarchar(MAX)
  -- Create a basic view
  SET @viewCreationStatement = ''CREATE VIEW '' + @viewName + '' as SELECT 1 as DUMMY'';
  EXEC dbo.sp_executesql @statement =@viewCreationStatement
END

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

DECLARE @ClusterName varchar(30), @Pos int
--Format the spaces
SET @scopeList = LTRIM(RTRIM(@scopeList))+ '',''
-- Find position of first occurance of comma
SET @Pos = CHARINDEX('','', @scopeList, 1)

IF (REPLACE(@scopeList, '','', '''') <> '''')
BEGIN
  SET @viewCreationStatement = ''ALTER VIEW '' + @viewName + '' as ''
  WHILE @Pos >0
  BEGIN
     -- find first scope
     SET @ClusterName = LTRIM(RTRIM(LEFT(@scopeList, @Pos - 1)))
IF (@ClusterName <> '''')
BEGIN
    SET @viewCreationStatement = @viewCreationStatement + '' select * from '' + @entityName + ''@'' + @ClusterName + '' where SnapshotVersion =( select max(SnapshotVersion) from  '' + @entityName + ''@'' + @ClusterName + '')''
END
SET @scopeList = RIGHT(@scopeList, LEN(@scopeList) - @Pos)
-- Find position of next comma
SET @Pos = CHARINDEX('','', @scopeList, 1)
IF (@Pos > 0)
BEGIN
    -- There are more scopse, so append union
    SET @viewCreationStatement = @viewCreationStatement + '' union ''
END
  END
END

EXEC dbo.sp_executesql @statement =@viewCreationStatement

COMMIT
'

--EXEC View_Creation_Procedure 'CH1StageApp01, BN1StageApp01, AM3PrdApp01', 'Node'

Event in C#

Events:
It is basically used for subscriber/publisher model.
subscriber of any delegate function can be declared as an event. In such case, Any operation can be done on the event inside the class, however only += and -+ can be done outside the class. eg:
public delegate void PriceChangedHandler();
class{
public event PriceChangedHandler PriceChanged;
}
Here inside the class we can do any operation on the PriceChanged but outside the class we can just do += and -=;

Standard event pattern:
 Class  extends System.EventArgs. It just contains some empty static property
 Next create delegate: return type should be void. It must accept 2 parameters. First is object  type. Second is subclass of EventArgs. Its name must end in "EventHandler"
 eg public delegate void EventHandler<TEventArgs>
  (object source, TEventArgs e) where TEventArgs : EventArgs;
 Next define event: public event EventHandler<PriceChangedEventArgs> PriceChanged;
 Finally, the pattern requires that you write a protected virtual method that fires the event. The name must match the name of the event, prefixed with the word"On", and then accept a single EventArgs argument: eg : protected virtual void OnPriceChanged (PriceChangedEventArgs e)
  {
    if (PriceChanged != null) PriceChanged (this, e);
  }

  Sample program:
  using System;

public class PriceChangedEventArgs : EventArgs
{
  public readonly decimal LastPrice;
  public readonly decimal NewPrice;

  public PriceChangedEventArgs (decimal lastPrice, decimal newPrice)
  {
    LastPrice = lastPrice; NewPrice = newPrice;
  }
}

public class Stock
{
  string symbol;
  decimal price;

  public Stock (string symbol) {this.symbol = symbol;}

  public event EventHandler<PriceChangedEventArgs> PriceChanged;
  protected virtual void OnPriceChanged (PriceChangedEventArgs e)
  {
    if (PriceChanged != null) PriceChanged (this, e);
  }

  public decimal Price
  {
    get { return price; }
    set
    {
      if (price == value) return;
      OnPriceChanged (new PriceChangedEventArgs (price, value));
      price = value;
    }
  }
}

class Test
{
  static void Main(  )
  {
    Stock stock = new Stock ("THPW");
    stock.Price = 27.10M;   //1
    // register with the PriceChanged event
    stock.PriceChanged += stock_PriceChanged; //2
    stock.Price = 31.59M; //3
stock.Price = 31.59M; //4
    stock.Price = 35.69M; //5
    stock.PriceChanged -= stock_PriceChanged; //6
    stock.Price = 37.89M;//7
  }

  static void stock_PriceChanged (object sender, PriceChangedEventArgs e)
  {
    if ((e.NewPrice - e.LastPrice) / e.LastPrice > 0.1M)
      Console.WriteLine ("Alert, 10% stock price increase!");
  }
}

In this program, executino starts with main. value of Price is set at 1. Since there is price change, it will call OnPriceChanged. Inside this funciton, since PriceChanged is null, it is not doing anything. At 2, It is adding method stock_PriceChanged to PriceChanged. Notice stock_PriceChanged is static with void return type. At 3, it is again setting value of Price and which is calling OnPriceChanged. Now PriceChanged is not null. So PriceChanged is called which actually is calling stock_PriceChanged with arguments of type object and PriceChangedEventArgs(subclass of EventArgs). At 4, there is no price change, so it returns. 5 is same as 3. At 6, function stock_PriceChanged is removed from PriceChanged event. So in 7, stock_PriceChanged is not called.

If we don't need any other information other than just that event is fired, we can use EventArgs.Empty
eg: Lets modify Stock class to incorporate this:
public class Stock
{
  string symbol;
  decimal price;

  public Stock (string symbol) {this.symbol = symbol;}

  public eventEventHandler PriceChanged;

protected virtual void OnPriceChanged (EventArgs e)
  {
    if (PriceChanged != null) PriceChanged (this, e);
  }

  public decimal Price
  {
    get { return price; }
    set
    {
      if (price == value) return;
      price = value;
      OnPriceChanged (EventArgs.Empty);
    }
  }
}

C# delegate

Delegates C#

In simple words it is instance referencing to a method.

eg: Suppose there is a method int method1(int x){}

Create a delegate instance: delegate int delegateInstance(int k);

Assign this delegate instance to method1: delegateInstance instance1=method1

Now when we call this delegate method, it will actually call the method1: instance1(3), it will call method1(3).

Arithmetic can also be done on the delegate method assignment. eg:

instance1+=method2;

Now when the instance1 is called, it will call both method1 and method2 in order.

We can also do instance1-=method1. Now when instance1 is called, it will only call method2.

When a delegate method is assigned to a class method, it also maintains the reference to the method. In case of static method, it maintains null

eg:delegateInstance instance1=method1

  instance1.Target == this  //true

  instance1.Method; // Int32 method1(Int32)

  

Generic delegate:

public delegate T Transformer<T> (T arg);

public class Util

{

  public static void Transform<T> (T[] values,Transformer<T> t)

  {

    for (int i = 0; i < values.Length; i++)

      values[i] = t(values[i]);

  }

}

class Test

{

  static void Main(  )

  {

    int[] values = new int[] {1, 2, 3};

    Util.Transform(values, Square);      // dynamically hook in Square

    foreach (int i in values)

      Console.Write (i + "  ");           // 1   4   9

  }

  static int Square (int x) { return x * x; }

}

A delegate design may be a better choice than an interface design if one or more of these conditions are true:

The interface defines only a single method

Multicast capability is needed(calling more than one methods at a time. Hit: +=)

The listener needs to implement the interface multiple times  

Assigning one delegate instance to another results in compile time error. They are incompatible to each other.

D d1=m1;

D d2=d1; //Compile time error

Two delegates pointing to same method are equal.

  delegate void d();

  D d1=m1;

  D d2=m1;

  d1==d2; //true

A delegate method can be assigned with method instance which takes more wide parameter. Eg if delegate is for parameter class c1. We can assign a method which accepts super class of class c1. This is called contravariance.

delegate void SpecificDelegate (SpecificClass s);

class SpecificClass {}

class Test

{

  static void Main(  )

  {

    SpecificDelegate specificDelegate = GeneralHandler;

    specificDelegate (new SpecificClass(  ));

  }

  static void GeneralHandler(object o)

  {

    Console.WriteLine(o.GetType(  )); // SpecificClass

  }

}

the return type of a delegate can be super class of the return type of its target method. This is called covariance.

eg delegate Asset DebtCollector(  );

class Asset {}

class House : Asset {}

class Test

{

  static void Main(  )

  {

     DebtCollector d = new DebtCollector (GetHomeSweetHome);

     Asset a = d(  );

     Console.WriteLine(a.GetType(  )); // House

  }

  static House GetHomeSweetHome() {return new House(  ); }

}

Reference: https://msdn.microsoft.com/en-us/library/orm-9780596527570-03-04.aspx