Code Mahek
  • Blog
  • About
  • Contact
  • Random Clicks
  • Resume
  • Ava Chat Bot

Fragrance
​of Programming

Execute C# Methods from SQL Server

9/2/2017

Comments

 
Recently I had a requirement in which I need to call a .NET library class method from SQL Server Stored Procedure and Microsoft SQL Server does provide that feature. Basically It was kind of string manipulation and we also used for calculating the numbers for which the algorithm was defined in .Net function. For example in our case we need to get the amount with GST included.

​Creating the Class Library
First step is create the .Net class library and we have to make sure the target framework supports the Microsoft SQL Server feature of calling the CLR methods. I have created a class library targeting .net framework 4.5 and will be using the class library in SQL Server 2014.

​First thing, we need to ensure that the method we need to call from SQL Server is marked as static and also we need to decorate the function with Microsoft.SqlServer.Server.SqlProcedure attribute. Below is my code which I am using.

​Register the Class Library in SQL Server
Once the class library is finalized and ready to execute from SQL Server, you need to execute some basic SQL script before utilizing it.
Register Class Library in SQL Server

Once the script has finished executing, you will find the registered assembly under the Database\Programmability\Assemblies node in SQL Server Management Studio. If you look under “Database\Programmability\Functions”, you'll find the method you registered earlier. You can now call your method as a standard T-SQL function call.
Comments
comments powered by Disqus

    RSS Feed

    Categories

    All
    Azure
    C#
    Cloud
    CodeProject
    CSharp
    DotNet Core
    Jetbrains
    .NET
    Rider
    Source Generators
    SQL
    Tech News
    Ubuntu
    Visual Studio 2015
    Visual Studio 2019
    Windows
    Windows 10

    Archives

    May 2020
    February 2019
    January 2019
    December 2018
    May 2018
    December 2017
    November 2017
    October 2017
    September 2017
    July 2017
    May 2017
    April 2017
    May 2015
    April 2015
    March 2015

    View my profile on LinkedIn

Location

Contact Us

    Subscribe Today!

Submit
  • Blog
  • About
  • Contact
  • Random Clicks
  • Resume
  • Ava Chat Bot