sql2005的xml字段類型在.net中的應用

2019-11-18 16:48:57

字體：大中小

來源：轉載

供稿：網友

今天要介紹的就是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 = "    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 = "    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="<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.Data access.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