Problem Statement
A requirement where we need to get a data from other sources (REST-based Web Service) and sync with on-premises (local) database. This should be done using a SQL Server Stored Procedure.

Solution
Before going to the implementation will understand what is C# SQL CLR is all about and how can we use it.

Since .NET Framework 2.0, we have a SQL CLR which is a small part of the complete Framework CLR. This SQL CLR is lightweight and supports a limited set of base class libraries of .NET Framework.

Using this SQL CLR, we can write any complex logic involving any database objects. This logic written in SQL CLR can be called from either Stored Procedure, Functions, Triggers etc.., We can use C# as the language to write these complex logic on SQL CLR.

Beginning with SQL Server 2005, SQL Server has a list of supported .NET Framework libraries, which have been tested to ensure that they meet reliability and security standards for interaction with SQL Server. Supported libraries do not need to be explicitly registered on the server before they can be used in your code; SQL Server loads them directly from the Global Assembly Cache (GAC).

The libraries/namespaces supported by CLR integration in SQL Server are:

  • CustomMarshalers
  • Microsoft.VisualBasic
  • Microsoft.VisualC
  • mscorlib
  • System
  • System.Configuration
  • System.Data
  • System.Data.OracleClient
  • System.Data.SqlXml
  • System.Deployment
  • System.Security
  • System.Transactions
  • System.Web.Services
  • System.Xml
  • System.Core
  • System.Xml.Linq

If you want to use any other libraries other than the above, need to explicitly register with SQL Server. Will understand more how to do it later in this article as we go.

One question we might be getting like for what kind of logics we need to go for this feature and how about performance? Yes, this feature might be used for

  • The team who are very good in .NET programming and little knowledge on T-SQL.
  • When we need to get data from other external sources like Web or REST API Services and vice versa.

There are many pros and cons using but I can say this feature is very good for calling other resources and getting or sending data to it.

From a performance point of view, its all depends on the logic we are implementing as when we see T-SQL is native and it uses the same memory place, whereas SQL CLR uses its dedicated memory. T-SQL is an interpreter and CLR uses compiler where the compiler will be faster.

I think we had a good understanding about SQL CLR and where exactly we need to use this feature. Now, will go to real action and start implementing it.

Implementation 
Firstly, we need to create a new SQL Server database project by selecting default installed SQL Templates  from Visual Studio as below are screenshots

Now you will be landed to the newly created project with no files presented.

We need to create a new  SQL CLR stored Procedure as below

Once created a new SQL CLR Stored Procedure, here you need to write any logic as per your requirement. But here our requirement is to call the external service from where we will fetch the information and return the same as the output.

For this, let assume we are having a REST API web service (http://techxposer.com/CurrencyRate/INR) where it returns all the INR rate against each CCurrency

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void spGetINRRates()
    {
        HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://techxposer.com/CurrencyRate/INR");

        request.Method = "GET";
        request.ContentLength = 0;
        request.Credentials = CredentialCache.DefaultCredentials;
        request.ContentType = "application/xml";
        request.Accept = "application/xml";

        using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
        {
            using (Stream receiveStream = response.GetResponseStream())
            {
                using (StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8))
                {
                    string strContent = readStream.ReadToEnd();
                    XmlDocument xdoc = new XmlDocument();
                    xdoc.LoadXml(strContent);

                    //Main Logic Begins here
                    SqlPipe pipe = SqlContext.Pipe;
                    SqlMetaData[] cols = new SqlMetaData[2];
                    cols[0] = new SqlMetaData("CurrencyName", SqlDbType.NVarChar, 1024);
                    cols[1] = new SqlMetaData("Amount", SqlDbType.Money);

                    foreach(XmlNode xnCurrency in xdoc.DocumentElement.ChildNodes)
                    {
                        SqlDataRecord record = new SqlDataRecord(cols);
                        pipe.SendResultsStart(record);

                        record.SetSqlString(0, new SqlString(xnCurrency.Attributes["CurrencyName"].Value));
                        record.SetSqlMoney(1, new SqlMoney(decimal.Parse(xnCurrency.Attributes["Amount"].Value)));
                    }

                    pipe.SendResultsEnd();
                }
            }
        }
        
    }
}

In the above code, we try to fetch the records from the REST API in the format of XML and parse it to create a result set.

The main points from the above code are

  • The SqlPipe object is used to send the results back to the caller.  The SqlContext object is automatically available and provides the SqlPipe object.
  • The SqlMetaData class is used to specify a column in the result set.  We specify the column name, type, and size.  We are having two columns in this case but you can return any multiple columns as needed.
  • The SqlDataRecord class is used to populate a single row in the result set.  It is initialized with the array of SqlMetaData objects (i.e. the columns).  The SetSqlString and SetSqlMoney methods are called to assign a value to each column based on the ordinal number (i.e. index in the array of SqlMetaData objects).
  • The SendResultsRow method of SqlPipe sends a single row back to the caller via the SqlDataRecord object.
  • The SendResultsEnd method of SqlPipe is used to indicate the end of the result set.

Note: Here we used XML as out format instead of JSON because we cannot use any JSON Serializer as for this we need to reference Newtonsoft JSON component and which is not supported by SQL Server by default.

Now build the application a new DLL component will be created with this we are done from the C# side. Now we need to get back to SQL Server end to register and use this component.

To call this component as a normal SQL Stored Procedure, we need to enable CLR on SQL Server and which will be disabled by default. Following are the statements will do this

 

sp_configure 'clr enabled', 1;   
GO   
RECONFIGURE;   
GO

Similarly, we need to make sure to enable trustworthy on given database. Following is the statement will enable it. 

alter database [techxposerDatabase] set trustworthy on;

Once we execute the above two sets of statements we are ready to register the component as an external assembly to the selected database as follows  

create ASSEMBLY PorticusSalesForceService 
        FROM 'D:\CurrencyApps\CurrencyService.dll' 
        WITH PERMISSION_SET = UNSAFE ;

As once we created the external assembly we need to create a Stored Procedure to call this assembly as we do in normal C# classes. 

CREATE PROCEDURE dbo.spGetINRRates  
AS EXTERNAL NAME CurrencyService.StoredProcedures.spGetINRRates;

That’s it.. Once SP is created you can run this stored procedure as you do with other normal SP’s. 

EXEC dbo.spGetINRRates()

Now you will see the results on your result grid.

Hope you understand the way we can call a REST service in C# SQL CLR in a SQL Server Database Project.

Happy Coding 🙂