public void InsertData(RatePolicyDetailModel model) { List <string> sqls = new List <string>(); List <SqlParameter[]> cmdParms = new List <SqlParameter[]>(); StringBuilder str = new StringBuilder(); str.AppendLine("Insert into RoomPolicy"); str.AppendLine(" (StartDate, EndDate,RoomType,WeekDays,BasicRate,DiscountRate,IsDelete,CreateUser,CreateTime,UpdateUser,UpdateTime) "); str.AppendLine(" Values (@StartDate, @EndDate, @RoomType, @WeekDays, @BasicRate, @DiscountRate, @IsDelete,@CreateUser, getdate(), @UpdateUser,getdate());"); SqlParameter[] paras = new SqlParameter[9]; paras[0] = new SqlParameter("@StartDate", model.StartDate); paras[1] = new SqlParameter("@EndDate", model.EndDate); paras[2] = new SqlParameter("@RoomType", model.RoomType); paras[3] = new SqlParameter("@WeekDays", Common.VariableConvert.ConvertStringToDBValue(model.WeekDays)); paras[4] = new SqlParameter("@BasicRate", model.BasicRate); paras[5] = new SqlParameter("@DiscountRate", Common.VariableConvert.ConvertStringToDBValue(model.DiscountRate)); paras[6] = new SqlParameter("@IsDelete", Common.VariableConvert.BitConverter(model.IsDelete)); paras[7] = new SqlParameter("@CreateUser", Common.VariableConvert.ConvertStringToDBValue(model.CreateUser)); paras[8] = new SqlParameter("@UpdateUser", Common.VariableConvert.ConvertStringToDBValue(model.UpdateUser)); sqls.Add(str.ToString()); cmdParms.Add(paras); SqlAccess mySqlAccess = new SqlAccess(); mySqlAccess.ExecuteNonQuerys(sqls, cmdParms); }
public bool IsExistRoomRatePolicy(RatePolicyDetailModel model) { StringBuilder sql = new StringBuilder(); sql.AppendLine("SELECT "); sql.AppendLine(" RoomPolicyID"); sql.AppendLine(" FROM RoomPolicy WITH(NOLOCK)"); sql.AppendLine(" WHERE 1=1"); sql.AppendLine(" and ((StartDate <= @StartDate and EndDate>=@StartDate)"); sql.AppendLine(" or (StartDate <= @EndDate and EndDate>=@EndDate))"); sql.AppendLine(" and RoomType = @RoomType"); if (model.OpertationStatus == GlobalVariable.UpdateStatus.Update) { sql.AppendLine(" and RoomPolicyID <> @RoomPolicyID"); } SqlParameter[] paras = new SqlParameter[4]; paras[0] = new SqlParameter("@StartDate", model.StartDate); paras[1] = new SqlParameter("@EndDate", model.EndDate); paras[2] = new SqlParameter("@RoomType", model.RoomType); paras[3] = new SqlParameter("@RoomPolicyID", model.RoomPolicyID); SqlAccess mySqlAccess = new SqlAccess(); DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras); List <RatePolicyDetailModel> ratePolicy = new List <RatePolicyDetailModel>(); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { return(true); } return(false); }
public RatePolicyDetailModel GetDataByID(string id) { StringBuilder sql = new StringBuilder(); sql.AppendLine("SELECT "); sql.AppendLine(" RoomPolicyID, StartDate, EndDate, RoomType,"); //sql.AppendLine(" RoomTypeName = case RoomType when 0 then 'King' when 1 then 'Queen' end, "); sql.AppendLine(" WeekDays, BasicRate, DiscountRate, IsDelete"); sql.AppendLine(" FROM RoomPolicy WITH(NOLOCK)"); sql.AppendLine(" WHERE RoomPolicyID=@RoomPolicyID"); SqlParameter[] paras = new SqlParameter[3]; paras[0] = new SqlParameter("@RoomPolicyID", id); SqlAccess mySqlAccess = new SqlAccess(); DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras); RatePolicyDetailModel ratePolicy = new RatePolicyDetailModel(); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { ratePolicy = DataMappingHelper <RatePolicyDetailModel> .DataTableToEntity(ds.Tables[0]); } return(ratePolicy); }
public void UpdateData(RatePolicyDetailModel model) { List <string> sqls = new List <string>(); List <SqlParameter[]> cmdParms = new List <SqlParameter[]>(); StringBuilder str = new StringBuilder(); str.AppendLine("update RoomPolicy set"); str.AppendLine(" StartDate=@StartDate"); str.AppendLine(" ,EndDate=@EndDate"); str.AppendLine(" ,RoomType=@RoomType"); str.AppendLine(" ,WeekDays=@WeekDays"); str.AppendLine(" ,BasicRate=@BasicRate"); str.AppendLine(" ,DiscountRate=@DiscountRate"); str.AppendLine(" ,IsDelete=@IsDelete"); str.AppendLine(" ,UpdateUser=@UpdateUser"); str.AppendLine(" ,UpdateTime=getdate()"); str.AppendLine(" where RoomPolicyID=@RoomPolicyID"); SqlParameter[] paras = new SqlParameter[9]; paras[0] = new SqlParameter("@StartDate", model.StartDate); paras[1] = new SqlParameter("@EndDate", model.EndDate); paras[2] = new SqlParameter("@RoomType", model.RoomType); paras[3] = new SqlParameter("@WeekDays", Common.VariableConvert.ConvertStringToDBValue(model.WeekDays)); paras[4] = new SqlParameter("@BasicRate", model.BasicRate); paras[5] = new SqlParameter("@DiscountRate", Common.VariableConvert.ConvertStringToDBValue(model.DiscountRate)); paras[6] = new SqlParameter("@IsDelete", Common.VariableConvert.BitConverter(model.IsDelete)); paras[7] = new SqlParameter("@UpdateUser", Common.VariableConvert.ConvertStringToDBValue(model.UpdateUser)); paras[8] = new SqlParameter("@RoomPolicyID", model.RoomPolicyID); sqls.Add(str.ToString()); cmdParms.Add(paras); SqlAccess mySqlAccess = new SqlAccess(); mySqlAccess.ExecuteNonQuerys(sqls, cmdParms); }