今天要介绍的就是sql2005的xml字段类型在.net中的应用。调用过程是：先运用并行化的办法把xml字段类型中的数据转换成model对象，对model对象操作后，再运用串行化的方法把model对象转变成xml格式，最后存储到数据库中。
我认为如果把复杂的业务关系数据存储在xml字段中，可简化数据库的设计，方便业务的处理。
这里写了个小demo：
假如我们有很多店铺信息，每个店铺都有一个shopid， 所以我们就把同一店铺的信息放在以shopid命名的文件夹下，当一台服务器放不下时，我们就部署多台，这样每台服务器存储的店铺是不一样的。这些服务器就构成了一个服务器群。出于需要，我们要把这个群复制多个，部署在不同的地区（注意，各个群的信息是相同的）。为了完成这个目的，我们先设计了数据模型 mservergroup（服务器群信息），mserver（服务器群下的服务器信息），mservershop（服务器对应的店铺）：
/// <summary>
/// 服务器群信息
/// </summary>
/// <remarks>
/// 用于存放点播文件服务器群的信息,比如主站的,北京站的,上海站的;各个站的数据相同.
/// 服务器群的目的是分散数据库的压力.
/// 目前只有主站的.
/// </remarks>
[serializable()]
public class mservergroup : basemodelentity
{
#region private
private int _servergroupid;
private string _servergroupname;
private mservercollection _servers;
#endregion
#region constructor
/// <summary>
/// 服务器群信息
/// </summary>
public mservergroup()
{
}
/// <summary>
/// 服务器群信息
/// </summary>
/// <param name="_servergroupid">服务器群id</param>
/// <param name="_servergroupname">服务器群名称</param>
public mservergroup(int _servergroupid, string _servergroupname)
{
this._servergroupid = _servergroupid;
this._servergroupname = _servergroupname;
}
#endregion
#region property
/// <summary>
/// 服务器群id
/// </summary>
public int servergroupid
{
get
{
return _servergroupid;
}
set
{
this._servergroupid = value;
}
}
/// <summary>
/// 服务器群名称
/// </summary>
public string servergroupname
{
get
{
return _servergroupname;
}
set
{
this._servergroupname = value;
}
}
/// <summary>
/// 服务器群下的服务器集合
/// </summary>
public mservercollection servers
{
get
{
return _servers;
}
set
{
this._servers = value;
}
}
#endregion
}
/// <summary>
/// 服务器群下的服务器信息
/// </summary>
/// <remarks>
/// 用于存放点播文件的服务信息
/// </remarks>
[serializable()]
public class mserver : basemodelentity
{
#region private
private int _serverid;
private string _servername;
private string _ip;
private string _domainname;
private string _dir;
private string _url;
private int _servergroupid;
private mservershopcollection _servershops;
#endregion
#region constructor
/// <summary>
/// 服务器信息
/// </summary>
public mserver()
{
}
/// <summary>
/// 服务器信息
/// </summary>
/// <param name="_serverid">服务器id</param>
/// <param name="_servername">服务器名称</param>
/// <param name="_ip">服务器ip</param>
/// <param name="_domainname">服务器域名</param>
/// <param name="_dir">文件存放目录</param>
/// <param name="_url">文件存放url</param>
/// <param name="_servergroupid">对应的服务器群id</param>
/// <param name="_servershops">服务器对应的店铺信息</param>
public mserver(int _serverid, string _servername, string _ip, string _domainname, string _dir, string _url, int _servergroupid, mservershopcollection _servershops)
{
this._serverid = _serverid;
this._servername = _servername;
this._ip = _ip;
this._domainname = _domainname;
this._dir = _dir;
this._url = _url;
this._servergroupid = _servergroupid;
this._servershops = _servershops;
}
/// <summary>
/// 服务器信息
/// </summary>
/// <param name="_serverid">服务器id</param>
/// <param name="_servername">服务器名称</param>
/// <param name="_ip">服务器ip</param>
/// <param name="_domainname">服务器域名</param>
/// <param name="_dir">文件存放目录</param>
/// <param name="_url">文件存放url</param>
/// <param name="_servergroupid">对应的服务器群id</param>
/// <param name="_xmlstrservershops">服务器对应的店铺信息的xml字符串</param>
public mserver(int _serverid, string _servername, string _ip, string _domainname, string _dir, string _url, int _servergroupid, string _xmlstrservershops)
{
this._serverid = _serverid;
this._servername = _servername;
this._ip = _ip;
this._domainname = _domainname;
this._dir = _dir;
this._url = _url;
this._servergroupid = _servergroupid;
this._servershops = common.utilities.serializationhelper<mservershopcollection>.fromxml(_xmlstrservershops);
}
#endregion
#region property
/// <summary>
/// 服务器id
/// </summary>
public int serverid
{
get
{
return _serverid;
}
set
{
this._serverid = value;
}
}
/// <summary>
/// 服务器名称
/// </summary>
public string servername
{
get
{
return _servername;
}
set
{
this._servername = value;
}
}
/// <summary>
/// 服务器ip
/// </summary>
public string ip
{
get
{
return _ip;
}
set
{
this._ip = value;
}
}
/// <summary>
/// 服务器域名
/// </summary>
public string domainname
{
get
{
return _domainname;
}
set
{
this._domainname = value;
}
}
/// <summary>
/// 文件存放目录
/// </summary>
public string dir
{
get
{
return dir;
}
set
{
this.dir = value;
}
}
/// <summary>
/// 文件存放url
/// </summary>
public string url
{
get
{
return _url;
}
set
{
this._url = value;
}
}
/// <summary>
/// 对应的服务器群id
/// </summary>
public int servergroupid
{
get
{
return _servergroupid;
}
set
{
this._servergroupid = value;
}
}
/// <summary>
/// 服务器对应的店铺信息
/// </summary>
public mservershopcollection servershops
{
get
{
return _servershops;
}
set
{
this._servershops = value;
}
}
#endregion
}
/// <summary>
/// 服务器对应的店铺
/// </summary>
/// <remarks>
/// 用于存放和服务器对应的店铺
/// </remarks>
[serializable()]
[xmlroot(elementname = "shop", namespace = "http://www.linkedu.com.cn/mservershop.xsd")]
public class mservershop : basemodelentity
{
#region private
private int _shopid;
private string _shopname;
#endregion
#region constructor
/// <summary>
/// 服务器对应的店铺信息
/// </summary>
public mservershop()
{
}
/// <summary>
/// 服务器对应的店铺信息
/// </summary>
/// <param name="_shopid">店铺id</param>
/// <param name="_shopname">店铺名称</param>
public mservershop(int _shopid, string _shopname)
{
this._shopid = _shopid;
this._shopname = _shopname;
}
#endregion
#region property
/// <summary>
/// 店铺id
/// </summary>
[xmlattribute]
public int shopid
{
get
{
return _shopid;
}
set
{
this._shopid = value;
}
}
/// <summary>
/// 店铺名称
/// </summary>
[xmlattribute]
public string shopname
{
get
{
return _shopname;
}
set
{
this._shopname = value;
}
}
#endregion
}
为了对模型的集合信息进行描述，我们有设计了mservergroupcollection（服务器群信息集合），mserver（服务器群下的服务器信息），mservershopcollection（服务器对应的店铺集合）
/// <summary>
/// 服务器群信息集合
/// </summary>
/// <remarks>
[serializable()]
[xmlroot("servergroups")]
public class mservergroupcollection : list<mservergroup>
{
/// <summary>
/// 服务器群信息集合
/// </summary>
public mservergroupcollection()
{
this._mservergroups = new list<mservergroup>();
}
private list<mservergroup> _mservergroups;
public list<mservergroup> mservergroups
{
get
{
return this._mservergroups;
}
set
{
this._mservergroups = value;
}
}
}
/// <summary>
/// 服务器群下的服务器信息集合
/// </summary>
[xmlroot("servers")]
[serializable()]
public class mservercollection : list<mserver>
{
/// <summary>
/// 服务器群下的服务器信息集合
/// </summary>
public mservercollection()
{
this._mservers = new list<mserver>();
}
private list<mserver> _mservers;
public list<mserver> mservers
{
get
{
return this._mservers;
}
set
{
this._mservers = value;
}
}
}
/// <summary>
/// 服务器对应的店铺集合
/// </summary>
[serializable()]
[xmlroot(elementname = "shops", namespace = "http://www.linkedu.com.cn/mservershop.xsd")]
public class mservershopcollection
{
private list<mservershop> _mservershops;
[xmlelement("shop")]
public list<mservershop> mservershops
{
get
{
return this._mservershops;
}
set
{
this._mservershops = value;
}
}
/// <summary>
/// 服务器对应的店铺集合类
/// </summary>
public mservershopcollection()
{
this._mservershops = new list<mservershop>();
}
}
经分析，服务器对应的店铺信息可用xml存储，设计格式如下（用xsd描述，设计好后，我们把它创建到数据库中）
create xml schema collection [dbo].[mservershop] as
n'<xsd:schema xmlns:xsd="http://www.w3.org/2001/xmlschema" xmlns:t="http://www.linkedu.com.cn/mservershop.xsd" targetnamespace="http://www.linkedu.com.cn/mservershop.xsd" elementformdefault="qualified">
<xsd:element name="shops">
<xsd:complextype>
<xsd:complexcontent>
<xsd:restriction base="xsd:anytype">
<xsd:sequence>
<xsd:element name="shop" type="t:servershop" minoccurs="0" maxoccurs="unbounded" />
</xsd:sequence>
</xsd:restriction>
</xsd:complexcontent>
</xsd:complextype>
</xsd:element>
<xsd:complextype name="servershop">
<xsd:complexcontent>
<xsd:restriction base="xsd:anytype">
<xsd:sequence />
<xsd:attribute name="shopid" type="xsd:int" use="required" />
<xsd:attribute name="shopname" type="xsd:string" use="required" />
</xsd:restriction>
</xsd:complexcontent>
</xsd:complextype>
</xsd:schema>'
最后，我设计了（服务器群信息 es_servergroup），（服务器群下的服务器信息 es_server）的数据表, 在 es_server 数据表中，我们把服务器对应的店铺信息放在es_server数据表下用xml表示，并加入上边设计的xsd约束。
create table [dbo].[es_servergroup](
[servergroupid] [int] not null,
[servergroupname] [nvarchar](20) collate chinese_prc_ci_as not null,
constraint [pk_es_servergroup] primary key clustered
(
[servergroupid] asc
)with (ignore_dup_key = off) on [primary]
) on [primary]
create table [dbo].[es_server](
[serverid] [int] not null,
[servergroupid] [int] null,
[servername] [nvarchar](20) collate chinese_prc_ci_as not null,
[ip] [nvarchar](15) collate chinese_prc_ci_as null,
[domainname] [nvarchar](20) collate chinese_prc_ci_as null,
[dir] [nvarchar](255) collate chinese_prc_ci_as null,
[url] [nvarchar](255) collate chinese_prc_ci_as null,
[servershops] [xml](content [dbo].[mservershop]) null,
constraint [pk_es_server] primary key clustered
(
[serverid] asc
)with (ignore_dup_key = off) on [primary]
) on [primary]
下一步，我开始设计数据访问接口，然后设计数据访问层和业务层，最后设计表现层。
为了演示方便，demo中我省去了接口的书写和业务层，我在表现层直接调用了数据访问层
数据访问层代码如下：
/// <summary>
/// 服务器群下的服务器信息数据访问层
/// </summary>
public class dserver
{
#region constructor
public dserver()
{
}
#endregion
#region public method
#region 得到当前
#region 得到某服务器信息 mserver getmserver(int _serverid)
/// <summary>
/// 得到某服务器信息
/// </summary>
/// <param name="_serverid">服务器的serverid</param>
/// <returns>得到某服务器信息</returns>
public mserver getmserver(int _serverid)
{
dataprovider dp = sqltools.helpwww.dataprovideruse;
using (idbconnection conn = dp.getconnection())
{
common.dataaccess.ormapping.iconvert ic = dp.geticonvert();
common.dataaccess.ormapping.mapping m = ic.getnewmapping();
m.addsql("select * from es_server where ");
m.addsql(" serverid=");
m.addsql("serverid", _serverid);
using (idatareader idr = m.executereader(conn))
{
if (idr.read())
{
return new mserver(_serverid, idr["servername"].tostring(), idr["ip"].tostring(), idr["domainname"].tostring(), idr["dir"].tostring(), idr["url"].tostring(), (int)idr["servergroupid"], idr["servershops"].tostring());
}
}
return null;
}
}
#endregion
#region 关于mservershop的操作
#region 得到服务器对应的店铺集合 mservershopcollection getmservershop(int _serverid)
/// <summary>
/// 得到服务器对应的店铺集合
/// </summary>
/// <param name="_serverid">服务器的serverid</param>
/// <returns>得到服务器对应的店铺数组</returns>
public mservershopcollection getmservershop(int _serverid)
{
dataprovider dp = sqltools.helpwww.dataprovideruse;
using (idbconnection conn = dp.getconnection())
{
common.dataaccess.ormapping.iconvert ic = dp.geticonvert();
common.dataaccess.ormapping.mapping m = ic.getnewmapping();
m.addsql("select servershops from es_server where ");
m.addsql(" serverid=");
m.addsql("serverid", _serverid);
string xmlstr = m.executescalar(conn).tostring();
return common.utilities.serializationhelper<mservershopcollection>.fromxml(xmlstr);
}
}
#endregion
#region 保存服务器对应的店铺信息 void savemservershops(int _serverid, mservershopcollection _servershops)
/// <summary>
/// 保存服务器对应的店铺信息
/// </summary>
/// <param name="_serverid">服务器的serverid</param>
/// <param name="_servershops">服务器对应的店铺信息集合</param>
public void savemservershops(int _serverid, mservershopcollection _servershops)
{
string xmlstr = common.utilities.serializationhelper<mservershopcollection>.toxml(_servershops);
dataprovider dp = sqltools.helpwww.dataprovideruse;
using (idbconnection conn = dp.getconnection())
{
common.dataaccess.ormapping.iconvert ic = dp.geticonvert();
common.dataaccess.ormapping.mapping m = ic.getnewmapping();
m.addsql("update es_server set servershops=n'");
m.addsql(xmlstr);
m.addsql("' where serverid=");
m.addsql(_serverid.tostring());
m.executenonquery(conn);
}
}
#endregion
#region 添加服务器对应的店铺信息 void addmservershop(int _serverid, mservershop _servershop)
/// <summary>
/// 添加服务器对应的店铺信息
/// </summary>
/// <param name="_serverid">服务器的serverid</param>
/// <param name="_servershop">服务器对应的店铺信息</param>
public void addmservershop(int _serverid, mservershop _servershop)
{
//update es_server set servershops.modify('declare namespace mi="http://www.linkedu.com.cn/mservershop.xsd"; insert (<mi:shop shopid="3" shopname="hopname3" />) as first into (//mi:shops)[1]') where serverid=1
//select serverid from es_server where serverid=1 and servershops.exist('declare namespace mi="http://www.linkedu.com.cn/mservershop.xsd"; //mi:shops/mi:shop[@shopid=4]')=1
//select servershops.value('declare namespace mi="http://www.linkedu.com.cn/mservershop.xsd"; (//mi:shops/mi:shop/@shopid)[1]=4', 'bit') as shopid from es_server where serverid=1 if @@rowcount > 0 begin select 1 end
string xmlstr = common.utilities.serializationhelper<mservershop>.toxml(_servershop, "mi");
dataprovider dp = sqltools.helpwww.dataprovideruse;
using (idbconnection conn = dp.getconnection())
{
common.dataaccess.ormapping.iconvert ic = dp.geticonvert();
common.dataaccess.ormapping.mapping m = ic.getnewmapping();
m.addsql("select serverid from es_server where serverid=" + _serverid.tostring());
m.addsql(" and servershops.exist('declare namespace mi=/"http://www.linkedu.com.cn/mservershop.xsd/"; //mi:shops/mi:shop[@shopid=" + _servershop.shopid.tostring() + "]')=1");
m.addsql(" if @@rowcount = 0 begin ");
m.addsql("update es_server set servershops.modify('declare namespace mi=/"http://www.linkedu.com.cn/mservershop.xsd/"; insert (");
m.addsql(xmlstr);
m.addsql(") as first into (//mi:shops)[1]') where serverid=" + _serverid.tostring() + " end");
m.executenonquery(conn);
}
}
#endregion
#region 删除服务器对应的店铺信息 void deletemservershop(int _serverid, int _shopid)
/// <summary>
/// 删除服务器对应的店铺信息
/// </summary>
/// <param name="_serverid">服务器的serverid</param>
/// <param name="_shopid">店铺id</param>
public void deletemservershop(int _serverid, int _shopid)
{
/*
update es_server
set servershops.modify('declare namespace mi="http://www.linkedu.com.cn/mservershop.xsd";
delete /mi:shops/mi:shop[@shopid=1]') where serverid=1
*/
dataprovider dp = sqltools.helpwww.dataprovideruse;
using (idbconnection conn = dp.getconnection())
{
common.dataaccess.ormapping.iconvert ic = dp.geticonvert();
common.dataaccess.ormapping.mapping m = ic.getnewmapping();
m.addsql("update es_server set servershops.modify('declare namespace mi=/"http://www.linkedu.com.cn/mservershop.xsd/";delete /mi:shops/mi:shop[@shopid=" + _shopid + "]') where serverid=" + _serverid);
m.executenonquery(conn);
}
}
#endregion
#region 修改服务器对应的店铺信息 void modifymservershop(int _serverid, mservershop _servershop)
/// <summary>
/// 修改服务器对应的店铺信息
/// </summary>
/// <param name="_serverid">服务器的serverid</param>
/// <param name="_servershop">服务器对应的店铺信息,其中以_servershop的shopid属性为主键</param>
public void modifymservershop(int _serverid, mservershop _servershop)
{
//update es_server set servershops.modify('declare namespace mi="http://www.linkedu.com.cn/mservershop.xsd";replace value of (/mi:shops/mi:shop[@shopid=128780281]/@shopname)[1] with "shopnamex"') where serverid=1
dataprovider dp = sqltools.helpwww.dataprovideruse;
using (idbconnection conn = dp.getconnection())
{
common.dataaccess.ormapping.iconvert ic = dp.geticonvert();
common.dataaccess.ormapping.mapping m = ic.getnewmapping();
m.addsql("update es_server set servershops.modify('declare namespace mi=/"http://www.linkedu.com.cn/mservershop.xsd/";replace value of (/mi:shops/mi:shop[@shopid=" + _servershop.shopid + "]/@shopname)[1] with /"" + _servershop.shopname + "/"') where serverid=" + _serverid);
m.executenonquery(conn);
}
}
#endregion
#endregion
#endregion
#region 增删改
#region 添加服务器信息 int add(int _serverid, string _servername, string _ip, string _domainname, string _dir, string _url, int _servergroupid)
/// <summary>
/// 添加服务器信息
/// </summary>
/// <param name="_serverid">服务器id</param>
/// <param name="_servername">服务器名称</param>
/// <param name="_ip">服务器ip</param>
/// <param name="_domainname">服务器域名</param>
/// <param name="_dir">文件存放目录</param>
/// <param name="_url">文件存放url</param>
/// <param name="_servergroupid">对应的服务器群id</param>
/// <param name="_servershops">服务器对应的店铺信息</param>
/// <returns>新加服务器是否成功</returns>
public bool add(int _serverid, string _servername, string _ip, string _domainname, string _dir, string _url, int _servergroupid)
{
dataprovider dp = sqltools.helpwww.dataprovideruse;
using (idbconnection conn = dp.getconnection())
{
common.dataaccess.ormapping.iconvert ic = dp.geticonvert();
common.dataaccess.ormapping.mapping m = ic.getnewmapping();
common.utilities.listdic<string, object> ld = new common.utilities.listdic<string, object>();
ld.add("serverid", _serverid);
ld.add("servername", _servername);
ld.add("ip", _ip);
ld.add("domainname", _domainname);
ld.add("dir", _dir);
ld.add("url", _url);
ld.add("servergroupid", _servergroupid);
m.insert(ld, "es_server");
return m.executenonquery(conn) > 0;
}
}
#endregion
#region 修改服务器信息 bool modify(int _serverid, string _servername, string _ip, string _domainname, string _dir, string _url, int _servergroupid)
/// <summary>
/// 修改服务器信息
/// </summary>
/// <param name="_serverid">服务器id</param>
/// <param name="_servername">服务器名称</param>
/// <param name="_ip">服务器ip</param>
/// <param name="_domainname">服务器域名</param>
/// <param name="_dir">文件存放目录</param>
/// <param name="_url">文件存放url</param>
/// <param name="_servergroupid">对应的服务器群id</param>
/// <param name="_servershops">服务器对应的店铺信息</param>
/// <returns>是否成功</returns>
public bool modify(int _serverid, string _servername, string _ip, string _domainname, string _dir, string _url, int _servergroupid)
{
dataprovider dp = sqltools.helpwww.dataprovideruse;
using (idbconnection conn = dp.getconnection())
{
common.dataaccess.ormapping.iconvert ic = dp.geticonvert();
common.dataaccess.ormapping.mapping m = ic.getnewmapping();
common.utilities.listdic<string, object> ld = new common.utilities.listdic<string, object>();
ld.add("servername", _servername);
ld.add("ip", _ip);
ld.add("domainname", _domainname);
ld.add("dir", _dir);
ld.add("url", _url);
ld.add("servergroupid", _servergroupid);
m.update(ld, "es_server");
m.addsql(" where serverid=");
m.addsql("serverid", _serverid);
return m.executenonquery(conn) > 0;
}
}
#endregion
#region 删除服务器信息 bool delete(int _serverid)
/// <summary>
/// 删除服务器信息
/// </summary>
/// <param name="_serverid">服务器的serverid</param>
/// <returns>是否成功</returns>
public bool delete(int _serverid)
{
dataprovider dp = sqltools.helpwww.dataprovideruse;
using (idbconnection conn = dp.getconnection())
{
common.dataaccess.ormapping.iconvert ic = dp.geticonvert();
common.dataaccess.ormapping.mapping m = ic.getnewmapping();
m.delete("es_server");
m.addsql(" where serverid=");
m.addsql("serverid", _serverid);
return m.executenonquery(conn) > 0;
}
}
#endregion
#endregion
#region 得到其它
#region 得到此服务器所属服务器群信息 mservergroup getservergroup(int _serverid)
/// <summary>
/// 得到此服务器所属服务器群信息
/// </summary>
/// <param name="_serverid">服务器id</param>
/// <returns>得到此服务器所属服务器群信息</returns>
public mservergroup getservergroup(int _serverid)
{
dataprovider dp = sqltools.helpwww.dataprovideruse;
using (idbconnection conn = dp.getconnection())
{
common.dataaccess.ormapping.iconvert ic = dp.geticonvert();
common.dataaccess.ormapping.mapping m = ic.getnewmapping();
m.addsql("select * from es_servergroup where servergroupid = (select servergroupid from es_server where serverid=");
m.addsql("serverid", _serverid);
m.addsql(")");
using (idatareader idr = m.executereader(conn))
{
return common.entity.modelentityhelp<mservergroup>.get_ilist(idr)[0];
}
}
}
#endregion
#endregion
#endregion
#region static
/// <summary>
/// 工厂方法得到dserver对象
/// </summary>
/// <returns>dserver对象</returns>
public static dserver factory()
{
return common.singleton.provider<dserver>.instance;
}
#endregion
}
/// <summary>
/// 服务器群信息数据访问层
/// </summary>
public class dservergroup
{
#region constructor
public dservergroup()
{
}
#endregion
#r
新闻热点
疑难解答
图片精选
