设为首页
加入收藏
站内地图
旧版入口
当前位置：首页 > 站长学院 > 网络编程 > ASP.NET

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

作者：佚名 出处：网络转载 时间：12-15 点击：

内容载入中...
     #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
  
    。

收藏本文：
【大 中 小】【打印页面】【推荐给朋友】【关闭窗口】

站长学院

推荐信息