自定义存款和储蓄进度和触发器

那一篇博客接着上一篇博客继续介绍 SQL CL奥德赛 Stored Procedure 和 CLMuranoTrigger,

这一篇博客接着上一篇博客继续介绍 SQL CL奥迪Q7 Stored Procedure 和 CL昂科拉Trigger,

hsrzyn 

.NET编程和SQL Server ——Sql Server 与CLR集成 

 

上一篇博客介绍了 SQL CLENVISION Function 的利用,以及 CLPAJERO 程序集的登记和 CL酷威Function 的登记。

上一篇博客介绍了 SQL CLRAV四 Function 的选择,以及 CLEvoque 程序集的注册和 CL奥迪Q五Function 的挂号。

SQLCLR

 

1、SQL Server 为何要与CLPRADO集成

一、 SQL Server
提供的仓库储存进程、函数等分外点儿,平常索要外部的代码来执行1些千斤的移植;

贰、与CL奥德赛集成可将原先要求独自的程序来贯彻的作用迁移到SQL Server
内部开始展览数量操作;

三、T-SQL数据查询语言在回去数据集方面很好,不过除了这一个之外表现不好。与CLHighlander的集成可化解那1题材;

四、.NET的操作代码和履行的进程比T-SQL快的很多。.NET程序是早就编写翻译好的贰进制代码,而不是作为存款和储蓄进度来创设,不再编译就平素可运转。

自家的上壹篇博客:SQL Server CLR 使用 C#
自定义函数

自我的上一篇博客:SQL Server CLR 使用 C#
自定义函数

什么是SQLCLR

SQL CL途观 (SQL Common Language Runtime) 是自 SQL Server 二〇〇五才面世的新职能,它将.NET Framework中的CL奥迪Q5服务注入到 SQL Server
中,使得.NET代码可在SQL Server服务器进度中履行。

经过在 Microsoft SQL Server 中托管 CL汉兰达(称为 CL途观集成),开荒人士可以在托管代码中编辑存储进程、触发器、用户定义函数、用户定义类型和用户定义聚合函数,
改换了原先只可以通过T-SQL语言来实现那么些效应的局面。因为托管代码在实践此前会编写翻译为本机代码,所以,在稍微方案中能够大大进步质量。

 

2、SQL Server 中的程序集(编写翻译、增添、修改、删除)

除非在增添了程序集后才能在该程序集的底蕴上确立CLRAV4存款和储蓄进度、CL奥迪Q叁函数等。

一、CL卡宴代码(编写翻译)→DLL文件(注册)→SQL Server
(作为数据库对象)→执行数据库操作 进度如下:

(1)将托管程序编写制定为一组类定义。编写好代码后编译成3个DLL文件;

储存进度、用户自定义函数、触发器的编纂为类的静态方法;

用户自定义类型、聚合函数编写为二个结构体。

(2)DLL文件上传SQL Server 磁盘上,并行使create assembly
将DLL程序集存款和储蓄到系统目录;

(三)创制SQL对象(函数、存款和储蓄进度、触发器等)并将其绑定到程序集的入口点;

储存过程:create procedure

用户自定义函数:create function

触发器:create trigger

用户自定义类型:create type

聚合函数:create aggregate

(4)像使用T-SQL例程1样选取。

二、SQL Server 中的程序集(创造程序集并上载到SQL Server
实例然后成立数据库对象)

(1)SQL Server
二零零六暗中认可情形下禁止使用了CLENVISION集成的意义,必需先启用CLQashqai集成后才能在SQL Server
访问.NET对象。

启用CLR集成

exec sp_configure ‘show advanced options’,’1′; 
go 
reconfigure; 
go 
exec sp_configure ‘clr enabled’,’1′;//开启CLR集成 
go 
reconfigure; 
go

解释

(贰)将DLL程序集增加到SQL Server 中。在SQL Server 中增添程序集使用create
assembly命令。

create assembly assembly_name(程序集名) 
[authorization owner_name] 
from {<client_assembly_specifier>|<assembly_bits>} 
[with permission_set={safe|external_自定义存款和储蓄进度和触发器。access|unsafe}]

其中,<client_assembly_specifier>:表示程序集所在的地面地点或互连网地方以及与程序集对应的清单文件名。

<assembly_bits>:表示结合程序集和依赖程序集的二进制值的列表。

permission_set={safe|external_access|unsafe :表示钦赐SQL Server
访问程序集时相程序集授予的1组访问权限,默许值为safe。

(三)修改程序集

alter assembly assembly_name

[from <client_assembly_specifier>|<assembly_bits>] 
[with <assembly_option>[,….n]] 
[drop file{file_name[,….n]|all}] 
[add file from client_file_specifier [as file_name]|file_bits as
file_name}[,….n]][;]

  其中,<assembly_option>::=permission_set=[{safe|external_access|unsafe}
| visibility={on|off} | unchecked data],其中visibility={on|off}:指示在开创CLQashqai函数、存款和储蓄进程、触发器、用户定义的类型以及用户自定义聚合函数时,该程序集是还是不是可见。假如设置为OFF则程序集只好由别的程序集调用。unchecked
data :暗许处境下,借使alter assembly
必须评释种种表行的一致性,则他将破产。该选项使得用户能够通过运用DBCC
CHECKTABLE将检查推迟到以往的某部时刻开始展览。

A、为顺序集增多文件:

alter assembly assembly_name

add file from client_file_specifier [as file_name]|file_bits as
file_name}[,….n]][;]

B、更新程序集:

use database_name

go

alter assembly assembly_name

drop file all

go

alter assembly assembly_name

from <client_assembly_specifier>|<assembly_bits>]

add file from client_file_specifier [as file_name]|file_bits as
file_name}[,….n]][;]

(4)删除程序集

删除程序集是,将从数据库中剔除程序集和它的保有关乎文件,如,源代码和调节和测试文件等。但万一该程序集被别的对象引用则赶回错误。

drop assembly assembly_name[,….n] 
[with no dependents]

内部, with no dependents
:表示只删除assembly_name而不删除该程序集引用的相关程序集。如若不钦命它,则drop
assembly 将去除assembly_name和具有相关程序集。

 

 

SQLCLR实例

Visual Studio 2010提供了“SQL
Server项目”类型,在那连串型的门类中,为5种基本SQL
CLCR-V实体定义了模版,使开荒职员能够很轻易的创设SQL CLR代码。

三、创建CLR函数(Function)

  要创制被SQL Server
引用的CL大切诺基程序则要求引用Microsoft.SqlServer.Server命名空间,创立CL奥迪Q7函数还索要动用该命名空间下的SqlFunctionAttribute天性类即将[Microsoft.SqlServer.ServerSqlFunction.]放置CL福睿斯函数的尾部。

一、成立CL卡宴标量值函数

(1)使用C#编写CL奥迪Q7标量值函数在VS20第10中学创造CL奥迪Q7函数后,编写翻译成DLL文件,并将该文件加多到数据库中。

(二)在SQL Server中动用CLXC90标量值函数 使用create
function创造引用注册程序集的函数。

create function –[schema_name.]function_name
//[schema_name.]如:[dbo.] 

%7B@parameter_name [as] [type_schema_name.]parameter_data_type
[=default]}[,….n] 

return {return_date_type} 
[with <clr_function_option> [,…n]] 
[as]external name assembly_name.class_name.method_name

  其中external name
assembly_name.class_name.method_name:钦命将先后集与函数绑定的点子。<clr_function_option>::={[returns
null on null input | called no null input] | [execute_as_clause] }
其中returns null on null input | called no null input] |
[execute_as_clause
]:钦点标量值函数的onNULLCall属性。假诺未钦命,则私下认可值为 called on null
input。那表示正是传递的参数为null,也将执行函数体。假诺在CL奥迪Q5函数中内定了returns
null on null input ,它提示当SQL
Server接收到的任何四个参数为null时,它能够回到null,而无须实际调用函数体。
优先采用create
function语句提示的习性。不能够为表值函数内定Onnullcall属性。

2、创立CLTucson表值函数 
(1)使用C#编写CL大切诺基表值函数 
CLHighlander表值函数只回去二个表,在.NET中中开创对应的函数,重返的结果是一个IEnumerable接口,用于表示八个集合。集合中是目的的实例并不是SQLServer中所识别的表,因而必要在函数的天性中钦定FillRowMethodName,这些参数的值是用来将.NET中的对象调换为表列的函数名。即将本性[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName=”FillSplitTable”)]放置与于表值函数的底部,以钦定该天性下的函数为CL凯雷德表值函数。当中,FillSplitTable是将.NET
中的对象调换为表列的函数名。还有用于将.NET中的对象转变为表列的办法必须为静态方法。第二个参数必须为System.Object类型,接下去的参数的个数正是列的个数。同时接下去的参数都必须申明为ref参数。SQLServer中回到的列的数据类型和一壹必须与该函数中ref参数的数据类型和各种相
同。编写完后编写翻译成DLL文件并增添到数据库中。 
(2)在SQLServer中利用CL帕杰罗表值函数 
A、更新程序集 
要在SQLServer中使用C#编写的CL福特Explorer表值函数,必须先更新程序集。 
如: 
alter assembly assembly_name 
from ‘程序集地址’ 
with permission_set=safe 
B、创立CL酷路泽表值函数 
create function [schema_name.]function_name 

{@parameter_name [as][type.schema_name.] 
parameter_data_type [=default]}[,…n] 

return table<clr_table_type_definition> 
[with <clr_function_option>[,…n] ] 
[order(<order_clause>)] 
[as]external name assembly_name.class_name.method_name[;] 
其中,<clr_table_type_definition>::=({column_name
data_type}[,…n])定义CL帕杰罗函数的表数据项目。表表明仅包罗列名称和数据类型。表始终放在主文件组中。
order(<order_clause>)钦命从表值函数中回到结果的各种。

3、在T-SQL中使用CLR函数 

四、CLR Stored Procedure

四、CLR Stored Procedure

积存进度

展开vs20拾,新建项目,选取“数据库”项目体系

997755.com澳门葡京 1

点击分明后会让选用“数据库连接”,连接受目的数据库。

在品种中右键新建,发现能够新建的项目有函数、存储进度、聚合函数、触发器和用户自定义类型:

997755.com澳门葡京 2

挑选“新建存款和储蓄进度”,生成的章程有“Microsoft.SqlServer.Server.SqlProcedure”标记,稍作修改,如下所示:

[Microsoft.SqlServer.Server.SqlProcedure]
   public static void HelloPro(SqlString name)
   {
       SqlContext.Pipe.Send("Hello, " + name.ToString());
   }

 

一个归纳的囤积进度就建好了(正是.net.中的叁个格局),不过要留心该存款和储蓄进度是未曾重回值的。布置后测试(关于如何安顿将在下壹节演示)如下图所示:

997755.com澳门葡京 3

事例中的类引用了System.Data.SqlTypes命名空间,其包罗了SQL Server
中本地数据类型对应的门类,比如下面的SqlString类型对应于数据库中的
nvarchar,长度最大为 五千 。那几个类提供壹种比.NET Framework
公共语言运营库 (CL凯雷德)
提供的数据类型越来越快更安全的替代方案。使用此命名空间中的类有助于堤防类型调换错误时出现精度损失的图景。还引述了Microsoft.SqlServer.Server
命名空间,该命名空间包罗将 Microsoft .NET Framework 公共语言运维库 (CL哈弗)
集成到 Microsoft SQL Server和SQL Server
数据库引擎进度执行环境时所要用到的类、接口和枚举。

代码应用了SqlContext.Pipe.Send()方法,它的指标是将字符串音信一向发送到客户端或当前出口使用者,借使未有那行代码,那么执行该存款和储蓄进程后取得不到其余值。

地点11分例子未有重临值,也未曾与数据库交互,上面那么些事例与数据库交互:

[Microsoft.SqlServer.Server.SqlProcedure]
   public static Int32  GetStudentIdByName(SqlString name)
   {       
       int id = 0;
       //使用上下文连接,也就是当前数据库连接
       using (SqlConnection conn = new SqlConnection("context connection=true"))
       {
           SqlCommand cmd = new SqlCommand();
           cmd.CommandText = "select id_student,name_student from student where name_student =@name";
           SqlParameter paraname = new SqlParameter ("@name",SqlDbType .NVarChar ,4000);
           paraname.Value = name;
           cmd.Parameters.Add(paraname);
           cmd.Connection = conn;
           conn.Open();
           SqlDataReader reader = cmd.ExecuteReader();          
           if (reader.Read())
               id =  reader.GetInt32(0);
           reader.Close();
       }
       return id;
   }

 

其1蕴藏进程完成的功力是依据学生的姓名获取学生的ID,当中name参数代表学生姓名,存款和储蓄进程的再次回到值就意味着ID。看测试结果

997755.com澳门葡京 4

 对于output型参数,只须求在参数前加多ref就能够,看上边包车型客车事例 

[Microsoft.SqlServer.Server.SqlProcedure]
  public static void TestOutPut(ref SqlString output)
  {
      output = "Hello," + output;
  }

 在此仅仅是做一例证表明难题,查看测试结果

997755.com澳门葡京 5

储存进程也能够回来结果集,看代码

[Microsoft.SqlServer.Server.SqlProcedure]
   public static void GetStudentByClassID(SqlInt32  id_class)
   {
       using (SqlConnection conn = new SqlConnection("context connection=true"))
       {
           SqlCommand cmd = new SqlCommand();
           cmd.CommandText = "select id_student,name_student from student where id_class =@id";
           SqlParameter paraname = new SqlParameter("@id", SqlDbType.Int );
           paraname.Value = id_class;
           cmd.Parameters.Add(paraname);
           cmd.Connection = conn;
           conn.Open();
           SqlDataReader reader = cmd.ExecuteReader();
           SqlContext.Pipe.Send(reader);
       }      
   }

 上面包车型大巴代码是依据班级的ID获取学生的新闻,测试结果:

997755.com澳门葡京 6

 在该例子中,也应用到了SqlContext.Pipe.Send(),而且发送的是SqlDataReader类型。该情势近年来提供了多少个重载方法,除了string、SqlDataReader外,还有SqlDataRecord。SqlDataRecord表示结果中的单个数据行及其元数据。

4、创设CLLAND存款和储蓄进程(Procedure)

1、使用C#编写CL途睿欧存款和储蓄进程所需的函数: 
  在C#中编辑可用于CLLX570存储进度引用的函数必须选用SqlProcedure属性标识。存款和储蓄进程不供给再次来到值,所以在C#中树立void函数就可以。存款和储蓄进度壹般用来查询并扭转3个查询的表,在c#中要求选用SqlPipe对象将表格结果与音信传播给客户端。壹般,通过SqlContext类的Pipe属性获得SqlPipe对象,后调用Pipe对象的Send()方法将表格结果或音讯传送给客户端,也许利用SqlPipe对象的ExecuteAndSend()方法将查询结果传送给客户端。ExecuteAndSend()方法提供了一种高功能的法子将查询结果传送给客户端。使用天性[Microsoft.SqlServer.Server.SqlProcedure]停放在储存过程调用的函数的尾部,用以标示该函数是用作CLRubicon存款和储蓄进程被调用的,CLEnclave存款和储蓄进程对应的函数。将C#编纂的代码编写翻译成DLL文件,并增加到数据库中。

二、在SQL Server中利用CLENVISION存款和储蓄进程

create {proc|procedure}[schema_name.]procedure_name [;number] 

{ @parameter [type_schema_name.] data_type } 
[varying] [=default] [out|output] [readonly] 
][,…n] 
[with <procedure_option> [,…n]] 
[for replication] 
as external name assembly_name.class_name.method_name [;]

其中,external name assembly_name.class_name.method_name钦赐.net
framework程序集的章程,以便程序集引用。class_name必须存在与该程序集中,而且点名的方法必须为此类的静态方法。

<procedure_option>::=[encryption] [recompile]

3、创造有output参数的CL本田UR-V存款和储蓄进程

储存进度中也能够应用output参数,带有output的参数的值在仓库储存进程里面被涂改后也会将修改应用到存款和储蓄进度表面相当于指针和ref参数。output参数对应于C#中的ref参数。

四、在T-SQL中选取CL帕杰罗存款和储蓄进程

 

 

接下去在前边的花色采用增多新项,选用 SQL CLLAND C# 存款和储蓄进程。

接下去在事先的类型接纳增添新项,选用 SQL CL昂Cora C# 存款和储蓄进程。

部署

在SQL Server二零零五/200九里面,CLMurano暗许是关门的。能够运用如下SQL语句开启CLLacrosse。

翻看意况:

sp_configure 'clr enabled'

 开启

exec sp_configure 'clr enabled',1  --1,启用clr\0,禁用clr
reconfigure

 增加主次集:

create ASSEMBLY [mySQLCLR]
FROM 'd:\mySQLCLR.dll'
WITH PERMISSION_SET = SAFE  

 在那之中,from前边的意味dll文件的门道。利用下边包车型地铁SQL能够查看该数据库已经注册的主次集。

select * from sys.assemblies 

 注册存款和储蓄进度:

上述多少个存储进度对应的登记SQL语句是:

CREATE PROCEDURE [dbo].[HelloPro]
    @name [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [mySQLCLR].[StoredProcedures].[HelloPro]
GO
 
 
CREATE PROCEDURE [dbo].[GetStudentIdByName]
    @name [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [mySQLCLR].[StoredProcedures].[GetStudentIdByName]
GO
 
 
CREATE PROCEDURE [dbo].[GetStudentByClassID]
    @id_class [int]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [mySQLCLR].[StoredProcedures].[GetStudentByClassID]
GO
 
 
CREATE PROCEDURE [dbo].[TestOutPut]
    @output [nvarchar](4000) OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [mySQLCLR].[StoredProcedures].[TestOutPut]
GO

 也就是

CREATE PROCEDURE 存储过程名 参数
WITH EXECUTE AS CALLER
AS EXTERNAL NAME 数据库中Assembly名称.程序集中Assembly名称.程序方法名

 相关语法在SQL Server的一路丛书中有分解。布置后方可在SQL Server
Management Studio中查看

997755.com澳门葡京 7

 “程序集”里面能够查阅到我们布置的项,在“存款和储蓄进程”里面也包蕴通过SQLCL大切诺基创造的仓库储存进程,与一般存款和储蓄进程不一致的是,那么些囤积进程的图标上有个“锁”,而且是不可编辑的。

也得以选择SQL Server Management Studio来增多程序集,如下图所示:

997755.com澳门葡京 8

在弹出的新建程序集窗口中浏览DLL文件就足以增加程序集。

更简便易行的秘诀是一向使用Visual
Studio来机关安排。在品种上右键,接纳“铺排”,也得以因而点击调节和测试菜单下的“运营调节和测试”、“先河施行”等来布局,在配置前,要求先连接受数据库(点击项目性质,在数据库选项卡中设置),布置后能够发现,全部的储存进程均安顿完结。

当中,在品种性质的选项卡页面,有“权限品级”的设置,可用以钦定向CL昂Cora程序集授予何种安全等级。

“安全”:程序集仅能进行本地数据访问和计量职责

“外部”:程序集能够推行本地数据访问和总计职分,还能够访问网络、文件系统、注册表和环境变量。

“不安全”:程序集的权力不受限制。

五、创建CLR触发器(Trigger)

触发器是数据库服务器中生出时间事活动执行的尤其存储进程。

DML触发器:假如用户通过DML事件数量,则履行DML触发器。DML事件是针对表或视图的insert、update
、或delete语句。

DDL触发器:用于响应各个DDL事件,首就算create、alter、drop语句。

        1、使用C#编写CLR触发器

         
为了能够在C#中处理触发器触发时的情事,Microsoft.SqlServer.Server命名空间提供了SqlTriggerContext
类。SqlTriggerContext
类提供所鼓舞的触发器的上下文信息,通过SqlContext.TriggerContext来收获。通过TriggerAction来收获触发的种类,SqlTriggerContext.TriggerAction
属性提示激发触发器的操作。在使用C#编写CL帕杰罗触发器是有极大恐怕用到触发器中的俩张特殊的表:insert和deleted的时候供给利用SqlCommand.如:

SqlConnection connection = new SqlConnection(“context connection=true”);

connection.Open();//展开链接

SqlCommand sqlcom=new SqlCommand();

sqlcom.CommandText=”Select * from “+”inserted”; //使用到inserted表

reader=sqlcom.ExecuteReader();//执行SQL语句

reader.Read();//读取数据

for(int columnNumber=0;columnNumber<triggerContext.ColumnCount;
columnNumber++)

{ //将每壹列的列名通过pipe.Send方法发送到客户端

Pipe.Send(“Update Column”+reader.GetName(columnNumber)+”?”

+triggerContext.IsUpdateColumn(columnNumber).Tostring());

}

reader.Close();//关闭链接
将C#编辑的代码编写翻译成DLL文件后增多到数据库并更新SQL Server中的程序集。

2、在SQL Server中使用CLR触发器

将次第集中的触发器函数加多到SQL Server中,要求用到create trigger命令。

create trigger [schema_name.]997755.com澳门葡京 , trigger_name 
on {table | view} 
[with <dml_trigger_option>[,…n]] 
{for | after | instead of} 
{ [insert] [,] [update] [,] [delete] } 
[with append] 
[not for replication] 
as external name assembly_name.class_name.method_name

其中,external name
assembly_name.class_name.method_name用于钦赐程序集与触发器绑定的法子。该措施不带别的参数而且必需再次来到空值。

       3、在T-SQL中使用CLR触发器

 

 

 

public partial class StoredProcedures
{
    /// <summary>
    /// 无输入参数,无输出参数,无输出结果,有输出消息,无返回值的存储过程
    /// </summary>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "HelloWorld")]
    public static void HelloWorld()
    {
        SqlContext.Pipe.Send("Hello World");
    }

    /// <summary>
    /// 有输入参数,无输出参数,无输出结果,无输出消息,有返回值的存储过程
    /// </summary>
    /// <param name="name"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStrLength")]
    public static SqlInt32 GetStrLength(SqlString str)
    {
        return str.ToString().Length;
    }

    /// <summary>
    /// 有输入参数,有输出参数,无输出结果,无输出消息,无返回值的存储过程
    /// </summary>
    /// <param name="name"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "SayHello")]
    public static void SayHello(SqlString name,out SqlString sayHello)
    {
        sayHello = "Hello " + name.ToString();
    }
}
public partial class StoredProcedures
{
    /// <summary>
    /// 无输入参数,无输出参数,无输出结果,有输出消息,无返回值的存储过程
    /// </summary>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "HelloWorld")]
    public static void HelloWorld()
    {
        SqlContext.Pipe.Send("Hello World");
    }

    /// <summary>
    /// 有输入参数,无输出参数,无输出结果,无输出消息,有返回值的存储过程
    /// </summary>
    /// <param name="name"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStrLength")]
    public static SqlInt32 GetStrLength(SqlString str)
    {
        return str.ToString().Length;
    }

    /// <summary>
    /// 有输入参数,有输出参数,无输出结果,无输出消息,无返回值的存储过程
    /// </summary>
    /// <param name="name"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "SayHello")]
    public static void SayHello(SqlString name,out SqlString sayHello)
    {
        sayHello = "Hello " + name.ToString();
    }
}

调试

利用强劲的VS,能够很轻易的对SQLCL科雷傲举行调剂,VS20拾新建的数据库项目中有个Test.Sql文件,在该公文中能够直接书写测试的T-SQL,也能够增添断点,

997755.com澳门葡京 9

就如任何品类那样调节和测试,运营到断点后按F1壹足以进来到相关的.net方法中,即时窗口、变量监视等效率一个都游人如织。

997755.com澳门葡京 10

假定在调节的时候报错如下图

997755.com澳门葡京 11

案由及消除:当 Visual Studio
调节和测试器不能够使用要调节的数据库服务器注册用户时,将发生此错误。最只怕的来由是不够对
SQL Server 实例的不可或缺权限。通过拥有 sysadmin 固定服务器剧中人物的登录老将Transact-SQL 编辑器连接到服务器是不够的;Visual Studio 用户的 Windows
登录名还非得是该 SQL Server 实例上 sysadmin 固定服务器角色的积极分子。

测试后的结果在“输出”选项卡中展现,细心观望的话,能够发现调节和测试的时候会先将先后集计划。

陆、创制用户定义聚合函数(Aggregate)

在SQL
Server中,平常索要对数据按组进行自定义的聚众操作,默认的聚合函数唯有SUM(),MAX(),MIN(),AVG()等,因而就要求定义用户自定义聚合函数。

1、使用C#编排聚合函数

始建用户自定义聚合函数必须采取天性[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]停放聚合函数的底部,以标识该函数是用户自定义聚合函数。别的成立的聚合函数还必须是可种类化的,使用天性[Serializable]标识。

聚合函数实际是2个布局类型也许说聚合函数对应的是二个struct类型而不是一个主意,在里边必须达成6个点子:

(1)Init()伊始化函数:
为要拍卖的每组行调用Init()方法。在这几个点子中,为要总计的每组行进行伊始化;

(二)Accumulate()定义具体集合操作的函数:
为全部组中的各种值调用那一个点子。那一个点子的参数必须是正确的拉长类型,还足以上用户定义的体系。该函数概念聚合函数的切实集合操作;

(三)Merge()合并函数:
聚合的结果必须和另3个聚集结果合并起来,调用Merge()方法。

(肆)Terminate()甘休函数:
在拍卖每1组的结尾一行后,调用该格局。那里,聚合的结果必须用正确的数据类型重返。

编写制定好聚合函数后再行编写翻译整个项目将DLL文件增多的数据库中。后使用alter
assembly命令将集结到SQL Server的主次集中。

二、在SQL Server中开创用户自定义聚合函数

在SQL
Server中创制用户自定义聚合函数以引用CLCRUISER中的聚合函数。创制用户自定义聚合函数使用create
aggregate命令。如下:

create aggregate [schema_name.] aggregate_name 

@param_name <input_sqltype>[,…n] 

returns <return_type> 
external name assembly_name [.class_name] 
<input_sqltype>::= 
system_scalar_type | {[udt_schema_name.] udt_type_name} 
<return_type>::= 
system_scalar_type | {[udt_schema_name.] udt_type_name}

其中,system_scalar_type:表示要存放输入参数值或重临值的随意三个SQL
Server系统标量数据类型。除了text、ntext和image之外的全部标量数据类型,都能够用作自定义聚合函数的参数。无法钦赐非标准化量类型(如cursor和table)。 
udt_schema_name:表示CL科雷傲用户定义类型所属的架构的称呼。假使未钦赐则数据库按以下依次引用udt_schema_name:本机SQL类型命名空间、当前数据库中当前用户的暗许架构、当前数据库中的dbo架构。

udt_type_name:表示近日数据库中以创办的CL逍客用户自定义类型的名号。假设未钦命udt_schema_name,则SQL
Server假定该品种属于当前用户的架构。

assembly_name [.class_name]
:表示钦定与用户定义的聚合函数绑定在壹道的主次集以及(可选)该程序集所属的架构名称和该程序集中实现该用户定义聚合函数的类名称。

叁、在T-SQL中采纳用户自定义聚合函数

create aggregate CountVowels 

@input nvarchar(4000) 

returns int 
external name TestAssembly.CountVowels 
go 
select City ,COUNT(City) as PersonCount,dbo.CountVowels(City) as
CityVowelsCount 
from Person.Address 
group by City 

挂号程序集和挂号存款和储蓄进度的 SQL 前边再贴出来,那里我们先看看结果。

注册程序集和注册存储过程的 SQL 后边再贴出来,那里大家先看看结果。

触发器

触发器与存款和储蓄进程看似,都以.net中的方法,不相同的正是艺术上的标号新闻。

看例子

[Microsoft.SqlServer.Server.SqlTrigger(Name = "Insert_ClassName", Target = "class", Event = "after Insert")]
 public static void Insert_ClassName()
 {
     int id = 0;
     string strSQL = "select id_class, grade_class,class_class from inserted";
     using (SqlConnection conn = new SqlConnection("context connection = true"))
     {
         SqlCommand cmd = new SqlCommand();
         cmd.CommandText = strSQL;
         cmd.Connection = conn;
         conn.Open();
         SqlDataReader reader = cmd.ExecuteReader();
         if( reader .Read ())
         {
             id = int.Parse(reader["id_class"].ToString ());
             strSQL = "update class set name_class ="+
                 " '" +reader ["grade_class"] + "年级" + reader ["class_class"] + "班级' "+
                 "where id_class = "+id ;
             reader.Close();
             cmd.CommandText = strSQL;
             cmd.ExecuteNonQuery();
         }
     }
 }

 

触发器使用Microsoft.SqlServer.Server.SqlTrigger标记,当中的Name为在数据库中触发器的名字,Target为表名,event是触发器的风云类型。

测试

997755.com澳门葡京 12

7、创设CLBMWX三用户定义类型(UDT)

始建CL汉兰达用户自定义类型来扩大SQL的种类系统,UDT可用以定义表中的列的品类或T-SQL中的变量或例程(存储进度、触发器等)参数的门类。用户定义类型实例能够是表中的列,比拍卖、函数或存款和储蓄进程中的变量,也许函数大概存款和储蓄过程的参数。

1、使用C#概念类型

用户定义类型必须兑现接口INullable,表明IsNull属性表示该品种是还是不是为空值,而且用户定义类型在C#可行三个可系列化的结构体表示,这一点和CLPAJERO用户自定义聚合函数1模一样。编写好C#代码后进行编写翻译生成DLL文件并革新到数据库中。

2、在SQL Server中采纳CL科雷傲用户定义类型

要创立CL昂科威用户定义类型需选择create
type命令,不仅能够创造基于SQL数据类型的用户自定义类型,也得以成立基于CLHaval的用户自定义类型。

create type [schema_name] type_name

external name assembly_name.[class_name]

三、使用CL索罗德用户自定义类型

create type myFirstType

external name myTypeAssembly.myFirstType

go

select table testMyFirstType

(

T myFirstType;

)

go

insert into testMyFirstType

values(‘1,7’);

insert into testMyFirstType

values(‘6,0’);

go

select T

from testMyFirstType

PS:如若您用的是 Visual Studio
20壹5,那么您能够在【项目路线>obj>Debug】文件夹上面找到自动生成的注册程序集和存款和储蓄进程的
SQL 语句。至于其它版本我们能够尝试。

PS:假设你用的是 Visual Studio
20一伍,那么您能够在【项目路线>obj>Debug】文件夹下边找到自动生成的挂号程序集和储存进度的
SQL 语句。至于别的版本我们能够试行。

函数

在数据库中,函数分为标量值函数、表值函数和聚合函数,标量值函数也正是回来“1个值”,表函数则重回贰个结果集。

实施存款和储蓄进度 HelloWorld:

实行存储进度 HelloWorld:

标量值函数(Scalar)

[Microsoft.SqlServer.Server.SqlFunction]
  public static SqlString myFunction(SqlString name)
  {
      return new SqlString("Hello," + name .ToString ());
  }

 

方法由“SqlFunction”标注,若是该函数必要拜访数据库,则要求将标注修改为:[Microsoft.SqlServer.Server.SqlFunction(DataAccess
= DataAccessKind .Read)].表示读取数据。SqlFunction还有以下属性:

IsDeterministic:提醒用户定义的函数是还是不是是分明性的,是则为 true;不然为
false;

Name :函数在 SQL Server
中登记时所运用的名称。不内定的话就是.net中的方法名;

IsPrecise:指示函数是还是不是涉嫌不确切的猜想,如浮点运算。是则为 true;不然为
false;

FillRowMethodName:方法的称呼,该情势与 电视F 协定所采纳的表值函数 (电视F)
在同三个类中。(见下一节)

TableDefinition:要是措施用作表值函数
(电视机F),则为1个字符串,该字符串表示结果的表定义(见下1节)。

测试如下:

997755.com澳门葡京 13

--执行存储过程 HelloWorld
exec [dbo].[HelloWorld]
--执行存储过程 HelloWorld
exec [dbo].[HelloWorld]

表值函数(TVF)

表值函数,重返的是个“表”(结果集),由此,在.net代码中回到的值应该完成Ienumerable的项目,

代码

  class student
   {
       public int ID { get; set; }
       public string Name { get; set; }
   }
[Microsoft.SqlServer.Server.SqlFunction(
       DataAccess = DataAccessKind.Read,
       FillRowMethodName = "F_GetReturnData",
       TableDefinition ="student_id int,student_name nvarchar(50)"
       )]
   public static IEnumerable f_GetStudentByClassId(SqlInt32 classid)
   {
       List<student> students = new List<student>();    
       using (SqlConnection conn = new SqlConnection("context connection= true"))
       {
           SqlCommand cmd = new SqlCommand();
           cmd.CommandText = "select  id_student,name_student from student where id_class =@id";
           SqlParameter paraname = new SqlParameter("@id", SqlDbType.Int);
           paraname.Value = classid;
           cmd.Parameters.Add(paraname);
           cmd.Connection = conn;
           conn.Open();
           SqlDataReader reader = cmd.ExecuteReader ();
           while (reader.Read())
           {
               students .Add (new student (){
                   ID = reader .GetInt32 (0),
                   Name = reader .GetString (1)});
           }             
           reader.Close();
       }
       return students;
   }
   public static void F_GetReturnData(object student, ref SqlInt32 id, ref SqlString name)
   {
       student s = student as student;
       id = s.ID;
       name = s.Name;
 }

 

下面的代码功能是基于班级ID再次回到学生音讯,f_GetStudentByClassId是在SQL
Server中可知的函数,方法前加标注SqlFunction,个中FillRowMethodName钦命“填充行”(往表中插入行)的方法
,TableDefinition表示回去的表的布局。如上例,表结构式含有student_id和student_name两个字段,将f_GetStudentByClassId获得的联谊在F_GetReturnData中填充给重回的表。需求注意的是,在填充行的方法(本例中的F_GetReturnData)中,第二个字段前面包车型地铁任何字段要与再次来到表的字段(TableDefinition中宣示的)类型一致,无法调换顺序,不然安排失利。

测试

997755.com澳门葡京 14

结果:

结果:

聚合函数

最近T-SQL拥有许多的停放聚合函数,如Sum、Count、马克斯,但松手聚合函数有时候并不能够满足急需,利用T-SQL不只怕新建聚合函数,但是SQL
CL昂Cora是足以的。

聚合函数也接受参数、再次来到值。传递给聚合函数的参数常常是1列值,平时与Group
By一块使用,将Group
By的每一个成效域的列传递给该聚合。聚合的职责就是在向其传递各样分力值的时候更新1个变量,将该变量值重返。

先看例子

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Format.UserDefined ,
    IsInvariantToNulls =true ,
    IsInvariantToDuplicates =false ,
    IsInvariantToOrder = false ,
    MaxByteSize = 8000)]
public struct myAggregate:IBinarySerialize
{
    public void Init()
    {
        studentname = new StringBuilder();
    }
    public void Accumulate(SqlString Value)
    {
        studentname.Append(Value.Value .ToString ());
        studentname.Append(",");
    }
    public void Merge(myAggregate Group)
    {
        studentname.Append(Group.studentname);
}
    public SqlString Terminate()
    {
        string result = studentname.ToString();
        result = result.Remove (result .LastIndexOf (','));
        return new SqlString(result);
    }
    
    private StringBuilder  studentname;
 
    public void Read(System.IO.BinaryReader r)
    {
        studentname = new StringBuilder(r.ReadString());
    }
 
  public void Write(System.IO.BinaryWriter w)
    {
        w.Write(this.studentname.ToString());
    }
}

 

聚合类必须具有多少个法子:Init,Accumulate、Merge、Terminate。

Init:初阶一个新的集合

Accumulate接受一个SQL类型,将分立值处理为汇集,

Terminate:重回一个SQL类型,在拍卖全数分力值之后回到最后的聚合值

Merge方法接受二个与该聚合本人类型相同的靶子,以便将其与实践实例相统壹。(SQL
Server有时会将为满意叁个查询所作的劳作分割到多少个线程上,因而须要对一回查询实行数次聚众,然后将结果合并在联合具名)

上述办法的目标是将同一班级的保有学员的真名以三个字符串的情势再次回到。在Accumulate方法上校有个别班级的具有学生姓名相加,在Terminate方法中,将最终收获的结果去除多余符号后重返。

测试

997755.com澳门葡京 15

集聚是SQL
CLHummerH二的好好应用,因为将待处理的数据值传递给了它们,所以它们仅须要实施总结任务,而不需求实行数量访问。

997755.com澳门葡京 16

997755.com澳门葡京 17

自定义类型(UDT)

从技术上来说,除了数据库本身所提供的花色外,我们能够成立其余类型,比如说对象。

先看例子

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct myType : INullable
{
    private double m_x;
 
    public double X
    {
        get { return m_x; }
        set { m_x = value; }
    }
    private double m_y;
 
    public double Y
    {
        get { return m_y; }
        set { m_y = value; }
    }
 
    private double m_z;
 
    public double Z
    {
        get { return m_z; }
        set { m_z = value; }
    }
public override string ToString()
    {
        if (this.IsNull)
        {
            return "null";
        }
        else
        {
            return this.m_x + "-" + this.m_y + "-" + this .m_z ;
      }
    }
 
    public bool IsNull
    {
        get
        {
            return m_Null;
 }
}
 
 public static myType Null
    {
        get
        {
            myType h = new myType();
            h.m_Null = true;
            return h;
        }
    }
 
 /// <summary>
    ///组织该类型对外的显示形式
    ///比如,下例是形如“x-y-z”的书写形式,通过该方法,将其分拆为对应字段的值
    /// </summary>
    /// <param name="s"></param>
    /// <returns></returns>
    public static myType Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        myType u = new myType();    
        string str = s.ToString();
        string[] xy = str.Split('-' );
        u.X = Convert.ToDouble(xy[0]);
        u.Y = Convert.ToDouble(xy[1]);
        u.Z = Convert.ToDouble(xy[2]);
        return u;
}  
 
    public string show()
    {
        return "X:" + this.Z.ToString() + ",Y:" + this.Y.ToString()  +",Z:" + this.Z.ToString();
}
 
    public static double sum(myType p)
    {
        return Math .Sqrt ( p.X * p.X + p.Y * p.Y  + p.Z  * p.Z );
    }
    public  double GetLength(myType p)
    {
        return Math.Sqrt(p.X * p.X + p.Y * p.Y + p.Z * p.Z);
    }
    private bool m_Null;
}

 

配备成功后,在表中新建字段,类型选为 myType 类型,保存。

997755.com澳门葡京 18

测试

997755.com澳门葡京 19

 

那就是出口信息,输出音讯和输出结果是不一致的,输出新闻是不能获得的(小编无法),而输出结果就也正是用
Select 语句询问出来的结果一律,是能够收获的。

那正是出口语资源音讯息,输出音讯和输出结果是不等同的,输出音讯是无法得到的(小编不能够),而输出结果就一定于用
Select 语句询问出来的结果同样,是能够取得的。

SQLCLR与T-SQL对比

一、 SQLCLBMWX5提供编制程序结构使数码操作和测算特别便于。

T-SQL 专门为数据库中的直接数据访问和操作而安排。就算 T-SQL
在数据访问和管理方面抢先,可是它并未有提供编制程序结构来使数据操作和计量越发便于。例如,T-SQL
不帮忙数组、集合、for-each 循环、位转移或类。固然在 T-SQL
中可以效仿当中一些构造,可是托管代码对那个构造提供合1帮助。根据方案的不等,那么些职能能够为使用托管代码完毕某个数据库功效提供让人心动的理由。

2、 对于计算和复杂的执行逻辑,托管代码比 T-SQL
更合乎,它周全援助广大复杂的任务,包含字符串处理和正则表明式。

由此 .NET Framework
库中提供的机能,能够访问数千个预生成的类和例程。能够很轻便从其余存储进度、触发器或用户定义函数进行访问。基类库
(BCL) 包涵的类提供用于字符串操作、高级数学生运动算、文件访问、加密等的功能。

三、 1般的话,函数和聚众是SQL CLLacrosse的优异应用。

SQL CL中华V 代码的开垦人员能够选拔 .NET Framework
API中留存的汪洋有用函数和类。这些类/函数库比 T-SQL
中援救的停放函数要抬高得多。其余,CLPAJERO 编制程序语言提供了 T-SQL
中所未有的拉长构造(例如数组和列表等)。与
T-SQL(它是一种解释语言)相比较,CL酷威编制程序语言之所以有着更加好的属性,是因为托管代码是已编译的。对于涉及算术计算、字符串处理、条件逻辑等的操作,托管代码的习性只怕要减价T-SQL 2个数目级。

4、
托管代码的1个独到之处是项目安全性,即确定保障代码只透过科学定义并且权限许可的章程访问类型。

在履行托管代码在此之前,CLEnclave将证古时候码是还是不是平安。例如,通过检查代码来确认保障不读取从前尚未写入的内部存款和储蓄器。CLHighlander还足以帮忙确定保证代码不操作非托管内部存款和储蓄器。

5、
开垦人士应该将SQLCLCR-V作为1种不恐怕利用T-SQL显式表明逻辑的准备化解方案。

SQLCL普拉多给开荒职员提供了另一种编写存款和储蓄进程的章程,然而利用T-SQL的注解性结构来拍卖依据集合的数据选用与修改要远远优于在.net中的进度化结构和ADO.NET对象模型中开始展览拍卖,由此SQLCLCRUISER无法作为落到实处业务层逻辑的替代品。那么依据那几个规则,开垦职员应该首先使用T-SQL消除难题。

6、 SQLCLR的局限

即使当中许多类可以从 SQL Server 的 CL中华V代码中动用,可是不切合服务器端使用的类(例如窗口类)将不能使用。

7、 面临的多少个选项

壹)选取 T-SQL 依然托管代码

在编写存款和储蓄进程、触发器和用户定义函数时,必须做的二个操纵是选取守旧的
T-SQL 照旧选拔 Visual Basic .NET 或 Visual C# 等 .NET Framework
语言。对于差不离或根本不供给进程逻辑的数码访问,请使用
T-SQL。对于有所复杂逻辑的 CPU 密集型函数和进度,或要选用 .NET Framework
的 BCL 时,请使用托管代码。

二)选用在服务器中推行大概在客户端中履行

控制动用 T-SQL
照旧托管代码的另三个成分是您愿意代码驻留的职位,驻留在服务器总结机上可能客户端计算机上。T-SQL
和托管代码均能够在服务器上运转。那样使代码和数码距离很近,能够选拔服务器的拍卖能力。另1方面,您恐怕希望防止将处理器密集型义务放在数据库服务器上。以往,半数以上客户端计算机十分强劲,您恐怕希望将尽恐怕多的代码放在客户端上,以使用客户端的处理能力。托管代码可以在客户端总计机上运营,而
T-SQL 不可能。

3)选取扩大存储过程只怕托管代码

扭转的增添存款和储蓄进度能够实施 T-SQL
存款和储蓄进程无法推行的成效。但是,扩充存款和储蓄进度会潜移默化 SQL Server
进度的完整性,而经过品种安全性验证的托管代码不会。此外,内部存款和储蓄器管理、线程和结构的调度以及一同服务在
CL奥迪Q7 的托管代码与 SQL Server 之间越来越深刻地合壹。通过 CLHaval集成,可以经过比扩张存款和储蓄进度更为安全、可伸缩性越来越强的措施来编排所需的蕴藏进度,以履行
T-SQL 中不也许履行的义务。

实践存款和储蓄进度 GetStrLength:

履行存款和储蓄进度 GetStrLength:

--执行存储过程 GetStrLength
declare @res int 
exec @res=[dbo].[GetStrLength] '123456' 
select @res 
--执行存储过程 GetStrLength
declare @res int 
exec @res=[dbo].[GetStrLength] '123456' 
select @res 

结果:

结果:

997755.com澳门葡京 20

997755.com澳门葡京 21

这个 C#
代码里面是有再次回到值的,也能够经过那种格局取获得重回值,不过那种再次来到值的方法只好回去
int 类型的重回值。

这个 C#
代码里面是有重临值的,也足以因此那种方法取得到重临值,不过那种再次来到值的章程只可以回去
int 类型的再次来到值。

设若急需多少个重返值呢?看上面的囤积进度,能够经过设置八个出口参数来完成。

假诺急需五个重临值呢?看下边包车型大巴贮存进程,能够透过安装多少个出口参数来完毕。

实施存款和储蓄进度 SayHello:

进行存款和储蓄进程 SayHello:

--执行存储过程 SayHello
declare @SayHello nvarchar(32)
exec [dbo].[SayHello] 'Brambling',@SayHello output 

select @SayHello
--执行存储过程 SayHello
declare @SayHello nvarchar(32)
exec [dbo].[SayHello] 'Brambling',@SayHello output 

select @SayHello

结果:

结果:

997755.com澳门葡京 22

997755.com澳门葡京 23

骨子里弄驾驭输入参数、输出参数、输出新闻、输出结果和重返值那些难题,CL宝马X5存储进度的介绍就足以完了。

实际上弄驾驭输入参数、输出参数、输出音信、输出结果和重返值那多少个难题,CLGL450存款和储蓄进度的牵线就可以完了。

可是存款和储蓄进程里面总是免不了要操作数据的,那么下边就看看对于数据库数据的操作和出口结果集的章程吧。

可是存款和储蓄进度之中总是免不了要操作数据的,那么上面就看看对于数据库数据的操作和出口结果集的办法呢。

   /// <summary>
    /// 根据学生学号获取学生姓名
    /// </summary>
    /// <param name="Id"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentNameByStuNo")]
    public static void GetStudentNameByStuNo(SqlString stuNo,out SqlString stoName)
    {
        stoName = string.Empty;

        //因为程序是在SQL Server内执行,所以连接字符串写成"context connection=true"即可
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select StuName from StudentInfo where StuNo=@StuNo;";

            SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);
            param.SqlValue = stuNo;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();
            if (dataReader.Read())
            {
                stoName = dataReader.GetString(0);
            }
            dataReader.Close();
        }
    }
   /// <summary>
    /// 根据学生学号获取学生姓名
    /// </summary>
    /// <param name="Id"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentNameByStuNo")]
    public static void GetStudentNameByStuNo(SqlString stuNo,out SqlString stoName)
    {
        stoName = string.Empty;

        //因为程序是在SQL Server内执行,所以连接字符串写成"context connection=true"即可
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select StuName from StudentInfo where StuNo=@StuNo;";

            SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);
            param.SqlValue = stuNo;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();
            if (dataReader.Read())
            {
                stoName = dataReader.GetString(0);
            }
            dataReader.Close();
        }
    }

执行存款和储蓄进程 GetStudentNameByStuNo:

实施存款和储蓄进程 GetStudentNameByStuNo:

declare @StuName nvarchar(32)
exec [GetStudentNameByStuNo] 'A001',@StuName output 
select @StuName 
exec [GetStudentNameByStuNo] 'A003',@StuName output 
select @StuName 
declare @StuName nvarchar(32)
exec [GetStudentNameByStuNo] 'A001',@StuName output 
select @StuName 
exec [GetStudentNameByStuNo] 'A003',@StuName output 
select @StuName 

结果:

结果:

997755.com澳门葡京 24

997755.com澳门葡京 25

能够看出我们经过输出参数获取到了再次回到值。如若未来本人供给取得整个学生的具有音讯吗?

可以看到大家透过输出参数获取到了重返值。借使今日笔者急需取得整个学生的全数音信呢?

即使能够因而设置两个出口参数获得,可是学生音讯的字段过多呢?上面看看输出结果集的方式。

即便能够因而设置多少个出口参数拿到,可是学生新闻的字段过多吗?下边看看输出结果集的章程。

   /// <summary>
    /// 根据学生的学号获取该学生的所有信息
    /// 返回的是一个结果集,即有多少条数据就返回多少条数据
    /// </summary>
    /// <param name="stuNo"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentInfoByStuNo_First")]
    public static void GetStudentInfoByStuNo_First(SqlString stuNo)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuNo=@StuNo;";

            SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);
            param.SqlValue = stuNo;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();
            SqlContext.Pipe.Send(dataReader);
            dataReader.Close();
        }
    }

    /// <summary>
    /// 根据学生的学号获取该学生的所有信息
    /// 这种方式效率比较高,是通过直接执行 SqlCommand 指令,然后把数据发送到客户端,不需要经过托管内存
    /// </summary>
    /// <param name="stuNo"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentInfoByStuNo_Second")]
    public static void GetStudentInfoByStuNo_Second(SqlString stuNo)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuNo=@StuNo;";

            SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);
            param.SqlValue = stuNo;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlContext.Pipe.ExecuteAndSend(comm);
        }
    }

    /// <summary>
    /// 根据学生的学号获取该学生的所有信息
    /// </summary>
    /// <param name="stuNo"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentInfoByStuNo_Third")]
    public static void GetStudentInfoByStuNo_Third(SqlString stuNo)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuNo=@StuNo;";

            SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);
            param.SqlValue = stuNo;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();

            SqlDataRecord dataRecord = new SqlDataRecord(
                new SqlMetaData[]
                {
                    new SqlMetaData("ID",SqlDbType.Int),
                    new SqlMetaData("StuNo",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuName",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuAge",SqlDbType.Int)
                }
            );

            if(dataReader.Read())
            {
                dataRecord.SetInt32(0,(int)dataReader["ID"]);
                dataRecord.SetString(1,(string)dataReader["StuNo"]);
                dataRecord.SetString(2,(string)dataReader["StuName"]);
                dataRecord.SetInt32(3,(int)dataReader["StuAge"]);
                SqlContext.Pipe.Send(dataRecord);
            }
            dataReader.Close();
        }
    }
   /// <summary>
    /// 根据学生的学号获取该学生的所有信息
    /// 返回的是一个结果集,即有多少条数据就返回多少条数据
    /// </summary>
    /// <param name="stuNo"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentInfoByStuNo_First")]
    public static void GetStudentInfoByStuNo_First(SqlString stuNo)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuNo=@StuNo;";

            SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);
            param.SqlValue = stuNo;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();
            SqlContext.Pipe.Send(dataReader);
            dataReader.Close();
        }
    }

    /// <summary>
    /// 根据学生的学号获取该学生的所有信息
    /// 这种方式效率比较高,是通过直接执行 SqlCommand 指令,然后把数据发送到客户端,不需要经过托管内存
    /// </summary>
    /// <param name="stuNo"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentInfoByStuNo_Second")]
    public static void GetStudentInfoByStuNo_Second(SqlString stuNo)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuNo=@StuNo;";

            SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);
            param.SqlValue = stuNo;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlContext.Pipe.ExecuteAndSend(comm);
        }
    }

    /// <summary>
    /// 根据学生的学号获取该学生的所有信息
    /// </summary>
    /// <param name="stuNo"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentInfoByStuNo_Third")]
    public static void GetStudentInfoByStuNo_Third(SqlString stuNo)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuNo=@StuNo;";

            SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);
            param.SqlValue = stuNo;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();

            SqlDataRecord dataRecord = new SqlDataRecord(
                new SqlMetaData[]
                {
                    new SqlMetaData("ID",SqlDbType.Int),
                    new SqlMetaData("StuNo",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuName",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuAge",SqlDbType.Int)
                }
            );

            if(dataReader.Read())
            {
                dataRecord.SetInt32(0,(int)dataReader["ID"]);
                dataRecord.SetString(1,(string)dataReader["StuNo"]);
                dataRecord.SetString(2,(string)dataReader["StuName"]);
                dataRecord.SetInt32(3,(int)dataReader["StuAge"]);
                SqlContext.Pipe.Send(dataRecord);
            }
            dataReader.Close();
        }
    }

实施存款和储蓄进程:

推行存储进程:

--执行存储过程 GetStudentInfoByStuNo_First
exec [GetStudentInfoByStuNo_First] 'A003'

--执行存储过程 GetStudentInfoByStuNo_Second
exec [GetStudentInfoByStuNo_Second] 'A003'

--执行存储过程 GetStudentInfoByStuNo_Third
exec [GetStudentInfoByStuNo_Third] 'A003'
--执行存储过程 GetStudentInfoByStuNo_First
exec [GetStudentInfoByStuNo_First] 'A003'

--执行存储过程 GetStudentInfoByStuNo_Second
exec [GetStudentInfoByStuNo_Second] 'A003'

--执行存储过程 GetStudentInfoByStuNo_Third
exec [GetStudentInfoByStuNo_Third] 'A003'

结果:

结果:

997755.com澳门葡京 26

997755.com澳门葡京 27

地方四个措施中,第一个措施和第3个艺术都是直接回到查询结果的,不过在事实上存储过程个中是不会那样写的,里面应该包罗有逻辑操作等等,所以就有了第多个办法。

上面多个方法中,第一个方法和第3个法子都以间接回到查询结果的,不过在事实上存款和储蓄进程在那之中是不会那样写的,里面应该包涵有逻辑操作等等,所以就有了第5个章程。

那么未来是回去的一条数据,借使是回到多条数据吧?第3种方法和第二种办法就不说了,因为这两种艺术都以回去结果集的。

那么今后是重临的一条数据,假使是回来多条数据吧?第二种艺术和第两种形式就隐瞒了,因为那两种方法都以重返结果集的。

   /// <summary>
    /// 根据年龄查询学生的信息
    /// 这种方式是一条数据返回一个结果集
    /// </summary>
    /// <param name="stuAge"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentsInfoByStuAge_Single")]
    public static void GetStudentsInfoByStuAge_Single(SqlInt32 stuAge)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuAge=@StuAge;";

            SqlParameter param = new SqlParameter("@StuAge", SqlDbType.Int);
            param.SqlValue = stuAge;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();

            SqlDataRecord dataRecord = new SqlDataRecord(
                new SqlMetaData[]
                {
                    new SqlMetaData("ID",SqlDbType.Int),
                    new SqlMetaData("StuNo",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuName",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuAge",SqlDbType.Int)
                }
            );

            while (dataReader.Read())
            {
                dataRecord.SetInt32(0, (int)dataReader["ID"]);
                dataRecord.SetString(1, (string)dataReader["StuNo"]);
                dataRecord.SetString(2, (string)dataReader["StuName"]);
                dataRecord.SetInt32(3, (int)dataReader["StuAge"]);
                //发送结果集到客户端
                SqlContext.Pipe.Send(dataRecord);
            }
            dataReader.Close();
        }
    }

    /// <summary>
    /// 根据年龄查询学生的信息
    /// 这种方式是所有的数据返回一个结果集
    /// </summary>
    /// <param name="stuAge"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentsInfoByStuAge_Multiple")]
    public static void GetStudentsInfoByStuAge_Multiple(SqlInt32 stuAge)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuAge=@StuAge;";

            SqlParameter param = new SqlParameter("@StuAge", SqlDbType.Int);
            param.SqlValue = stuAge;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();

            SqlDataRecord dataRecord = new SqlDataRecord(
                new SqlMetaData[]
                {
                    new SqlMetaData("ID",SqlDbType.Int),
                    new SqlMetaData("StuNo",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuName",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuAge",SqlDbType.Int)
                }
            );

            //标记结果集的开始
            SqlContext.Pipe.SendResultsStart(dataRecord);
            while (dataReader.Read())
            {
                dataRecord.SetInt32(0, (int)dataReader["ID"]);
                dataRecord.SetString(1, (string)dataReader["StuNo"]);
                dataRecord.SetString(2, (string)dataReader["StuName"]);
                dataRecord.SetInt32(3, (int)dataReader["StuAge"]);
                //填充数据到结果集
                SqlContext.Pipe.SendResultsRow(dataRecord);
            }
            //标记结果集的结束
            SqlContext.Pipe.SendResultsEnd();
            dataReader.Close();
        }
    }
   /// <summary>
    /// 根据年龄查询学生的信息
    /// 这种方式是一条数据返回一个结果集
    /// </summary>
    /// <param name="stuAge"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentsInfoByStuAge_Single")]
    public static void GetStudentsInfoByStuAge_Single(SqlInt32 stuAge)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuAge=@StuAge;";

            SqlParameter param = new SqlParameter("@StuAge", SqlDbType.Int);
            param.SqlValue = stuAge;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();

            SqlDataRecord dataRecord = new SqlDataRecord(
                new SqlMetaData[]
                {
                    new SqlMetaData("ID",SqlDbType.Int),
                    new SqlMetaData("StuNo",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuName",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuAge",SqlDbType.Int)
                }
            );

            while (dataReader.Read())
            {
                dataRecord.SetInt32(0, (int)dataReader["ID"]);
                dataRecord.SetString(1, (string)dataReader["StuNo"]);
                dataRecord.SetString(2, (string)dataReader["StuName"]);
                dataRecord.SetInt32(3, (int)dataReader["StuAge"]);
                //发送结果集到客户端
                SqlContext.Pipe.Send(dataRecord);
            }
            dataReader.Close();
        }
    }

    /// <summary>
    /// 根据年龄查询学生的信息
    /// 这种方式是所有的数据返回一个结果集
    /// </summary>
    /// <param name="stuAge"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentsInfoByStuAge_Multiple")]
    public static void GetStudentsInfoByStuAge_Multiple(SqlInt32 stuAge)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuAge=@StuAge;";

            SqlParameter param = new SqlParameter("@StuAge", SqlDbType.Int);
            param.SqlValue = stuAge;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();

            SqlDataRecord dataRecord = new SqlDataRecord(
                new SqlMetaData[]
                {
                    new SqlMetaData("ID",SqlDbType.Int),
                    new SqlMetaData("StuNo",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuName",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuAge",SqlDbType.Int)
                }
            );

            //标记结果集的开始
            SqlContext.Pipe.SendResultsStart(dataRecord);
            while (dataReader.Read())
            {
                dataRecord.SetInt32(0, (int)dataReader["ID"]);
                dataRecord.SetString(1, (string)dataReader["StuNo"]);
                dataRecord.SetString(2, (string)dataReader["StuName"]);
                dataRecord.SetInt32(3, (int)dataReader["StuAge"]);
                //填充数据到结果集
                SqlContext.Pipe.SendResultsRow(dataRecord);
            }
            //标记结果集的结束
            SqlContext.Pipe.SendResultsEnd();
            dataReader.Close();
        }
    }

执行存款和储蓄进度:

实践存款和储蓄进度:

--执行存储过程 GetStudentsInfoByStuAge_Single
exec [dbo].[GetStudentsInfoByStuAge_Single] '18'

--执行存储过程 GetStudentsInfoByStuAge_Multiple
exec [dbo].[GetStudentsInfoByStuAge_Multiple] '18'
--执行存储过程 GetStudentsInfoByStuAge_Single
exec [dbo].[GetStudentsInfoByStuAge_Single] '18'

--执行存储过程 GetStudentsInfoByStuAge_Multiple
exec [dbo].[GetStudentsInfoByStuAge_Multiple] '18'

结果:

结果:

997755.com澳门葡京 28

997755.com澳门葡京 29

能够很掌握的看看,方法1是一条数据重返1个结果集,方法贰是独具数据重回一个结实集。

能够很通晓的看到,方法1是一条数据重临一个结出集,方法2是兼具数据再次回到一个结果集。

下边贴出注册存款和储蓄进度的 SQL
语句,注册程序集的就不贴了,作者的上1篇博客有过介绍。

上面贴出注册存款和储蓄进度的 SQL
语句,注册程序集的就不贴了,小编的上一篇博客有过介绍。

--注册存储过程 HelloWorld
CREATE PROCEDURE [dbo].[HelloWorld] 
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[HelloWorld];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 GetStrLength
CREATE PROCEDURE [dbo].[GetStrLength] 
    @str [nvarchar](MAX)
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStrLength];        --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 SayHello
CREATE PROCEDURE [dbo].[SayHello] 
    @name [nvarchar](MAX), 
    @sayHello [nvarchar](MAX) OUTPUT
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[SayHello];        --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 GetStudentNameByStuNo
CREATE PROCEDURE [dbo].[GetStudentNameByStuNo] 
    @stuNo [nvarchar](MAX), 
    @stoName [nvarchar](MAX) OUTPUT
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentNameByStuNo];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 GetStudentInfoByStuNo_First
CREATE PROCEDURE [dbo].[GetStudentInfoByStuNo_First] 
    @stuNo [nvarchar](MAX)
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentInfoByStuNo_First];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 GetStudentInfoByStuNo_Second
CREATE PROCEDURE [dbo].[GetStudentInfoByStuNo_Second] 
    @stuNo [nvarchar](MAX)
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentInfoByStuNo_Second];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 GetStudentInfoByStuNo_Third
CREATE PROCEDURE [dbo].[GetStudentInfoByStuNo_Third] 
    @stuNo [nvarchar](MAX)
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentInfoByStuNo_Third];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 GetStudentsInfoByStuAge_Single
CREATE PROCEDURE [dbo].[GetStudentsInfoByStuAge_Single] 
    @stuAge [int]
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentsInfoByStuAge_Single];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 GetStudentsInfoByStuAge_Multiple
CREATE PROCEDURE [dbo].[GetStudentsInfoByStuAge_Multiple] 
    @stuAge [int]
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentsInfoByStuAge_Multiple];    --EXTERNAL NAME 程序集名.类名.方法名
GO
--注册存储过程 HelloWorld
CREATE PROCEDURE [dbo].[HelloWorld] 
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[HelloWorld];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 GetStrLength
CREATE PROCEDURE [dbo].[GetStrLength] 
    @str [nvarchar](MAX)
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStrLength];        --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 SayHello
CREATE PROCEDURE [dbo].[SayHello] 
    @name [nvarchar](MAX), 
    @sayHello [nvarchar](MAX) OUTPUT
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[SayHello];        --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 GetStudentNameByStuNo
CREATE PROCEDURE [dbo].[GetStudentNameByStuNo] 
    @stuNo [nvarchar](MAX), 
    @stoName [nvarchar](MAX) OUTPUT
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentNameByStuNo];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 GetStudentInfoByStuNo_First
CREATE PROCEDURE [dbo].[GetStudentInfoByStuNo_First] 
    @stuNo [nvarchar](MAX)
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentInfoByStuNo_First];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 GetStudentInfoByStuNo_Second
CREATE PROCEDURE [dbo].[GetStudentInfoByStuNo_Second] 
    @stuNo [nvarchar](MAX)
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentInfoByStuNo_Second];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 GetStudentInfoByStuNo_Third
CREATE PROCEDURE [dbo].[GetStudentInfoByStuNo_Third] 
    @stuNo [nvarchar](MAX)
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentInfoByStuNo_Third];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 GetStudentsInfoByStuAge_Single
CREATE PROCEDURE [dbo].[GetStudentsInfoByStuAge_Single] 
    @stuAge [int]
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentsInfoByStuAge_Single];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册存储过程 GetStudentsInfoByStuAge_Multiple
CREATE PROCEDURE [dbo].[GetStudentsInfoByStuAge_Multiple] 
    @stuAge [int]
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentsInfoByStuAge_Multiple];    --EXTERNAL NAME 程序集名.类名.方法名
GO

 

 

五、CLR Trigger

五、CLR Trigger

接下去选用增添新项,选取 SQL CLRubicon C# 触发器。

接下去采纳加多新项,选择 SQL CLMurano C# 触发器。

1、DML 触发器

1、DML 触发器

(1) after trigger

(1) after trigger

public partial class Triggers
{
    /// <summary>
    /// 输出操作的数据
    /// </summary>
    [Microsoft.SqlServer.Server.SqlTrigger(Name = "FirstSqlTrigger", Target = "StudentInfo", Event = "FOR INSERT,UPDATE,DELETE")]
    public static void FirstSqlTrigger()
    {
        switch (SqlContext.TriggerContext.TriggerAction)
        {
            case TriggerAction.Insert:
                GetInsertedOrDeleted(InsOrDel.Inserted);
                break;
            case TriggerAction.Update:
                GetInsertedOrDeleted(InsOrDel.Inserted);
                GetInsertedOrDeleted(InsOrDel.Deleted);
                break;
            case TriggerAction.Delete:
                GetInsertedOrDeleted(InsOrDel.Deleted);
                break;
            default:
                break;
        }
    }

    /// <summary>
    /// 获取操作的数据或之后的数据
    /// </summary>
    /// <param name="insOrDel"></param>
    /// <returns></returns>
    private static void GetInsertedOrDeleted(InsOrDel insOrDel)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from " + insOrDel.ToString() + ";";
            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();

            SqlDataRecord dataRecord = new SqlDataRecord(
                new SqlMetaData[]
                {
                    new SqlMetaData("ID",SqlDbType.Int),
                    new SqlMetaData("StuNo",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuName",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuAge",SqlDbType.Int)
                }
            );

            if (dataReader.Read())
            {
                dataRecord.SetInt32(0, (int)dataReader["ID"]);
                dataRecord.SetString(1, (string)dataReader["StuNo"]);
                dataRecord.SetString(2, (string)dataReader["StuName"]);
                dataRecord.SetInt32(3, (int)dataReader["StuAge"]);
                //发送结果集到客户端
                SqlContext.Pipe.Send(dataRecord);
            }
            dataReader.Close();
        }
    }

    private enum InsOrDel
    {
        Inserted,
        Deleted
    }
}
public partial class Triggers
{
    /// <summary>
    /// 输出操作的数据
    /// </summary>
    [Microsoft.SqlServer.Server.SqlTrigger(Name = "FirstSqlTrigger", Target = "StudentInfo", Event = "FOR INSERT,UPDATE,DELETE")]
    public static void FirstSqlTrigger()
    {
        switch (SqlContext.TriggerContext.TriggerAction)
        {
            case TriggerAction.Insert:
                GetInsertedOrDeleted(InsOrDel.Inserted);
                break;
            case TriggerAction.Update:
                GetInsertedOrDeleted(InsOrDel.Inserted);
                GetInsertedOrDeleted(InsOrDel.Deleted);
                break;
            case TriggerAction.Delete:
                GetInsertedOrDeleted(InsOrDel.Deleted);
                break;
            default:
                break;
        }
    }

    /// <summary>
    /// 获取操作的数据或之后的数据
    /// </summary>
    /// <param name="insOrDel"></param>
    /// <returns></returns>
    private static void GetInsertedOrDeleted(InsOrDel insOrDel)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from " + insOrDel.ToString() + ";";
            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();

            SqlDataRecord dataRecord = new SqlDataRecord(
                new SqlMetaData[]
                {
                    new SqlMetaData("ID",SqlDbType.Int),
                    new SqlMetaData("StuNo",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuName",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuAge",SqlDbType.Int)
                }
            );

            if (dataReader.Read())
            {
                dataRecord.SetInt32(0, (int)dataReader["ID"]);
                dataRecord.SetString(1, (string)dataReader["StuNo"]);
                dataRecord.SetString(2, (string)dataReader["StuName"]);
                dataRecord.SetInt32(3, (int)dataReader["StuAge"]);
                //发送结果集到客户端
                SqlContext.Pipe.Send(dataRecord);
            }
            dataReader.Close();
        }
    }

    private enum InsOrDel
    {
        Inserted,
        Deleted
    }
}

测试 SQL 语句:

测试 SQL 语句:

  -- Insert 操作
  insert into StudentInfo(StuNo,StuName,StuAge)
  values('A006','小飞',20)

  -- Update 操作
  update StudentInfo set StuName='小飞飞' where StuNo='A006' 

  -- Delete 操作
  delete from StudentInfo where StuNo='A006'
  -- Insert 操作
  insert into StudentInfo(StuNo,StuName,StuAge)
  values('A006','小飞',20)

  -- Update 操作
  update StudentInfo set StuName='小飞飞' where StuNo='A006' 

  -- Delete 操作
  delete from StudentInfo where StuNo='A006'

结果:

结果:

997755.com澳门葡京 30

997755.com澳门葡京 31

那边说喜宝(Hipp)下,Microsoft.SqlServer.Server.SqlTrigger 有多少个天性。

此处说美赞臣(Meadjohnson)下,Microsoft.SqlServer.Server.SqlTrigger 有四个特性。

Name:表示触发器的名号。

Name:表示触发器的称谓。

Target:表示触发器的目的表的名目。

Target:表示触发器的指标表的称谓。

伊夫nt:表示触发执行触发器的动作。

伊夫nt:表示触发执行触发器的动作。

 

 

(2) instead of trigger

(2) instead of trigger

public partial class Triggers
{
    /// <summary>
    /// 输出操作类型
    /// </summary>
    [Microsoft.SqlServer.Server.SqlTrigger(Name = "InsteadOfTrigger",Target = "StudentInfo",Event = "INSTEAD OF INSERT,UPDATE,DELETE")]
    public static void InsteadOfTrigger()
    {
        SqlDataRecord dataRecord = new SqlDataRecord(
            new SqlMetaData[]
            {
                new SqlMetaData("Message",SqlDbType.NVarChar,128)
            }
        );

        switch (SqlContext.TriggerContext.TriggerAction)
        {
            case TriggerAction.Insert:
                dataRecord.SetString(0, "Insert操作");
                break;
            case TriggerAction.Update:
                dataRecord.SetString(0, "Update操作");
                break;
            case TriggerAction.Delete:
                dataRecord.SetString(0, "Delete操作");
                break;
            default:
                dataRecord.SetString(0, "Nothing");
                break;
        }
        SqlContext.Pipe.Send(dataRecord);
    }
}
public partial class Triggers
{
    /// <summary>
    /// 输出操作类型
    /// </summary>
    [Microsoft.SqlServer.Server.SqlTrigger(Name = "InsteadOfTrigger",Target = "StudentInfo",Event = "INSTEAD OF INSERT,UPDATE,DELETE")]
    public static void InsteadOfTrigger()
    {
        SqlDataRecord dataRecord = new SqlDataRecord(
            new SqlMetaData[]
            {
                new SqlMetaData("Message",SqlDbType.NVarChar,128)
            }
        );

        switch (SqlContext.TriggerContext.TriggerAction)
        {
            case TriggerAction.Insert:
                dataRecord.SetString(0, "Insert操作");
                break;
            case TriggerAction.Update:
                dataRecord.SetString(0, "Update操作");
                break;
            case TriggerAction.Delete:
                dataRecord.SetString(0, "Delete操作");
                break;
            default:
                dataRecord.SetString(0, "Nothing");
                break;
        }
        SqlContext.Pipe.Send(dataRecord);
    }
}

测试 SQL 语句:

测试 SQL 语句:

-- Insert 操作
insert into StudentInfo(StuNo,StuName,StuAge)
values('A006','小飞',20)

-- Update 操作
update StudentInfo set StuName='小飞飞' where StuNo='A006' 

-- Delete 操作
delete from StudentInfo where StuNo='A006'
-- Insert 操作
insert into StudentInfo(StuNo,StuName,StuAge)
values('A006','小飞',20)

-- Update 操作
update StudentInfo set StuName='小飞飞' where StuNo='A006' 

-- Delete 操作
delete from StudentInfo where StuNo='A006'

结果:

结果:

997755.com澳门葡京 32

997755.com澳门葡京 33

Instead of
是壹种卓殊的触发器,它只进行触发器自个儿,约等于触发器里面包车型客车操作,

Instead of
是一种特有的触发器,它只进行触发器自己,也便是触发器里面的操作,

就此 Insert、Update、Delete 操作是不履行的,只是用于触发该触发器,而且
Instead of 触发器会覆盖掉 after 触发器。

从而 Insert、Update、Delete 操作是不实施的,只是用来触发该触发器,而且
Instead of 触发器会覆盖掉 after 触发器。

 

 

2、DDL 触发器

2、DDL 触发器

DDL
触发器又分为数据库级其他触发器和服务器级其余触发器,那里只介绍数据库级其他触发器。

DDL
触发器又分为数据库品级的触发器和服务器等级的触发器,那里只介绍数据库等级的触发器。

public partial class Triggers
{
    /// <summary>
    /// 禁止删除表和删除存储过程的 DDL 触发器
    /// </summary>
    [Microsoft.SqlServer.Server.SqlTrigger(Name = "SecondSqlTrigger")]
    public static void SecondSqlTrigger()
    {
        switch (SqlContext.TriggerContext.TriggerAction)
        {
            case TriggerAction.DropTable:
                try
                {
                    Transaction tran = Transaction.Current;
                    tran.Rollback();
                }
                catch
                {
                }
                SqlContext.Pipe.Send("You have no authority");
                break;
            case TriggerAction.DropProcedure:
                try
                {
                    Transaction tran = Transaction.Current;
                    tran.Rollback();
                }
                catch
                {
                }
                SqlContext.Pipe.Send("You have no authority");
                break;
            default:
                break;
        }
    }
}
public partial class Triggers
{
    /// <summary>
    /// 禁止删除表和删除存储过程的 DDL 触发器
    /// </summary>
    [Microsoft.SqlServer.Server.SqlTrigger(Name = "SecondSqlTrigger")]
    public static void SecondSqlTrigger()
    {
        switch (SqlContext.TriggerContext.TriggerAction)
        {
            case TriggerAction.DropTable:
                try
                {
                    Transaction tran = Transaction.Current;
                    tran.Rollback();
                }
                catch
                {
                }
                SqlContext.Pipe.Send("You have no authority");
                break;
            case TriggerAction.DropProcedure:
                try
                {
                    Transaction tran = Transaction.Current;
                    tran.Rollback();
                }
                catch
                {
                }
                SqlContext.Pipe.Send("You have no authority");
                break;
            default:
                break;
        }
    }
}

那边 DDL 的触发器,只须要钦点触发器名称的脾气就足以了。

那里 DDL 的触发器,只须求内定触发器名称的习性就可以了。

测试 SQL 语句:

测试 SQL 语句:

--删除表 StudentInfo
drop table StudentInfo
--删除表 StudentInfo
drop table StudentInfo

结果:

结果:

997755.com澳门葡京 34

997755.com澳门葡京 35

上面贴出注册触发器的 SQL 语句。

上边贴出注册触发器的 SQL 语句。

--注册触发器 FirstSqlTrigger
CREATE TRIGGER [FirstSqlTrigger] 
ON StudentInfo    --目标表
FOR INSERT,UPDATE,DELETE        --指定触发的操作
AS 
EXTERNAL NAME [UserDefinedSqlClr].[Triggers].[FirstSqlTrigger];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册触发器 InsteadOfTrigger
CREATE TRIGGER [InsteadOfTrigger] 
ON StudentInfo    --目标表
INSTEAD OF INSERT,UPDATE,DELETE        --指定触发的操作
AS 
EXTERNAL NAME [UserDefinedSqlClr].[Triggers].[InsteadOfTrigger];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册触发器 SecondSqlTrigger
CREATE TRIGGER [SecondSqlTrigger] 
ON database  --数据库级别触发器
for drop_table,drop_procedure        --指定触发的操作
AS 
EXTERNAL NAME [UserDefinedSqlClr].[Triggers].[SecondSqlTrigger];    --EXTERNAL NAME 程序集名.类名.方法名
GO
--注册触发器 FirstSqlTrigger
CREATE TRIGGER [FirstSqlTrigger] 
ON StudentInfo    --目标表
FOR INSERT,UPDATE,DELETE        --指定触发的操作
AS 
EXTERNAL NAME [UserDefinedSqlClr].[Triggers].[FirstSqlTrigger];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册触发器 InsteadOfTrigger
CREATE TRIGGER [InsteadOfTrigger] 
ON StudentInfo    --目标表
INSTEAD OF INSERT,UPDATE,DELETE        --指定触发的操作
AS 
EXTERNAL NAME [UserDefinedSqlClr].[Triggers].[InsteadOfTrigger];    --EXTERNAL NAME 程序集名.类名.方法名
GO

--注册触发器 SecondSqlTrigger
CREATE TRIGGER [SecondSqlTrigger] 
ON database  --数据库级别触发器
for drop_table,drop_procedure        --指定触发的操作
AS 
EXTERNAL NAME [UserDefinedSqlClr].[Triggers].[SecondSqlTrigger];    --EXTERNAL NAME 程序集名.类名.方法名
GO

删去存款和储蓄进度和删除触发器的 SQL
语句看似,唯一须求小心的正是删除数据库级其余触发器时,须要在后头加上 on
database,例如:

剔除存储进度和删除触发器的 SQL
语句看似,唯1必要留意的正是删除数据库级其他触发器时,须要在末端加上 on
database,例如:

--删除数据库级别触发器 SecondSqlTrigger
drop trigger [SecondSqlTrigger] on database
--删除数据库级别触发器 SecondSqlTrigger
drop trigger [SecondSqlTrigger] on database

实在触发器本人就很少用到,因为对此数据量大的时候,尤其影响属性,所以那里不多做牵线。

事实上触发器本人就很少用到,因为对于数据量大的时候,特别影响属性,所以那边不多做牵线。

能够参见那里:CLR
触发器.aspx)

能够参考那里:CLR
触发器.aspx)

 

 

六、总结

六、总结

终归写完了。。。

算是写完了。。。

实际上 CL君越 自定义函数、存款和储蓄进度和触发器等,不必然比 T-SQL
好用,准确的话性能稍微差一些。

实则 CL智跑 自定义函数、存款和储蓄进度和触发器等,不自然比 T-SQL
好用,准确的话质量稍微差一点。

唯独那只是提供1种艺术,遇到 T-SQL 不能缓解时方可思量的一种格局。

然则那只是提供一种方法,蒙受 T-SQL 不可能一举成功时方可考虑的一种办法。

到底明白的更加多,会的越来越多,碰着标题处理的法子就越多。

毕竟明白的愈来愈多,会的越多,际遇题目处理的法门就越来越多。

 

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website