public ModelRequest()
{
pagination = new PageInationInfo {
Size = 10
};
conditions = new TestMasterInfo.Condtions();
}
protected void bindMaster(string name)
{
TestBiz.Master biz = new TestBiz.Master();
var _pagination = pagination;
DataTable dt = biz.Load(ref _pagination, Name: name);
pagination = _pagination;
gvMaster.DataSource = dt; //先存他的SOURCE
gvMaster.DataBind(); //在BIND
Pager.pagination = pagination;
upGridView.Update();
}
// POST api/<controller>
public dynamic Post(string id, [FromBody] dynamic value)
{
bool result = false;
if (id == "load")
{
ModelRequest model = JsonConvert.DeserializeObject <ModelRequest>(JsonConvert.SerializeObject(value));
var biz = new TestBiz.Master();
PageInationInfo pagination = model.pagination;
ModelResponse resultLoad = new ModelResponse {
conditions = model.conditions
};
resultLoad.data = biz.Load(ref pagination, model.conditions).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"),
UpdaueTime = i.Field <DateTime>("UpdaueTime")
})
.ToList();
resultLoad.pagination = new ModelResponse.PaginationInfo {
Index = pagination.Index, Size = pagination.Size, Total = pagination.Total
};
return(resultLoad);
}
if (id == "create")
{
TestMasterInfo model = JsonConvert.DeserializeObject <TestMasterInfo>(JsonConvert.SerializeObject(value));
if (ModelState.IsValid)
{
var biz = new TestBiz.Master();
return(biz.Insert(model));
}
return("資料驗證錯誤");
}
return(result);
}
public DataTable Load(ref PageInationInfo pagination, TestMasterInfo.Condtions Condtions)
{
DataTable result = new DataTable();
try
{
var db = new TestMasterDB();
result = db.Load(ref pagination, Condtions.SID, ID: Condtions.ID, NO: Condtions.NO, Name: Condtions.Name, Phone: Condtions.Phone
, Address: Condtions.Address,
BirthdayFrom: Condtions.BirthdayFrom, BirthdayTo: Condtions.BirthdayTo, AgeFrom: Condtions.AgeFrom, AgeTo: Condtions.AgeTo);
}
catch (Exception ex)
{
var dbExpLog = new ExpLogDB();
dbExpLog.Insert(new ExpLogInfo {
ClassName = "TestBiz.Master", MethodName = "Load", ErrMsg = ex.Message
});
}
return(result);
}
/// <summary>
/// 依條件取多筆資料
/// </summary>
/// <param name="SID"></param>
/// <param name="ID"></param>
/// <param name="NO"></param>
/// <param name="Name"></param>
/// <param name="Phone"></param>
/// <param name="Address"></param>
/// <param name="BirthdayFrom"></param>
/// <param name="BirthdayTo"></param>
/// <param name="AgeFrom"></param>
/// <param name="AgeTo"></param>
/// <returns></returns>
public DataTable Load(ref PageInationInfo pagination, int?SID = null, string ID = null, string NO = null, string Name = null, string Phone = null,
string Address = null, DateTime?BirthdayFrom = null, DateTime?BirthdayTo = null,
decimal?AgeFrom = null, decimal?AgeTo = null)
{
DataTable result = new DataTable();
try
{
var db = new TestMasterDB();
result = db.Load(ref pagination, SID, ID: ID, NO: NO, Name: Name, Phone: Phone, Address: Address,
BirthdayFrom: BirthdayFrom, BirthdayTo: BirthdayTo, AgeFrom: AgeFrom, AgeTo: AgeTo);
}
catch (Exception ex)
{
var dbExpLog = new ExpLogDB();
dbExpLog.Insert(new ExpLogInfo {
ClassName = "TestBiz.Master", MethodName = "Load", ErrMsg = ex.Message
});
}
return(result);
}
protected void Pager_PageIndexChanged(object sender, EventArgs e, PageInationInfo pagination)
{
this.pagination = pagination;
bindMaster(null);
}
public DataTable Load(ref PageInationInfo pagination, TestMasterInfo.Condtions Condtions)
{
Database db = base.GetDatabase();
StringBuilder sbCmd = new StringBuilder();
sbCmd.Append(@"
SELECT ROW_NUMBER() over(order by updauetime desc, SID desc) rowno,
*
FROM [TestMaster] WITH (Nolock)
WHERE (1=1) "
);
#region 條件式
if (Condtions.SID.HasValue)
{
sbCmd.Append(" AND SID=@SID");
}
if (!string.IsNullOrEmpty(Condtions.ID))
{
sbCmd.Append(" AND ID=@ID");
}
if (!string.IsNullOrEmpty(Condtions.NO))
{
sbCmd.Append(" AND NO=@NO");
}
if (!string.IsNullOrEmpty(Condtions.Name))
{
sbCmd.Append(" AND Name like @Name");
}
if (!string.IsNullOrEmpty(Condtions.Phone))
{
sbCmd.Append(" AND Phone=@Phone");
}
if (!string.IsNullOrEmpty(Condtions.Address))
{
sbCmd.Append(" AND Address=@Address");
}
if (Condtions.BirthdayFrom.HasValue)
{
sbCmd.Append(" AND Cast(Birthday as date) >= @BirthdayFrom");
}
if (Condtions.BirthdayTo.HasValue)
{
sbCmd.Append(" AND Cast(Birthday as date) <= @BirthdayTo");
}
if (Condtions.AgeFrom.HasValue)
{
sbCmd.Append(" AND Age >= @AgeFrom");
}
if (Condtions.AgeTo.HasValue)
{
sbCmd.Append(" AND Age <= @AgeTo");
}
#endregion
string sql = sbCmd.ToString();
DbCommand dbCommand = db.GetSqlStringCommand(sql);
#region Add In Parameter
if (Condtions.SID.HasValue)
{
db.AddInParameter(dbCommand, "@SID", DbType.Int32, Condtions.SID.Value);
}
if (!string.IsNullOrEmpty(Condtions.ID))
{
db.AddInParameter(dbCommand, "@ID", DbType.String, Condtions.ID);
}
if (!string.IsNullOrEmpty(Condtions.NO))
{
db.AddInParameter(dbCommand, "@NO", DbType.String, Condtions.NO);
}
if (!string.IsNullOrEmpty(Condtions.Name))
{
db.AddInParameter(dbCommand, "@Name", DbType.String, $"%{Condtions.Name}%");
}
if (!string.IsNullOrEmpty(Condtions.Phone))
{
db.AddInParameter(dbCommand, "@Phone", DbType.String, Condtions.Phone);
}
if (!string.IsNullOrEmpty(Condtions.Address))
{
db.AddInParameter(dbCommand, "@Address", DbType.String, Condtions.Address);
}
if (Condtions.BirthdayFrom.HasValue)
{
db.AddInParameter(dbCommand, "@BirthdayFrom", DbType.DateTime, Condtions.BirthdayFrom.Value.Date);
}
if (Condtions.BirthdayTo.HasValue)
{
db.AddInParameter(dbCommand, "@BirthdayTo", DbType.DateTime, Condtions.BirthdayTo.Value.Date);
}
if (Condtions.AgeFrom.HasValue)
{
db.AddInParameter(dbCommand, "@AgeFrom", DbType.Decimal, Condtions.AgeFrom.Value);
}
if (Condtions.AgeTo.HasValue)
{
db.AddInParameter(dbCommand, "@AgeTo", DbType.Decimal, Condtions.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]);
}
public async Task <ActionResult> List(PageInationInfo pagination, Condtions condtions, string view = "")
{
#region Pager
//if (pagination == null) pagination = new PageInationInfo();
//if (pager.HasValue) pagination.Index = pager.Value;
//pagination.Index = Index;
//pagination.Size = Size;
//pagination.Total = Total;
//int lastPage = pagination.Total / pagination.Size;
//if ((pagination.Total % pagination.Size) != 0) lastPage += 1;
//if (pager == "first") pagination.Index = 1;
//if (pager == "prev" && pagination.Index > 1) pagination.Index -= 1;
//if (pager == "next" && ((pagination.Index + 1) <= lastPage)) pagination.Index += 1;
//if (pager == "last") pagination.Index = lastPage;
//if (!string.IsNullOrEmpty(pager) && pager.StartsWith("page"))
//{
// pagination.Index = Convert.ToInt32(pager.Replace("page", ""));
//}
#endregion
{
#region 取資料
//var biz = new teresa.business.TestBiz.Master();
var request = new ApiRequest
{
pagination = pagination,
condtions = condtions
};
var biz = new ApiBiz();
var response = await biz.Post <ApiResponse>("http://localhost/API/api/", "test", "load", request);
//DataTable dt = biz.Load(ref pagination, Condtions: condtions);
#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"),
// UpdaueTime = i.Field<DateTime>("UpdaueTime")
// })
// .ToList();
//}
#endregion
#endregion 註解尾
#region ViewBag
//ViewBag.Index = pagination.Index;
//ViewBag.Size = pagination.Size;
//ViewBag.Total = pagination.Total;
//ViewBag.ConditonName = condtions.Name;
ViewBag.condtions = response.condtions;
ViewBag.Pager = response.pagination;
#endregion
//return View(model);
string path = "~/Views/Shared/Test/_List.cshtml";
if (!string.IsNullOrEmpty(view))
{
path = $"~/Views/Shared/Test/_List_{view}.cshtml";
}
return(PartialView(path, response.data));
}
}
