.NET编程和SQL——SQL与CLR-UDT集成

数据库调优实验要求测试基于CLR的UDT的性能，但是这个从没听过的东西配置起来实在太麻烦了，教程很少而且好多都是无法实现的。终于找到一个比较靠谱的教程，在改正了其中一些错误后可以实现了。教程地址为:

http://msdn.microsoft.com/zh-cn/magazine/cc164013(en-us).aspx

下面介绍如何在Visual Studio 2010和SQL Server环境下实现SQL与CLR集成。这里只介绍具体方法：

1.在VS2010中建立C#控制台工程,编写类型定义的代码,并生成.dll文件. 下面附代码.

另外,这里生成.dll文件的方法是: 解决方案上右键->属性->应用程序面板中,输出类型选择"类库". 

代码完成后F5调试即可生成.dll文件,这时会弹窗报错,不用管.

using System;
using System.Data.SqlTypes;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
 
[Serializable]
[SqlUserDefinedType (Format.UserDefined, MaxByteSize = 150)]
public class Address: INullable, IComparable, IBinarySerialize {
    private bool is_Null;
    private string m_address1;
    private string m_address2;
    private string m_address3;
    private string m_city;
    private string m_state;
    private string m_zip;

    #region INullable Members

    public bool IsNull {
        get {return (is_Null);}
    }

    public static Address Null {
        get {
            Address addr = new Address();
            addr.is_Null = true;
            return (addr);
        }
    }

    #endregion

    #region Constructor
    public Address() {
        this.is_Null = true;
        this.m_address1 = "";
        this.m_address2 = "";
        this.m_address3 = "";
        this.m_city = "";
        this.m_state = "";
        this.m_zip = "";

    }
    #endregion

    #region String Conversion Members
    public override string ToString ()
    {
        if (this.IsNull) 
            return "null";
        else
        {
            string delim = new string( (new char[] {';'}));
            return (this.m_address1 + delim + this.m_address2 
                    + delim + this.m_address3 + delim 
                    + this.m_city + delim + this.m_state 
                    + delim + this.m_zip);
        }
    }
    public static Address Parse(SqlString s)
    {
        if(s.IsNull)
            return null;
        else
        {
            Address addr = new Address();
            string str = Convert.ToString(s);
            string[] a = null;
            a = str.Split(new char[] {':'} );
            if (a.Length >= 6)
            {
                addr.m_address1 = a[0] == null ? "" : a[0];
                addr.m_address2 = a[1];
                addr.m_address3 = a[2];
                addr.m_city = a[3];
                addr.m_state = a[4];
                addr.m_zip = a[5];
            }
            else
            {
                addr.m_address1 = "";
                addr.m_address2 = "";
                addr.m_address3 = "";
                addr.m_city = "";
                addr.m_state = "";
                addr.m_zip = "";
            }
            addr.is_Null = false;
            return (addr);
        }
    }
    #endregion
    
    #region Class Properties
    public string address1
    {
        get {return (this.m_address1);}
        set 
        {
            this.m_address1 = value;
            this.is_Null = false;
        }
    }
    public string address2
    {
        get {return (this.m_address2);}
        set 
        {
            this.m_address2 = value;
            this.is_Null = false;
        }
    }
    public string address3
    {
        get {return (this.m_address3);}
        set 
        {
            this.m_address3 = value;
            this.is_Null = false;
        }
    }
    public string city
    {
        get {return (this.m_city);}
        set 
        {
            this.m_city = value;
            this.is_Null = false;
        }
    }
    public string state
    {
        get {return (this.m_state);}
        set 
        {
            this.m_state = value;
            this.is_Null = false;
        }
    }
    public string zip
    {
        get {return (this.m_zip);}
        set 
        {
            this.m_zip = value;
            this.is_Null = false;
        }
    }

    public string cityStateZip()
    {
        return this.m_city + ", " + this.m_state + " "
                           + this.m_zip;
    }
    #endregion

    #region IComparable Members

    //Override the Equals method        
    public override bool Equals (object other)
    {
        return this.CompareTo (other) == 0;
    }

    //Override the GetHashCode method
    public override int GetHashCode ()
    {
        if (this.IsNull)
            return 0;

        return this.ToString ().GetHashCode ();
    }

    public int CompareTo (object other)
    {
        if (other == null)
            return 1; //by definition

        Address addr = other as Address;
    
        if (addr == null)
            throw new ArgumentException ("the argument to compare is not a Address");

        if (this.IsNull)
        {
            if (addr.IsNull)
                return 0;

            return -1;
        }

        if (addr.IsNull)
            return 1;

        return this.ToString().CompareTo(addr.ToString());
    }
    
    #endregion

    #region IBinarySerialize Members
    public void Write (System.IO.BinaryWriter w)
    {
        byte header = (byte)(this.IsNull ? 1 : 0);

        w.Write (header);
        if (header == 1)
        {
            return;
        }

        w.Write(this.address1);
        w.Write(this.address2);
        w.Write(this.address3);
        w.Write(this.city);
        w.Write(this.state);
        w.Write(this.zip);

    }

    public void Read (System.IO.BinaryReader r)
    {
        byte header = r.ReadByte();

        if (header == 1)
        {
            this.is_Null = true;
            return;
        }

        this.is_Null = false;
        this.m_address1 = r.ReadString();
        this.m_address2 = r.ReadString();
        this.m_address3 = r.ReadString();
        this.m_city = r.ReadString();
        this.m_state = r.ReadString();
        this.m_zip = r.ReadString();
    }
    #endregion

}

2.在SQL Server中使用UDT程序集

SQL Server默认情况下禁用了CLR集成的功能，必需先启用CLR集成后才能在SQL Server 访问.NET对象。

启用CLR集成

sp_configure 'clr enabled', 1 
GO 
RECONFIGURE 
GO 

然后就可以连接程序集,格式如下:

CREATE ASSEMBLY ASSEMBLY_NAME FROM 'PATH\ASSEMBLY_NAME.dll'

具体到我的工程中,如下

CREATE Assembly YukonCLR FROM 
'E:\programs\C# WPF\Address\Address\bin\Debug\Address.dll'

其中"Address.dll"是之前生成的dll文件,前面其地址.

关联成功后,我们还需要在数据库中定义相对应的数据类型,这样就可以像使用int或varchar一样使用我们前面定义的Address类型了. 定义方式如下:

create type Address external name YukonCLR.Address

之后就可以使用Address类型了.

我在测试中使用的整个代码如下:

sp_configure 'clr enabled', 1 
GO 
RECONFIGURE 
GO 

--导入dll文件,关联C#文件定义的类型
CREATE Assembly YukonCLR FROM 
'E:\programs\C# WPF\Address\Address\bin\Debug\Address.dll'

--在数据库中定义对应的数据类型
create type Address external name YukonCLR.Address

go
--数据类型的使用
DECLARE @addr Address
declare @addr2 Address, @addr3 Address

IF @addr3 IS NULL
    PRINT 'YES'
ELSE
    PRINT 'NO'

set @addr = convert(Address, '')
SET @addr.address1 = '1 Main Street'
SET @addr.city = 'Dallas'
SET @addr.state = 'TX'
SET @addr.zip = '75321'
PRINT @addr.cityStateZip()

SET @addr2 = CAST(@addr AS Address)
PRINT @addr2.cityStateZip()



--删除添加的项目
drop type Address
drop assembly YuKonCLR

总结:

在我的代码中,对原文的代码进行了一些修改,比如

1.C#文件中修改了Address类的转移构造函数,使得其参数不需要严格限定为":"分隔的六个字符串.

2.SQL代码中,对于Address具体数据域的访问应该用"."而非"::"

3.SQL代码中,声明的Address类型变量必须进行初始化  set @addr = convert(Address, '')  才能使用,否则下面赋值会报错.