上次解释了几个易犯错的地方，当然对于大神们那都是小菜一碟了，今天来说说后台请求数据，分页，返回json数据
废话不多说献上代码
private string QueryList(ArrayList arrayList) { //ArrayList ret = new ArrayList(); string order = this.Request.Form["order"]; string sort = this.Request.Form["sort"]; string rows = this.Request.Form["rows"]; string page = this.Request.Form["page"]; //page=1&rows=10&sort=UserName&order=desc int iSortIndex, iPage, iRows; string[] colums = new string[] { "moveid", "daytime", "outsite", "insite", "inputman", "handleman", "outdate", "outid", "availability", "invaliddate", "status", "preconcert", "checked", "chkdate", "fin_checked", "remark", "used", "deleted"}; //if (!int.TryParse(sidx, out iSortIndex)) //{ // iSortIndex = -1; //} iSortIndex = 1; if (!int.TryParse(page, out iPage)) { iPage = 1; } if (!int.TryParse(rows, out iRows) || iRows > 100 || iRows <= 0) { iRows = 20; } int total = 0; //传入表名 JGDataQuery gq = new JGDataQuery("move"); //传入搜索条件 if (!string.IsNullOrEmpty(Request.Form["IdOrName"])) { if (Erp.Common.Verify.IsNumber(Request.Form["IdOrName"])) { gq.AddQueryParam("moveid", Request.Form["IdOrName"]); } } DataTable dt = gq.GetListByPage(colums, iPage, iRows, 1, sort,out total);
//返回json return Erp.Common.ToEasyJson.TableToJson(dt,total); }
上面查询数据是封装了一个类，包括查询条件和数据分页sql,当然用存储过程是最好，最快的啦，但是系统关联表太多，为了不麻烦的频繁改，就拼sql啦，为了扩展方便嘛，偷偷懒
public class JGDataQuery { public string TableName { get; private set; } string sqlWhere; List<SqlParameter> sqlParaList = new List<SqlParameter>(); public JGDataQuery(string tableName) { // //TODO: 在此处添加构造函数逻辑 // if (string.IsNullOrEmpty(tableName)) { throw new NullReferenceException("表名不能为空;"); } TableName = tableName; } public void AddQueryParam(string name, object paramValue) { sqlWhere += " and " + name + "=@" + name; sqlParaList.Add(new SqlParameter(name, paramValue)); } /// <summary> /// 同一列值多值 /// </summary> /// <param name="columnName"></param> /// <param name="key"></param> /// <param name="keyValue"></param> public void AddInParams(string columnName, List<string> listParams) { sqlWhere += " and " + columnName + " in ("; foreach (string item in listParams) { sqlWhere += item + ','; } if (sqlWhere.Length > 0) { sqlWhere = sqlWhere.Remove(sqlWhere.LastIndexOf(','), 1); } sqlWhere += " ) "; } public void AddLikeQueryParam(string name, object paramValue) { sqlWhere += " and " + name + " like '%@" + name + "%' "; sqlParaList.Add(new SqlParameter(name, paramValue)); } public void AddLetterThanParm(string name, object paramValue) { sqlWhere += " and " + name + "<@" + name; sqlParaList.Add(new SqlParameter(name, paramValue)); } public void AddGreaterThanParm(string name, object paramValue) { sqlWhere += " and " + name + ">@" + name; sqlParaList.Add(new SqlParameter(name, paramValue)); } public void AddNoEqualParam(string name, object paramValue) { sqlWhere += name + "<>@" + name + " and "; sqlParaList.Add(new SqlParameter(name, paramValue)); } public void AddBetweenQueryParam(string starttime, string endtime, object paramValue1, object paramValue2) { sqlWhere += " and " + starttime + " < @" + starttime; sqlWhere += " and " + endtime + "< @" + endtime; sqlParaList.Add(new SqlParameter(starttime, paramValue1)); sqlParaList.Add(new SqlParameter(endtime, paramValue2)); } /// <summary> /// Or条件查询 /// </summary> string sqlOrWhere = string.Empty; public void AddlikeOrParam(string name, object paramValue) { //sqlOrWhere += name + " like @" + name + " or "; //sqlParaList.Add(new SqlParameter(name, " '%" + paramValue + "%' ")); sqlOrWhere += name + " like '%" + paramValue + "%' or "; sqlParaList.Add(new SqlParameter(name, " '%" + paramValue + "%' ")); } /// <summary> /// 分页获取数据列表 /// </summary> /// <param name="columns">需要查询的列明数组</param> /// <param name="page">当前页</param> /// <param name="pageSize">页码</param> /// <param name="sortIndex">排序索引</param> /// <param name="sortDirction">排序升降</param> /// <returns></returns> public DataTable GetListByPage(string[] columns, int page, int pageSize, int sortIndex, string sortDirction, out int total) { StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT "); if (columns == null || columns.Length == 0) strSql.Append(" * "); else { for (int i = 0; i < columns.Length; i++) { strSql.Append(columns[i] + ","); } strSql.Remove(strSql.Length - 1, 1); } strSql.Append(" FROM ( "); strSql.Append(" SELECT ROW_NUMBER() OVER ("); if (sortIndex >= 0 && sortIndex < columns.Length - 1) { strSql.Append(" order by T." + columns[sortIndex]); if (sortDirction != null && (sortDirction == "asc" || sortDirction == "desc")) { strSql.Append(" " + sortDirction); } } strSql.Append(")AS Row, T.* from " + TableName + " T "); if (sqlParaList != null && sqlParaList.Count > 0) { strSql.Append(" where 1=1"); if (!string.IsNullOrEmpty(sqlWhere)) { strSql.Append(sqlWhere.TrimEnd(',')); } if (!string.IsNullOrEmpty(sqlOrWhere)) { strSql.Append(" and ( " + sqlOrWhere.Substring(0, sqlOrWhere.Length - 3) + " )"); } } strSql.Append(" ) TT"); strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", (pageSize * (page - 1) + 1), pageSize * page); //获取列表总行数 total = GetListCount(pageSize); return SqlHelper.ExecuteDataTable(SqlHelper.SHOP_CONN_STR, CommandType.Text, strSql.ToString(), sqlParaList.ToArray()); } /// <summary> /// 获取列表总行数 /// </summary> /// <returns></returns> public int GetListCount(int pageSize) { StringBuilder strSql = new StringBuilder("select count(1)" + " from " + TableName); if (sqlParaList != null && sqlParaList.Count > 0) { strSql.Append(" where 1=1"); if (!string.IsNullOrEmpty(sqlWhere)) { strSql.Append(sqlWhere.TrimEnd(',')); } if (!string.IsNullOrEmpty(sqlOrWhere)) { strSql.Append(" and (" + sqlOrWhere.Substring(0, sqlOrWhere.Length - 3) + ")"); } } object obj = SqlHelper.ExecuteScalar(SqlHelper.SHOP_CONN_STR, CommandType.Text, strSql.ToString(), sqlParaList.ToArray()); int RowsCount = 0; if (obj != null) { return RowsCount = (int)obj; } else { return 0; } //int total = 0; // //if (RowsCount % pageSize == 0) // //{ // // total = RowsCount / pageSize; // //} // //else // //{ // // total = RowsCount / pageSize + 1; // //} // return total; }
拼接json的方法
public static string TableToJson(DataTable table,int total) { if (table == null) return ""; StringBuilder sb = new StringBuilder(); sb.Append("{"); sb.Append("\"total\":" + total + ","); sb.Append("\"rows\":["); foreach (DataRow row in table.Rows) { sb.Append("{"); foreach (DataColumn col in table.Columns) { sb.Append("\"" + col.ColumnName + "\":\"" + row[col.ColumnName] + "\","); } sb.Remove(sb.Length - 1, 1); sb.Append("},"); } sb.Remove(sb.Length - 1, 1); sb.Append("]"); sb.Append("}"); return sb.ToString(); }
