SQL Server 2005中的CLR总结和自定义类型(UDT)

文章导航 SQL Server 2005 学习笔记系列文章导航

 

       CLR的最后一节，之前我写过关于自定义函数和XP的小例子，中间有朋友说不太合理，我也承认，呵呵 ，因为我当时也没有想起来用什么来说明它们的作用，不过功能相信大家都 明白了，有朋友问自定义类型是怎么回事，在这里我做个简单的介绍吧;

 1.用户定义的类型 (简称UDT)

        在早期的2000版本中也存在着这样的功能，使用用户定义的类型 (UDT)，可以扩展数据库的标量类型系统（不仅仅为系统类型定义您自己的别名，这在 SQL Server 以前的版本中一直可用）这是官方给下的定义，呵呵，说到这里不知道 大家有没有在数据里使用过UDT，如果有的话好最好，如果没有的话建议大家使用一下，可以根据向导来创建，在Sql2005里的可编辑性---类型--用户自定义类型，右击就可以看到创建功能了，定义 UDT 就像用托管代码编写类，创建程序集，然后使用“create type”语句在 SQL Server 中注册该类型一样简单。下面是实现 UDT 的主干代码：先看看它的格式吧

 

 

[SqlUserDefinedTypeAttribute(Format.Native)]
   public struct SimpleUdt: INullable
   {
      //返回结果
      public override string ToString() {...}
      public bool IsNull { get; }
      public static SimpleUdt Null { get; }
      public static SimpleUdt Parse(SqlString s) {...}
      ...
   }

create type simpleudt from [myassembly].[SimpleUdt]

create table t (mycolumn simpleudt)

 这些格式 相信只要使用Sql创建过UDT的朋友都会很熟悉的；

         在创建之前我们应该先了解另外的一个概念（标量类型系统）标量类型系统包括 SQLServer 附带的列类型（如 int、nvarchar 和 uniqueidentifier 等类型）。例如，使用 UDT，可以定义您自己的、用于列定义的类型。如果您的类型确实是一个适合建模为列的原子值，这个时候就可以使用UDT了；

         UDT不是在任何时候都可以使用的，请要使用 UDT 来对复杂的业务对象（如雇员、联系人或客户）进行建模。您将会陷入 UDT 的所有列限制（如，8KB 大小限制、索引限制）和在更新 UDT 值时更新整个值的不利方面。对于复杂类型，UDT 不是合适的数据建模抽象；因此对于这种情况，最好使用中间层对象相关映射技术。这点我测试过，如果你你部署过的类型需要更新的话，则数据库就不能有正在使用这个类型的表，如果有的话会提示更新不成功的;要把所有使用它的地方都修改过来才能部署，所以很不方便，部署完了，还要再修改为这个UPT才能生效;所以经常改动的类型或是增长的类型就不要使用UDT了;

         UDT 上的每个操作（除了比较）都要求 UDT 的值反序列化，接着进行方法调用。这种模式有与之相关的固定开销。如果要将类型建模为 UDT（相对于表中的列），则在访问类型属性（相对于表中的列）时应该考虑这种差别。如果类型上的行为非常复杂，则应该考虑使用 UDT。如果类型没有任何与之相关的行为，则应该考虑将数据存储为表中的列。

        下面我以一个例子的形式来说明它的功能吧， 也许我的例子不是很合适，但是说明功能应该是没有问题的，我们来定义一个这样的类型，如果你输入的值是数字而为1，否则为0，这样一个UDT，下面我们看一下过程 吧;

         我们要可以打开之前的项目，单击项目添加新建项

我们的自定义类型名称 是INullable，单击添加，我们看一下生成的代码如下

 

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct isNumber : INullable
{
    public override string ToString()
    {
        // 用您的代码替换下列代码
        return "";
    }

    public bool IsNull
    {
        get
        {
            // 在此处放置代码
            return m_Null;
        }
    }

    public static isNumber Null
    {
        get
        {
            isNumber h = new isNumber();
            h.m_Null = true;
            return h;
        }
    }

    public static isNumber Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        isNumber u = new isNumber();
        // 在此处放置代码
        return u;
    }

    // 这是占位符方法
    public string Method1()
    {
        //在此处插入方法代码
        return "Hello";
    }

    // 这是占位符静态方法
    public static SqlString Method2()
    {
        //在此处插入方法代码
        return new SqlString("Hello");
    }

    // 这是占位符字段成员
    public int var1;
    // 私有成员
    private bool m_Null;
}

 

  

 这是Ms一个Hello的程序，我们只要稍加改动就可以实现 我们的功能 ，我们先得定义一个方法来实现 验证是否为数字 的，我选择正则表达式;

 

 

 /// <summary>
    /// 是否数字字符串
    /// </summary>
    /// <param name="inputData">输入字符串</param>
    /// <returns></returns>
    public static bool IsNumber(string inputData)
    {
        Regex RegNumber = new Regex("^[0-9]+$");
        Match m = RegNumber.Match(inputData);
        return m.Success;
    }

 大家也可以采用其它的方法，我来看要怎么实现 ，我们先要在返回值的函数里实现，返回类型，我们可以看到上面的类里的 public override string ToString()
 方法返回的是一个NUll值

 我们让它返回为变量

 

// 这是占位符字段成员
    public int var1;

 

的值，这时我们还要修改一个返回前的方法

修改后的方法如下

 

  public static isNumber Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        isNumber u = new isNumber();

        // 返回int类型在这里
        if (IsNumber(s.ToString().Trim()))
        {
            u.var1 = 1;
        }
        else
        {
            u.var1 = 0;
        }

        // 在此处放置代码
        return u;
    }

 好了代码就这些下面是所有代码附上

 

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct isNumber : INullable
{
    public override string ToString()
    {
        // 用您的代码替换下列代码
        return var1.ToString();
    }

    public bool IsNull
    {
        get
        {
            // 在此处放置代码
            return m_Null;
        }
    }

    public static isNumber Null
    {
        get
        {
            isNumber h = new isNumber();
            h.m_Null = true;
            return h;
        }
    }

    public static isNumber Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        isNumber u = new isNumber();

        // 返回int类型在这里
        if (IsNumber(s.ToString().Trim()))
        {
            u.var1 = 1;
        }
        else
        {
            u.var1 = 0;
        }

        // 在此处放置代码
        return u;
    }
    /// <summary>
    /// 是否数字字符串
    /// </summary>
    /// <param name="inputData">输入字符串</param>
    /// <returns></returns>
    public static bool IsNumber(string inputData)
    {
        Regex RegNumber = new Regex("^[0-9]+$");
        Match m = RegNumber.Match(inputData);
        return m.Success;
    }

    // 这是占位符方法
    public string Method1()
    {
        //在此处插入方法代码
        return "Hello";
    }

    // 这是占位符静态方法
    public static SqlString Method2()
    {
        //在此处插入方法代码
        return new SqlString("Hello");
    }

    // 这是占位符字段成员
    public int var1;
    // 私有成员
    private bool m_Null;
}

下一步我们部署一下就行了，方法请参考（SQL Server 2005中的CLR(1) ），可以在Sql2005 里看到

 

我们可以修改这个表，这个时候我们就能看到自己的数据类型了，

效果如下图

选择我们的类型保存就可以了

下面我在表里写一些数据大家看一下

按我们的方法列type3就是UDT，我们要吧看的出来如果正常的话，前两行应该是0，而第三行是1，第四行和第五行应该是0，最后一行是1

我们看一下执行的结果

和我们想的是一样的，呵呵用法 基本上就是这样,在使用的过程中，大家要注意一下，不要使用Clr来处理增长的建模，怎么说呢，它毕竟是一个自定义的类型，有自己的缺陷，比如说每行的长度不能超过8KB等，如果要使用更高级点的功能大家自己动手吧;