Calling REST API Service From SQL Server Using C# SQL CLR

Problem Statement

 

There is a requirement where we need to get 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.

In this post, you'll learn how to call a REST Service from a SQL Server stored procedure.

 

Solution

 

Before going to the implementation we will understand what 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, you need to explicitly register with SQL Server. We will understand more about how to do it later in this article as we go.

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

• The teams who are very good in .NET programming and little knowledge of 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 to using this 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, it 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 a compiler where the compiler will be faster.

 

I think we had a good understanding of SQL CLR and where exactly we need to use this feature. Now, we 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 on the newly created project with no files presented.

 

 

 

We need to create a new SQL CLR stored procedure as below.

 

 

 

Once you've 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 us assume we are having a REST API web service (http://c-sharpcorner.com/CurrencyRate/INR) where it returns all the INR rates against each currency

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void spGetINRRates()
    {
        HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://c-sharpcorner.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 our format instead of JSON because we cannot use any JSON Serializer, as for this we need to reference the Newtonsoft JSON component which is not supported by SQL Server by default.

 

Now build the application and 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 a normal SQL Stored Procedure, we need to enable CLR on SQL Server which will be disabled by default. Following are the statements that will do this.

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

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

alter database [csh
arpDatabase] 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 create 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 SPs.

EXEC dbo.spGetINRRates()

Now you will see the results on your result grid.

 

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

 

Happy Coding :)