sql T_sql 关于CLR扩展函数的使用(1)

什么是SQL Server扩展函数呢？它实际上就是把C#或VB.NET的代码拿到SQL Server上去执行。反过来思考，那就是当你想对表数据进行比较复杂的逻辑处理时，写SQL又太麻烦，那么你就可以是否可以通过SQLCLR来解决这个问题了。

SQL CLR (SQL Common Language Runtime) 是自 SQL Server 2005 才出现的新功能，它将.NET Framework中的CLR服务注入到 SQL Server 中，让 SQL Server 的部份数据库对象可以使用 .NET Framework 的编程语言开发（目前只支持VB.NET和C#），包括预存程序、用户自定义函数、触发程序、用户自定义型别以及用户自定义汇总函数等功能。

几个定义：

1.Stored Procedures （SP:存储过程）
2.User Defined Function ( UDF:用户自定义函数)
3.User Definied Types (UDT:用户定义类型）
4.User Definied Aggregate（UDA:聚合函数）
5.Triggers（触发器）

下面我们来看看CLR是怎么实现的

①，我们需要对SQL2005或者SQL2008进行配置。

打开 SQL Server 外围应用配置器 --》功能外围应用配置器

找到CLR集成， 启用CLR集成，点击应用，确

②,通过VS2008创建数据库项目

 

创建后，我们还需要对项目进行数据库的配置，按照下图配置你想部署的数据库（我这里的例子是将项目部署到数据库test上）

 

我们来新建一个自定义函数类 FunctionClass.cs

 

接下来我们改造FunctionClass.cs 类，代码如下：

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

 

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString strcount(string str)

    {

        // 在此处放置代码

        //return new SqlString("Hello");

        return str.Length.ToString();

    }

 

 

    [SqlFunction(DataAccess = DataAccessKind.Read)]

    public static int ReturnOrderCount(string tb)

    {

        using (SqlConnection conn

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

        {

            conn.Open();

            SqlCommand cmd = new SqlCommand(

                "SELECT COUNT(*) AS 'Order Count' FROM " + tb + "", conn);

            return (int)cmd.ExecuteScalar();

        }

    }

   

   

    /// <summary>

    /// 返回一个唯一值

    /// </summary>

    /// <param name="Tb">对应的数据库表名</param>

    /// <param name="ColumnId">字段的Id</param>

    /// <param name="ColumnName">返回的字段名称</param>

    /// <param name="Id">Id的值</param>

    /// <returns>返回一个唯一值</returns>

    [SqlFunction(DataAccess = DataAccessKind.Read)]

 

    public static string GetNameById(string Tb, string ColumnId, string ColumnName, string Id)

    {

        string sql = "select " + ColumnName + " from " + Tb + " where " + ColumnId + "='" + Id + "'";

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

        SqlDataAdapter sda = new SqlDataAdapter();

        sda.SelectCommand = new SqlCommand(sql, conn);

        DataSet ds = new DataSet();

        sda.Fill(ds);

        DataTable dt = ds.Tables[0];

        if (dt != null)

        {

            return dt.Rows[0][0].ToString();

        }

        else

        {

            return "";

        }

    }

 

  

};

 

接下来我们编译并部署项目，我们打开数据库，会看到我们在项目中创建的3个方法：

strcount(string str)

ReturnOrderCount(string tb)

GetNameById(string Tb, string ColumnId, string ColumnName, string Id)

 

到此，我们就可以在数据库上运行这些函数了，我们来看下效果。

 

select dbo.strcount('abcde')

结果是：

 

 

select  dbo.ReturnOrderCount('instore')

 

结果是：

select dbo.GetNameById('instore','id','proname','8')

结果是：

 

附录数据库表结构以及数据：

 

 

备注：这个是返回标量函数的做法，后续我会发表关于 sql T_sql 关于CLR扩展函数的使用(2) 返回表值函数的方法做法。