SQL Server CLR: Assembly to Read/Write from/to REST API

In this example we’ll create SQL Server Common Language Runtime (CLR) to read/write from/to REST API.

Technology used:

Visual Studio 2019 (C# Windows .NET Class Library)
SQL Server 2019 (on premises)

Authentication: Basic (base64 encoded Username:Password)

Requests:

HTTP	SQL
GET	SELECT
POST	INSERT

Table of Contents

Visual Studio 2019

Create new C#, Windows, Class Library (.NET Framework) project.

Name: SQL_CLR_REST_API:

Add Newtonsoft.Json Nuget package:

Rename Class.1.cs to StoredProcedures.cs:

using Microsoft.SqlServer.Server;
using Newtonsoft.Json;
using System;
using System.Data.SqlTypes;
using System.IO;
using System.Net;
using System.Text;

public class StoredProcedures
{
    [SqlProcedure]

    #region GET
    public static void GET
    (
        SqlString url
        , SqlString username
        , SqlString password
        , out SqlChars returnValue
        , out SqlChars status
        , out SqlChars error
    )
    {
        string _returnValue = string.Empty;
        string _status = string.Empty;
        string _error = string.Empty;
        string _authorization = Convert.ToBase64String(Encoding.Default.GetBytes(((string)username + ":" + (string)password)));

        try
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create((string)url);
            request.ContentType = "application/json";
            request.Method = "GET";
            request.Headers["Authorization"] = "Basic " + _authorization;

            HttpWebResponse response = (HttpWebResponse)request.GetResponse();
            Encoding enc = Encoding.GetEncoding("utf-8");
            StreamReader responseStream = new StreamReader(response.GetResponseStream(), enc);
            _returnValue = responseStream.ReadToEnd();
            _status = response.StatusCode.ToString();
            _error = "No Error";

            response.Close();
            responseStream.Dispose();
        }
        catch (WebException ex)
        {
            if (ex.Status == WebExceptionStatus.ProtocolError)
            {
                var response = ex.Response as HttpWebResponse;
                if (ex.Response != null)
                { _status = "(" + (int)response.StatusCode + ") " + response.StatusCode; }
                else
                { _status = "No HTTP Status available"; }
            }
            else
            { _status = "No HTTP Status available"; }

            if (ex.Response != null)
            {
                _error = "Error (web exception, response generated): "
                        + new StreamReader(ex.Response.GetResponseStream()).ReadToEnd();
            }
            else
            {
                _error = "Error (web exception, no response): "
                        + "; " + ex.Message
                        + "; " + ex.StackTrace
                        + "; " + ex.TargetSite
                        + "; " + ex.Status;
            }
        }
        finally
        {
            returnValue = new SqlChars(new SqlString(_returnValue));
            status = new SqlChars(new SqlString(_status));
            error = new SqlChars(new SqlString(_error));
        }
    }
    #endregion

    #region POST
    public class Person
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public decimal PayRate { get; set; }
        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }
    }

    public static void POST
    (
        SqlString url
        , SqlString username
        , SqlString password
        , SqlString firstName
        , SqlString lastName
        , SqlDecimal payRate
        , SqlDateTime startDate
        , SqlDateTime endDate
        , out SqlChars returnValue
        , out SqlChars status
        , out SqlChars error
    )
    {
        string _returnValue = string.Empty;
        string _status = string.Empty;
        string _error = string.Empty;
        string _authorization = Convert.ToBase64String(Encoding.Default.GetBytes(((string)username + ":" + (string)password)));

        try
        {
            Person person = new Person
            {
                FirstName = firstName.Value,
                LastName = lastName.Value,
                PayRate = payRate.Value,
                StartDate = startDate.Value,
                EndDate = endDate.Value
            };

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create((string)url);
            request.ContentType = "application/json";
            request.Method = "POST";

            request.Headers["Authorization"] = "Basic " + _authorization;

            using (var stream = new StreamWriter(request.GetRequestStream()))
            {
                stream.Write(JsonConvert.SerializeObject(person));
                stream.Flush();
                stream.Close();
            }

            HttpWebResponse response = (HttpWebResponse)request.GetResponse();
            Encoding enc = Encoding.GetEncoding("utf-8");
            StreamReader responseStream = new StreamReader(response.GetResponseStream(), enc);

            _returnValue = responseStream.ReadToEnd();
            _status = response.StatusCode.ToString();
            _error = "No Error";

            response.Close();
        }
        catch (WebException ex)
        {
            if (ex.Status == WebExceptionStatus.ProtocolError)
            {
                var response = ex.Response as HttpWebResponse;
                if (ex.Response != null)
                { _status = "(" + (int)response.StatusCode + ") " + response.StatusCode; }
                else
                { _status = "No HTTP Status available"; }
            }
            else
            { _status = "No HTTP Status available"; }

            if (ex.Response != null)
            {
                _error = "Error (web exception, response generated): "
                        + new StreamReader(ex.Response.GetResponseStream()).ReadToEnd();
            }
            else
            {
                _error = "Error (web exception, no response): "
                        + "; " + ex.Message
                        + "; " + ex.StackTrace
                        + "; " + ex.TargetSite
                        + "; " + ex.Status;
            }
        }
        finally
        {
            returnValue = new SqlChars(new SqlString(_returnValue));
            status = new SqlChars(new SqlString(_status));
            error = new SqlChars(new SqlString(_error));
        }
    }
    #endregion
}

Build Solution (Ctrl+Shift+B)

Windows File Explorer

Copy all the .dll (Newtonsoft.Json.dll, SQL_CLR_REST_API.dll) from <Project Folder>/bin/Debug to C:\Temp.

SQL Server

Create database:

USE [master];
GO
IF (DB_ID(N'SQL_CLR_REST_API') IS NOT NULL)
  BEGIN
    ALTER DATABASE SQL_CLR_REST_API SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SQL_CLR_REST_API;
  END
GO
CREATE DATABASE SQL_CLR_REST_API;
GO

USE SQL_CLR_REST_API;
GO

ALTER DATABASE SQL_CLR_REST_API
SET TRUSTWORTHY ON;
GO

USE SQL_CLR_REST_API;
GO

IF (SCHEMA_ID('custom') IS NULL)
BEGIN EXEC sp_executesql N'CREATE SCHEMA custom;'; END
GO

Create Assembly:

USE SQL_CLR_REST_API;
GO

CREATE ASSEMBLY SQL_CLR_REST_API
FROM 'C:\Temp\SQL_CLR_REST_API.dll'
	WITH PERMISSION_SET = UNSAFE;
GO

Some referenced assemblies are missing:

System.Runtime.Serialization (version=4.0.0.0; publickeytoken=b77a5c561934e089)
System.ServiceModel.Internals (version=4.0.0.0; publickeytoken=31bf3856ad364e35)
SMDiagnostics (version=4.0.0.0; publickeytoken=b77a5c561934e089)

Paste them in C:\Temp from (on my computer) the following locations:

C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.Runtime.Serialization\v4.0_4.0.0.0__b77a5c561934e089
C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.ServiceModel.Internals\v4.0_4.0.0.0__31bf3856ad364e35
C:\Windows\Microsoft.NET\assembly\GAC_MSIL\SMDiagnostics\v4.0_4.0.0.0__b77a5c561934e089

Create Stored Procedure “GET”:

USE SQL_CLR_REST_API;
GO

CREATE PROCEDURE [custom].SQL_CLR_REST_API_GET
	@URL NVARCHAR(1024)
	, @Username NVARCHAR(128)
	, @Password NVARCHAR(128)
	, @ReturnValue NVARCHAR(MAX) OUTPUT
	, @Status NVARCHAR(MAX) OUTPUT
	, @Error NVARCHAR(MAX) OUTPUT
WITH EXECUTE AS CALLER
AS

EXTERNAL NAME [SQL_CLR_REST_API].[StoredProcedures].[GET];
GO

If you got an error message Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option., execute:

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

Test Stored Procedure “GET”:

USE SQL_CLR_REST_API;
GO

DECLARE
	@ReturnValue NVARCHAR(MAX)
	, @Status NVARCHAR(MAX)
	, @Error NVARCHAR(MAX);
	
EXEC [custom].SQL_CLR_REST_API_GET
	@URL = N'http://localhost:60252/api/Person'
	, @Username = N'Jack'
	, @Password = N'Jones'
	, @ReturnValue = @ReturnValue OUT
	, @Status = @Status OUT
	, @Error = @Error OUT;

SELECT
  @ReturnValue AS [@ReturnValue]
  , @Status AS [@Status]
  , @Error AS [@Error];

Create Stored Procedure “POST”:

USE SQL_CLR_REST_API;
GO

CREATE PROCEDURE [custom].SQL_CLR_REST_API_POST
	@URL NVARCHAR(1024)
	, @Username NVARCHAR(128)
	, @Password NVARCHAR(128)
  , @FirstName NVARCHAR(128)
  , @LastName NVARCHAR(128)
  , @PayRate DECIMAL(19, 4)
	, @StartDate DATETIME
	, @EndDate DATETIME
  , @ReturnValue NVARCHAR(MAX) OUTPUT
	, @Status NVARCHAR(MAX) OUTPUT
	, @Error NVARCHAR(MAX) OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQL_CLR_REST_API].[StoredProcedures].[POST];
GO

Test Stored Procedure “POST”:

USE SQL_CLR_REST_API;
GO

DECLARE
	@ReturnValue NVARCHAR(MAX)
	, @Status NVARCHAR(MAX)
	, @Error NVARCHAR(MAX);

EXEC [custom].SQL_CLR_REST_API_POST
	@URL = N'http://localhost:60252/api/Person'
	, @Username = N'Jack'
	, @Password = N'Jones'
  , @FirstName = N'Ali'
  , @LastName = N'Baba'
  , @PayRate = 13.45
	, @StartDate = '1984-08-11 00:00:00.000'
	, @EndDate = '2003-12-14 00:00:00.000'
	, @ReturnValue = @ReturnValue OUT
	, @Status = @Status OUT
	, @Error = @Error OUT;

SELECT
  @ReturnValue AS [@ReturnValue]
  , @Status AS [@Status]
  , @Error AS [@Error];

Verify the “POST” execution:

Keep it simple :-)

SQL Server CLR: Assembly to Read/Write from/to REST API

Visual Studio 2019

Windows File Explorer

SQL Server

AUTHOR

Peter Lalovsky

Leave a comment Cancel reply