.若A,B两个实体是一对多的包含关系。利用存储过程，实现数据访问层层的一个实例。
来源：互联网 发布：市场化营销网络 编辑：程序博客网 时间：2024/04/30 01:23
1.访问层代码如下：
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using Adpost.Common.Function;
namespace Adpost.Common.DataDAL
{
/// <summary>
/// </summary>
public class Hotel:EyouSoft.IDAL.DHTemplate.IHotel
{
#region IHotel 成员
/// <summary>
/// 新增酒店
/// </summary>
/// <param name="model">酒店实体</param>
/// <returns>true:成功 false:失败</returns>
public int Add(EyouSoft.Model.DHTemplate.Hotel model)
{
#region 参数
SqlParameter[] parameters = {
new SqlParameter("@Result",SqlDbType.Int),
new SqlParameter("@SystemId", SqlDbType.Int),
new SqlParameter("@CityId", SqlDbType.Int),
new SqlParameter("@HotelName", SqlDbType.NVarChar),
new SqlParameter("@ShowPosition", SqlDbType.TinyInt),
new SqlParameter("@Star", SqlDbType.TinyInt),
new SqlParameter("@Remark", SqlDbType.NVarChar),
new SqlParameter("@HotelInfo", SqlDbType.NVarChar),
new SqlParameter("@HotelSeat", SqlDbType.NVarChar),
new SqlParameter("@AttachService", SqlDbType.NVarChar),
new SqlParameter("@ServiceItem", SqlDbType.NVarChar),
new SqlParameter("@FoodService", SqlDbType.NVarChar),
new SqlParameter("@PastimeItem", SqlDbType.NVarChar),
new SqlParameter("@RoomServiceItem", SqlDbType.NVarChar),
new SqlParameter("@Rescind", SqlDbType.NVarChar),
new SqlParameter("@OrderChange", SqlDbType.NVarChar),
new SqlParameter("@NoShow", SqlDbType.NVarChar),
new SqlParameter("@Refundment", SqlDbType.NVarChar),
new SqlParameter("@RoomPrice", SqlDbType.NVarChar),
new SqlParameter("@CheckInAndOut", SqlDbType.NVarChar),
new SqlParameter("@PersonOut", SqlDbType.NVarChar),
new SqlParameter("@EnglishService", SqlDbType.NVarChar),
new SqlParameter("@OperatorId", SqlDbType.Int),
new SqlParameter("@HotelRoomXML", SqlDbType.NVarChar)
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Value = model.SystemId;
parameters[2].Value = model.CityId;
parameters[3].Value = model.HotelName;
parameters[4].Value = model.ShowPosition;
parameters[5].Value = model.Star;
parameters[6].Value = model.Remark;
parameters[7].Value = model.HotelInfo;
parameters[8].Value = model.HotelSeat;
parameters[9].Value = model.AttachService;
parameters[10].Value = model.ServiceItem;
parameters[11].Value = model.FoodService;
parameters[12].Value = model.PastimeItem;
parameters[13].Value = model.RoomServiceItem;
parameters[14].Value = model.Rescind;
parameters[15].Value = model.OrderChange;
parameters[16].Value = model.NoShow;
parameters[17].Value = model.Refundment;
parameters[18].Value = model.RoomPrice;
parameters[19].Value = model.CheckinAndOut;
parameters[20].Value = model.PersonOut;
parameters[21].Value = model.EnglishService;
parameters[22].Value = model.OperatorId;
parameters[23].Value = CreateRoomXML(model.HotelRoomInfo);
#endregion
int rowset = 0;
DALSQLHelper.RunProcedure("proc_Hotel_AddHotelInfo", parameters,out rowset);
return (int)parameters[0].Value;
}
/// <summary>
/// 修改酒店
/// </summary>
/// <param name="model">酒店实体</param>
/// <returns>true:成功 false:失败</returns>
public bool Update(EyouSoft.Model.DHTemplate.Hotel model)
{
#region 参数
SqlParameter[] parameters = {
new SqlParameter("@Id",SqlDbType.Int),
new SqlParameter("@Result",SqlDbType.Int),
new SqlParameter("@CityId", SqlDbType.Int),
new SqlParameter("@HotelName", SqlDbType.NVarChar),
new SqlParameter("@ShowPosition", SqlDbType.TinyInt),
new SqlParameter("@Star", SqlDbType.TinyInt),
new SqlParameter("@Remark", SqlDbType.NVarChar),
new SqlParameter("@HotelInfo", SqlDbType.NVarChar),
new SqlParameter("@HotelSeat", SqlDbType.NVarChar),
new SqlParameter("@AttachService", SqlDbType.NVarChar),
new SqlParameter("@ServiceItem", SqlDbType.NVarChar),
new SqlParameter("@FoodService", SqlDbType.NVarChar),
new SqlParameter("@PastimeItem", SqlDbType.NVarChar),
new SqlParameter("@RoomServiceItem", SqlDbType.NVarChar),
new SqlParameter("@Rescind", SqlDbType.NVarChar),
new SqlParameter("@OrderChange", SqlDbType.NVarChar),
new SqlParameter("@NoShow", SqlDbType.NVarChar),
new SqlParameter("@Refundment", SqlDbType.NVarChar),
new SqlParameter("@RoomPrice", SqlDbType.NVarChar),
new SqlParameter("@CheckInAndOut", SqlDbType.NVarChar),
new SqlParameter("@PersonOut", SqlDbType.NVarChar),
new SqlParameter("@EnglishService", SqlDbType.NVarChar),
new SqlParameter("@HotelRoomXML", SqlDbType.NVarChar)
};
parameters[0].Value = model.Id;
parameters[1].Direction = ParameterDirection.Output;
parameters[2].Value = model.CityId;
parameters[3].Value = model.HotelName;
parameters[4].Value = model.ShowPosition;
parameters[5].Value = model.Star;
parameters[6].Value = model.Remark;
parameters[7].Value = model.HotelInfo;
parameters[8].Value = model.HotelSeat;
parameters[9].Value = model.AttachService;
parameters[10].Value = model.ServiceItem;
parameters[11].Value = model.FoodService;
parameters[12].Value = model.PastimeItem;
parameters[13].Value = model.RoomServiceItem;
parameters[14].Value = model.Rescind;
parameters[15].Value = model.OrderChange;
parameters[16].Value = model.NoShow;
parameters[17].Value = model.Refundment;
parameters[18].Value = model.RoomPrice;
parameters[19].Value = model.CheckinAndOut;
parameters[20].Value = model.PersonOut;
parameters[21].Value = model.EnglishService;
parameters[22].Value = CreateRoomXML(model.HotelRoomInfo);
#endregion
int rowset = 0;
DALSQLHelper.RunProcedure("proc_Hotel_UpdateHotelInfo", parameters,out rowset);
return (int)parameters[1].Value == 1 ? true : false;
}
/// <summary>
/// 删除酒店
/// </summary>
/// <param name="Ids">酒店编号集合</param>
/// <returns>true:成功 false:失败</returns>
public bool Delete(params int[] Ids)
{
return DALSQLHelper.ExecuteSql("update tbl_DHHotel set IsDelete = '1' where Id in (" + ConvertToString(Ids) + ");") > 0 ? true : false;
}
/// <summary>
/// 获取酒店详细信息
/// </summary>
/// <param name="Id">酒店编号</param>
/// <returns>酒店实体</returns>
public EyouSoft.Model.DHTemplate.Hotel GetModel(int Id)
{
EyouSoft.Model.DHTemplate.Hotel model = null;
StringBuilder sql = new StringBuilder();
sql.Append("select Id,SystemId,CityId,HotelName,ShowPosition,Star,Remark,HotelInfo,HotelSeat,AttachService,ServiceItem,FoodService,");
sql.Append(" PastimeItem,RoomServiceItem,Rescind,OrderChange,NoShow,Refundment,RoomPrice,CheckInAndOut,");
sql.Append(" PersonOut,EnglishService,OperatorId,IssueTime,IsDelete,");
sql.Append(" (select a.Id,a.HotelId,a.RoomType,a.BreakFast,a.NetWork,a.BedType,a.Price,a.StartTime,a.EndTime from tbl_DHHotelRoom a where a.HotelId = tbl_DHHotel.Id for xml raw,root('root')) as HotelRoomXML");
sql.AppendFormat(" from tbl_DHHotel where Id = {0}", Id);
using (IDataReader rdr = DALSQLHelper.ExecuteReader(sql.ToString()))
{
if (rdr.Read())
{
#region 基本信息
model = new EyouSoft.Model.DHTemplate.Hotel();
model.Id = Convert.ToInt32(rdr["Id"]);
model.SystemId = Convert.ToInt32(rdr["SystemId"]);
model.CityId = Convert.ToInt32(rdr["CityId"]);
model.HotelName = rdr.IsDBNull(rdr.GetOrdinal("HotelName")) ? "" : rdr["HotelName"].ToString();
if (!rdr.IsDBNull(rdr.GetOrdinal("ShowPosition")))
model.ShowPosition = (EyouSoft.Model.DHTemplate.Enum.ShowPosition)int.Parse(rdr["ShowPosition"].ToString());
if (!rdr.IsDBNull(rdr.GetOrdinal("Star")))
model.Star = (EyouSoft.Model.DHTemplate.Enum.HotelStar)int.Parse(rdr["Star"].ToString());
model.Remark = rdr.IsDBNull(rdr.GetOrdinal("Remark")) ? "" : rdr["Remark"].ToString();
model.HotelInfo = rdr.IsDBNull(rdr.GetOrdinal("HotelInfo")) ? "" : rdr["HotelInfo"].ToString();
model.HotelSeat = rdr.IsDBNull(rdr.GetOrdinal("HotelSeat")) ? "" : rdr["HotelSeat"].ToString();
model.AttachService = rdr.IsDBNull(rdr.GetOrdinal("AttachService")) ? "" : rdr["AttachService"].ToString();
model.ServiceItem = rdr.IsDBNull(rdr.GetOrdinal("ServiceItem")) ? "" : rdr["ServiceItem"].ToString();
model.FoodService = rdr.IsDBNull(rdr.GetOrdinal("FoodService")) ? "" : rdr["FoodService"].ToString();
model.PastimeItem = rdr.IsDBNull(rdr.GetOrdinal("PastimeItem")) ? "" : rdr["PastimeItem"].ToString();
model.RoomServiceItem = rdr.IsDBNull(rdr.GetOrdinal("RoomServiceItem")) ? "" : rdr["RoomServiceItem"].ToString();
model.Rescind = rdr.IsDBNull(rdr.GetOrdinal("Rescind")) ? "" : rdr["Rescind"].ToString();
model.OrderChange = rdr.IsDBNull(rdr.GetOrdinal("OrderChange")) ? "" : rdr["OrderChange"].ToString();
model.NoShow = rdr.IsDBNull(rdr.GetOrdinal("NoShow")) ? "" : rdr["NoShow"].ToString();
model.Refundment = rdr.IsDBNull(rdr.GetOrdinal("Refundment")) ? "" : rdr["Refundment"].ToString();
model.RoomPrice = rdr.IsDBNull(rdr.GetOrdinal("RoomPrice")) ? "" : rdr["RoomPrice"].ToString();
model.CheckinAndOut = rdr.IsDBNull(rdr.GetOrdinal("CheckInAndOut")) ? "" : rdr["CheckInAndOut"].ToString();
model.PersonOut = rdr.IsDBNull(rdr.GetOrdinal("PersonOut")) ? "" : rdr["PersonOut"].ToString();
model.EnglishService = rdr.IsDBNull(rdr.GetOrdinal("EnglishService")) ? "" : rdr["EnglishService"].ToString();
model.OperatorId = Convert.ToInt32(rdr["OperatorId"]);
model.IssueTime = Convert.ToDateTime(rdr["IssueTime"]);
model.IsDelete = rdr["IsDelete"].ToString() == "1" ? true : false;
model.HotelRoomInfo = GetHotelRoomList(rdr.IsDBNull(rdr.GetOrdinal("HotelRoomXML")) ? null : rdr["HotelRoomXML"].ToString());
#endregion
}
}
return model;
}
/// <summary>
/// 分页获取酒店信息列表
/// </summary>
/// <param name="PageSize">每页显示条数</param>
/// <param name="PageIndex">当前页码</param>
/// <param name="RecordCount">总记录数</param>
/// <param name="SystemId">系统编号</param>
/// <param name="CityId">酒店所属城市编号 =0返回全部</param>
/// <param name="HotelName">酒店名称 模块查询</param>
/// <param name="HotelStar">酒店星级 =null返回全部</param>
/// <param name="ShowPosition">显示位置 =null返回全部</param>
/// <returns>酒店信息列表</returns>
public IList<EyouSoft.Model.DHTemplate.Hotel> GetList(int PageSize, int PageIndex, ref int RecordCount, int SystemId, int CityId, string HotelName, EyouSoft.Model.DHTemplate.Enum.HotelStar? HotelStar, EyouSoft.Model.DHTemplate.Enum.ShowPosition? ShowPosition)
{
IList<EyouSoft.Model.DHTemplate.Hotel> ls = new List<EyouSoft.Model.DHTemplate.Hotel>();
EyouSoft.Model.DHTemplate.Hotel model = null;
StringBuilder strSQL = new StringBuilder();
strSQL.AppendFormat(" SystemId = {0} and IsDelete = '0'", SystemId);
if (CityId != 0)
strSQL.AppendFormat(" and CityId = {0}",CityId);
if (!string.IsNullOrEmpty(HotelName))
strSQL.AppendFormat(" and HotelName like '%{0}%'", HotelName);
if (HotelStar != null)
strSQL.AppendFormat(" and Star = {0}", (byte)HotelStar);
if (ShowPosition != null)
strSQL.AppendFormat(" and (ShowPosition = {0} or ShowPosition = 3)", (byte)ShowPosition);
StringBuilder fields = new StringBuilder();
fields.Append("Id,SystemId,CityId,HotelName,ShowPosition,Star,Remark,HotelInfo,HotelSeat,");
fields.Append(" (case when exists(select 1 from tbl_DHHotelOrder b where tbl_DHHotel.Id = b.HotelId) then '1' else '0' end) as HasOrder,");
fields.Append(" (select a.Id,a.HotelId,a.RoomType,a.BreakFast,a.NetWork,a.BedType,a.Price,a.StartTime,a.EndTime from tbl_DHHotelRoom a where a.HotelId = tbl_DHHotel.Id and tbl_DHHotel.IsDelete='0' for xml raw,root('root')) as HotelRoomXML");
using (IDataReader rdr = DALSQLHelper.GetExportPageList(PageSize, PageIndex, "tbl_DHHotel", fields.ToString(), strSQL.ToString(), "IssueTime", 1, ref RecordCount))
{
while (rdr.Read())
{
#region 基本信息
model = new EyouSoft.Model.DHTemplate.Hotel();
model.Id = Convert.ToInt32(rdr["Id"]);
model.SystemId = Convert.ToInt32(rdr["SystemId"]);
model.CityId = Convert.ToInt32(rdr["CityId"]);
model.HotelName = rdr["HotelName"].ToString();
if (!rdr.IsDBNull(rdr.GetOrdinal("ShowPosition")))
model.ShowPosition = (EyouSoft.Model.DHTemplate.Enum.ShowPosition)int.Parse(rdr["ShowPosition"].ToString());
if (!rdr.IsDBNull(rdr.GetOrdinal("Star")))
model.Star = (EyouSoft.Model.DHTemplate.Enum.HotelStar)int.Parse(rdr["Star"].ToString());
model.Remark = rdr.IsDBNull(rdr.GetOrdinal("Remark")) ? "" : rdr["Remark"].ToString();
model.HotelInfo = rdr.IsDBNull(rdr.GetOrdinal("HotelInfo")) ? "" : rdr["HotelInfo"].ToString();
model.HotelSeat = rdr.IsDBNull(rdr.GetOrdinal("HotelSeat")) ? "" : rdr["HotelSeat"].ToString();
model.HasOrder = rdr["HasOrder"].ToString() == "1" ? true : false;
model.HotelRoomInfo = GetHotelRoomList(rdr.IsDBNull(rdr.GetOrdinal("HotelRoomXML")) ? null : rdr["HotelRoomXML"].ToString());
#endregion
ls.Add(model);
}
}
return ls;
}
#endregion
#region 私有方法
/// <summary>
/// 转XML格式
/// </summary>
/// <param name="ContactXML">XML</param>
/// <returns></returns>
private IList<EyouSoft.Model.DHTemplate.HotelRoom> GetHotelRoomList(string roomXML)
{
if (string.IsNullOrEmpty(roomXML))
return null;
IList<EyouSoft.Model.DHTemplate.HotelRoom> ResultList = new List<EyouSoft.Model.DHTemplate.HotelRoom>();
EyouSoft.Model.DHTemplate.HotelRoom model = null;
XmlDocument xmlDoc = new XmlDocument();
XmlNodeList xmlNodeLs = null;
xmlDoc.LoadXml(roomXML);
xmlNodeLs = xmlDoc.GetElementsByTagName("row");
if (xmlNodeLs != null && xmlNodeLs.Count > 0)
{
foreach (XmlNode node in xmlNodeLs)
{
model = new EyouSoft.Model.DHTemplate.HotelRoom();
model.Id = Convert.ToInt32(node.Attributes["Id"].Value);
model.HotelId = Convert.ToInt32(node.Attributes["HotelId"].Value);
model.RoomType = node.Attributes["RoomType"].Value;
model.BreakFast = (EyouSoft.Model.DHTemplate.Enum.FoodType)System.Enum.Parse(typeof(EyouSoft.Model.DHTemplate.Enum.FoodType), node.Attributes["BreakFast"].Value);
model.NetWork = node.Attributes["NetWork"].Value == "1" ? true:false;
model.BedType = node.Attributes["BedType"].Value;
model.Price = Convert.ToDecimal(node.Attributes["Price"].Value);
model.StartTime = Convert.ToDateTime(node.Attributes["StartTime"].Value);
model.EndTime = Convert.ToDateTime(node.Attributes["EndTime"].Value);
ResultList.Add(model);
}
}
return ResultList;
}
/// <summary>
/// 构造酒店房间XML信息
/// </summary>
/// <param name="roomInfo"></param>
/// <returns></returns>
private string CreateRoomXML(IList<EyouSoft.Model.DHTemplate.HotelRoom> roomInfo)
{
if (roomInfo == null || roomInfo.Count == 0)
return "";
StringBuilder strXml = new StringBuilder();
strXml.Append("<ROOT>");
foreach (EyouSoft.Model.DHTemplate.HotelRoom model in roomInfo)
{
//<ROOT><RoomInfo HotelId="酒店编号" RoomType="房型" BreakFast="1" NetWork="1" BedType="床型" Price="1.1" StartTime="" EndTime=""/></ROOT>
strXml.AppendFormat("<RoomInfo HotelId=/"{0}/" RoomType=/"{1}/" BreakFast=/"{2}/" NetWork=/"{3}/" BedType=/"{4}/" Price=/"{5}/" StartTime=/"{6}/" EndTime=/"{7}/" />",
Utils.ReplaceXmlSpecialCharacter(model.HotelId.ToString()),
Utils.ReplaceXmlSpecialCharacter(model.RoomType.ToString()),
Utils.ReplaceXmlSpecialCharacter(((byte)model.BreakFast).ToString()),
Utils.ReplaceXmlSpecialCharacter(model.NetWork ? "1":"0"),
Utils.ReplaceXmlSpecialCharacter(model.BedType.ToString()),
Utils.ReplaceXmlSpecialCharacter((model.Price).ToString()),
Utils.ReplaceXmlSpecialCharacter((model.StartTime).ToString()),
Utils.ReplaceXmlSpecialCharacter((model.EndTime).ToString()));
}
strXml.Append("</ROOT>");
return strXml.ToString();
}
/// <summary>
/// 转换成字符串
/// </summary>
/// <param name="Ids"></param>
/// <returns></returns>
private string ConvertToString(params int[] Ids)
{
string strIds = string.Empty;
foreach (int str in Ids)
{
strIds += "'" + str.ToString().Trim() + "',";
}
strIds = strIds.Trim(',');
return strIds;
}
#endregion
}
}
其中Utils涉及的方法 Utils.ReplaceXmlSpecialCharacter()如下：
/// <summary>
/// 替换XML敏感字符
/// </summary>
/// <param name="s">输入字符串</param>
/// <returns></returns>
public static string ReplaceXmlSpecialCharacter(string s)
{
if (!string.IsNullOrEmpty(s))
{
return s.Replace("&", "&").Replace("<", "<").Replace(">", ">").Replace("'", "'").Replace("/"", """);
}
return s;
}
2.涉及存储过程
proc_Hotel_AddHotelInfo
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=========================================
--描述：新增酒店信息
--=========================================
ALTER proc [dbo].[proc_Hotel_AddHotelInfo]
@SystemId int,
@CityId int,
@HotelName nvarchar(255),
@ShowPosition tinyint,
@Star tinyint,
@Remark nvarchar(max),
@HotelInfo nvarchar(max),
@HotelSeat nvarchar(max),
@AttachService nvarchar(max),
@ServiceItem nvarchar(max),
@FoodService nvarchar(max),
@PastimeItem nvarchar(max),
@RoomServiceItem nvarchar(max),
@Rescind nvarchar(max),
@OrderChange nvarchar(max),
@NoShow nvarchar(max),
@Refundment nvarchar(max),
@RoomPrice nvarchar(max),
@CheckInAndOut nvarchar(max),
@PersonOut nvarchar(max),
@EnglishService nvarchar(max),
@OperatorId int,
--酒店房型XML XML:<ROOT><RoomInfo HotelId="酒店编号" RoomType="房型" BreakFast="1" NetWork="1" BedType="床型" Price="1.1" StartTime="" EndTime=""/></ROOT>
@HotelRoomXML nvarchar(max),
@Result INT OUTPUT--操作结果 正值1：成功 负值或0：失败
as
begin
declare @sql nvarchar(max)
declare @hotelId int
DECLARE @hdoc INT
SET @Result=0
begin try
begin transaction tran_add
--添加酒店基本信息
set @sql = 'insert into tbl_DHHotel
([SystemId]
,[CityId]
,[HotelName]
,[ShowPosition]
,[Star]
,[Remark]
,[HotelInfo]
,[HotelSeat]
,[AttachService]
,[ServiceItem]
,[FoodService]
,[PastimeItem]
,[RoomServiceItem]
,[Rescind]
,[OrderChange]
,[NoShow]
,[Refundment]
,[RoomPrice]
,[CheckInAndOut]
,[PersonOut]
,[EnglishService]
,[OperatorId])
values( '+cast(@SystemId as nvarchar)+',
'+cast(@CityId as nvarchar)+',
N'''+@HotelName+''',
'+cast(@ShowPosition as nvarchar)+',
'+cast(@Star as nvarchar)+',
N'''+@Remark+''',
N'''+@HotelInfo+''',
N'''+@HotelSeat+''',
N'''+@AttachService+''',
N'''+@ServiceItem+''',
N'''+@FoodService+''',
N'''+@PastimeItem+''',
N'''+@RoomServiceItem+''',
N'''+@Rescind+''',
N'''+@OrderChange+''',
N'''+@NoShow+''',
N'''+@Refundment+''',
N'''+@RoomPrice+''',
N'''+@CheckInAndOut+''',
N'''+@PersonOut+''',
N'''+@EnglishService+''',
'+cast(@OperatorId as nvarchar)+')'
exec(@sql)
select @hotelId = @@identity
--添加酒店房间信息
if @HotelRoomXML is not null and len(@HotelRoomXML)>0
begin
EXECUTE sp_xml_preparedocument @hdoc OUTPUT,@HotelRoomXML
INSERT INTO tbl_DHHotelRoom(HotelId,RoomType,BreakFast,NetWork,BedType,Price,StartTime,EndTime)
SELECT @hotelId,RoomType,BreakFast,NetWork,BedType,Price,StartTime,EndTime
FROM OPENXML(@hdoc,'/ROOT/RoomInfo')
WITH(RoomType nvarchar(255),BreakFast tinyint,NetWork char(1),BedType nvarchar(255),Price money,StartTime datetime,EndTime datetime)
EXECUTE sp_xml_removedocument @hdoc
end
commit transaction tran_add
set @Result = @hotelId
return @Result
end try
begin catch
rollback transaction tran_add
set @Result = 0
return @Result
end catch
end
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
proc_Hotel_UpdateHotelInfo
--=========================================
--描述：修改酒店信息
--=========================================
ALTER proc [dbo].[proc_Hotel_UpdateHotelInfo]
@Id int, --酒店编号
@CityId int,
@HotelName nvarchar(255),
@ShowPosition tinyint,
@Star tinyint,
@Remark nvarchar(max),
@HotelInfo nvarchar(max),
@HotelSeat nvarchar(max),
@AttachService nvarchar(max),
@ServiceItem nvarchar(max),
@FoodService nvarchar(max),
@PastimeItem nvarchar(max),
@RoomServiceItem nvarchar(max),
@Rescind nvarchar(max),
@OrderChange nvarchar(max),
@NoShow nvarchar(max),
@Refundment nvarchar(max),
@RoomPrice nvarchar(max),
@CheckInAndOut nvarchar(max),
@PersonOut nvarchar(max),
@EnglishService nvarchar(max),
--酒店房型XML XML:<ROOT><RoomInfo HotelId="酒店编号" RoomType="房型" BreakFast="早餐" NetWork="网络" BedType="床型" Price="价格" /></ROOT>
@HotelRoomXML nvarchar(max),
@Result INT OUTPUT--操作结果 正值1：成功 负值或0：失败
as
begin
declare @sql nvarchar(max)
DECLARE @hdoc INT
SET @Result=0
begin try
begin transaction tran_update
--修改酒店基本信息
set @sql = 'update tbl_DHHotel set
[CityId] = '+cast(@CityId as nvarchar)+'
,[HotelName] = N'''+@HotelName+'''
,[ShowPosition] = '+cast(@ShowPosition as nvarchar)+'
,[Star] = '+cast(@Star as nvarchar)+'
,[Remark] = N'''+@Remark+'''
,[HotelInfo] = N'''+@HotelInfo+'''
,[HotelSeat] = N'''+@HotelSeat+'''
,[AttachService] = N'''+@AttachService+'''
,[ServiceItem] = N'''+@ServiceItem+'''
,[FoodService] = N'''+@FoodService+'''
,[PastimeItem] = N'''+@PastimeItem+'''
,[RoomServiceItem] = N'''+@RoomServiceItem+'''
,[Rescind] = N'''+@Rescind+'''
,[OrderChange] = N'''+@OrderChange+'''
,[NoShow] = N'''+@NoShow+'''
,[Refundment] = N'''+@Refundment+'''
,[RoomPrice] = N'''+@RoomPrice+'''
,[CheckInAndOut] = N'''+@CheckInAndOut+'''
,[PersonOut] = N'''+@PersonOut+'''
,[EnglishService] = N'''+@EnglishService+'''
where Id = '+cast(@Id as nvarchar)+''
exec(@sql)
--添加酒店房间信息
if @HotelRoomXML is not null and len(@HotelRoomXML)>0
begin
delete from tbl_DHHotelRoom where HotelId = @Id
EXECUTE sp_xml_preparedocument @hdoc OUTPUT,@HotelRoomXML
INSERT INTO tbl_DHHotelRoom(HotelId,RoomType,BreakFast,NetWork,BedType,Price,StartTime,EndTime)
SELECT @Id,RoomType,BreakFast,NetWork,BedType,Price,StartTime,EndTime
FROM OPENXML(@hdoc,'/ROOT/RoomInfo')
WITH(RoomType nvarchar(255),BreakFast tinyint,NetWork char(1),BedType nvarchar(255),Price money,StartTime datetime,EndTime datetime)
EXECUTE sp_xml_removedocument @hdoc
end
commit transaction tran_update
set @Result = 1
return @Result
end try
begin catch
rollback transaction tran_update
set @Result = 0
return @Result
end catch
end
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
- .若A,B两个实体是一对多的包含关系。利用存储过程，实现数据访问层层的一个实例。
- 光脚丫学LINQ(038)：由两个一对多的关系跨越联接表实体类访问多对多关系数据
- 利用存储过程把两个表的数据插入到一个新表中（存储过程中写两个游标）
- Mysql 存储过程之函数-如判断两个以逗号分隔字符串的包含关系
- Drupal中如何实现实体之间的一对多，或多对多关系？
- 在一个存储过程A中调用另外一个存储过程B返回的结果集
- Hibernate实体映射——单边的一对多关系
- 光脚丫学LINQ(033)：建立映射关系的两个实体类必须分别包含一个主键列成员
- oracle利用游标实现返回一个表形式数据集合的函数及存储过程
- 这是oracle包实体里面的一个存储过程 oracle存储过程中的if...elseif...else用法
- mysql用存储过程写入 一个表A中没有存在在另一个表B的数据
- 一个既包含a又包含b 的sql 语句
- 关于Extjs拥有了一对一的关系 或者一对多的关系的数据的存储问题
- 一个HIBERNATE一对多的实例
- ###### 有关联关系的【多表对应实体类的编写步骤】+ssh注解。实例：crm实体编写过程
- 利用ajax+jquery 实现一对多关系中select列表的联动
- mybaties的批量插入只有一个值是变量的 是一对多关系表 的批量插入
- 存储过程，比较两个表的数据
- Bash Shell 字符串切割
- The Memory
- 什么是多态
- linux shell 一些判断总结
- 常用的C变量的定义方式：指针有关
- .若A,B两个实体是一对多的包含关系。利用存储过程，实现数据访问层层的一个实例。
- 查看端口占用，并关闭它还有它的祖宗？
- 字符常量与转义字符
- 积分
- javascript无提示关闭窗口
- PHP中foreach循环详解
- PHP 读取文本文件内容写入数据库
- mysql库 :(分类求和 汇总排序)
- MySQL数值类型——FLOAT、DOUBLE和DECIMAL类型
