Thursday, June 28, 2007

CLR INTEGRATION IN SQL SERVER 2005

CLR Integration

T-SQL has been the conventional method of writing database objects such as stored procedures, triggers, aggregates, and so forth. SQL Server 2005 introduces a new array of possibilities because of its tight integration with the .NET Framework. It allows you to write stored procedures, triggers, and other such objects in a .NET-compliant language such as C# and VB.NET, compile them as a dynamic link library, and register them inside SQL Server.

The .NET Managed code provides 5 types of user defined SQL Server Routines :

User-Defined Procedures

User-Defined Triggers

User-Defined Functions

User-Defined Types

User-Defined Aggregates

Steps for Creating CLR Stored Procedures

1. Open VS 2005 IDE. Goto File->New Project-Choose Language->Database->Sql Server Project

2. Establish connection to A DATABASE in SQL SERVER 2005

Type the following code:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static int PrintMessage(string msg)

{

int i = 0;

try

{

SqlContext.Pipe.Send(msg);

}

catch (Exception ex)

{

i = 1;

SqlContext.Pipe.Send(ex.Message);

}

return i;

}

[Microsoft.SqlServer.Server.SqlProcedure]

public static void InsertUser(SqlString username, SqlString password)

{

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

string query = "INSERT INTO dbo.tbl_User(Username,Password) VALUES(@Username,@Password)";

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

try

{

SqlParameter[] sqlParams = new SqlParameter[2];

sqlParams[0] = new SqlParameter("@Username", username);

sqlParams[1] = new SqlParameter("@Password", password);

insertCommand.Parameters.AddRange(sqlParams);

cn.Open();

insertCommand.ExecuteNonQuery();

}

catch (Exception ex)

{

SqlContext.Pipe.Send(ex.Message);

}

finally

{

cn.Close();

}

}

}

}

[Microsoft.SqlServer.Server.SqlProcedure]

public static void GetUser(SqlInt32 userid)

{

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

string query = "SELECT Username,Password FROM dbo.tbl_User WHERE UserId = @UserId";

using (SqlCommand selectCommand = new SqlCommand(query, cn))

{

try

{

SqlParameter[] sqlParams = new SqlParameter[1];

sqlParams[0] = new SqlParameter("@UserId", userid);

selectCommand.Parameters.AddRange(sqlParams);

cn.Open();

//SqlDataReader dr = selectCommand.ExecuteReader();

//SqlContext.Pipe.Send(dr);

SqlContext.Pipe.ExecuteAndSend(selectCommand);

}

catch (Exception ex)

{

SqlContext.Pipe.Send(ex.Message);

}

finally

{

cn.Close();

}

}

}

}

};

3. Build the Project

4. Deploy the Project

5. Enable CLR Integration in SQL SERVER 2005 by typing the following command

EXEC sp_configure @configname = 'clr enabled', @configvalue = 1

RECONFIGURE WITH OVERRIDE

GO

5. Execute the stored procedures from Query Analyzer or call them from any windows/web application.

Happy Programming!!!!!!!