Skip to content

Instantly share code, notes, and snippets.

@JahsonKim
Last active May 17, 2023 12:45
Show Gist options
  • Save JahsonKim/05e6af7744f2d7ef814e5ed331419db5 to your computer and use it in GitHub Desktop.
Save JahsonKim/05e6af7744f2d7ef814e5ed331419db5 to your computer and use it in GitHub Desktop.
C# SQL Server CLR example.
using Microsoft.SqlServer.Server;
//------------------------------------------------------------------------------
// <copyright file="CSSqlClassFile.cs" company="Microsoft">
// Copyright (c) Microsoft Corporation. All rights reserved.
// </copyright>
//------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.Net;
using System.Text;
public class CLRExample
{
/*
*params
*postdata-post data from SQL server. can be a single item or JSON String
*returnVal- The response returned form the Web API to SQL server
*
*/
[Microsoft.SqlServer.Server.SqlProcedure]
public static void postData(SqlString postData, out SqlString returnval)
{
string feedData = string.Empty;
try
{
string API_END_POINT = "YOUR END POINT URL";
HttpWebRequest request = null;
HttpWebResponse response = null;
Stream stream = null;
StreamReader streamReader = null;
request = (HttpWebRequest)WebRequest.Create(url);
/* If you are using a proxy server then set ypur proxy server ip and port.*/
WebProxy proxyObject = new WebProxy("proxy server ip and port", true);
proxyObject.Credentials = CredentialCache.DefaultCredentials;
request.Proxy = proxyObject;
request.UserAgent = "CLR web client on SQL Server";
request.Method = "POST";
request.ContentType = "application/x-www-form-urlencoded";
//if you have any headers add them to the request.
// request.Headers.Add("header key", "value");
byte[] byteArray = Encoding.UTF8.GetBytes(postData.ToString());
request.ContentLength = byteArray.Length;
Stream dataStream = request.GetRequestStream();
dataStream.Write(byteArray, 0, byteArray.Length);
dataStream.Close();
response = (HttpWebResponse)request.GetResponse();
stream = response.GetResponseStream();
streamReader = new StreamReader(stream);
feedData = streamReader.ReadToEnd();
response.Close();
stream.Dispose();
streamReader.Dispose();
}
catch (Exception ex)
{
SqlContext.Pipe.Send(ex.Message.ToString());
}
returnval = feedData;
}
}
CREATE PROCEDURE [dbo].[postData]
@postData NVARCHAR(4000),
@returnval NVARCHAR(2000) OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLRAssembly].[CLRExample].[postData]
--After creating the clrExample.cs class in visual studio generate a dll file that you add into your
--SQL server with the name CLRAssembly.
--this name depends on the name of your project its not neccessarily CLRAssembly.
--To add the assembly in your database expand the database->Programmatically->Assemblies->Right Click -> Add Assembly.
--Navigate to the location of your
--assembly file then click Ok to add the assembly.
--[CLRExample]-> name of the class in clrExample.cs
--[postData]->name of your method.
--In order to use your assembly you may need to set the UNSAFE/ UNRESTRICTED permission while adding it in your database.
--This requires
--asymetric key which you can generate using the following query.
USE MASTER
GO
CREATE
ASYMMETRIC KEY CLRAssembly
FROM EXECUTABLE FILE = N'PATH TO YOUR DLL \CLRAssembly.dll'
CREATE LOGIN [ClrPermissionsLogin]
FROM ASYMMETRIC KEY CLRAssembly;
GRANT UNSAFE ASSEMBLY TO [ClrPermissionsLogin];
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment