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]);
}
