آموزش گام به گام ساخت Stored Procedure CLR

در این مقاله با پیروی از گام های زیر Stored Procedure CLR را میسازیم مقاله ای به صورت قدم به قدم مراحل ساخت را برسی میکند.همراه من باشید در ادامه

ویژوال استودیوی ۲۰۱۰ یا بالاتر را اجرا کنید.

یک پروژه ی جدید بسازید.

پروژه ی پایگاه داده را انتخاب کنید.

پروژه را بسازید که به یک پایگاه داده مرجع نیز اجتیاج دارید.

در Solution Explorer بر روی نام پروژه راست کلیک کنید و گزینه ی افزودن را انتخاب کنید.

روش ذخیره سازی را انتخاب کنید

فایل جدیدی به نام “myTestStoredProcedure” را بیافزایید.

فایل جدید مانند زیر خواهد بود :

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 void myTestStoredProcedure()   
{   
//Right some code here   
}   
};

من در حال نوشتن کدی برای سه Stored Procedure زیر هستم :

myTestStoredProcedure : چاپ یک پیام به آسانی

spGetRolesList : ردیف های جدول را نمایش دهد

spGetEmployeeList : نمایش ردیف های جدول برای گروه سنی به خصوصی

کد های زیر ساخت یک Stored Procedure CLR با استفاده از ویژوال استودیو را نشان میدهد :

using System;   
using System.Data;   
using System.Data.SqlClient;   
using System.Data.SqlTypes;   
using Microsoft.SqlServer.Server;   
public partial class StoredProcedures    
{   
///     
/// Prints a Message     
///     
[Microsoft.SqlServer.Server.SqlProcedure]   
public static void myTestStoredProcedure()    
{   
//Simple proc     
SqlPipe objSqlPipe = SqlContext.Pipe;   
objSqlPipe.Send("Hi! I am simple CLR PROC");   
}   
///     
/// Proc to Show Rows of [EmployeeDB]..[Roles] table     
///     
 [Microsoft.SqlServer.Server.SqlProcedure]   
public static void spGetRolesList()    
{   
//It returns rows from Roles table     
SqlConnection conn = new SqlConnection();   
conn.ConnectionString = "Context Connection=true";   
SqlCommand cmd = new SqlCommand();   
cmd.Connection = conn;   
cmd.CommandText = @   
"Select * from [dbo].[Roles] Order By HireDate";   
conn.Open();   
SqlDataReader sqldr = cmd.ExecuteReader();   
SqlContext.Pipe.Send(sqldr);   
sqldr.Close();   
conn.Close();   
}   
///     
/// It shows rows from Employee table on basis of supplied age     
///     
/// a specified age     
 [Microsoft.SqlServer.Server.SqlProcedure]   
public static void spGetEmployeeList(Int32 intAge)    
{   
//It returns rows from Employee table on basis of supplied age     
SqlConnection conn = new SqlConnection();   
conn.ConnectionString = "Context Connection=true";   
SqlCommand cmd = new SqlCommand();   
cmd.Connection = conn;   
conn.Open();   
cmd.CommandText = "Select * from [dbo].[Employees] Where Age >=@intAge Order By Age";   
SqlParameter paramAge = new SqlParameter();   
paramAge.Value = intAge;   
paramAge.Direction = ParameterDirection.Input;   
paramAge.DbType = DbType.Int32;   
paramAge.ParameterName = "@intAge";   
cmd.Parameters.Add(paramAge);   
SqlDataReader sqldr = cmd.ExecuteReader();   
SqlContext.Pipe.Send(sqldr);   
sqldr.Close();   
conn.Close();   
}   
};

ساخت و گسترش اسمبلی

Ctrl+Shift+B را بفشارید یا گزینه ی Build را انتخاب کنید.

اگر پروژه با موفقیت ساخته شد حال زمان گسترش اسمبلی در راهنمای SQLServer است.

بر روی نام پروژه در Solution Explorer کلیک کنید.

بر روی گسترش کلیک کنید.

نوار وضعیت را چک کنید.

اگر گسترش با موفقیت انجام شد شما میتوانید روش CLR را در Server Explorer چک کنید.

EmployeeDB node را Expand کنید.

Assembly node را نیز Expand نمایید.

اجرای Stored Procedure CLR

از Server Explorer ویژوال استودیو استفاده کنید.

AStepAheadProcVisual node را Expand نمایید.

در اینجا میتوانید یک فایل کلاس و فایل Assembly.info شامل سه روش را ببینید.

بر روی هر Stored Procedure کلیک کنید (گزینه های موجود : Open, Execute,Step Into Stored Procedure).

Open : مسیردهی به روشی خاص

Execute : اجرای روش انتخاب شده و نتایج در پنجره ی خروجی نمایش داده خواهد شد.

Step into Stored Procedure : برنامه را با روش انتخاب شده debug میکند.

استفاده از استودیوی مدیریت SQLServer

استودیوی مدیریت SQLServer تان را باز کنید (اگر که قبلا باز نکرده بودید)

کد زیر را در پنجره ی Query وارد نمایید.

Use [EmployeeDB]
Go
DECLARE @Role int
SET @Role = 28
Exec [dbo].[spGetEmployeeList] @Role
Go

کد بالا EmployeeList با کارمندان سن ۲۸ یا بالاتر را نمایش میدهد.

در عین حال روش های دیگر را اجرا میکند.

Use [EmployeeDB]
Go
Exec [dbo].[myTestStoredProcedure]
Go
Exec [dbo].[spGetRolesList]
Go
DECLARE @Role int
SET @Role = 28
Exec [dbo].[spGetEmployeeList] @Role
Go

Script های پایگاه داده

در زیر Script های جدول استفاده شده در مثال داده شده آمده است :

USE [master]
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'EmployeeDB')
BEGIN
DROP DATABASE [EmployeeDB]
CREATE DATABASE [EmployeeDB]
END
USE [EmployeeDB]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[employees]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[employees]
CREATE TABLE [dbo].[employees](
[id] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[firstname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('First Name'),
[lastname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('Last Name'),
[age] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('19'),
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Roles]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[Roles]
CREATE TABLE [dbo].[Roles](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Role] [nvarchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsRetiree] [bit] NOT NULL,
[HireDate] [datetime] NOT NULL,
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

به یاد داشته باشید :

اگر خطا دریافت کردید : اجرای کد کاربر در چارچوب .NET غیر فعال است. گزینه ی پیکربندی “clr enabled” را فعال کنید.زمان اجرای بالا با استفاده از SQLSERVER2005/2008 کد زیر را در Query Analyzer اجرا کنید.

sp_configure 'clr enabled', 1
go
reconfigure
go

همچنین میتوانید کد روش را با ویژوال استودیو Debug کنید با شروع Debugging در منوی DEBUG.