/// <summary> /// 真分页情况下,返回分页数据 /// </summary> /// <param name="template"></param> /// <param name="pPageNo"></param> /// <param name="pPageSize"></param> /// <param name="pWhere"></param> /// <param name="paras"></param> /// <param name="pOrderby"></param> /// <returns></returns> public DataTable GetDataIsTruePaged(TemplateModel template, int pPageNo, int pPageSize, string pWhere, string pOrderby, bool paged = true) { DbHelperSQL dbhelper = new DbHelperSQL(template.SQLBuilder.ConnectionStrings); DataTable dt; string sql = string.Empty; if (template.IsTruePaged == true) //真分页,表示直接从数据库取数据 { StringBuilder sb = new StringBuilder(); //分页语句 例:select * from (select row_number() over(order by @@servername) as bs_row_num,* from BS_TEMPLATE_MAIN where TemplateName like N'%%')t where bs_row_num>4 and bs_row_num <8 sb.Append(@"select * from (select row_number() over(order by "); if (!string.IsNullOrWhiteSpace(pOrderby)) { sb.Append(pOrderby); } else if (!string.IsNullOrWhiteSpace(template.SQLBuilder.Orderby)) { sb.Append(template.SQLBuilder.Orderby); } else { sb.Append("@@servername"); } sb.Append(") as bs_row_num, "); sb.Append(template.SQLBuilder.SelectSQL); sb.Append(" from "); sb.Append(template.SQLBuilder.TableName); sb.Append(template.SQLBuilder.DefauleQuery.Replace("[本人]", BaseComponent.GetCurrentUserLoginName())); if (!string.IsNullOrEmpty(pWhere)) { sb.Append(" and "); sb.Append(pWhere); } sb.Append(")t"); if (paged) { string startPage = ((pPageNo - 1) * pPageSize).ToString(); string endPage = (pPageNo * pPageSize + 1).ToString(); sb.Append(" where bs_row_num>"); sb.Append(startPage); sb.Append(" and bs_row_num<"); sb.Append(endPage); } BaseComponent.Info("GetDataIsTruePaged SQL:" + sb.ToString()); dt = dbhelper.ExecuteTable(CommandType.Text, sb.ToString()); return(dt); } return(null); }
public int GetTotalCount(TemplateModel template, string pWhere) { DbHelperSQL dbhelper = new DbHelperSQL(template.SQLBuilder.ConnectionStrings); StringBuilder sb = new StringBuilder(); sb.Append("select count(*) from "); sb.Append(template.SQLBuilder.TableName); sb.Append(template.SQLBuilder.DefauleQuery.Replace("[本人]", BaseComponent.GetCurrentUserLoginName())); if (!string.IsNullOrWhiteSpace(pWhere)) { sb.Append(" and "); sb.Append(pWhere); } BaseComponent.Info("GetTotalCount SQL:" + sb.ToString()); return((int)dbhelper.ExecuteScalar(CommandType.Text, sb.ToString())); }
/// <summary> /// 返回总页数 /// </summary> /// <param name="templateID"></param> /// <param name="pageSize"></param> /// <param name="where"></param> /// <returns></returns> public string PageCount(TemplateModel template, string pPageSize, string pWhere) { DbHelperSQL dbhelper = new DbHelperSQL(template.SQLBuilder.ConnectionStrings); string pageSize = pPageSize; string where = template.SQLBuilder.DefauleQuery.Replace("[本人]", BaseComponent.GetCurrentUserLoginName()) + pWhere; string result = "0"; string sql = "select count(*) from " + template.SQLBuilder.TableName + " where " + where; object rows = dbhelper.ExecuteScalar(CommandType.Text, sql); BaseComponent.Info("SQL:" + sql); if (rows != null && rows.ToString() != "") { result = Convert.ToString(Convert.ToInt32(rows)); } return(result); }
/// <summary> /// 假分页情况下,返回所有数据 /// </summary> /// <param name="template"></param> /// <param name="paras"></param> /// <returns></returns> public DataTable GetDataIsNotTruePaged(TemplateModel template, string pWhere, SqlParameter[] paras, string orderBy = "") { DbHelperSQL dbhelper = new DbHelperSQL(template.SQLBuilder.ConnectionStrings); DataTable dt; if (template.SQLBuilder.IsProcudure == true) { //SqlParameter[] newParas = new SqlParameter[template.SQLBuilder.Parameters.Length + paras.Length]; //template.SQLBuilder.Parameters.CopyTo(newParas, 0); //paras.CopyTo(newParas, template.SQLBuilder.Parameters.Length); List <SqlParameter> listParas = new List <SqlParameter>(); if (paras != null && paras.Count() > 0) { listParas.AddRange(paras.ToList()); } if (template.SQLBuilder.Parameters != null && template.SQLBuilder.Parameters.Count() > 0) { foreach (var dic in template.SQLBuilder.Parameters) { listParas.Add(new SqlParameter(dic.Key, dic.Value.Replace("[本人]", BaseComponent.GetCurrentUserLoginName()))); } } DataTable returnDt = dbhelper.ExecuteTable(CommandType.StoredProcedure, template.SQLBuilder.TableName, listParas.ToArray()); string filter = string.Empty; if (template.SQLBuilder.DefauleQuery != "where 1=1") { filter += template.SQLBuilder.DefauleQuery.Substring(7).Replace("[本人]", BaseComponent.GetCurrentUserLoginName()); } if (!string.IsNullOrWhiteSpace(pWhere)) { filter += " and " + pWhere; } BaseComponent.Info("存储过程本人查询条件:" + filter); var rows = returnDt.Select(filter); dt = returnDt.Clone(); for (int i = 0; i < rows.Length; i++) { dt.ImportRow(rows[i]); } //列排序,计算列 int columnIndex = 0; foreach (string proColumnName in template.SQLBuilder.ColumnNames.Split(',')) { if (dt.Columns.Contains(proColumnName)) { dt.Columns[proColumnName].SetOrdinal(columnIndex); } else { dt.Columns.Add(proColumnName); dt.Columns[proColumnName].Expression = template.SQLBuilder.ProcCalColumns[proColumnName]; dt.Columns[proColumnName].SetOrdinal(columnIndex); } columnIndex++; } //while (dt.Columns.Count > columnIndex) //不用的列全干掉,性能上说不定有奇效:) //{ // dt.Columns.RemoveAt(columnIndex); //} //排序 //string sortview = string.Empty; string sortview = template.SQLBuilder.Orderby; if (!string.IsNullOrEmpty(orderBy)) { sortview = orderBy; //string sortcolname = orderBy.Split(' ')[0].ToLower().Trim(); //if (dorderby.Contains(sortcolname + " ")) //{ // string[] dorderlist = dorderby.Split(','); // foreach (string ds in dorderlist) // { // if (ds.Contains(sortcolname + " ")) // { // sortview += orderBy + ","; // } // else // { // sortview += ds + ","; // } // } //} //else //{ // sortview = dorderby + "," + orderBy; //} } //else //{ // sortview = dorderby; //} if (!string.IsNullOrEmpty(sortview)) { dt.DefaultView.Sort = sortview.TrimEnd(','); //TODO:行汇总排序 } dt = dt.DefaultView.ToTable(); } else { StringBuilder sb = new StringBuilder(); sb.Append("select "); sb.Append(template.SQLBuilder.SelectSQL); sb.Append(" from "); sb.Append(template.SQLBuilder.TableName); sb.Append(template.SQLBuilder.DefauleQuery.Replace("[本人]", BaseComponent.GetCurrentUserLoginName())); if (!string.IsNullOrWhiteSpace(pWhere)) { sb.Append(" and "); sb.Append(pWhere); } if (!string.IsNullOrWhiteSpace(template.SQLBuilder.Orderby))//TODO:行汇总排序 { sb.Append(" order by "); //排序 //string sortview = string.Empty; string sortview = template.SQLBuilder.Orderby; if (!string.IsNullOrEmpty(orderBy)) { sortview = orderBy; //string sortcolname = orderBy.Split(' ')[0].ToLower().Trim(); //if (dorderby.Contains(sortcolname + " ")) //{ // string[] dorderlist = dorderby.Split(','); // foreach (string ds in dorderlist) // { // if (ds.Contains(sortcolname + " ")) // { // sortview += orderBy + ","; // } // else // { // sortview += ds + ","; // } // } //} //else //{ // sortview = dorderby + "," + orderBy; //} } //else //{ // sortview = dorderby; //} sb.Append(sortview.TrimEnd(',')); } dt = dbhelper.ExecuteTable(CommandType.Text, sb.ToString()); BaseComponent.Info("SQL:" + sb.ToString()); } DataTable dtResult = dt.Copy(); dtResult.Columns.Add("bs_row_num"); dtResult.Columns.Add("bs_is_groupby"); //分组统计 if (template.GroupBy != null && template.GroupBy.Count > 0) { //表中汇总 var middleGroup = template.GroupBy.Where(p => p.IsAtLast == false); foreach (GroupBy gb in middleGroup) //每次循环后 得到分组后的数据 { DataTable distinct = dtResult.DefaultView.ToTable(true, gb.Columns.Split(',')); DataTable tempResult = dtResult.Clone(); foreach (DataRow dr in distinct.Rows) { string select = string.Empty; foreach (string colName in gb.Columns.Split(',')) { select += colName + "='" + dr[colName] + "' and "; } if (!string.IsNullOrWhiteSpace(select)) { select = select.Substring(0, select.Length - 5); } DataRow[] rows = dtResult.Select(select); //取出一组数据 //temp用来存储筛选出来的数据 DataTable temp = dtResult.Clone(); foreach (DataRow row in rows) { temp.Rows.Add(row.ItemArray); tempResult.Rows.Add(row.ItemArray); } DataRow groupDr = tempResult.NewRow(); foreach (string colName in gb.Columns.Split(',')) { groupDr[colName] = dr[colName]; } foreach (var groupName in gb.GroupByColumns) { string[] gn = groupName.Value.Split(','); string res = Convert.ToString(temp.Compute(gn[0], "bs_is_groupby is null")); if (gn[1] == "-1")//自动小数 { groupDr[groupName.Key] = temp.Compute(gn[0], "bs_is_groupby is null"); } else { double comresult = 0.00; if (!string.IsNullOrEmpty(res)) { double.TryParse(res, out comresult); } if (comresult != 0.0) { groupDr[groupName.Key] = comresult.ToString("f" + gn[1]); } else { groupDr[groupName.Key] = DBNull.Value; } } } groupDr["bs_is_groupby"] = "1"; groupDr["bs_row_num"] = "小计(合计)"; tempResult.Rows.Add(groupDr); } dtResult = tempResult.Copy(); } //表尾汇总 var lastGroup = template.GroupBy.Where(p => p.IsAtLast == true); foreach (GroupBy gb in lastGroup) { //DataTable newdt = new DataTable(); //newdt = dtResult.Clone(); //DataRow[] realRows = dtResult.Select("bs_is_groupby is null"); //foreach (DataRow row in realRows) //{ // newdt.Rows.Add(row.ItemArray); //} if (string.IsNullOrEmpty(gb.Columns)) { DataRow groupDr = dtResult.NewRow(); foreach (var groupName in gb.GroupByColumns) { string[] gn = groupName.Value.Split(','); string res = Convert.ToString(dtResult.Compute(gn[0], "bs_is_groupby is null")); if (gn[1] == "-1")//自动小数 { groupDr[groupName.Key] = dtResult.Compute(gn[0], "bs_is_groupby is null"); } else { double comresult = 0.00; if (!string.IsNullOrEmpty(res)) { double.TryParse(res, out comresult); } if (comresult != 0.0) { groupDr[groupName.Key] = comresult.ToString("f" + gn[1]); } else { groupDr[groupName.Key] = DBNull.Value; } } } groupDr["bs_is_groupby"] = "1"; groupDr["bs_row_num"] = "总计"; dtResult.Rows.Add(groupDr); } else { DataTable distinct = dt.DefaultView.ToTable(true, gb.Columns.Split(',')); foreach (DataRow dr in distinct.Rows) { string select = string.Empty; foreach (string colName in gb.Columns.Split(',')) { select += colName + "='" + dr[colName] + "' and "; } if (!string.IsNullOrWhiteSpace(select)) { select = select.Substring(0, select.Length - 5); } DataRow[] rows = dtResult.Select(select); //取出一组数据 //temp用来存储筛选出来的数据 DataTable temp = dtResult.Clone(); foreach (DataRow row in rows) { temp.Rows.Add(row.ItemArray); } DataRow groupDr = dtResult.NewRow(); foreach (string colName in gb.Columns.Split(',')) { groupDr[colName] = dr[colName]; } foreach (var groupName in gb.GroupByColumns) { string[] gn = groupName.Value.Split(','); string res = Convert.ToString(temp.Compute(gn[0], "bs_is_groupby is null")); if (gn[1] == "-1")//自动小数 { groupDr[groupName.Key] = temp.Compute(gn[0], "bs_is_groupby is null"); } else { double comresult = 0.00; if (!string.IsNullOrEmpty(res)) { double.TryParse(res, out comresult); } if (comresult != 0.0) { groupDr[groupName.Key] = comresult.ToString("f" + gn[1]); } else { groupDr[groupName.Key] = DBNull.Value; } } } groupDr["bs_is_groupby"] = "1"; groupDr["bs_row_num"] = "小计(合计)"; dtResult.Rows.Add(groupDr); } } } } int index = 1; foreach (DataRow dr in dtResult.Rows) { if (!string.IsNullOrEmpty(Convert.ToString(dr["bs_is_groupby"]))) { continue; } dr["bs_row_num"] = index.ToString(); index++; } return(dtResult); }