sql2005的xml字段类型在.net中的应用

经分析，服务器对应的店铺信息可用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