当前位置：.Net技术首页 >> Asp.Net开发 >> sql2005的xml字段类型在.net中的应用3

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

2007-10-15 08:00:00 作者：来源：互联网浏览次数：0 文字大小：【大】【中】【小】

简介：#region 保存服务器对应的店铺信息 void SaveMServerShops(int _ServerID, MServerShopCollection _ServerShops) /// /// 保存服务器对应的店铺信息 /// /// 服务器的ServerID /// 服务器对应的店铺信息集合 p...

关键字：.net 字段 sql2005 类型应用 xml

#region 保存服务器对应的店铺信息 void SaveMServerShops(int _ServerID, MServerShopCollection _ServerShops)

///

/// 保存服务器对应的店铺信息

///

/// 服务器的ServerID

/// 服务器对应的店铺信息集合

public void SaveMServerShops(int _ServerID, MServerShopCollection _ServerShops)

{

string xmlStr = Common.Utilities.SerializationHelper.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)

///

/// 添加服务器对应的店铺信息

///

/// 服务器的ServerID

/// 服务器对应的店铺信息

public void AddMServerShop(int _ServerID, MServerShop _ServerShop)

{

//update ES_Server set ServerShops.modify('declare namespace mi="http://www.linkedu.com.cn/MServerShop.xsd"; insert () 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.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)

///

/// 删除服务器对应的店铺信息

///

/// 服务器的ServerID

/// 店铺ID

public void DeleteMServerShop(int _ServerID, int _ShopID)

{

UPDATE ES_Server

SET ServerShops.modify('declare namespace mi="http://www.linkedu.com.cn/MServerShop.xsd";

delete /mi:Shops/mi:Shop[@ShopID=1]') where ServerID=1

DataProvider dp = SqlTools.HelpWWW.DataProviderUse;

using (IDbConnection conn = dp.GetConnection())

{

Common.DataAccess.ORMapping.IConvert ic = dp.GetIConvert();

Common.DataAccess.ORMapping.Mapping m = ic.GetNewMapping();

m.AddSql("UPDATE ES_Server SET ServerShops.modify('declare namespace mi=\"http://www.linkedu.com.cn/MServerShop.xsd\";delete /mi:Shops/mi:Shop[@ShopID=" + _ShopID + "]') where ServerID=" + _ServerID);

m.ExecuteNonQuery(conn);

}

#endregion

#region 修改服务器对应的店铺信息 void ModifyMServerShop(int _ServerID, MServerShop _ServerShop)

///

/// 修改服务器对应的店铺信息

///

/// 服务器的ServerID

/// 服务器对应的店铺信息,其中以_ServerShop的ShopID属性为主键

public void ModifyMServerShop(int _ServerID, MServerShop _ServerShop)

{

//UPDATE ES_Server SET ServerShops.modify('declare namespace mi="http://www.linkedu.com.cn/MServerShop.xsd";replace value of (/mi:Shops/mi:Shop[@ShopID=128780281]/@ShopName)[1] with "ShopNamex"') where ServerID=1

DataProvider dp = SqlTools.HelpWWW.DataProviderUse;

using (IDbConnection conn = dp.GetConnection())

{

Common.DataAccess.ORMapping.IConvert ic = dp.GetIConvert();

Common.DataAccess.ORMapping.Mapping m = ic.GetNewMapping();

m.AddSql("UPDATE ES_Server SET ServerShops.modify('declare namespace mi=\"http://www.linkedu.com.cn/MServerShop.xsd\";replace value of (/mi:Shops/mi:Shop[@ShopID=" + _ServerShop.ShopID + "]/@ShopName)[1] with \"" + _ServerShop.ShopName + "\"') where ServerID=" + _ServerID);

m.ExecuteNonQuery(conn);

}

#endregion

#region 增删改

#region 添加服务器信息 int Add(int _ServerID, string _ServerName, string _IP, string _DomainName, string _Dir, string _Url, int _ServerGroupID)

///

/// 添加服务器信息

///

/// 服务器ID

/// 服务器名称

/// 服务器IP

/// 服务器域名

/// 文件存放目录

/// 文件存放Url

/// 对应的服务器群ID

/// 服务器对应的店铺信息

/// 新加服务器是否成功

public bool Add(int _ServerID, string _ServerName, string _IP, string _DomainName, string _Dir, string _Url, int _ServerGroupID)

{

DataProvider dp = SqlTools.HelpWWW.DataProviderUse;

using (IDbConnection conn = dp.GetConnection())

{

Common.DataAccess.ORMapping.IConvert ic = dp.GetIConvert();

Common.DataAccess.ORMapping.Mapping m = ic.GetNewMapping();

Common.Utilities.ListDic ld = new Common.Utilities.ListDic();

ld.Add("ServerID", _ServerID);

ld.Add("ServerName", _ServerName);

ld.Add("IP", _IP);

ld.Add("DomainName", _DomainName);

ld.Add("Dir", _Dir);

ld.Add("Url", _Url);

ld.Add("ServerGroupID", _ServerGroupID);

m.Insert(ld, "ES_Server");

return m.ExecuteNonQuery(conn) > 0;

}

#endregion

#region 修改服务器信息 bool Modify(int _ServerID, string _ServerName, string _IP, string _DomainName, string _Dir, string _Url, int _ServerGroupID)

///

/// 修改服务器信息

///

/// 服务器ID

/// 服务器名称

/// 服务器IP

/// 服务器域名

/// 文件存放目录

/// 文件存放Url

/// 对应的服务器群ID

/// 服务器对应的店铺信息

/// 是否成功

public bool Modify(int _ServerID, string _ServerName, string _IP, string _DomainName, string _Dir, string _Url, int _ServerGroupID)

{

DataProvider dp = SqlTools.HelpWWW.DataProviderUse;

using (IDbConnection conn = dp.GetConnection())

{

Common.DataAccess.ORMapping.IConvert ic = dp.GetIConvert();

Common.DataAccess.ORMapping.Mapping m = ic.GetNewMapping();

Common.Utilities.ListDic ld = new Common.Utilities.ListDic();

ld.Add("ServerName", _ServerName);

ld.Add("IP", _IP);

ld.Add("DomainName", _DomainName);

ld.Add("Dir", _Dir);

ld.Add("Url", _Url);

ld.Add("ServerGroupID", _ServerGroupID);

m.Update(ld, "ES_Server");

m.AddSql(" where ServerID=");

m.AddSql("ServerID", _ServerID);

return m.ExecuteNonQuery(conn) > 0;

}

#endregion

#region 删除服务器信息 bool Delete(int _ServerID)

///

/// 删除服务器信息

///

/// 服务器的ServerID

/// 是否成功

public bool Delete(int _ServerID)

{

DataProvider dp = SqlTools.HelpWWW.DataProviderUse;

using (IDbConnection conn = dp.GetConnection())

{

Common.DataAccess.ORMapping.IConvert ic = dp.GetIConvert();

Common.DataAccess.ORMapping.Mapping m = ic.GetNewMapping();

m.Delete("ES_Server");

m.AddSql(" where ServerID=");

m.AddSql("ServerID", _ServerID);

return m.ExecuteNonQuery(conn) > 0;

}

#endregion

#region 得到其它

#region 得到此服务器所属服务器群信息 MServerGroup GetServerGroup(int _ServerID)

///

/// 得到此服务器所属服务器群信息

///

/// 服务器ID

/// 得到此服务器所属服务器群信息

public MServerGroup GetServerGroup(int _ServerID)

{

DataProvider dp = SqlTools.HelpWWW.DataProviderUse;

using (IDbConnection conn = dp.GetConnection())

{

Common.DataAccess.ORMapping.IConvert ic = dp.GetIConvert();

Common.DataAccess.ORMapping.Mapping m = ic.GetNewMapping();

m.AddSql("select * from ES_ServerGroup where ServerGroupID = (select ServerGroupID from ES_Server where ServerID=");

m.AddSql("ServerID", _ServerID);

m.AddSql(")");

using (IDataReader idr = m.ExecuteReader(conn))

{

return Common.Entity.ModelEntityHelp.Get_IList(idr)[0];

}

#endregion

#region static

///

/// 工厂方法得到DServer对象

///

/// DServer对象

public static DServer Factory()

{

return Common.Singleton.Provider.Instance;

}

#endregion

}

///

/// 服务器群信息数据访问层

///

public class DServerGroup

{

#region constructor

public DServerGroup()

{

}

#endregion

#region public method

#region 得到当前

#region 得到所有服务器群信息 MServerGroupCollection GetAllServerGroup()

///

/// 得到所有服务器群信息

///

/// 所有服务器群信息

public MServerGroupCollection GetAllServerGroup()

{

string sql = "select * from ES_ServerGroup";

DataTable dt = SqlTools.HelpWWW.ExecuteDataTable(CommandType.Text, sql);

return Common.Entity.ModelEntityCollectionHelp.Get_Collection(dt);

}

#endregion

#region 得到某服务器群信息 MServerGroup GetMServerGroup(int _ServerGroupID)

///

/// 得到某服务器群信息

///

/// 服务器群的ServerGroupID

/// 得到某服务器群信息

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.Get_IList(idr)[0];

}

#endregion

#region 增删改

#region 添加服务器群信息 bool Add(int _ServerGroupID, string _ServerGroupName)

///

/// 添加服务器群信息

///

/// 服务器群的ServerGroupID

/// 服务器群的名称

/// 添加服务器群是否成功

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 ld = new Common.Utilities.ListDic();

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)

///

/// 修改服务器群信息

///

/// 服务器群的ServerGroupID

/// 服务器群的名称

/// 是否成功

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 ld = new Common.Utilities.ListDic();

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)

///

/// 删除服务器群信息

///

/// 服务器群的ServerGroupID

/// 是否成功

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

#region 得到其它

#region 得到某服务器群的所有服务器信息 MServerCollection GetServers(int _ServerGroupID)

///

/// 得到某服务器群的所有服务器信息

///

/// 服务器群ID

/// 某服务器群的所有服务器信息

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.FromXML(XMLobj.ToString());

MServer mserver = new MServer(serverID, serverName, ip, domainName, dir, url, serverGroupID, shops);

list.Add(mserver);

}

return list;

}

#endregion

#region static

///

/// 工厂方法得到DServerGroup对象

///

/// DServerGroup对象

public static DServerGroup Factory()

{

return Common.Singleton.Provider.Instance;

}

#endregion

}

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

做人要厚道，请注明转自酷网动力(www.ASPCOOL.COM)。

责任编辑：admin

本文引用地址： http://www.3pcode.com/net/2007/10/91426.htm

发表评论　加入收藏　告诉好友　打印本页　关闭窗口　返回顶部

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

最新文章

更多

推荐文章

更多

热点文章

更多