In this blog post, you will learn how to call the C# function from Sql Server 2019. Sometimes we have a requirement like execute C# methods from SQL Server Stored Procedures or Triggers and Microsoft SQL Server does provide that feature.

• Keep reading on Interview Questions on SQL Server

Execute C# Method from SQL Server

Let’s get started by creating a .Net Class library and we have to make sure the target framework supports the Microsoft SQL Server feature of calling the CLR methods. In the following example, I have created a class library targetting .Net Framework 4.7.2 and will be using the class library methods in SQL Server 2019.

Make sure that the C# function 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. Find the below source code:-

using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

namespace ClassLibrarySQL
{
    public class Class1
    {
        public const double gstTax = 0.10;

        [SqlProcedure]
        public static SqlDouble AddTax(SqlDouble originalAmount)
        {
            SqlDouble taxAmount = originalAmount * gstTax;

            return originalAmount + taxAmount;
        }
    }
}

Now, you need to register the above-created class library in the SQL Server by executing the below SQL script.

Use ClassLibrarySQL
GO

Create Assembly ClassLibrarySQL from 'F:\My Development\ClassLibrarySQL\ClassLibrarySQL\bin\Debug\ClassLibrarySQL.dll' 
GO

CREATE FUNCTION AddGstTax(@inputOne float)
RETURNS [float] WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS 
EXTERNAL NAME [ClassLibrarySQL].[ClassLibrarySQL.Class1].[AddTax]
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE;
EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;
Go

SELECT dbo.AddGstTax(10.2);

Download Source Code and SQL Script

Conclusion

I hope you liked this article on the call C# function from SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

The post How to call C# function from SQL Server appeared first on DotNetTec.