Sunday 9 September 2012

How to use dot net function into SQL Server



Step 1: Create your function in dot net using C#/VB.net





using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer; // This NameSpace Important to Add

namespace SumNum
{

    public class SumNum
    {  
        //****** this line is Importanct to write********//
        [Microsoft.SqlServer.Server.SqlFunction()]
        public static int SumNumbers(int a, int b)
        {
            return a + b;
        }
    }
}
  
Step 2: Find Your Application Dll(In My Example the DLL is SumNum.dll)

Copy You Dll file Location
Like in my example, the Dll Location is:  

D:\SumNum\SumNum\bin\Debug\SumNum.dll
 


step 3: Go to Sql Server Management Studio and write given Below queries


-- this query will enable DOT NET CLR function in your SQL SERVER 2005




sp_configure'clr enabled', 1
RECONFIGURE WITH OVERRIDE

-- Add Your DOT NET .Dll Assembly in your SQL SERVER 2005




CREATE ASSEMBLY MyDotNetFunction
FROM 'D:\SumNum\SumNum\bin\Debug\SumNum.dll'
 

-- you may also use Alter Command to change in Assembly function





create FUNCTION [dbo].[MySum](@a [int], @b [int])
RETURNS [int] WITH EXECUTE AS CALLER
AS EXTERNAL NAME [MyDotNetFunction].[SumNum.SumNum].[SumNumbers]

Step 4 : Now You may use this function in your SQL Queries as see given below:




select dbo.MySum(id,Head_id) as MyFunctionValue from emp


OutPut:


Cheers, Thank You



1 comment:

  1. This is really fantastic approach when you stuck in any critical situation in Sql server..Great Job Jeetu !!!!

    ReplyDelete