◇今天要介绍的就是sql2005的xml字...... 今天要介绍的就是sql2005的xml字段类型在.net中的应用。调用过程是:先运用并行化的办法把xml字段类型中的数据转换成Model对象,对Model对象操作后,再运用串行化的方法把Model对象转变成xml格式,最后存储到数据库中。 我认为如果把复杂的业务关系数据存储在xml字段中,可简化数据库的设计,方便业务的处理。 这里写了个小demo: 假如我们有很多店铺信息,每个店铺都有一个ShopID, 所以我们就把同一店铺的信息放在以ShopID命名的文件夹下,当一台服务器放不下时,我们就部署多台,这样每台服务器存储的店铺是不一样的。这些服务器就构成了一个服务器群。出于需要,我们要把这个群复制多个,部署在不同的地区(注意,各个群的信息是相同的)。为了完成这个目的,我们先设计了数据模型 MServerGroup(服务器群信息),MServer(服务器群下的服务器信息),MServerShop(服务器对应的店铺): /// /// 服务器群信息 /// /// /// 用于存放点播文件服务器群的信息,比如主站的,北京站的,上海站的;各个站的数据相同. /// 服务器群的目的是分散数据库的压力. /// 目前只有主站的. /// [Serializable()] public class MServerGroup : BaseModelEntity { #region private private int _ServerGroupID; private string _ServerGroupName; private MServerCollection _Servers; #endregion #region constructor /// /// 服务器群信息 /// public MServerGroup() { } /// /// 服务器群信息 /// /// 服务器群ID /// 服务器群名称 public MServerGroup(int _ServerGroupID, string _ServerGroupName) { this._ServerGroupID = _ServerGroupID; this._ServerGroupName = _ServerGroupName; } #endregion #region property /// /// 服务器群ID /// public int ServerGroupID { get { return _ServerGroupID; } set { this._ServerGroupID = value; } } /// /// 服务器群名称 /// public string ServerGroupName { get { return _ServerGroupName; } set { this._ServerGroupName = value; } } /// /// 服务器群下的服务器集合 /// public MServerCollection Servers { get { return _Servers; } set { this._Servers = value; } } #endregion } /// /// 服务器群下的服务器信息 /// /// /// 用于存放点播文件的服务信息 /// [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 /// /// 服务器信息 /// public MServer() { } /// /// 服务器信息 /// /// 服务器ID /// 服务器名称 /// 服务器IP /// 服务器域名 /// 文件存放目录 /// 文件存放Url /// 对应的服务器群ID /// 服务器对应的店铺信息 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; } /// /// 服务器信息 /// /// 服务器ID /// 服务器名称 /// 服务器IP /// 服务器域名 /// 文件存放目录 /// 文件存放Url /// 对应的服务器群ID /// 服务器对应的店铺信息的xml字符串 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 } #endregion #region property /// /// 服务器ID /// public int ServerID { get { return _ServerID; } set { this._ServerID = value; } } /// /// 服务器名称 /// public string ServerName { get { return _ServerName; } set { this._ServerName = value; } } /// /// 服务器IP /// public string IP { get { return _IP; } set { this._IP = value; } } /// /// 服务器域名 /// public string DomainName { get { return _DomainName; } set { this._DomainName = value; } } /// /// 文件存放目录 /// public string Dir { get { return Dir; } set { this.Dir = value; } } /// /// 文件存放Url /// public string Url { get { return _Url; } set { this._Url = value; } } /// /// 对应的服务器群ID /// public int ServerGroupID { get { return _ServerGroupID; } set { this._ServerGroupID = value; } } /// /// 服务器对应的店铺信息 /// public MServerShopCollection ServerShops { get { return _ServerShops; } set { this._ServerShops = value; } } #endregion } /// /// 服务器对应的店铺 /// /// /// 用于存放和服务器对应的店铺 /// [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 /// /// 服务器对应的店铺信息 /// public MServerShop() { } /// /// 服务器对应的店铺信息 /// /// 店铺ID /// 店铺名称 public MServerShop(int _ShopID, string _ShopName) { this._ShopID = _ShopID; this._ShopName = _ShopName; } #endregion #region property /// /// 店铺ID /// [XmlAttribute] public int ShopID { get { return _ShopID; } set { this._ShopID = value; } } /// /// 店铺名称 /// [XmlAttribute] public string ShopName { get { return _ShopName; } set { this._ShopName = value; } } #endregion } 为了对模型的集合信息进行描述,我们有设计了MServerGroupCollection(服务器群信息集合),MServer(服务器群下的服务器信息),MServerShopCollection(服务器对应的店铺集合) /// /// 服务器群信息集合 /// /// [Serializable()] [XmlRoot("ServerGroups")] public class MServerGroupCollection : List { /// /// 服务器群信息集合 /// public MServerGroupCollection() { this._MServerGroups = new List } private List public List { get { return this._MServerGroups; } set { this._MServerGroups = value; } } } /// /// 服务器群下的服务器信息集合 /// [XmlRoot("Servers")] [Serializable()] public class MServerCollection : List { /// /// 服务器群下的服务器信息集合 /// public MServerCollection() { this._MServers = new List } private List public List { get { return this._MServers; } set { this._MServers = value; } } } /// /// 服务器对应的店铺集合 /// [Serializable()] [XmlRoot(ElementName = "Shops", Namespace = "http://www.linkedu.com.cn/MServerShop.xsd")] public class MServerShopCollection { private List [XmlElement("Shop")] public List { get { return this._MServerShops; } set { this._MServerShops = value; } } /// /// 服务器对应的店铺集合类 /// public MServerShopCollection() { this._MServerShops = new List } } 经分析,服务器对应的店铺信息可用xml存储,设计格式如下(用xsd描述,设计好后,我们把它创建到数据库中) CREATE XML SCHEMA COLLECTION [dbo].[MServerShop] AS N' 最后,我设计了(服务器群信息 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中我省去了接口的书写和业务层,我在表现层直接调用了数据访问层 数据访问层代码如下: /// /// 服务器群下的服务器信息数据访问层 /// public class DServer { #region constructor public DServer() { } #endregion #region public method #region 得到当前 #region 得到某服务器信息 MServer GetMServer(int _ServerID) /// /// 得到某服务器信息 /// /// 服务器的ServerID /// 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) /// /// 得到服务器对应的店铺集合 /// /// 服务器的ServerID /// 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 } } #endregion #region 保存服务器对应的店铺信息 void SaveMServerShops(int _ServerID, MServerShopCollection _ServerShops) /// /// 保存服务器对应的店铺信息 /// /// 服务器的ServerID /// 服务器对应的店铺信息集合 public void SaveMServerShops(int _ServerID, MServerShopCollection _ServerShops) { string xmlStr = Common.Utilities.SerializationHelper 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) /// /// 添加服务器对应的店铺信息 /// /// 服务器的ServerID /// 服务器对应的店铺信息 public void AddMServerShop(int _ServerID, MServerShop _ServerShop) { //update ES_Server set ServerShops.modify('declare namespace mi="http://www.linkedu.com.cn/MServerShop.xsd"; insert ( //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 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) /// /// 删除服务器对应的店铺信息 /// /// 服务器的ServerID /// 店铺ID 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) /// /// 修改服务器对应的店铺信息 /// /// 服务器的ServerID /// 服务器对应的店铺信息,其中以_ServerShop的ShopID属性为主键 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) /// /// 添加服务器信息 /// /// 服务器ID /// 服务器名称 /// 服务器IP /// 服务器域名 /// 文件存放目录 /// 文件存放Url /// 对应的服务器群ID /// 服务器对应的店铺信息 /// 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 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) /// /// 修改服务器信息 /// /// 服务器ID /// 服务器名称 /// 服务器IP /// 服务器域名 /// 文件存放目录 /// 文件存放Url /// 对应的服务器群ID /// 服务器对应的店铺信息 /// 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 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) /// /// 删除服务器信息 /// /// 服务器的ServerID /// 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) /// /// 得到此服务器所属服务器群信息 /// /// 服务器ID /// 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 } } } #endregion #endregion #endregion #region static /// /// 工厂方法得到DServer对象 /// /// public static DServer Factory() { return Common.Singleton.Provider } #endregion } /// /// 服务器群信息数据访问层 /// public class DServerGroup { #region constructor public DServerGroup() { } #endregion #r |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-10-1 01:20 , Processed in 0.188829 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.