public ActionResult Index(IndexVM objIndexVM)
{
try
{
QueryBaseData objQueryBaseData = new QueryBaseData();
objQueryBaseData.QueryParam["IsEnable"] = 1;
if (!string.IsNullOrWhiteSpace(objIndexVM.Name))
{
objQueryBaseData.QueryParam["Name"] = objIndexVM.Name.Trim();
}
if (!string.IsNullOrWhiteSpace(objIndexVM.Nickname))
{
objQueryBaseData.QueryParam["Nickname"] = objIndexVM.Nickname.Trim();
}
objQueryBaseData.DataTableParam.PageStartRow = objIndexVM.Start;
objQueryBaseData.DataTableParam.PageRowCnt = objIndexVM.Length;
objQueryBaseData.DataTableParam.OrderColumn = objIndexVM.OrderBy;
objQueryBaseData.DataTableParam.OrderDir = objIndexVM.OrderDir.ToString();
ContactInfoRepository objContactInfoRepository = new ContactInfoRepository();
List <ContactInfoExData> liContactInfoExData = objContactInfoRepository.GetContactInfoByCondition(objQueryBaseData);
int iTotalCount = (liContactInfoExData.Any() ? liContactInfoExData.Select(e => e.TotalCount).FirstOrDefault() : 0);
DataTableResVM <ContactInfoData> objDataTableResVM = new DataTableResVM <ContactInfoData>(objIndexVM.Draw, iTotalCount, iTotalCount, liContactInfoExData);
return(Content(JsonConvert.SerializeObject(objDataTableResVM), "application/json"));
}
catch (Exception ex)
{
DataTableResVM <ContactInfoData> objDataTableResVM = new DataTableResVM <ContactInfoData>(ex.Message);
return(Content(JsonConvert.SerializeObject(objDataTableResVM), "application/json"));
}
}
public List <ContactInfoExData> GetContactInfoByCondition(QueryBaseData objQueryBaseData)
{
List <ContactInfoExData> liContactInfoExData = null;
try
{
using (var objConnect = GetDBConnection())
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT *, (SELECT COUNT(1) FROM Tbl_ContactInfo) AS TotalCount FROM Tbl_ContactInfo");
sbSQL.AppendLine("WHERE 1=1");
#region [Query Condition]
if (objQueryBaseData.QueryParam.Any())
{
if (objQueryBaseData.QueryParam.ContainsKey("IsEnable"))
{
sbSQL.AppendLine("AND IsEnable=@IsEnable");
}
if (objQueryBaseData.QueryParam.ContainsKey("Name"))
{
sbSQL.AppendLine("AND Name LIKE @Name");
}
if (objQueryBaseData.QueryParam.ContainsKey("Nickname"))
{
sbSQL.AppendLine("AND Nickname LIKE @Nickname");
}
}
#endregion
#region [Order]
string strSort = objQueryBaseData.DataTableParam.OrderColumn + " " + objQueryBaseData.DataTableParam.OrderDir;
if (!string.IsNullOrWhiteSpace(strSort))
{
sbSQL.AppendLine("ORDER BY");
sbSQL.AppendLine("CASE WHEN @Sort = 'Name ASC' THEN Name END ASC,");
sbSQL.AppendLine("CASE WHEN @Sort = 'Name DESC' THEN Name END DESC,");
sbSQL.AppendLine("CASE WHEN @Sort = 'Nickname ASC' THEN Nickname END ASC,");
sbSQL.AppendLine("CASE WHEN @Sort = 'Nickname DESC' THEN Nickname END DESC,");
sbSQL.AppendLine("CASE WHEN @Sort = 'Gender ASC' THEN Gender END ASC,");
sbSQL.AppendLine("CASE WHEN @Sort = 'Gender DESC' THEN Gender END DESC,");
sbSQL.AppendLine("CASE WHEN @Sort = 'Age ASC' THEN Age END ASC,");
sbSQL.AppendLine("CASE WHEN @Sort = 'Age DESC' THEN Age END DESC");
}
else
{
sbSQL.AppendLine("ORDER BY ContactInfoID ASC");
}
#endregion
#region [Paging]
if (null != objQueryBaseData.DataTableParam.PageStartRow && null != objQueryBaseData.DataTableParam.PageRowCnt)
{
sbSQL.AppendLine("OFFSET @Start ROWS FETCH NEXT @Length ROWS ONLY");
}
#endregion
liContactInfoExData = objConnect.Query <ContactInfoExData>(sbSQL.ToString(), new {
IsEnable = (objQueryBaseData.QueryParam.ContainsKey("IsEnable") ? objQueryBaseData.QueryParam["IsEnable"] : 0),
Name = (objQueryBaseData.QueryParam.ContainsKey("Name") ? "%" + objQueryBaseData.QueryParam["Name"] + "%" : string.Empty),
Nickname = (objQueryBaseData.QueryParam.ContainsKey("Nickname") ? "%" + objQueryBaseData.QueryParam["Nickname"] + "%" : string.Empty),
Sort = strSort ?? string.Empty,
Start = objQueryBaseData.DataTableParam.PageStartRow ?? 0,
Length = objQueryBaseData.DataTableParam.PageRowCnt ?? 0
}).ToList();
}
}
catch (Exception ex)
{
throw ex;
}
return(liContactInfoExData);
}
