SQL Server CLR全攻略

最新推荐文章于 2022-08-23 08:33:21 发布

物联网-云

最新推荐文章于 2022-08-23 08:33:21 发布

阅读量1k

点赞数

文章标签： sql server 2008

CLR介绍和配置

Microsoft SQL Server 现在具备与 Microsoft Windows .NET Framework 的公共语言运行时 (CLR) 组件集成的功能。CLR 为托管代码提供服务，例如跨语言集成、代码访问安全性、对象生存期管理以及调试和分析支持。对于 SQL Server 用户和应用程序开发人员来说，CLR集成意味着您现在可以使用任何.NET Framework 语言（包括 MicrosoftVisual Basic .NET 和 MicrosoftVisual C#）编写存储过程、触发器、用户定义类型、用户定义函数（标量函数和表值函数）以及用户定义的聚合函数。

下面列出了这一集成的其中一些主要优点：

更好的编程模型。

改进了安全和安全性。

能够定义数据类型和聚合函数。

通过标准化环境简化了开发。

具备改善性能和可扩展性的潜力。

既然CLR这么灵活，那么我们如何在T-SQL和CLR之间做出选择呢？一般情况下，我们当以T-SQL为主，在下面几种情况下，应该考虑使用CLR：

SQL中涉及大量的逻辑判断和逻辑运算。比如需要在数据库级别自定义加密算法，解密算法等。

T-SQL无法处理需求。比如需要在SQL中进行正规表达式的判断等。

逻辑判断过于复杂，需要使用大量游标进行处理。这种情况一般要进行测试，因为CLR不一定比游标快，关键是看你怎么写。

下面介绍一下如何开启CLR和两种创建CLR的方法。关于如何在VS中编写CLR程序，请看后续章节。

开启CLR。

在SQL Server2005/2008里面，CLR默认是关闭的。可以使用如下SQL语句开启CLR。

sp_configure'show advanced options', 1;

RECONFIGURE;

sp_configure 'clr enabled', 1;

RECONFIGURE;

创建CLR。

方法一：使用DLL文件进行创建

CREATE ASSEMBLY AssemblyName from ‘DLLPath’

例如：CREATE ASSEMBLYAssemblyName from ‘c:\hello.dll’

方法二:使用文件16进制流进行创建

CREATE ASSEMBLY AssemblyName from 文件十六进制流

例如:CREATE ASSEMBLYAssemblyName from 0x123344……

其中0x12334……是文件c:\hello.dll的十六进制流。可以使用UltraEdit等编辑器把相关dll文件的十六进制流copy出来。

相比较，方法一简单，属于外部引用，通过创建就可以知道是哪个文件。但是dll文件路径必须可以被SQLServer所访问，且部署时dll要随SQLServer变化而变化。

方法二是把流写进SQLServer中，从一定程度上屏蔽了代码。如果此dll不大的话，推荐使用方法二来创建CLR。

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/tjvictor/archive/2009/10/25/4726933.aspx

CLR存储过程

从这一节开始呢，我们就要开始CLR的编程之旅了。在这之前，我先把本节中需要了解的两个新类SqlDataRecord和SqlMetaData，及五个新方法SqlContext.Pipe.SendResultsStart，SqlContext.Pipe.SendResultsRow，SqlContext.Pipe.SendResultsEnd，SqlContext.Pipe.Send和SqlContext.Pipe.ExecuteAndSend进行一下必要的说明，方便大家阅读后续的代码。

首先SqlDataRecord和SqlMetaData是数据集合和原数据的意思。可以简单的把SqlDataRecord理解成DataTable，把SqlMetaData理解成DataColumn。我们再向SqlDataRecord里面填充数据之前要先执行SqlContext.Pipe.SendResultStart()方法，告诉数据库下面开始填充数据，使用SqlContext.Pipe.SendResultRow方法来填充数据，填充结束后使用SqlContext.Pipe.SendResultEnd方法来结束填充。这些都是基本流程，没什么好解释的，只要照着去做就可以了。

SqlContext.Pipe.Send是向客户端发送一条结果，SqlContext.Pipe.ExecuteAndSend是执行一条语句。

下面我将用几个实际的简单例子来说明如何使用这几个方法。

1.使用SqlContext.Pipe.Send构建无参无返回值的存储过程

[Microsoft.SqlServer.Server.SqlProcedure]

public static void USP_SayHello()

{

SqlContext.Pipe.Send("USP:Hello TJVictor!");

}

2.使用SqlContext.Pipe.Send构建带参无返回值的存储过程

[Microsoft.SqlServer.Server.SqlProcedure]

public static voidUSP_SayHelloByParameter(SqlString msg)

{

SqlContext.Pipe.Send(msg.ToString());

}

3.使用SqlContext.Pipe.Send构建带参有返回值的存储过程

[Microsoft.SqlServer.Server.SqlProcedure]

public static SqlInt32USP_SayHelloByReturn(SqlString msg)

{

returnmsg.ToString().Length;

}

4.使用SqlCommand来执行语句，注意这里使用了SQLServer自带的pubs数据库

[Microsoft.SqlServer.Server.SqlProcedure]

public static voidUSP_ExecuteBySqlCommand(SqlString stor_id, SqlString stor_name)

{

//由于程序是在SQL Server内执行，所以连接字符串写成"contextconnection=true"即可

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

{

con.Open();

SqlCommand com = new SqlCommand(

string.Format("insert into stores values('{0}','{1}')",stor_id, stor_name), con);

com.ExecuteNonQuery();

}

5.使用ExecuteAndSend来执行语句，注意这里使用了SQLServer自带的pubs数据库

[Microsoft.SqlServer.Server.SqlProcedure]

public static void USP_ExecuteByExecuteAndSend(SqlStringstor_id, SqlString stor_name)

{

//由于程序是在SQL Server内执行，所以连接字符串写成"contextconnection=true"即可

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

{

con.Open();

SqlCommand com = new SqlCommand(

string.Format("insert into stores values('{0}','{1}')",stor_id, stor_name), con);

SqlContext.Pipe.ExecuteAndSend(com);

}

4和5的执行结果一样，但是在CLR中推荐使用方式5，这是将结果返回到客户端的最高效方法，因为数据不必复制到托管内存即传输到网络缓冲区。

6.使用PipeSend来发送单条记录

[Microsoft.SqlServer.Server.SqlProcedure]

public static void UPS_PipeSendSqlDataRecord()

{

//像构造Table一样来构造SqlDataRecord，其中SqlMetaData类似DataColumn

SqlDataRecord dataRecord = new SqlDataRecord(new SqlMetaData[] {

new SqlMetaData("Col1", SqlDbType.NVarChar,100),

new SqlMetaData("Col2", SqlDbType.Int)

});

for (intcount = 1; count < 5; count++)

{

//SqlDataRecord.SetString类似DataRow的功能，像Table中填充值

dataRecord.SetString(0, count.ToString());

dataRecord.SetInt32(1, count);

//通过Send来发送

SqlContext.Pipe.Send(dataRecord);

}

7.使用PipeSendResult来发送结果集

[Microsoft.SqlServer.Server.SqlProcedure]

public static voidUPS_PipeSendResultSqlDataRecord()

{

//像构造Table一样来构造SqlDataRecord，其中SqlMetaData类似DataColumn

SqlDataRecord dataRecord = new SqlDataRecord(new SqlMetaData[] {

new SqlMetaData("Col1", SqlDbType.NVarChar,100),

new SqlMetaData("Col2", SqlDbType.Int)

});

//开始填充

SqlContext.Pipe.SendResultsStart(dataRecord);

for (intcount = 0; count < 5; count++)

{

//SqlDataRecord.SetString类似DataRow的功能，像Table中填充值

dataRecord.SetString(0, count.ToString());

dataRecord.SetInt32(1, count);

//通过SendResultsRow把数据填充到Table，相关于Table.Rows.Add(DataRow);

SqlContext.Pipe.SendResultsRow(dataRecord);

}

//填充结束，返回结果集

SqlContext.Pipe.SendResultsEnd();

}

其中6与7的不同在于：6返回5个结果集，其中每个结果集只有一条数据。7返回一个结果集，里面有5条数据。

最后说一下CLR存储过程的部署:

Create proc 存储过程名 as EXTERNAL NAME 数据库中Assembly名称.程序集中Assembly名称.程序方法名。

注意，如果你的程序中有命名空间的话，要这样写：

Create proc 存储过程名 as EXTERNAL NAME 数据库中Assembly名称.“程序命名空间.程序集中Assembly名称”.程序方法名。

下面的SQL是创建CLR程序集和CLR存储过程的SQL语句，假设我们编译好的dll位于C:\CLRDemo.dll，我们的程序没有命名空间(默认情况下，新建的SQL工程都没有命名空间，请注意)

use pubs

create assembly CLRDemoAssemly

from 'c:\CLRDemo.dll'

create proc USP_SayHello as EXTERNAL NAMECLRDemoAssemly.StoredProcedures.USP_SayHello

create proc USP_SayHelloByParameter (@Msgnvarchar(128)) as EXTERNAL NAMECLRDemoAssemly.StoredProcedures.USP_SayHelloByParameter

create proc USP_SayHelloByReturn (@Msgnvarchar(128)) as EXTERNAL NAMECLRDemoAssemly.StoredProcedures.USP_SayHelloByReturn

create proc USP_ExecuteBySqlCommand (@Idnvarchar(4),@Name nvarchar(32)) as EXTERNAL NAMECLRDemoAssemly.StoredProcedures.USP_ExecuteBySqlCommand

create proc USP_ExecuteByExecuteAndSend (@Idnvarchar(4),@Name nvarchar(32)) as EXTERNAL NAMECLRDemoAssemly.StoredProcedures.USP_ExecuteByExecuteAndSend

create proc UPS_PipeSendSqlDataRecord as EXTERNALNAME CLRDemoAssemly.StoredProcedures.UPS_PipeSendSqlDataRecord

create proc UPS_PipeSendResultSqlDataRecord asEXTERNAL NAME CLRDemoAssemly.StoredProcedures.UPS_PipeSendResultSqlDataRecord

调用方式：

exec USP_SayHello

exec USP_SayHelloByParameter 'Hello,TJVictor again'

declare @Result int

exec @Result=USP_SayHelloByReturn 'Hello,TJVictoragain'

select @Result

exec USP_ExecuteBySqlCommand '1234','TestUSP_ExecuteBySqlCommand'

exec USP_ExecuteByExecuteAndSend '5678','TestUSP_ExecuteByExecuteAndSend'

exec UPS_PipeSendSqlDataRecord

exec UPS_PipeSendResultSqlDataRecord

删除方式：注意删除Assembly时，一定要先把引用此Assembly的所有东西删除。

drop proc USP_SayHello

drop proc USP_SayHelloByParameter

drop proc USP_SayHelloByReturn

drop proc USP_ExecuteBySqlCommand

drop proc USP_ExecuteByExecuteAndSend

drop proc UPS_PipeSendSqlDataRecord

drop proc UPS_PipeSendResultSqlDataRecord

drop assembly CLRDemoAssemly

下面附带完整程序源代码

viewplaincopy to clipboardprint?

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

///<summary>

/// 使用SqlContext.Pipe.Send构建无参无返回值的存储过程

///</summary>

[Microsoft.SqlServer.Server.SqlProcedure]

publicstatic void USP_SayHello()

{

SqlContext.Pipe.Send("USP:Hello TJVictor!");

}

///<summary>

/// 使用SqlContext.Pipe.Send构建带参无返回值的存储过程

///</summary>

[Microsoft.SqlServer.Server.SqlProcedure]

publicstatic void USP_SayHelloByParameter(SqlString msg)

{

SqlContext.Pipe.Send(msg.ToString());

}

///<summary>

/// 使用SqlContext.Pipe.Send构建带参有返回值的存储过程

///</summary>

[Microsoft.SqlServer.Server.SqlProcedure]

publicstatic SqlInt32 USP_SayHelloByReturn(SqlString msg)

{

return msg.ToString().Length;

}

///<summary>

/// 使用SqlCommand来执行语句，注意这里使用了SQLServer自带的pubs数据库

///</summary>

///<param name="name"></param>

[Microsoft.SqlServer.Server.SqlProcedure]

publicstatic void USP_ExecuteBySqlCommand(SqlString stor_id, SqlStringstor_name)

{

//由于程序是在SQL Server内执行，所以连接字符串写成"contextconnection=true"即可

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

{

con.Open();

SqlCommand com = new SqlCommand(

string.Format("insert into stores (stor_id,stor_name)values('{0}','{1}')", stor_id, stor_name), con);

com.ExecuteNonQuery();

}

///<summary>

/// 使用ExecuteAndSend来执行语句，注意这里使用了SQLServer自带的pubs数据库

///</summary>

///<param name="name"></param>

[Microsoft.SqlServer.Server.SqlProcedure]

publicstatic void USP_ExecuteByExecuteAndSend(SqlString stor_id, SqlString stor_name)

{

//由于程序是在SQL Server内执行，所以连接字符串写成"contextconnection=true"即可

using(SqlConnection con = new SqlConnection("contextconnection=true"))

{

con.Open();

SqlCommand com = new SqlCommand(

string.Format("insert into stores (stor_id,stor_name)values('{0}','{1}')", stor_id, stor_name), con);

SqlContext.Pipe.ExecuteAndSend(com);

}

///<summary>

/// 使用PipeSend来发送单条记录

/// </summary>

[Microsoft.SqlServer.Server.SqlProcedure]

publicstatic void UPS_PipeSendSqlDataRecord()

{

//像构造Table一样来构造SqlDataRecord，其中SqlMetaData类似DataColumn

SqlDataRecord dataRecord = new SqlDataRecord(new SqlMetaData[] {

new SqlMetaData("Col1", SqlDbType.NVarChar,100),

new SqlMetaData("Col2", SqlDbType.Int)

});

for(int count = 1; count < 5; count++)

{

//SqlDataRecord.SetString类似DataRow的功能，像Table中填充值

dataRecord.SetString(0, count.ToString());

dataRecord.SetInt32(1, count);

//通过Send来发送

SqlContext.Pipe.Send(dataRecord);

}

/// <summary>

/// 使用PipeSendResult来发送结果集

///</summary>

[Microsoft.SqlServer.Server.SqlProcedure]

publicstatic void UPS_PipeSendResultSqlDataRecord()

{

//像构造Table一样来构造SqlDataRecord，其中SqlMetaData类似DataColumn

SqlDataRecord dataRecord = newSqlDataRecord(new SqlMetaData[] {

new SqlMetaData("Col1", SqlDbType.NVarChar,100),

new SqlMetaData("Col2", SqlDbType.Int)

});

//开始填充

SqlContext.Pipe.SendResultsStart(dataRecord);

for(int count = 0; count < 10; count++)

{

//SqlDataRecord.SetString类似DataRow的功能，像Table中填充值

dataRecord.SetString(0, count.ToString());

dataRecord.SetInt32(1, count);

//通过SendResultsRow把数据填充到Table，相关于Table.Rows.Add(DataRow);

SqlContext.Pipe.SendResultsRow(dataRecord);

}

//填充结束，返回结果集

SqlContext.Pipe.SendResultsEnd();

}

};

CLR标量函数、表值函数和聚合函数(UDA)

本节主要介绍使用CLR创建标量函数，表值函数和聚合函数。

所谓标量函数指的就是此函数只返回一个值。表值函数返回值是一个表。聚合函数是在select语句中使用的，用来聚合一个结果集，类似于Sum()或是Count()等内置的函数，而且真正的自定义聚合函数目前只能用CLR来实现。

下面的例子使用了SQLServer自带的pubs数据库。

1.CLR标量函数

1.1无参函数

///

/// 标量函数，不带参数

///

[Microsoft.SqlServer.Server.SqlFunction(

DataAccess = DataAccessKind.Read,

IsDeterministic = true)]

publicstatic SqlString UF_Scalar_SayHello()

{

string returnValue = "null";

//由于程序是在SQL Server内执行，所以连接字符串写成"contextconnection=true"即可

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

{

conn.Open();

SqlCommand com = new SqlCommand("select top 1 [au_lname] from[dbo].[authors]",conn);

using (SqlDataReader dr =com.ExecuteReader(CommandBehavior.CloseConnection))

{

if (dr.Read())

returnValue = dr.GetString(0);//返回au_lname

}

return returnValue;//返回"null”

}

CLR函数用Microsoft.SqlServer.Server.SqlFunction特征进行修饰。里面的参数含义为：DataAccess = DataAccessKind.Read表示可访问数据表。关于SqlFunctionAttribute的属性将附录在文章的最后。

///

/// 标量函数，带参数

///

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlStringUF_Scalar_SayHelloByPar(SqlString par)

{

return par;

}

2.CLR表值函数

表值与标量函数有些不同。因为要返回一个数据集合，所以一定要用一个填充数据的方法，在属性中用FillRowMethodName来表示，且返回值应该为IEnumerable类型。代码如下：

1.首先自定义返回类型

public class ReturnData

{

public SqlString Name { get; set; }

public SqlString Password { get; set; }

public ReturnData(string name, string password)

{

this.Name = name;

this.Password = password;

}

2.写CLR表值函数

[Microsoft.SqlServer.Server.SqlFunction(

DataAccess = DataAccessKind.Read,

FillRowMethodName = "FillRow_ReturnData",//这里是此函数的具体填充方法

IsDeterministic = true)]

publicstatic IEnumerable UF_Table_GetReturnData()

{

ListreturnDataList = new List();

returnDataList.Add(new ReturnData("a", "a"));

returnDataList.Add(new ReturnData("b", "b"));

returnDataList.Add(new ReturnData("c", "c"));

return returnDataList;

}

3.写填充方法

public static void FillRow_ReturnData(objectreturnDataObj,

out SqlString name,

out SqlString password)

{

ReturnData item = returnDataObj as ReturnData;

name= "";

password = "";

if (item != null)

{

name = item.Name;

password = item.Password;

}

这样一个表值函数就写好了。确定有点麻烦，但是表值在某种情况下，也是不可替代的。

3.CLR聚合函数

用户自定义的CLR聚合类中必须四个函数:Init,Accumulate,Merge,Terminate。Init用户初始化，Accumulate用来实现具体的聚合算法，Merge用来执行每一次的聚合逻辑顺序，Terminate用来将聚合的结果返回。

下面的代码显示了字符串的自定义聚合

#region Aggregation

[Serializable]

[StructLayout(LayoutKind.Sequential)]

[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(

Format.UserDefined,

IsInvariantToDuplicates = false,

IsInvariantToNulls = true,

IsInvariantToOrder = false,

MaxByteSize=8000)]

public class StringAgg : IBinarySerialize

{

privateStringBuilder strBuffer;

publicvoid Init()

{

strBuffer = new StringBuilder();

}

publicvoid Accumulate(SqlString str)

{

strBuffer.Append(string.Format("{0},", str));

}

publicvoid Merge(StringAgg Group)

{

Accumulate(Group.Terminate());

}

publicSqlString Terminate()

{

return strBuffer.ToString();

}

#regionIBinarySerialize Members

publicvoid Read(System.IO.BinaryReader r)

{

strBuffer = new StringBuilder(r.ReadString());

}

publicvoid Write(System.IO.BinaryWriter w)

{

w.Write(strBuffer.ToString());

}

#endregion

}

#endregion;

4.创建函数的SQL脚本及调用方法

关于CLR Assembly的创建方法前面已经讲过了，这里不再重复

--创建函数

create function UF_Scalar_SayHello()

returns nvarchar(32)

as EXTERNAL NAMECLRDemoAssemly.UserDefinedFunctions.UF_Scalar_SayHello

create function UF_Scalar_SayHelloByPar(@Parnvarchar(32))

returns nvarchar(32)

as EXTERNAL NAMECLRDemoAssemly.UserDefinedFunctions.UF_Scalar_SayHelloByPar

create function UF_Table_GetReturnData()

returns table(Name nvarchar(32),Passwordnvarchar(32))

as EXTERNAL NAME CLRDemoAssemly.UserDefinedFunctions.UF_Table_GetReturnData

create AGGREGATE StringAgg(@Par nvarchar(32))

returns nvarchar(max)

EXTERNAL NAME CLRDemoAssemly.StringAgg

select dbo.UF_Scalar_SayHello()

select dbo.UF_Scalar_SayHelloByPar('HelloTJVictor')

select * from dbo.UF_Table_GetReturnData()

select dbo.StringAgg(au_lname) from dbo.authors

5.SqlFunctionAttribute的属性

名称说明

DataAccess 指示函数是否需要访问存储在 SQL Server 的本地实例中的用户数据。

FillRowMethodName 方法的名称，该方法与 TVF 协定所使用的表值函数 (TVF)在同一个类中。

IsDeterministic 指示用户定义的函数是否是确定性的。

IsPrecise 指示函数是否涉及不精确的计算，如浮点运算。

Name 函数在 SQL Server 中注册时所使用的名称。

SystemDataAccess 指示函数是否需要访问存储在 SQL Server 的系统目录或虚拟系统表中的数据。

TableDefinition 如果方法用作表值函数 (TVF)，则为一个字符串，该字符串表示结果的表定义。

TypeId 当在派生类中实现时，获取该 Attribute 的唯一标识符。

6.SqlUserDefinedAggregateAttribute的属性

名称说明

Format 序列化格式为 Format 的值之一。如果选择Native，则聚合类一定要被[StructLayout(LayoutKind.Sequential)]修饰；如果选择UserDefined，则聚合类一定要继承IBinarySerialize接口，自己写序列化方法。

IsInvariantToDuplicates 指示聚合是否与重复值无关。

IsInvariantToNulls 指示聚合是否与空值无关。

IsInvariantToOrder 指示聚合是否与顺序无关。

IsNullIfEmpty 指示在没有对任何值进行累积时聚合是否返回空引用。

MaxByteSize 聚合实例的最大大小。

Name 聚合的名称。

TypeId 当在派生类中实现时，获取该 Attribute 的唯一标识符。

7.附录完整程序

view plaincopy to clipboardprint?

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Collections;

using System.Collections.Generic;

using System.Text;

using System.Runtime.InteropServices;

public partial class UserDefinedFunctions

{

#region Scalar

///<summary>

/// 标量函数，不带参数

///</summary>

///<returns></returns>

[Microsoft.SqlServer.Server.SqlFunction(

DataAccess = DataAccessKind.Read)]

publicstatic SqlString UF_Scalar_SayHello()

{

string returnValue = "null";

//由于程序是在SQL Server内执行，所以连接字符串写成"contextconnection=true"即可

using(SqlConnection conn = new SqlConnection("contextconnection=true"))

{

conn.Open();

SqlCommand com = new SqlCommand("select top 1 [au_lname] from[dbo].[authors]", conn);

using (SqlDataReader dr =com.ExecuteReader(CommandBehavior.CloseConnection))

{

if (dr.Read())

returnValue = dr.GetString(0);

}

return returnValue;

}

///<summary>

/// 标量函数，带参数

///</summary>

///<param name="par"></param>

///<returns></returns>

[Microsoft.SqlServer.Server.SqlFunction]

publicstatic SqlString UF_Scalar_SayHelloByPar(SqlString par)

{

return par;

}

#endregion

#regionTable

///<summary>

/// 表值函数。

///</summary>

///<returns></returns>

[Microsoft.SqlServer.Server.SqlFunction(

DataAccess = DataAccessKind.Read,

FillRowMethodName = "FillRow_ReturnData",

IsDeterministic = true)]

publicstatic IEnumerable UF_Table_GetReturnData()

{

List<ReturnData> returnDataList = newList<ReturnData>();

returnDataList.Add(new ReturnData("a", "a"));

returnDataList.Add(new ReturnData("b", "b"));

returnDataList.Add(new ReturnData("c", "c"));

return returnDataList;

}

publicclass ReturnData

{

public SqlString Name { get; set; }

public SqlString Password { get; set;}

public ReturnData(string name, string password)

{

this.Name = name;

this.Password = password;

}

publicstatic void FillRow_ReturnData(object returnDataObj,

out SqlString name,

out SqlStringpassword)

{

ReturnData item = returnDataObj as ReturnData;

name= "";

password = "";

if(item != null)

{

name = item.Name;

password = item.Password;

}

#endregion

};

#region Aggregation

[Serializable]

[StructLayout(LayoutKind.Sequential)]

[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(

Format.UserDefined,

IsInvariantToDuplicates = false,

IsInvariantToNulls = true,

IsInvariantToOrder = false,

MaxByteSize=8000)]

public class StringAgg : IBinarySerialize

{

privateStringBuilder strBuffer;

publicvoid Init()

{

strBuffer = new StringBuilder();

}

publicvoid Accumulate(SqlString str)

{

strBuffer.Append(string.Format("{0},", str));

}

public voidMerge(StringAgg Group)

{

Accumulate(Group.Terminate());

}

publicSqlString Terminate()

{

return strBuffer.ToString();

}

#regionIBinarySerialize Members

publicvoid Read(System.IO.BinaryReader r)

{

strBuffer = new StringBuilder(r.ReadString());

}

publicvoid Write(System.IO.BinaryWriter w)

{

w.Write(strBuffer.ToString());

}

#endregion

}

#endregion;

CLR触发器

CLR可以实现DML和DDL两种触发形式，但是本人一般不建议使用CLR的触发器，主要是考虑到效率问题。比如我们使用trigger来实现发mail等操作时，就要考虑pop3或是smtp等待时间，因为trigger本事就是个事务，也就是说，在smtp等待时间也算在了整个事务中，这样就会大大影响效率。

1.CLR DML触发器

DML指的是数据操作语言，也就是通常的insert,update和delete操作。这个触发器主要实现在对pubs数据库中的author表进行insert,update,delete时，会显示相应的操作名称。

///

/// 把insert,update,delete操作都显示出来

///

[Microsoft.SqlServer.Server.SqlTrigger(

Name= "UF_DML_Trigger",

Target = "dbo.authors",

Event= "FOR INSERT, UPDATE, DELETE")]

publicstatic void UF_DML_Trigger()

{

switch (SqlContext.TriggerContext.TriggerAction)

{

case TriggerAction.Insert:

SqlContext.Pipe.Send("Trigger Insert");

break;

case TriggerAction.Update:

SqlContext.Pipe.Send("TriggerUpdate");

break;

case TriggerAction.Delete:

SqlContext.Pipe.Send("Trigger Delete");

break;

default:

break;

}

2.CLR DDL触发器

DDL指的是数据定义语言，也就是通常说的create table，drop procedure等。这段代码主要实现了禁止删除pubs数据库上面的存储过程的功能。当有删除存储过程操作时，就自动回滚。

///

/// DDL示例：无法删除存储过程，回滚操作

///

[Microsoft.SqlServer.Server.SqlTrigger(

Name= "UF_DDL_Trigger",

Target = "pubs",

Event= "DropProcedure")]

publicstatic void UF_DDL_Trigger()

{

switch (SqlContext.TriggerContext.TriggerAction)

{

case TriggerAction.DropProcedure:

try

{

// Get the current transactionand roll it back.

Transaction trans = Transaction.Current;

trans.Rollback();

SqlContext.Pipe.Send("Drop Proc has Rollback");

}

catch (SqlException ex)

{

// Catch the expected exception.

}

break;

default:

break;

}

3.部署及调用SQL 脚本

关于CLR Assembly的创建方法前面已经讲过了，这里不再重复

--Create CLR Trigger

CREATE TRIGGER UF_DML_Trigger

ON dbo.authors

FOR INSERT,update,delete

AS EXTERNAL NAMECLRDemoAssemly.Triggers.UF_DML_Trigger;

CREATE TRIGGER UF_DDL_Trigger

ON database

for drop_procedure

as EXTERNAL NAMECLRDemoAssemly.Triggers.UF_DDL_Trigger;

4.TriggerAction属性列表

成员名称说明

AlterAppRole 已执行 ALTER APPLICATION ROLE Transact-SQL 语句。

AlterAssembly 已执行 ALTER ASSEMBLY Transact-SQL 语句。

AlterBinding 当事件通知在数据库或服务器实例上创建时，会指定 ALTER_REMOTE_SERVICE_BINDING 事件类型。

AlterFunction 已执行 ALTERFUNCTION Transact-SQL 语句。

AlterIndex 已执行 ALTER INDEX Transact-SQL 语句。

AlterLogin 已执行 ALTER LOGIN Transact-SQL 语句。

AlterPartitionFunction 已执行 ALTER PARTITION FUNCTION Transact-SQL 语句。

AlterPartitionScheme 已执行 ALTER PARTITION SCHEME Transact-SQL 语句。

AlterProcedure 已执行 ALTERPROCEDURE Transact-SQL 语句。

AlterQueue 已执行 ALTER QUEUE Transact-SQL 语句。

AlterRole 已执行 ALTER ROLE Transact-SQL 语句。

AlterRoute 已执行 ALTER ROUTE Transact-SQL 语句。

AlterSchema 已执行 ALTER SCHEMA Transact-SQL 语句。

AlterService 已执行 ALTERSERVICE Transact-SQL 语句。

AlterTable 已执行 ALTER TABLE Transact-SQL 语句。

AlterTrigger 已执行 ALTERTRIGGER Transact-SQL 语句。

AlterUser 已执行 ALTER USER Transact-SQL 语句。

AlterView 已执行 ALTER VIEW Transact-SQL 语句。

CreateAppRole 已执行 CREATEAPPLICATION ROLE Transact-SQL 语句。

CreateAssembly 已执行 CREATEASSEMBLY Transact-SQL 语句。

CreateBinding 当事件通知在数据库或服务器实例上创建时，会指定 CREATE_REMOTE_SERVICE_BINDING 事件类型。

CreateContract 已执行 CREATECONTRACT Transact-SQL 语句。

CreateEventNotification 已执行 CREATE EVENT NOTIFICATION Transact-SQL 语句。

CreateFunction 已执行 CREATEFUNCTION Transact-SQL 语句。

CreateIndex 已执行 CREATE INDEX Transact-SQL 语句。

CreateLogin 已执行 CREATE LOGIN Transact-SQL 语句。

CreateMsgType 已执行 CREATEMESSAGE TYPE Transact-SQL 语句。

CreatePartitionFunction 已执行 CREATE PARTITION FUNCTION Transact-SQL 语句。

CreatePartitionScheme 已执行 CREATE PARTITION SCHEME Transact-SQL 语句。

CreateProcedure 已执行 CREATEPROCEDURE Transact-SQL 语句。

CreateQueue 已执行 CREATE QUEUE Transact-SQL 语句。

CreateRole 已执行 CREATE ROLE Transact-SQL 语句。

CreateRoute 已执行 CREATE ROUTE Transact-SQL 语句。

CreateSchema 已执行 CREATESCHEMA Transact-SQL 语句。

CreateSecurityExpression

CreateService 已执行 CREATESERVICE Transact-SQL 语句。

CreateSynonym 已执行 CREATESYNONYM Transact-SQL 语句。

CreateTable 已执行 CREATE TABLE Transact-SQL 语句。

CreateTrigger 已执行 CREATETRIGGER Transact-SQL 语句。

CreateType 已执行 CREATE TYPE Transact-SQL 语句。

CreateUser 已执行 CREATE USER Transact-SQL 语句。

CreateView 已执行 CREATE VIEW Transact-SQL 语句。

Delete 已执行 DELETE Transact-SQL 语句。

DenyObject 已执行 DENY Object Permissions Transact-SQL 语句。

DenyStatement 已执行 DENYTransact-SQL 语句。

DropAppRole 已执行 DROP APPLICATION ROLE Transact-SQL 语句。

DropAssembly 已执行 DROPASSEMBLY Transact-SQL 语句。

DropBinding 当事件通知在数据库或服务器实例上创建时，会指定 DROP_REMOTE_SERVICE_BINDING 事件类型。

DropContract 已执行 DROPCONTRACT Transact-SQL 语句。

DropEventNotification 已执行 DROP EVENT NOTIFICATION Transact-SQL 语句。

DropFunction 已执行 DROPFUNCTION Transact-SQL 语句。

DropIndex 已执行 DROP INDEX Transact-SQL 语句。

DropLogin 已执行 DROP LOGIN Transact-SQL 语句。

DropMsgType 已执行 DROP MESSAGE TYPE Transact-SQL 语句。

DropPartitionFunction 已执行 DROP PARTITION FUNCTION Transact-SQL 语句。

DropPartitionScheme 已执行 DROPPARTITION SCHEME Transact-SQL 语句。

DropProcedure 已执行 DROPPROCEDURE Transact-SQL 语句。

DropQueue 已执行 DROP QUEUE Transact-SQL 语句。

DropRole 已执行 DROP ROLE Transact-SQL 语句。

DropRoute 已执行 DROP ROUTE Transact-SQL 语句。

DropSchema 已执行 DROP SCHEMA Transact-SQL 语句。

DropSecurityExpression

DropService 已执行 DROP SERVICE Transact-SQL 语句。

DropSynonym 已执行 DROP SYNONYM Transact-SQL 语句。

DropTable 已执行 DROP TABLE Transact-SQL 语句。

DropTrigger 已执行 DROP TRIGGER Transact-SQL 语句。

DropType 已执行 DROP TYPE Transact-SQL 语句。

DropUser 已执行 DROP USER Transact-SQL 语句。

DropView 已执行 DROP VIEW Transact-SQL 语句。

GrantObject

GrantStatement

Insert 已执行 INSERT Transact-SQL 语句。

Invalid 出现一个无效触发操作，该操作不向用户公开。

RevokeObject

RevokeStatement

Update 已执行 UPDATE Transact-SQL 语句。

5.完整程序

view plaincopy to clipboardprint?

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.Transactions;

public partial class Triggers

{

///<summary>

/// 把insert,update,delete操作都显示出来

///</summary>

[Microsoft.SqlServer.Server.SqlTrigger(

Name= "UF_DML_Trigger",

Target = "dbo.authors",

Event= "FOR INSERT, UPDATE, DELETE")]

publicstatic void UF_DML_Trigger()

{

switch (SqlContext.TriggerContext.TriggerAction)

{

case TriggerAction.Insert:

SqlContext.Pipe.Send("Trigger Insert");

break;

case TriggerAction.Update:

SqlContext.Pipe.Send("TriggerUpdate");

break;

case TriggerAction.Delete:

SqlContext.Pipe.Send("Trigger Delete");

break;

default:

break;

}

///<summary>

/// DDL示例：无法删除存储过程，回滚操作

///</summary>

[Microsoft.SqlServer.Server.SqlTrigger(

Name= "UF_DDL_Trigger",

Target = "pubs",

Event= "DropProcedure")]

publicstatic void UF_DDL_Trigger()

{

switch (SqlContext.TriggerContext.TriggerAction)

{

case TriggerAction.DropProcedure:

try

{

// Get the current transaction and roll it back.

Transaction trans = Transaction.Current;

trans.Rollback();

SqlContext.Pipe.Send("Drop Proc has Rollback");

}

catch (SqlException ex)

{

// Catch the expected exception.

}

break;

default:

break;

}

CLR自定义数据类型

一、这是这个系列的最后一节了，自定义复杂数据类型目前只能通过CLR来实现。为了在 SQLServer 中运行，您的 UDT 必须实现 UDT 定义中的以下要求：

1.该 UDT 必须指定Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute。System.SerializableAttribute 可选用，但建议使用。

2.UDT 必须通过创建公共的 static（MicrosoftVisual Basic 中为 Shared)Null 方法，在类或结构中实现 System.Data.SqlTypes.INullable 接口。默认情况下，SQL Server 是可识别 Null 的。这是为使在 UDT 中执行的代码能够识别 Null 值所必需的。

3.UDT 必须包含支持从其进行分析的公共 static （或 Shared）Parse 方法以及用于转换到对象的字符串表示形式的 ToString 方法。

4.具有用户定义序列化格式的 UDT 必须实现System.Data.IBinarySerialize 接口并提供 Read 和 Write 方法。

5.该 UDT 必须实现System.Xml.Serialization.IXmlSerializable，或者所有公共字段和属性必须均属于 XML 可序列化类型或者使用XmlIgnore 属性进行修饰（如果要求替代标准序列化）。

6.一个 UDT 对象必须只存在一个序列化。如果序列化或反序列化例程识别了某一特定对象的多个表示形式，则验证将失败。

7.为了确保服务器将字节顺序的比较用于 UDT 值，SqlUserDefinedTypeAttribute.IsByteOrdered必须为 true。

8.在类中定义的 UDT 必须具有不采用任何参数的公共构造函数。您可以选择创建其他重载类构造函数。

9.该 UDT 必须将数据元素作为公共字段或属性过程公开。

10.公共名称不能长于 128 个字符，并且必须符合在标识符中定义的针对标识符的 SQL Server 命名规则。

11.sql_variant 列不能包含 UDT 的实例。

12.继承的成员无法从 Transact-SQL 访问，因为 SQL Server类型系统不知道 UDT 中的继承层次结构。但是，您可以在创建类的结构时使用继承，并且可以在该类型的托管代码实现方式中调用此类方法。

13.成员不能被重载，但类构造函数除外。如果您创建某一重载方法，则在 SQL Server 中注册程序集或创建类型时将不会引发错误。在运行时将检测到重载的方法，而不是在创建类型时检测到。只要永不调用重载的方法，重载的方法就可以存在于类中。一旦您调用重载的方法，就会引发错误。

14.任何 static（或 Shared）成员都必须声明为常量或声明为只读。静态成员将无法改变。

15.从 SQL Server 2008 开始，如果 SqlUserDefinedTypeAttribute.MaxByteSize 字段设置为 -1，则序列化 UDT 在大小上可达到大对象 (LOB) 大小限制（目前为 2 GB）。该 UDT 的大小不能超过在MaxByteSized 字段中指定的值。

二、下面的代码实现了坐标的自定义类型

view plaincopy to clipboardprint?

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer.Server;

using System.Runtime.InteropServices;

using System.Data.SqlTypes;

[Serializable]

[StructLayout(LayoutKind.Sequential)]

[SqlUserDefinedType(Format.Native, IsByteOrdered =true)]

public class UDT_Point : INullable

{

privateint x;

///<summary>

/// X坐标

///</summary>

publicint X

{

get {return x; }

set {x = value; }

}

privateint y;

///<summary>

/// Y坐标

///</summary>

publicint Y

{

get {return y; }

set {y = value; }

}

//是否为NULL标志

privatebool _isNull;

publicUDT_Point(int x, int y)

{

this._isNull = false;

this.x= x;

this.y = y;

}

publicUDT_Point()

{

this._isNull = true;

this.x = this.y = 0;

}

///<summary>

/// 加法运算符

///</summary>

///<param name="item"></param>

///<returns></returns>

[SqlMethod(OnNullCall = false)]

publicUDT_Point Add(UDT_Point item)

{

//被加数为空，直接返回this

if(item._isNull)

return this;

return new UDT_Point(item.x + this.x, item.y + this.y);

}

///<summary>

/// 减法运算符

///</summary>

///<param name="item"></param>

///<returns></returns>

[SqlMethod(OnNullCall = false)]

publicUDT_Point Minus(UDT_Point item)

{

//被减数为空，直接返回this

if(item._isNull)

return this;

return new UDT_Point(this.x - item.x, this.y - item.y);

}

///<summary>

/// 解析函数。必须有

///</summary>

///<param name="strString"></param>

///<returns></returns>

publicstatic UDT_Point Parse(SqlString strString)

{

int x= int.Parse(strString.Value.Split(',')[0]);

int y= int.Parse(strString.Value.Split(',')[1]);

return new UDT_Point(x, y);

}

///<summary>

/// 为空处理，必须有

///</summary>

publicstatic UDT_Point Null

{

get

{

return new UDT_Point();

}

///<summary>

/// 显示函数，必须有

///</summary>

///<returns></returns>

publicoverride string ToString()

{

return string.Format("({0},{1})", this.x, this.y);

}

#regionINullable Members

///<summary>

/// 实现为NULL接口

///</summary>

publicbool IsNull

{

get {return this._isNull; }

set {this._isNull = true; }

}

#endregion

}

三、部署及调用SQL脚本

CREATE TYPEdbo.UDT_Point
EXTERNAL NAME CLRDemoAssemly.[UDT_Point];

物联网-云

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
SQL Server CLR全攻略

CLR介绍和配置Microsoft SQL Server 现在具备与 Microsoft Windows .NET Framework 的公共语言运行时 (CLR) 组件集成的功能。CLR 为托管代码提供服务，例如跨语言集成、代码访问安全性、对象生存期管理以及调试和分析支持。对于 SQL Server 用户和应用程序开发人员来说，CLR集成意味着您现在可以使用任何.NET Framework
复制链接

扫一扫