您的位置:新葡亰496net > 网络数据库 > 新葡亰496net自定义函数

新葡亰496net自定义函数

发布时间:2019-09-27 14:15编辑:网络数据库浏览(198)

    一、简介

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

     

    hsrzyn 

    原著地址:

    Microsoft SQL Server 贰零零陆现在,完成了对 Microsoft .NET Framework 的公共语言运维时(CL奇骏)的集成。
    CL猎豹CS6 集成使得现在得以行使 .NET Framework 语言编写代码,进而能够在 SQL Server 上运转,将来就足以因此 C# 来编写 SQL Server 自定义函数、存款和储蓄进度、触发器等。
    本身最早的指标是因为在 SQL Server 数据库中相遇数字的十进制与十六进制的互动调换难点,也看过一些模式吧,但是最终我却选用了用 CL宝马X3 来做,毕竟在 C# 中两三行代码就会搞定的标题。。。

    一、SQL Server 为何要与CLKoleos集成

    1、 SQL Server 提供的存放进度、函数等十二分点滴,平时需求外表的代码来施行一些千斤的移植;

    2、与CL奥迪Q7集成可将原来需求单独的顺序来落到实处的作用迁移到SQL Server 内部进行多少操作;

    3、T-SQL数据查询语言在回去数据集方面很好,然则除却表现倒霉。与CL智跑的集成可消除这一标题;

    4、.NET的操作代码和进行的进程比T-SQL快的非常多。.NET程序是早已编写翻译好的二进制代码,并非作为存款和储蓄进度来营造,不再编写翻译就一直可运营。

    SQLCLR

     

    [翻译]在SQL Server中使用CLR调用.NET方法

     

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

    唯有在增添了程序集后技艺在该程序集的根基上确立CL奇骏存款和储蓄进度、CLMercedes-EQ函数等。

    1、CL中华V代码(编写翻译)→DLL文件(注册)→SQL Server (作为数据库对象)→实行数据库操作 进程如下:

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

    仓库储存进程、顾客自定义函数、触发器的编纂为类的静态方法;

    客商自定义类型、聚合函数编写为八个结构体。

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

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

    积累进度:create procedure

    客商自定义函数:create function

    触发器:create trigger

    客户自定义类型:create type

    聚合函数:create aggregate

    (4)像使用T-SQL例程同样接纳。

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

    (1)SQL Server 二〇一〇暗中认可境况下禁止使用了CLEvoque集成的作用,必得先启用CLRAV4集成后才具在SQL Server 访谈.NET对象。

    启用CLR集成

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

    解释

    (2)将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 访谈程序集时相程序集授予的一组访谈权限,暗许值为safe。

    (3)修改程序集

    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}:提醒在开立CL揽胜极光函数、存款和储蓄进程、触发器、客商定义的项目以及客户自定义聚合函数时,该程序集是还是不是可知。如若设置为OFF则程序集只可以由其他程序集调用。unchecked data :默许情形下,假如alter assembly 必需表明各样表行的一致性,则他将倒闭。该选项使得客户能够透过利用DBCC CHECKTABLE将检查推迟到事后的某部时刻张开。

    A、为顺序集添Gavin件:

    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

    SQL CLSportage (SQL Common Language Runtime) 是自 SQL Server 二〇〇六才出现的新效率,它将.NET Framework中的CL哈弗服务注入到 SQL Server 中,使得.NET代码可在SQL Server服务器进度中施行。

    透过在 Microsoft SQL Server 中托管 CL奥迪Q5(称为 CLCR-V集成),开辟人士能够在托管代码中编辑存款和储蓄进度、触发器、客商定义函数、客户定义类型和客商定义聚合函数, 改变了原先只可以通过T-SQL语言来完结这一个职能的规模。因为托管代码在推行在此以前会编写翻译为本机代码,所以,在稍微方案中能够大大升高品质。

     

    原著揭橥日期:二〇〇六.05.17
    作者:Mark Smith
    翻译:webabcd

    二、配置 SQL Server CLR

    三、创建CLR函数(Function)

      要创制被SQL Server 引用的CLRAV4程序则需求援引Microsoft.SqlServer.Server命名空间,创制CL奇骏函数还索要利用该命名空间下的SqlFunctionAttribute本性类将要[Microsoft.SqlServer.ServerSqlFunction.]停放CL奇骏函数的头顶。

    1、成立CLLAND标量值函数

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

    (2)在SQL Server中利用CLLacrosse标量值函数 使用create function创建引用注册程序集的函数。

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

    {@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君越函数中内定了returns null on null input ,它提示当SQL Server接收到的别的一个参数为null时,它能够回到null,而无须实际调用函数体。 优先利用create function语句提示的质量。无法为表值函数钦命Onnullcall属性。

    2、创造CL帕拉梅拉表值函数 
    (1)使用C#编写CLEvoque表值函数 
    CL奥迪Q5表值函数只回去八个表,在.NET中中创造对应的函数,再次回到的结果是三个IEnumerable接口,用于表示三个凑合。集合中是指标的实例并不是SQLServer中所识别的表,由此需求在函数的性子中钦点FillRowMethodName,那一个参数的值是用来将.NET中的对象调换为表列的函数名。将在天性[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillSplitTable")]停放与于表值函数的头顶,以钦点该性情下的函数为CL奇骏表值函数。在那之中,FillSplitTable是将.NET 中的对象转换为表列的函数名。还应该有用于将.NET中的对象转换为表列的方法必得为静态方法。第一个参数必得为System.Object类型,接下去的参数的个数正是列的个数。同不平时候接下去的参数都必需表明为ref参数。SQLServer中回到的列的数据类型和各种必得与该函数中ref参数的数据类型和顺序同样。编写完后编写翻译成DLL文件并增加到数据库中。 
    (2)在SQLServer中动用CLPRADO表值函数 
    A、更新程序集 
    要在SQLServer中使用C#编辑的CL凯雷德表值函数,必得先更新程序集。 
    如: 
    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])定义CLLAND函数的表数据项目。表评释仅包括列名称和数据类型。表始终放在主文件组中。 order(<order_clause>)内定从表值函数中回到结果的逐个。

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

    SQLCLR实例

    Visual Studio 2008提供了“SQL Server项目”类型,在那体系型的类型中,为5种为主SQL CL本田UR-V实体定义了模版,使开采职员可以很轻松的成立SQL CL库罗德代码。

    介绍
    我们一道来做个示范,在.NET中新建一个类,并在这么些类里新建贰个方法,然后在SQL Server中调用这么些主意。根据微软所述,通过宿主 Microsoft .NET Framework 2.0 公共语言运行库 (CL大切诺基),SQL Server 二〇〇六举世闻名地拉长了数据库编制程序模型。 那使得开荒人士能够用任何CLXC90语言(如C#、VB.NET或C 等)来写存款和储蓄进程、触发器和客户自定义函数。

    开启 CLR:

    四、创设CL索罗德存款和储蓄进度(Procedure)

    1、使用C#编写CL兰德Qashqai存款和储蓄进程所需的函数: 
      在C#中编辑可用以CL逍客存款和储蓄进度援引的函数必须选取SqlProcedure属性标志。存款和储蓄进度无需重回值,所以在C#中树立void函数就可以。存款和储蓄进度平常用来查询并生成叁个询问的表,在c#中需求选拔SqlPipe对象将表格结果与音信传播给顾客端。日常,通过SqlContext类的Pipe属性获得SqlPipe对象,后调用Pipe对象的Send()方法将表格结果或音信传递给客商端,恐怕选拔SqlPipe对象的ExecuteAndSend()方法将查询结果传送给客商端。ExecuteAndSend()方法提供了一种高功能的不二等秘书籍将查询结果传送给顾客端。使用性情[Microsoft.SqlServer.Server.SqlProcedure]停放在积累进程调用的函数的尾部,用以标示该函数是用作CL奥迪Q7存款和储蓄进度被调用的,CLTiggo存款和储蓄进程对应的函数。将C#编纂的代码编写翻译成DLL文件,并增多到数据库中。

    2、在SQL Server中运用CLENCORE存款和储蓄进程

    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_新葡亰496net,name.class_name.method_name钦定.net framework程序集的点子,以便程序集援用。class_name必需存在与该程序聚焦,何况点名的措施必得为此类的静态方法。

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

    3、创立有output参数的CL揽胜极光存储进程

    储存进度中也足以利用output参数,带有output的参数的值在蕴藏进程之中被修改后也会将修改应用到存款和储蓄进度表面约等于指针和ref参数。output参数对应于C#中的ref参数。

    4、在T-SQL中使用CLEnclave存款和储蓄进度

     

     

    积攒进程

    开拓vs二〇〇九,新建项目,接纳“数据库”项目项目

    新葡亰496net 1

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

    在等级次序中右键新建,开掘可以新建的品类有函数、存款和储蓄进程、聚合函数、触发器和顾客自定义类型:

    新葡亰496net 2

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

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

     

    三个总结的仓库储存过程就建好了(就是.net.中的三个办法),可是要小心该存款和储蓄进度是绝非再次回到值的。铺排后测验(关于怎么着安顿就要下一节演示)如下图所示:

    新葡亰496net 3

    事例中的类援引了System.Data.SqlTypes命名空间,其包括了SQL Server 中本地数据类型对应的品种,比方上边的SqlString类型对应于数据库中的 nvarchar,长度最大为 6000 。那么些类提供一种比.NET Framework 公共语言运营库 (CL库罗德) 提供的数据类型越来越快更安全的代表方案。使用此命名空间中的类有助于防范类型转变错误时现身精度损失的情景。还引用了Microsoft.SqlServer.Server 命名空间,该命名空间包涵将 Microsoft .NET Framework 公共语言运维库 (CLENVISION) 集成到 Microsoft SQL Server和SQL Server 数据库引擎进度实行情况时所要用到的类、接口和枚举。

    代码应用了SqlContext.Pipe.Send()方法,它的指标是将字符串新闻平昔发送到客商端或当前出口使用者,若无那行代码,那么实行该存款和储蓄进度后获得不到其余值。

    地点十二分例子未有再次来到值,也从不与数据库交互,上面那些事例与数据库交互:

    [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;
       }

     

    以此蕴藏进程完结的功用是依赖学生的人名获取学生的ID,当中name参数代表学生姓名,存款和储蓄进度的重临值就表示ID。看测验结果

    新葡亰496net 4

     对于output型参数,只供给在参数前增多ref就能够,看上面包车型客车例证 

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

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

    新葡亰496net 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获取学生的音信,测验结果:

    新葡亰496net 6

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

    小编们怎样完成这几个作用吗?
    为了选取CL普拉多,大家需求做如下几步:
        1、在.NET中新建多个类,并在这些类里新建贰个public方法。
        2、编译这么些类为叁个DLL。
        3、在SQL Server中注册这些DLL。
        4、新建三个SQL Server函数来访问钦赐的.NET方法。

    --开启所有服务器配置
    sp_configure 'show advanced options', 1; 
    RECONFIGURE WITH override 
    GO 
    --开启 CLR
    sp_configure 'clr enabled', 1; 
    RECONFIGURE WITH override 
    GO
    

    五、创建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#编写CLEnclave触发器是有相当的大恐怕用到触发器中的俩张特殊的表: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.] 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触发器

     

     

     

    部署

    在SQL Server2006/2009里面,CLENVISION默许是关闭的。能够动用如下SQL语句开启CL普拉多。

    查看情状:

    sp_configure 'clr enabled'

     开启

    exec sp_configure 'clr enabled',1  --1,启用clr,禁用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中查阅

    新葡亰496net 7

     “程序集”里面能够查看见大家安顿的项,在“存款和储蓄进程”里面也带有通过SQLCL途乐创设的仓库储存进程,与平日存款和储蓄进程不一致的是,那几个囤积过程的Logo上有个“锁”,况兼是不行编辑的。

    也得以运用SQL Server Management Studio来加多程序集,如下图所示:

    新葡亰496net 8

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

    更简约的不二诀要是直接选取Visual Studio来机关陈设。在档案的次序上右键,选取“计划”,也能够通过点击调节和测验菜单下的“运维调试”、“开端执行”等来布局,在布署前,供给先连接受数据库(点击项目性质,在数据库选项卡中设置),安顿后得以窥见,全部的仓储进程均安排完结。

    中间,在类型性质的选项卡页面,有“权限品级”的装置,可用以钦定向CL索罗德程序集授予何种安全等级。

    “安全”:程序集仅能奉行本地数据访谈和计算职责

    “外界”:程序集能够推行当地数据访问和计量任务,还是能够访谈网络、文件系统、注册表和处境变量。

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

    接下去,我们一起来产生一个演示
    率先,在Visual Studio中新建一个名称为“SQLServerCLRTest”的类库项目。 然后,新建三个名称叫“CL奥迪Q7Functions”的类,并在其内加多三个名称叫“HelloWold”的方式,代码如下:

    关闭 CLR:

    六、创建客商定义聚合函数(Aggregate)

    在SQL Server中,平时索要对数据按组举行自定义的联谊操作,暗中认可的聚合函数唯有SUM(),MAX(),MIN(),AVG()等,因而就须求定义顾客自定义聚合函数。

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

    创设客户自定义聚合函数必需利用性子[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]停放聚合函数的头顶,以标记该函数是客商自定义聚合函数。别的创设的聚合函数还非得是可类别化的,使用特性[Serializable]标识。

    聚合函数实际是五个布局类型或许说聚合函数对应的是三个struct类型并非二个方法,在中间必得兑现4个主意:

    (1)Init()初阶化函数: 为要管理的每组行调用Init()方法。在这一个艺术中,为要计算的每组行举办起先化;

    (2)Accumulate()定义具体群集操作的函数: 为全数组中的各种值调用这一个措施。这么些措施的参数必需是没有错的拉长类型,仍是能够上客商定义的连串。该函数概念聚合函数的实际集结操作;

    (3)Merge()合併函数: 聚合的结果必需和另叁个凑合结果合併起来,调用Merge()方法。

    (4)Terminate()甘休函数: 在拍卖每一组的结尾一行后,调用该方式。这里,聚合的结果必得用科学的数据类型再次回到。

    编纂好聚合函数后再度编译整个项目将DLL文件增添的数据库中。后使用alter assembly命令将聚集到SQL Server的顺序集中。

    2、在SQL Server中开创客户自定义聚合函数

    在SQL Server中创建客商自定义聚合函数以引用CLRAV4中的聚合函数。创建客商自定义聚合函数使用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] :表示钦点与顾客定义的聚合函数绑定在联合具名的顺序集以及(可选)该程序集所属的架构名称和该程序集中实现该客商定义聚合函数的类名称。

    3、在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 

    调试

    动用强劲的VS,能够很轻易的对SQLCLMurano举办调解,VS二零零六新建的数据库项目中有个Test.Sql文件,在该文件中能够直接书写测量试验的T-SQL,也能够增添断点,

    新葡亰496net 9

    仿佛另外品类那样调节和测验,运营到断点后按F11方可步向到有关的.net方法中,即时窗口、变量监视等效用八个都游人如织。

    新葡亰496net 10

    假设在调试的时候报错如下图

    新葡亰496net 11

    原因及化解:当 Visual Studio 调节和测验器无法利用要调治的数据库服务器注册顾客时,将生出此错误。最或许的缘故是远远不足对 SQL Server 实例的尤为重要权限。通过具有 sysadmin 固定服务器剧中人物的记名名将Transact-SQL 编辑器连接到服务器是缺乏的;Visual Studio 客商的 Windows 登入名还必得是该 SQL Server 实例上 sysadmin 固定服务器角色的分子。

    测验后的结果在“输出”选项卡中展现,留心阅览的话,能够窥见调节和测量试验的时候会先将顺序集布置。

    新葡亰496net 12public class CLRFunctions 
    新葡亰496net 13新葡亰496net 14新葡亰496net 15{
    新葡亰496net 16    public static string HelloWorld(string Name) 
    新葡亰496net 17新葡亰496net 18    新葡亰496net 19{
    新葡亰496net 20        return ("Hello "   Name);
    新葡亰496net 21    }
    新葡亰496net 22}

    --关闭所有服务器配置
    sp_configure 'show advanced options', 0; 
    RECONFIGURE WITH override 
    GO 
    --关闭 CLR
    sp_configure 'clr enabled', 0; 
    RECONFIGURE WITH override 
    GO
    

    七、创设CL福睿斯客户定义类型(UDT)

    成立CLENVISION顾客自定义类型来扩徐熙媛女士女士QL的类型系统,UDT可用来定义表中的列的门类或T-SQL中的变量或例程(存款和储蓄进程、触发器等)参数的项目。用户定义类型实例能够是表中的列,比拍卖、函数或存款和储蓄进度中的变量,或许函数只怕存款和储蓄进程的参数。

    1、使用C#概念类型

    客商定义类型必得兑现接口INullable,申明IsNull属性表示该品种是还是不是为空值,并且客户定义类型在C#卓有成效二个可连串化的结构身体表面示,那点和CLXC90客户自定义聚合函数一样。编写好C#代码后实行编写翻译生成DLL文件并创新到数据库中。

    2、在SQL Server中利用CL奥德赛客商定义类型

    要成立CLPRADO客户定义类型需采取create type命令,既可以够创制基于SQL数据类型的客商自定义类型,也能够创制基于CLOdyssey的客户自定义类型。

    create type [schema_name] type_name

    external name assembly_name.[class_name]

    3、使用CLSportage客商自定义类型

    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

    触发器

    触发器与积累进程看似,都以.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是触发器的风浪类型。

    测试

    新葡亰496net 23

    那是二个很轻便的艺术(为了让SQL Server能够调用它,它必得倘若public和static的),那些办法有贰个string类型的参数,再次回到消息为“Hello”加上你传入的参数。

    在背后注册 CL奥迪Q5程序集时,产生因操作权限难题而招致的败诉时,能够尝试进行上边的 SQL 语句,这里小编把 SQL 一并贴出来。

    函数

    在数据库中,函数分为标量值函数、表值函数和聚合函数,标量值函数也等于重临“一个值”,表函数则重返一个结果集。

    今日,大家要求编写翻译那一个项目为二个DLL,并在SQL Server中注册它。 那也是相比较轻易的,在VS中右键单击项目,采取“生成”后前后相继就能够生成三个DLL。 假诺您的类别是调节和测验形式的话,那么就能够在如下所示那样的门路里找到编写翻译好的DLL。

    --权限不够时,设置目标数据库为可信赖的,例如:Test
    ALTER DATABASE [Test] SET TRUSTWORTHY ON 
    
    --修改数据库所有者为当前登录的用户,也可以为其他用户,例如:sa
    EXEC sp_changedbowner 'sa'
    

    标量值函数(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:方法的称呼,该措施与 TVF 协定所采用的表值函数 (TVF) 在同三个类中。(见下一节)

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

    测验如下:

    新葡亰496net 24

    新葡亰496net 25C:Documents and Settingsmark.smithMy DocumentsVisual Studio 2005ProjectsSQLServerCLRTestSQLServerCLRTestbinDebugSQLServerCLRTest.dll

     

    表值函数(电视F)

    表值函数,再次来到的是个“表”(结果集),由此,在.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中声称的)类型一致,不可能沟通顺序,不然安插失利。

    测试

    新葡亰496net 26

    找到那个DLL后,大家就能够把它拷贝到大家的SQL Server机器上了,假诺是一模二样机器的话大家只要记住那个渠道就能够。

    三、CLR Function

    聚合函数

    前段时间T-SQL具备非常多的停放聚合函数,如Sum、Count、马克斯,但放到聚合函数有的时候候并不可能知足急需,利用T-SQL不能新建聚合函数,然则SQL CLENVISION是能够的。

    聚合函数也接到参数、重临值。传递给聚合函数的参数平常是一列值,平时与Group By一块使用,将Group By的每一种效能域的列传递给该聚合。聚合的职分就是在向其传递种种分力值的时候更新三个变量,将该变量值重临。

    先看例子

    [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方法中,将最终获得的结果去除多余符号后再次来到。

    测试

    新葡亰496net 27

    聚焦是SQL CL奥迪Q5的巧妙应用,因为将待管理的数据值传递给了它们,所以它们仅要求实行总括职分,而没有要求开展多少访谈。

    启用CLR功能
    暗中认可情状下,SQL Server中的CLENVISION是关门的,所以大家须要施行如下命令展开CLEvoque:

    开发 Visual Studio 新建二个 SQL Server 数据库项目,这里须求注意 .NET Framework 的版本。
    因为本人的目的数据库为 SQL Server 二零零六,所以这里本人选拔的是 .NET Framework 3.5 的本子。
    然后加多新建项,选拔 SQL CLENVISION C# 客户自定义函数,先从标量函数初叶。

    自定义类型(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 类型,保存。

    新葡亰496net 28

    测试

    新葡亰496net 29

     

    新葡亰496net 30exec sp_configure 'clr enabled',1   
    新葡亰496net 31reconfigure   
    新葡亰496net 32go 

     

    SQLCLR与T-SQL对比

    1、 SQLCLLAND提供编制程序结构使数码操作和测算越发便于。

    T-SQL 特地为数据库中的直接数据访问和操作而安插。就算 T-SQL 在数量访谈和管理方面超过,不过它从不提供编制程序结构来使数据操作和测算特别轻巧。比如,T-SQL 不扶助数组、集结、for-each 循环、位转移或类。纵然在 T-SQL 中能够效仿其中一些构造,不过托管代码对那些组织提供合一帮忙。依照方案的两样,那一个成效可以为利用托管代码实现某个数据库功效提供令人心动的理由。

    2、 对于计算和千头万绪的推行逻辑,托管代码比 T-SQL 更合乎,它周到支持广大复杂的职责,包蕴字符串管理和正则表明式。

    经过 .NET Framework 库中提供的作用,能够访谈数千个预生成的类和例程。能够很轻便从任何存款和储蓄进程、触发器或客户定义函数实行访谈。基类库 (BCL) 包涵的类提供用于字符串操作、高等数学生运动算、文件访谈、加密等的成效。

    3、 日常的话,函数和群集是SQL CL汉兰达的绝妙应用。

    SQL CL景逸SUV 代码的开拓职员可以行使 .NET Framework API中留存的豁达有用函数和类。这一个类/函数库比 T-SQL 中匡助的放置函数要增加得多。另外,CL逍客 编制程序语言提供了 T-SQL 中所未有的增加构造(比如数组和列表等)。与 T-SQL(它是一种解释语言)比较,CLOdyssey编制程序语言之所以具有更好的属性,是因为托管代码是已编译的。对于涉及算术总括、字符串管理、条件逻辑等的操作,托管代码的性质或许要巨惠T-SQL 八个多少级。

    4、 托管代码的多个优点是项目安全性,即确定保证代码只经过准显然义而且权限许可的措施访谈类型。

    在实施托管代码此前,CLR将证南宋码是不是安全。比方,通过检查代码来担保不读取以前并未有写入的内部存款和储蓄器。CL奥迪Q5还是能够支持确定保障代码不操作非托管内部存款和储蓄器。

    5、 开辟职员应该将SQLCLTiguan作为一种无法利用T-SQL显式表达逻辑的准备建设方案。

    SQLCL索罗德给开采人士提供了另一种编写存款和储蓄进度的主意,然而使用T-SQL的申明性结构来管理依据会集的数据选拔与修改要远远优于在.net中的进程化结商谈ADO.NET对象模型中实行管理,由此SQLCL途胜无法同日而语贯彻业务层逻辑的代替品。那么依照那个准绳,开垦人士应该率先应用T-SQL化解难题。

    6、 SQLCLR的局限

    就算个中好些个类能够从 SQL Server 的 CL奥迪Q3代码中采取,然而不切合服务器端使用的类(举例窗口类)将不只怕利用。

    7、 面临的多少个接纳

    1)选用 T-SQL 仍旧托管代码

    在编写存款和储蓄进程、触发器和客户定义函数时,必须做的一个操纵是采用守旧的 T-SQL 依然采用 Visual Basic .NET 或 Visual C# 等 .NET Framework 语言。对于大致或根本无需进程逻辑的数据访谈,请使用 T-SQL。对于有着复杂逻辑的 CPU 密集型函数和进程,或要利用 .NET Framework 的 BCL 时,请使用托管代码。

    2)采取在服务器中实践只怕在客商端中执行

    调控使用 T-SQL 如故托管代码的另贰个要素是您愿意代码驻留的职位,驻留在服务器计算机上也许客商端Computer上。T-SQL 和托管代码均能够在服务器上运营。那样使代码和多少路程非常近,能够选用服务器的管理才能。另一方面,您大概希望幸免将拍卖器密集型义务放在数据库服务器上。现在,大许多客商端Computer特别有力,您可能希望将尽心多的代码放在客户端上,以使用顾客端的管理手艺。托管代码能够在客商端Computer上运维,而 T-SQL 不能够。

    3)接纳扩张存款和储蓄进程只怕托管代码

    变化的恢宏存款和储蓄进度能够施行 T-SQL 存款和储蓄进程异常的小概施行的功用。可是,扩大存款和储蓄进程会影响 SQL Server 进度的完整性,而由此品种安全性验证的托管代码不会。其它,内部存款和储蓄器管理、线程和布局的调治以及三头服务在 CL兰德酷路泽 的托管代码与 SQL Server 之间更深入地融会。通过 CLCRUISER集成,能够由此比扩大存款和储蓄进程越是安全、可伸缩性更加强的格局来编排所需的积攒进程,以推行T-SQL 中不能够执行的天职。

    注册DLL
    为了调用大家写的老大形式,要求在SQL Server中登记大家恰好编译好的那五个DLL。 大家得以在数据库中利用如下命令来注册DLL(路线为你的DLL文件的门路)

    1、标量函数

    新葡亰496net 33新葡亰496net自定义函数。CREATE ASSEMBLY asmHelloWorld FROM 'C:SQLServerCLRTest.dll'   

    public partial class UserDefinedFunctions
    {
        /// <summary>
        /// 10进制转16进制
        /// </summary>
        /// <param name="strNumber"></param>
        /// <returns></returns>
        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "ConvertToHexadecimal")]
        public static SqlString ConvertToHexadecimal(SqlString strNumber)
        {
            SqlString result = string.Empty;
            string str = strNumber.ToString();
            int number = 0;
            if (int.TryParse(str, out number))
            {
                result = number.ToString("X");
            }
            return result;
        }
    
        /// <summary>
        /// 16进制转10进制
        /// </summary>
        /// <param name="strNumber"></param>
        /// <returns></returns>
        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "ConvertToDecimal")]
        public static SqlString ConvertToDecimal(SqlString strNumber)
        {
            SqlString result = string.Empty;
            string str = strNumber.ToString();
            int number = 0;
            try
            {
                number = int.Parse(str, System.Globalization.NumberStyles.HexNumber);
                result = Convert.ToString(number, 10);
            }
            catch
            {
            }
            return result;
        }
    }
    

    在SQL Server中调用我们的.NET方法
    为了调用.NET方法,大家能够写三个SQL Server自定义函数,并在其Nelly用“EXTE猎豹CS6NAL NAME”来打招呼SQL Server使用CL中华V功用。 代码如下:

     

    新葡亰496net 34CREATE FUNCTION dbo.clrHelloWorld   
    新葡亰496net 35(   
    新葡亰496net 36    @name as nvarchar(200)   
    新葡亰496net 37)    
    新葡亰496net 38RETURNS nvarchar(200)   
    新葡亰496net 39AS EXTERNAL NAME asmHelloWorld.[SQLServerCLRTest.CLRFunctions].HelloWorld 

    2、表值函数

    上边的自定义函数做了两项专门的学业。 首先是宣称了一个nvarchar参数,它同样于.NET里的string类型(假诺将其安装为varchar况兼后边使用了“EXTEEscortNAL NAME”的话就能够报错)。然后选取“EXTE奥迪Q5NAL NAME”来调用.NET方法。 语法如下:

    public partial class UserDefinedFunctions
    {
        /// <summary>
        /// SQL Server 字符串分割方法
        /// </summary>
        /// <param name="separator"></param>
        /// <param name="pendingString"></param>
        /// <returns></returns>
        [Microsoft.SqlServer.Server.SqlFunction(
            DataAccess = DataAccessKind.Read,
            IsDeterministic = true,
            Name = "SqlSplit",
            FillRowMethodName = "SqlSplit_FillRow",
            TableDefinition = "SerialNumber int,StringValue nvarchar(1024)")]
        public static IEnumerable SqlSplit(SqlString separator, SqlString pendingString)
        {
            string _separator = string.Empty;
            string _pendingString = string.Empty;
            if (separator.IsNull)
            {
                _separator = ",";
            }
            else
            {
                _separator = separator.ToString();
                if (string.IsNullOrEmpty(_separator))
                {
                    _separator = ",";
                }
            }
    
            if (pendingString.IsNull)
            {
                return null;
            }
            else
            {
                _pendingString = pendingString.ToString();
                if (string.IsNullOrEmpty(_pendingString))
                {
                    return null;
                }
            }
    
            string[] strs = _pendingString.Split(new string[] { _separator }, StringSplitOptions.RemoveEmptyEntries);
            if (strs.Length <= 0)
            {
                return null;
            }
    
            List<ResultData> resultDataList = new List<ResultData>();
            for (int i = 0; i < strs.Length; i  )
            {
                resultDataList.Add(new ResultData(i   1, strs[i]));
            }
            return resultDataList;
        }
    
        /// <summary>
        /// 填充数据方法
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="serialNumber"></param>
        /// <param name="stringValue"></param>
        public static void SqlSplit_FillRow(Object obj, out SqlInt32 SerialNumber, out SqlString StringValue)
        {
            ResultData resultData = (ResultData)obj;
            SerialNumber = resultData.SerialNumber;
            StringValue = resultData.StringValue;
        }
    
        /// <summary>
        /// 定义返回类型
        /// </summary>
        public class ResultData
        {
            /// <summary>
            /// 序号,即行号
            /// </summary>
            public SqlInt32 SerialNumber { get; set; }
    
            /// <summary>
            /// 分割后的每个子字符串
            /// </summary>
            public SqlString StringValue { get; set; }
    
            public ResultData(SqlInt32 serialNumber, SqlString stringValue)
            {
                SerialNumber = serialNumber;
                StringValue = stringValue;
            }
        }
    }
    

    新葡亰496net 40前后相继集名.类名.方法名

    SqlFunctionAttribute 的性情及介绍:

    而是,当自家动用这么些语法调用.NET方法的时候,SQL Server就能够报错,所感觉了让它平常办事,小编利用了之类语法:

    --属性                    --说明
    --DataAccess            --指示该函数是否涉及访问存储在SQL Server的数据
    --FillRowMethodName        --在同一个类的方法的名称作为表值函数(TVF),这个参数在表值函数中才会用到,用于指定表值函数的数据填充方法
    --IsDeterministic        --指示用户定义的函数是否是确定性的
    --IsPrecise                --指示函数是否涉及不精确计算,如浮点运算
    --Name                    --函数在SQL Server中注册时使用的函数的名称
    --SystemDataAccess        --指示该函数是否需要访问存储在系统目录或SQL Server虚拟系统表中的数据
    --TableDefinition        --如果方法作为表值函数(TVF),则为一个字符串,该字符串表示表结构的定义
    

    新葡亰496net 41次第集名.[类名].方法名

    标量函数与表值函数能够写在同二个类公事之中,况兼能够蕴含多少个,可是聚合函数就十分了,今后须要充裕二个新项,选取SQL CLRAV4 C# 聚合。

    于今大家就可以由此如下语句调用.NET方法了:

     

    新葡亰496net 42SELECT dbo.clrHelloWorld('Mark')

    3、聚合函数

    当您运营这段代码的时候,就能够赢得多个回到结果“Hello 马克”。

    自个儿这里写的那些聚合函数的作用是把七个字符串拼为一个字符串,笔者此前还真有碰着这种情况须要的。

    我们经过八个很简单的身体力行演示了怎么样促成SQL Server的CLCRUISER,它能够给我们带来多数十三分有效的增派。

    [Serializable]
    [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
        Format.UserDefined, 
        IsInvariantToDuplicates = false, 
        IsInvariantToNulls = true, 
        IsInvariantToOrder = false, 
        MaxByteSize = 8000, 
        Name = "SumString")]
    public struct UserDefinedSqlAggregate : IBinarySerialize
    {
        private StringBuilder stringBuilder;
    
        /// <summary>
        /// 查询处理器使用此方法初始化聚合的计算
        /// </summary>
        public void Init()
        {
            stringBuilder = new StringBuilder();
        }
    
        /// <summary>
        /// 查询处理器使用此方法累计聚合值
        /// </summary>
        /// <param name="Value"></param>
        public void Accumulate(SqlString Value)
        {
            stringBuilder.Append(string.Format("{0},", Value));
        }
    
        /// <summary>
        /// 查询处理器使用此方法合并聚合的多个部分计算的值
        /// </summary>
        /// <param name="Group"></param>
        public void Merge(UserDefinedSqlAggregate Group)
        {
            stringBuilder.Append(Group.stringBuilder);
        }
    
        /// <summary>
        /// 此方法用于返回完成聚合计算的结果
        /// </summary>
        /// <returns></returns>
        public SqlString Terminate()
        {
            return new SqlString(stringBuilder.ToString());
        }
    
        #region Implement interface IBinarySerialize
        /// <summary>
        /// 读
        /// </summary>
        /// <param name="r"></param>
        public void Read(System.IO.BinaryReader r)
        {
            stringBuilder = new StringBuilder(r.ReadString());
        }
    
        /// <summary>
        /// 写
        /// </summary>
        /// <param name="w"></param>
        public void Write(System.IO.BinaryWriter w)
        {
            w.Write(stringBuilder.ToString());
        }
        #endregion
    }
    

    SqlUserDefinedAggregateAttribute 的属性及介绍:

    --属性                        --说明
    --Format                    --选择序列化的 Format 格式,默认选择 Native,表示使用本地序列化格式。如果选择 UserDefined,则聚合类需要实现 IBinarySerialize 接口
    --IsInvariantToDuplicates    --指示聚合是否与重复的值相计算保持不变
    --IsInvariantToNulls        --指示聚合是否与空值相计算保持不变
    --IsInvariantToOrder        --指示聚合最后计算的结果是否与顺序无关
    --IsNullIfEmpty                --指示在没有对任何值进行累计时,聚合返回值是否为 null 
    --MaxByteSize                 --聚合实例的最大大小(以字节为单位)
    --Name                        --聚合函数的名称
    

    接下来生成项目,接下去注册程序集和挂号函数就能够动用了。

     

    4、注册 CLR 程序集

    挂号程序集的点子有以下三种:

    先是种,这种艺术注册程序集相比较轻松,不过劣势正是前后相继集无法活动或删除。

    --注册CLR程序集方式一,指定程序集DLL的路径
    USE Test 
    GO 
    CREATE ASSEMBLY UserDefinedClrAssembly 
    --AUTHORIZATION sa        --指定数据库所有者,默认为当前用户
    FROM 'C:UsersAdministratorDesktopCLR AssemblyUserDefinedSqlClr.dll'        --指定文件路径
    WITH PERMISSION_SET = UNSAFE;        --指定程序集的权限
                                    --SAFE:无法访问外部系统资源;
                                    --EXTERNAL_ACCESS:可以访问某些外部系统资源;
                                    --UNSAFE:可以不受限制的访问外部系统资源
    GO 
    

    此地若是爆发因为程序集拒绝访谈的荒谬,那就把Computer顾客 伊夫ryone 的权力改为完全调整就足以了。

    第三种,这种方式注册程序集稍微复杂一些,可是好处正是注册成功之后,能够运动还是删除DLL文件,只要不是改动员搬迁移数据库,都不用再行挂号。

    --注册CLR程序集方式二,指定程序集DLL的16进制文件流
    USE Test 
    GO 
    CREATE ASSEMBLY UserDefinedClrAssembly 
    --AUTHORIZATION sa        --指定数据库所有者,默认为当前用户
    FROM 0x4D5A90000300000004000000FFFF0000B8000000000000004000000000    --指定DLL的16进制文件流(当然没这么少,我删掉了)
    WITH PERMISSION_SET = UNSAFE;        --指定程序集的权限
                                    --SAFE:无法访问外部系统资源;
                                    --EXTERNAL_ACCESS:可以访问某些外部系统资源;
                                    --UNSAFE:可以不受限制的访问外部系统资源
    GO 
    

    取得DLL的16进制文件流,能够动用 UltraEdit 这几个软件,具体操作方法这里就十分的少说了。

    挂号成功之后,能够运用上边包车型客车 SQL 语句查看程序集的音讯,还包蕴查询自定义的函数、存款和储蓄进程等的SQL语句,那么些上边注册函数之后方可用到。

    --查看程序集信息
    SELECT * FROM sys.assemblies 
    
    --查看模块信息,即自定义函数、视图、存储过程、触发器等等
    SELECT * FROM sys.sql_modules
    GO 
    

     

    5、注册函数

    上面是三种函数的注册格局的 SQL 语句。

    USE Test 
    GO 
    
    --注册标量函数 ConvertToHexadecimal 
    CREATE FUNCTION [dbo].[ConvertToHexadecimal](@strNumber NVARCHAR(128))
    RETURNS NVARCHAR(128) 
    WITH EXECUTE AS CALLER        --用于在用户在执行函数的时候对引用的对象进行权限检查
    AS 
    EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedFunctions].[ConvertToHexadecimal]    --EXTERNAL NAME 程序集名.类名.方法名
    GO 
    
    --注册标量函数 ConvertToDecimal 
    CREATE FUNCTION [dbo].[ConvertToDecimal](@strNumber NVARCHAR(128))
    RETURNS NVARCHAR(128) 
    WITH EXECUTE AS CALLER        --用于在用户在执行函数的时候对引用的对象进行权限检查
    AS 
    EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedFunctions].[ConvertToDecimal]    --EXTERNAL NAME 程序集名.类名.方法名
    GO 
    
    --注册表值函数 SqlSplit 
    CREATE FUNCTION [dbo].[SqlSplit](@separator NVARCHAR(32),@string NVARCHAR(MAX))
    RETURNS TABLE 
    (
        SerialNumber INT,
        StringValue NVARCHAR(1024)
    )
    WITH EXECUTE AS CALLER        --用于在用户在执行函数的时候对引用的对象进行权限检查
    AS 
    EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedFunctions].[SqlSplit]    --EXTERNAL NAME 程序集名.类名.方法名
    GO 
    
    --注册聚合函数 SumString 
    CREATE AGGREGATE [dbo].[SumString](@params NVARCHAR(128))
    RETURNS NVARCHAR(MAX) 
    EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedSqlAggregate]    --EXTERNAL NAME 程序集名.类名
    GO 
    

    注册函数成功以后,接下去测验一下。

    DECLARE @TempTable TABLE
    (
        Id INT NOT NULL,
        Name NVARCHAR(32) NOT NULL 
    )
    INSERT INTO @TempTable (
        Id,
        [Name]
    )
    SELECT '1','小张' UNION ALL 
    SELECT '2','小明' UNION ALL 
    SELECT '2','小丽' UNION ALL 
    SELECT '2','小李' UNION ALL 
    SELECT '3','小王' UNION ALL 
    SELECT '3','小舞' 
    
    SELECT dbo.ConvertToHexadecimal('15')
    
    SELECT dbo.ConvertToDecimal('FC')
    
    SELECT * FROM SqlSplit(',',',123,456,789,')
    
    SELECT Id,dbo.SumString([Name]) Names 
    FROM @TempTable 
    GROUP BY Id 
    

    结果如图。

    新葡亰496net 43

     

    上边是删除函数和删除程序集的 SQL 语句,就算或许用不到,但是照旧贴出来吧。

    那边需求注意的是,删除程序集时要保管不设有函数、存款和储蓄进度、触发器等对前后相继集的援用。

    --删除标量函数 ConvertToHexadecimal 
    DROP FUNCTION dbo.ConvertToHexadecimal
    
    --删除标量函数 ConvertToDecimal 
    DROP FUNCTION dbo.ConvertToDecimal
    
    --删除表值函数 SqlSplit 
    DROP FUNCTION dbo.SqlSplit
    
    --删除聚合函数 SumString 
    DROP FUNCTION dbo.SumString
    
    --删除程序集 UserDefinedClrAssembly 
    DROP ASSEMBLY UserDefinedClrAssembly
    

     

    本想一篇写完的,依旧算了,存款和储蓄进程和触发器留待下一篇。

    其实存款和储蓄过程和触发器也没怎么了,只是 C# 代码差别而已,别的注册之类的几近。

     

    那边推荐一篇博客,大家也足以去看那篇,写得照旧挺完整的,某个地点都是以史为鉴于此。

     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net自定义函数

    关键词: