之所以会去了解这个,是因为在最近的一个项目中需要用BizTalk来处理业务数据,而每一笔业务数据又对应着表中的很多条记录,发现做BizTalk的同事在实现中,每条记录都要调用一次存储过程,而且主表数据在每一次调用时都要传入,感觉有点不太好。于是想着是不是能用table类型作为存储过程的参数。研究了一下,似乎不行。但是可以用自定义的数据类型。
定义最普通的自定义类型很简单,见MSDN示例:
FROM varchar ( 11 ) NOT NULL ;
详细的到帮助文档上看吧。这里主要说一下怎么把用C#定义的类引入到SQL中作为自定的数据类型。
首先,在VS中定义一个类,这个类必须满足以下几点:
1、添加Serializable特性以标记为是可序列化的;
2、添加SqlUserDefinedType特性,注意设置该特性的一些属性值,如Format, Name, MaxByteSize,注意若Format设置为UserDefined的话一定要指定MaxByteSize值;
3、实现IBinarySerialize接口,该接口中声明了Read和Write两个方法,分别是用于从BinaryReader对象中读取数据到类中的属性和把类中的属性写入到BinaryReader对象;
4、实现INullable接口,该接口中声明了IsNull的get访问器,用于在sql中判断该类型的变量是否为null;
5、实现静态的返回类型为当前类类型的Null只读属性,返回一个在sql中认为为null的实例;
6、实现静态的Parse方法。该方法只有一个SqlString类型参数;
7、重载基类的ToString方法;
暂且只知道这一些,来看看我测试用的C#类吧:
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
namespace LfxSqlType
{
[Serializable]
[SqlUserDefinedType(Format.UserDefined, Name = " Person " , MaxByteSize = 100 )]
public class Person : IBinarySerialize, INullable
{
public string Name;
public int Age;
public char Sex;
#region IBinarySerialize 成员
public void Read(System.IO.BinaryReader r)
{
string s = r.ReadString();
string [] values = s.Split( ' | ' );
Name = values[ 0 ];
Age = Convert.ToInt32(values[ 1 ]);
Sex = Convert.ToChar(values[ 2 ]);
}
public void Write(System.IO.BinaryWriter w)
{
w.Write( string .Format( " {0}|{1}|{2} " , Name, Age.ToString(), Sex));
}
#endregion
#region INullable 成员
public bool IsNull
{
get { return string .IsNullOrEmpty(Name); }
}
#endregion
public static Person Null
{
get {
Person p = new Person();
p.Name = string .Empty;
return p;
}
}
public static Person Parse(SqlString str)
{
Person p = new Person();
string [] values = str.Value.Split( ' | ' );
p.Name = values[ 0 ];
p.Age = Convert.ToInt32(values[ 1 ]);
p.Sex = Convert.ToChar(values[ 2 ]);
return p;
}
public override string ToString()
{
if ( this .IsNull)
return " NULL " ;
else
return string .Format( " {0}|{1}|{2} " , Name, Age.ToString(), Sex);
}
}
}
再来看看SQL中如何根据这个类来创建自定义类型。首先,得引用该类所在的程序集:
from ' E:\NetSample\WinForm\SqlCustomerType\LfxSqlType\bin\Debug\LfxSqlType.dll '
语法是create assembly sql中的程序集名 from 程序集路径。具体参见MSDN。下一步,创建具体的类型:
external name lfxtype. [ LfxSqlType.Person ]
语法跟上边的也很类似了。create type 类型名 external name sql中程序集名.[C#类完全限定名]
接下来就可以做调用测试了:
create proc test
@p person
as
print @p .Name
go
-- 定义变量
declare @p person
-- 赋值
set @p = convert (person ,N ' lfx|2|y ' )
-- 执行存储过程
exec test @p
-- 弄个应该为null的值
set @p = convert (person, ' |2|y ' )
-- 判断是不是真为null
if @p is null
print ' null '
是不是很简单?呵呵