public ActionResult Read(TestMasterInfo.Conditions conditions) { var biz = new rita.Business.TestBiz.Master(); var model = biz.Load(SID: conditions.SID, ID: conditions.ID, NO: conditions.NO); return(PartialView("~/Views/Shared/Test/_Read.cshtml", model)); }
public ModelRequest() { pagination = new PaginationInfo { Size = 10 }; conditions = new TestMasterInfo.Conditions(); }
public async Task <ActionResult> List(PaginationInfo pagination, TestMasterInfo.Conditions conditions, string view = "") { #region 取資料 //var biz = new rita.Business.TestBiz.Master(); //DataTable dt = biz.Load(ref pagination, conditions: conditions); var request = new ApiReruest { pagination = pagination, conditions = conditions }; var biz = new ApiBiz(); var response = await biz.Post <ApiResponse>("http://localhost/api/api/", "test", "load", request); #region //Linq //List<TestMasterInfo> model = new List<TestMasterInfo>(); //if (dt != null && dt.Rows.Count > 0) //{ // model = // dt.AsEnumerable() // .Select(i => new TestMasterInfo // { // SID = i.Field<int>("SID"), // ID = i.Field<string>("ID"), // NO = i.Field<string>("NO"), // Name = i.Field<string>("Name"), // Address = i.Field<string>("Address"), // Phone = i.Field<string>("Phone"), // Age = i.Field<decimal?>("Age"), // Birthday = i.Field<DateTime?>("Birthday"), // CreateTime = i.Field<DateTime>("CreateTime"), // UpdateTime = i.Field<DateTime>("UpdateTime") // }) // .ToList(); //} #endregion #endregion #region ViewBag ViewBag.Pager = response.pagination; ViewBag.Conditions = response.conditions; #endregion string path = "~/Views/Shared/Test/_List.cshtml"; if (!string.IsNullOrEmpty(view)) { path = $"~/Views/Shared/Test/_List_{view}.cshtml"; } //return View(model); return(PartialView(path, response.data)); }
public DataTable Load(ref PaginationInfo pagination, TestMasterInfo.Conditions conditions) { DataTable result = new DataTable(); try { var db = new TestMasterDB(); result = db.Load(ref pagination, conditions); } catch (Exception ex) { var dbExpLog = new ExpLogDB(); dbExpLog.Insert(new ExpLogInfo { ClassName = "TestBiz.Master", MethodName = "Load", ErrMsg = ex.Message }); } return(result); }
public DataTable Load(ref PaginationInfo pagination, TestMasterInfo.Conditions conditions) { Database db = base.GetDatabase(); StringBuilder sbCmd = new StringBuilder(); sbCmd.Append(@" SELECT ROW_NUMBER() over(order by updatetime desc, SID desc) rowno, * FROM [TestMaster] WITH (Nolock) WHERE (1=1) " ); #region 條件式 if (conditions.SID.HasValue) { sbCmd.Append(" AND SID=@SID"); } if (!string.IsNullOrEmpty(conditions.ID)) { sbCmd.Append(" AND ID=@ID"); } if (!string.IsNullOrEmpty(conditions.NO)) { sbCmd.Append(" AND NO=@NO"); } if (!string.IsNullOrEmpty(conditions.Name)) { sbCmd.Append(" AND Name like @Name"); } if (!string.IsNullOrEmpty(conditions.Phone)) { sbCmd.Append(" AND Phone like @Phone"); } if (!string.IsNullOrEmpty(conditions.Address)) { sbCmd.Append(" AND Address like @Address"); } if (conditions.BirthdayFrom.HasValue) { sbCmd.Append(" AND Cast(Birthday as date) >= @BirthdayFrom"); } if (conditions.BirthdayTo.HasValue) { sbCmd.Append(" AND Cast(Birthday as date) <= @BirthdayTo"); } if (conditions.AgeFrom.HasValue) { sbCmd.Append(" AND Age >= @AgeFrom"); } if (conditions.AgeTo.HasValue) { sbCmd.Append(" AND Age <= @AgeTo"); } #endregion string sql = sbCmd.ToString(); DbCommand dbCommand = db.GetSqlStringCommand(sql); #region Add In Parameter if (conditions.SID.HasValue) { db.AddInParameter(dbCommand, "@SID", DbType.Int32, conditions.SID.Value); } if (!string.IsNullOrEmpty(conditions.ID)) { db.AddInParameter(dbCommand, "@ID", DbType.String, conditions.ID); } if (!string.IsNullOrEmpty(conditions.NO)) { db.AddInParameter(dbCommand, "@NO", DbType.String, conditions.NO); } if (!string.IsNullOrEmpty(conditions.Name)) { db.AddInParameter(dbCommand, "@Name", DbType.String, $"%{conditions.Name}%"); } if (!string.IsNullOrEmpty(conditions.Phone)) { db.AddInParameter(dbCommand, "@Phone", DbType.String, $"%{conditions.Phone}%"); } if (!string.IsNullOrEmpty(conditions.Address)) { db.AddInParameter(dbCommand, "@Address", DbType.String, $"%{conditions.Address}%"); } if (conditions.BirthdayFrom.HasValue) { db.AddInParameter(dbCommand, "@BirthdayFrom", DbType.DateTime, conditions.BirthdayFrom.Value.Date); } if (conditions.BirthdayTo.HasValue) { db.AddInParameter(dbCommand, "@BirthdayTo", DbType.DateTime, conditions.BirthdayTo.Value.Date); } if (conditions.AgeFrom.HasValue) { db.AddInParameter(dbCommand, "@AgeFrom", DbType.Decimal, conditions.AgeFrom.Value); } if (conditions.AgeTo.HasValue) { db.AddInParameter(dbCommand, "@AgeTo", DbType.Decimal, conditions.AgeTo.Value); } #endregion if (pagination.Total == 0) { dbCommand.CommandText = "with x as ( " + sql + " ) select count(*) from x"; pagination.Total = (int)ExecuteScalar(db, dbCommand); } dbCommand.CommandText = "with x as (" + sql + ") select * from x where rowno between @s and @e"; db.AddInParameter(dbCommand, "@s", DbType.Int32, (pagination.Index - 1) * pagination.Size + 1); db.AddInParameter(dbCommand, "@e", DbType.Int32, pagination.Size * pagination.Index); return(ExecuteDataSet(db, dbCommand).Tables[0]); }