sql2005的xml字段类型在.net中的应用
2006-12-14 17:20:00 来源：WEB开发网 今天要介绍的就是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
- ››SQL Server 2008 R2 下如何清理数据库日志文件
- ››sqlite 存取中文的解决方法
- ››SQL2005、2008、2000 清空删除日志
- ››SQL Server 2005和SQL Server 2000数据的相互导入...
- ››sql server 2008 在安装了活动目录以后无法启动服...
- ››sqlserver 每30分自动生成一次
- ››sqlite 数据库 对 BOOL型 数据的插入处理正确用法...
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
中查找“sql2005的xml字段类型在.net中的应用”更多相关内容
中查找“sql2005的xml字段类型在.net中的应用”更多相关内容更多精彩
赞助商链接
