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!!!!!!!
