Reader Level:
Articles

Writing Managed User Defined Function using C#

By Laishram Priyokumar Singh on February 22, 2007
This article is trying to explain the simple and required steps that are require starting the creation of Manage User Defined Functions using C#.
  • 0
  • 0
  • 33512

Introduction:

With the integration of the CLR with SQL Server 2005, we can create database objects using modern object-oriented languages like VB.NET and C#.

This article is trying to explain the simple and required steps that are require starting the creation of Manage User Defined Functions using C#.

The Project:

We will create a Visual Studio 2005 database project for the managed Trigger.

Creating the Database project:

Open Microsoft Visual Studio 2005 and create a SQL Server Project.

File->New->Project->Database
 

Adding a database reference:

Now it will ask for a database reference. Add one.



Add a User Defined Function:

Right click on the Project and add a User Defined Function.

The file Function1.cs:

Past following lines to the file Function1.cs.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

 

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlInt64 Function1(SqlInt32 a, SqlInt32 b)

    {

        // Put your code here

        // return new SqlString("Hello");

        return (a + b);

    }
};

Deploy the User Defined Function:

Build the project and then deploy it.

Test the Trigger:

Make sure the clr is enabled with your SQL Server by running the following sql.

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;
GO

Now execute the function with two integer parameter.



Function Defined!!!!

Laishram Priyokumar Singh

MCSD .NET and Brainbench Advance Java certified Team Leader. Working with Wesley Clover Communication Solution and well experienced in J2EE  and .NET technologies with strong RDBMS knowledge.

COMMENT USING

Trending up