SQLCLR

 

 

什么是SQLCLR

SQL CLR (SQL Common Language Runtime) 是自 SQL Server 2005 才出现的新功能，它将.NET Framework中的CLR服务注入到 SQL Server 中，使得.NET代码可在SQL Server服务器进程中执行。

通过在 Microsoft SQL Server 中托管 CLR（称为 CLR 集成），开发人员可以在托管代码中编写存储过程、触发器、用户定义函数、用户定义类型和用户定义聚合函数, 改变了以前只能通过T-SQL语言来实现这些功能的局面。因为托管代码在执行之前会编译为本机代码，所以，在有些方案中可以大大提高性能。

 

SQLCLR实例

Visual Studio 2008提供了“SQL Server项目”类型，在这种类型的项目中，为5种基本SQL CLR实体定义了模板，使开发人员可以很容易的创建SQL CLR代码。

存储过程

打开vs2010，新建项目，选择“数据库”项目类型

点击确定后会让选择“数据库连接”，连接到目标数据库。

在项目中右键新建，发现可以新建的类型有函数、存储过程、聚合函数、触发器和用户自定义类型：

选择“新建存储过程”，生成的方法有“Microsoft.SqlServer.Server.SqlProcedure”标记，稍作修改，如下所示：

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

 

一个简单的存储过程就建好了（就是.net.中的一个方法），但是要注意该存储过程是没有返回值的。部署后测试（关于如何部署将在下一节演示）如下图所示：

例子中的类引用了System.Data.SqlTypes命名空间，其包含了SQL Server 中本地数据类型对应的类型，比如上面的SqlString类型对应于数据库中的 nvarchar，长度最大为 4000 。这些类提供一种比.NET Framework 公共语言运行库 (CLR) 提供的数据类型更快更安全的替代方案。使用此命名空间中的类有助于防止类型转换错误时出现精度损失的情况。还引用了Microsoft.SqlServer.Server 命名空间，该命名空间包含将 Microsoft .NET Framework 公共语言运行库 (CLR) 集成到 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。看测试结果

 对于output型参数，只需要在参数前添加ref即可，看下面的例子 

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

 在此仅仅是做一例子说明问题，查看测试结果

存储过程也可以返回结果集，看代码

 [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获取学生的信息，测试结果：

 在该例子中，也使用到了SqlContext.Pipe.Send()，而且发送的是SqlDataReader类型。该方法目前提供了三个重载方法，除了string、SqlDataReader外，还有SqlDataRecord。SqlDataRecord表示结果中的单个数据行及其元数据。

部署

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

查看状态：

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中查看

 “程序集”里面可以查看到我们部署的项，在“存储过程”里面也包含通过SQLCLR创建的存储过程，与一般存储过程不同的是，这些存储过程的图标上有个“锁”，而且是不可编辑的。

也可以利用SQL Server Management Studio来添加程序集，如下图所示：

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

更简单的方法是直接利用Visual Studio来自动部署。在项目上右键，选择“部署”，也可以通过点击调试菜单下的“启动调试”、“开始执行”等来部署，在部署前，需要先连接到数据库（点击项目属性，在数据库选项卡中设置），部署后可以发现，所有的存储过程均部署完毕。

其中，在项目属性的选项卡页面，有“权限级别”的设置，可用于指定向CLR程序集授予何种安全级别。

“安全”：程序集仅能执行本地数据访问和计算任务

“外部”：程序集可以执行本地数据访问和计算任务，还能访问网络、文件系统、注册表和环境变量。

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

调试

利用强大的VS，可以很容易的对SQLCLR进行调试，VS2010新建的数据库项目中有个Test.Sql文件，在该文件中可以直接书写测试的T-SQL，也可以添加断点，

就像其他项目那样调试，运行到断点后按F11可以进入到相关的.net方法中，即时窗口、变量监视等功能一个都不少。

如果在调试的时候报错如下图

原因及解决：当 Visual Studio 调试器无法使用要调试的数据库服务器注册用户时，将发生此错误。最可能的原因是缺少对 SQL Server 实例的必要权限。通过拥有 sysadmin 固定服务器角色的登录名将 Transact-SQL 编辑器连接到服务器是不够的；Visual Studio 用户的 Windows 登录名还必须是该 SQL Server 实例上 sysadmin 固定服务器角色的成员。

测试后的结果在“输出”选项卡中显示，细心观察的话，可以发现调试的时候会先将程序集部署。

触发器

触发器与存储过程类似，都是.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是触发器的事件类型。

测试

函数

在数据库中，函数分为标量值函数、表值函数和聚合函数，标量值函数也就是返回“一个值”，表函数则返回一个结果集。

标量值函数（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：如果方法用作表值函数 (TVF)，则为一个字符串，该字符串表示结果的表定义(见下一节)。

测试如下：

表值函数（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中声明的）类型一致，不能调换顺序，否则部署失败。

测试

聚合函数

目前T-SQL拥有许多的内置聚合函数，如Sum、Count、Max，但内置聚合函数有时候并不能满足需要，利用T-SQL无法新建聚合函数，但是SQL CLR是可以的。

聚合函数也接收参数、返回值。传递给聚合函数的参数通常是一列值，通常与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方法中，将最终得到的结果去除多余符号后返回。

测试

聚合是SQL CLR的出色应用，因为将待处理的数据值传递给了它们，所以它们仅需要执行计算任务，而不需要进行数据访问。

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

测试

 

SQLCLR与T-SQL对比

1、 SQLCLR提供编程结构使数据操作和计算更加容易。

T-SQL 专门为数据库中的直接数据访问和操作而设计。尽管 T-SQL 在数据访问和管理方面领先，但是它没有提供编程结构来使数据操作和计算更加容易。例如，T-SQL 不支持数组、集合、for-each 循环、位转移或类。尽管在 T-SQL 中可以模拟其中某些构造，但是托管代码对这些构造提供集成支持。根据方案的不同，这些功能可以为使用托管代码实现某些数据库功能提供令人心动的理由。

2、 对于计算和复杂的执行逻辑，托管代码比 T-SQL 更适合，它全面支持许多复杂的任务，包括字符串处理和正则表达式。

通过 .NET Framework 库中提供的功能，可以访问数千个预生成的类和例程。可以很容易从任何存储过程、触发器或用户定义函数进行访问。基类库 (BCL) 包括的类提供用于字符串操作、高级数学运算、文件访问、加密等的功能。

3、 一般来说，函数和聚合是SQL CLR的出色应用。

SQL CLR 代码的开发人员可以利用 .NET Framework API中存在的大量有用函数和类。这个类/函数库比 T-SQL 中支持的内置函数要丰富得多。此外，CLR 编程语言提供了 T-SQL 中所没有的丰富构造（例如数组和列表等）。与 T-SQL（它是一种解释语言）相比，CLR 编程语言之所以具有更好的性能，是因为托管代码是已编译的。对于涉及算术计算、字符串处理、条件逻辑等的操作，托管代码的性能可能要优于 T-SQL 一个数量级。

4、 托管代码的一个优点是类型安全性，即确保代码只通过正确定义并且权限许可的方式访问类型。

在执行托管代码之前，CLR 将验证代码是否安全。例如，通过检查代码来确保不读取以前未曾写入的内存。CLR 还可以帮助确保代码不操作非托管内存。

5、 开发人员应该将SQLCLR作为一种无法使用T-SQL显式表达逻辑的备选解决方案。

SQLCLR给开发人员提供了另一种编写存储过程的方法，但是利用T-SQL的声明性结构来处理基于集合的数据选择与修改要远远优于在.net中的过程化结构和ADO.NET对象模型中进行处理，因此SQLCLR不能作为实现业务层逻辑的替代品。那么根据这个规则，开发人员应该首先使用T-SQL解决问题。

6、 SQLCLR的局限

尽管其中许多类可以从 SQL Server 的 CLR 代码中使用，但是不适合服务器端使用的类（例如窗口类）将无法使用。

7、 面临的几个选择

1）选择 T-SQL 还是托管代码

在编写存储过程、触发器和用户定义函数时，必须做的一个决定是使用传统的 T-SQL 还是使用 Visual Basic .NET 或 Visual C# 等 .NET Framework 语言。对于几乎或根本不需要过程逻辑的数据访问，请使用 T-SQL。对于具有复杂逻辑的 CPU 密集型函数和过程，或要使用 .NET Framework 的 BCL 时，请使用托管代码。

2）选择在服务器中执行还是在客户端中执行

决定使用 T-SQL 还是托管代码的另一个因素是您希望代码驻留的位置，驻留在服务器计算机上还是客户端计算机上。T-SQL 和托管代码均可以在服务器上运行。这样使代码和数据距离很近，可以利用服务器的处理能力。另一方面，您可能希望避免将处理器密集型任务放在数据库服务器上。现在，大多数客户端计算机非常强大，您可能希望将尽可能多的代码放在客户端上，以利用客户端的处理能力。托管代码可以在客户端计算机上运行，而 T-SQL 不能。

3）选择扩展存储过程还是托管代码

生成的扩展存储过程可以执行 T-SQL 存储过程无法执行的功能。但是，扩展存储过程会影响 SQL Server 进程的完整性，而通过类型安全性验证的托管代码不会。另外，内存管理、线程和构造的调度以及同步服务在 CLR 的托管代码与 SQL Server 之间更深入地集成。通过 CLR 集成，可以通过比扩展存储过程更加安全、可伸缩性更强的方式来编写所需的存储过程，以执行 T-SQL 中无法执行的任务。