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

        今天要介绍的就是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

        #region public method

        #region 得到当前
        #region 得到所有服务器群信息 MServerGroupCollection GetAllServerGroup()
        /// <summary>
        /// 得到所有服务器群信息
        /// </summary>
        /// <returns>所有服务器群信息</returns>
        public MServerGroupCollection GetAllServerGroup()
        {
            string sql = "select * from ES_ServerGroup";
            DataTable dt = SqlTools.HelpWWW.ExecuteDataTable(CommandType.Text, sql);
            return Common.Entity.ModelEntityCollectionHelp<MServerGroup, MServerGroupCollection>.Get_Collection(dt);
        }
        #endregion

        #region 得到某服务器群信息 MServerGroup GetMServerGroup(int _ServerGroupID)
        /// <summary>
        /// 得到某服务器群信息
        /// </summary>
        /// <param name="_ServerGroupID">服务器群的ServerGroupID</param>
        /// <returns>得到某服务器群信息</returns>
        public MServerGroup GetMServerGroup(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();
                m.AddSql("select * from ES_ServerGroup where ");
                m.AddSql("ServerGroupID", "ServerGroupID", Common.DataAccess.ORMapping.OperType.Equals, _ServerGroupID);
                using (IDataReader idr = m.ExecuteReader(conn))
                {
                    return Common.Entity.ModelEntityHelp<MServerGroup>.Get_IList(idr)[0];
                }
            }
        }
        #endregion
        #endregion

        #region 增删改
        #region 添加服务器群信息 bool Add(int _ServerGroupID, string _ServerGroupName)
        /// <summary>
        /// 添加服务器群信息
        /// </summary>
        /// <param name="_ServerGroupID">服务器群的ServerGroupID</param>
        /// <param name="_ServerGroupName">服务器群的名称</param>
        /// <returns>添加服务器群是否成功</returns>
        public bool Add(int _ServerGroupID, string _ServerGroupName)
        {
            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("ServerGroupID", _ServerGroupID);
                ld.Add("ServerGroupName", _ServerGroupName);
                m.Insert(ld, "ES_ServerGroup");
                return m.ExecuteNonQuery(conn) > 0;
            }
        }
        #endregion

        #region 修改服务器群信息 bool Modify(int _ServerGroupID, string _ServerGroupName)
        /// <summary>
        /// 修改服务器群信息
        /// </summary>
        /// <param name="_ServerGroupID">服务器群的ServerGroupID</param>
        /// <param name="_ServerGroupName">服务器群的名称</param>
        /// <returns>是否成功</returns>
        public bool Modify(int _ServerGroupID, string _ServerGroupName)
        {
            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("ServerGroupName", _ServerGroupName);
                m.Update(ld, "ES_ServerGroup");
                m.AddSql(" where ");
                m.AddSql("ServerGroupID", "ServerGroupID", Common.DataAccess.ORMapping.OperType.Equals, _ServerGroupID);
                return m.ExecuteNonQuery(conn) > 0;
            }
        }
        #endregion

        #region 删除服务器群信息 bool Delete(int _ServerGroupID)
        /// <summary>
        /// 删除服务器群信息
        /// </summary>
        /// <param name="_ServerGroupID">服务器群的ServerGroupID</param>
        /// <returns>是否成功</returns>
        public bool Delete(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();
                m.Delete("ES_ServerGroup");
                m.AddSql(" where ");
                m.AddSql("ServerGroupID", "ServerGroupID", Common.DataAccess.ORMapping.OperType.Equals, _ServerGroupID);
                return m.ExecuteNonQuery(conn) > 0;
            }
        }
        #endregion
        #endregion

        #region 得到其它
        #region 得到某服务器群的所有服务器信息 MServerCollection GetServers(int _ServerGroupID)
        /// <summary>
        /// 得到某服务器群的所有服务器信息
        /// </summary>
        /// <param name="_ServerGroupID">服务器群ID</param>
        /// <returns>某服务器群的所有服务器信息</returns>
        public MServerCollection GetServers(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();
                m.AddSql("select * from ES_Server where ");
                m.AddSql("ServerGroupID", "ServerGroupID", Common.DataAccess.ORMapping.OperType.Equals, _ServerGroupID);
                DataTable dt = m.ExecuteDataTable(conn);
                MServerCollection list = new MServerCollection();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    int serverID = (int)dt.Rows[i]["ServerID"];
                    string serverName = (string)dt.Rows[i]["ServerName"];
                    string ip = (string)dt.Rows[i]["IP"];
                    string domainName = (string)dt.Rows[i]["DomainName"];
                    string dir = (string)dt.Rows[i]["Dir"];
                    string url = (string)dt.Rows[i]["Url"];
                    int serverGroupID = (int)dt.Rows[i]["ServerGroupID"];
                    object xmlobj = dt.Rows[i]["ServerShops"];
                    if (xmlobj != null && xmlobj.ToString() != "")
                    {
                        Linkedu.ModelEntity.MServerShopCollection shops = Common.Utilities.SerializationHelper<Linkedu.ModelEntity.MServerShopCollection>.FromXML(xmlobj.ToString());
                        MServer mserver = new MServer(serverID, serverName, ip, domainName, dir, url, serverGroupID, shops);
                        list.Add(mserver);
                    }
                }
                return list;
            }
        }
        #endregion
        #endregion

        #endregion

        #region static
        /// <summary>
        /// 工厂方法得到DServerGroup对象
        /// </summary>
        /// <returns>DServerGroup对象</returns>
        public static DServerGroup Factory()
        {
            return Common.Singleton.Provider<DServerGroup>.Instance;
        }
        #endregion
    }

http://www.cnblogs.com/laiwen/archive/2006/12/13/591454.html