public override DataTable SelTop(PageSetting config) { if (string.IsNullOrEmpty(config.t2))//单表 { config.sql = "SELECT " + config.fields + " FROM " + config.t1; } else { config.sql = "SELECT " + config.fields + " FROM {0} A LEFT JOIN {1} B ON {2} "; config.sql = string.Format(config.sql, config.t1, config.t2, config.on); } if (!string.IsNullOrEmpty(config.where)) { config.sql += " WHERE " + config.where; } if (!string.IsNullOrEmpty(config.order)) { config.sql += config.order; } config.sql += " LIMIT " + config.psize; config.DealWithAlias(); return(ExecuteTable(new SqlModel() { sql = config.sql })); }
//--------------分页与连结 public override DataTable SelPage(PageSetting config) { int offset = config.psize * (config.cpage - 1); if (string.IsNullOrEmpty(config.t2)) { config.sql = "SELECT " + config.fields + " FROM " + config.t1 + " WHERE " + config.where + " LIMIT " + offset + "," + config.psize; config.countsql = "SELECT COUNT(*) FROM " + config.t1 + " WHERE " + config.where; } else { config.sql = "SELECT " + config.fields + " FROM " + config.t1 + " A LEFT JOIN " + config.t2 + " B ON " + config.on + " WHERE " + config.where + " LIMIT " + offset + "," + config.psize; config.countsql = "SELECT COUNT(*) FROM " + config.t1 + " A LEFT JOIN " + config.t2 + " B ON " + config.on + " WHERE " + config.where; } config.DealWithAlias(); SqlModel countMod = new SqlModel(config.countsql, null); SqlModel model = new SqlModel(config.sql, null); if (config.sp != null && config.sp.Length > 0) { countMod.AddSpToList(config.sp); model.AddSpToList(config.sp); } config.itemCount = DataConvert.CLng(ExecuteScalar(countMod)); config.pageCount = GetPageCount(config.itemCount, config.psize); return(ExecuteTable(model)); }
//--------------分页与连结 public override DataTable SelPage(PageSetting config) { if (!string.IsNullOrEmpty(config.t2)) { config.sql = "SELECT TOP " + config.psize + config.fields + " FROM " + config.t1 + " A " + config.join + " " + config.t2 + " B ON " + config.on + " WHERE "; if (config.cursize > 0) { config.sql += config.pk + " Not In(SELECT TOP " + config.cursize + " " + config.pk + " FROM " + config.t1 + " A LEFT JOIN " + config.t2 + " B ON " + config.on + " WHERE " + config.where + config.order + ") AND "; } config.sql += config.where + config.order; config.countsql = "SELECT COUNT(*) FROM " + config.t1 + " A LEFT JOIN " + config.t2 + " B ON " + config.on + " WHERE " + config.where; } else { config.sql = "SELECT TOP " + config.psize + config.fields + " FROM " + config.t1 + " A WHERE "; if (config.cursize > 0) { config.sql += config.pk + " Not In(Select Top " + config.cursize + " " + config.pk + " FROM " + config.t1 + " WHERE " + config.where + config.order + ") And "; } config.sql += config.where + config.order; config.countsql = "SELECT COUNT(*) FROM " + config.t1 + " WHERE " + config.where; } config.DealWithAlias(); SqlModel sp = new SqlModel(); config.itemCount = DataConvert.CLng(ExecuteScala(new SqlModel(config.countsql, config.sp))); config.pageCount = GetPageCount(config.itemCount, config.psize); return(ExecuteTable(new SqlModel(config.sql, config.sp))); }
public override DataTable SelTop(PageSetting config) { SqlToOracle(config); config.sql = "SELECT T.*,ROWNUM FROM ({0}) T WHERE ROWNUM<=" + config.psize; string innersql = ""; if (string.IsNullOrEmpty(config.t2))//单表 { innersql = "SELECT " + config.fields + " FROM " + config.t1 + " A "; } else { innersql = "SELECT " + config.fields + " FROM " + config.t1 + " A LEFT JOIN " + config.t2 + " B ON " + config.on; } if (!string.IsNullOrEmpty(config.where)) { innersql += " WHERE " + config.where; } if (!string.IsNullOrEmpty(config.order)) { innersql += config.order; } config.sql = string.Format(config.sql, innersql); config.DealWithAlias(); return(ExecuteTable(new SqlModel() { sql = config.sql })); }
public DataTable SelPageByRow(PageSetting config) { //select top 10 * from (select row_number() over(order by UserID) as rownumber,* from ZL_User where UserID>1) Awhere rownumber > 40 if (string.IsNullOrEmpty(config.order)) { throw new Exception("行号分页,必须输入排序条件"); } int preSize = config.psize * (config.cpage - 1); if (preSize < 0) { preSize = 0; } string orderField = config.order.Replace("A.", "").Replace("B.", ""); //排序 string alias = string.IsNullOrEmpty(config.T1Alias) ? config.t1 : config.T1Alias; //用于判断标签 //不需要再用Order条件 if (!string.IsNullOrEmpty(config.t2) && !string.IsNullOrEmpty(config.t1))//双表 { config.sql = "SELECT TOP " + config.psize + " * FROM (SELECT ROW_Number() OVER (" + config.order + ") RowNum," + config.fields + " FROM " + config.t1 + " A " + config.join + " " + config.t2 + " B ON " + config.on + " WHERE " + config.where + ") " + alias + " WHERE RowNum>" + preSize; config.countsql = "SELECT COUNT(*) FROM " + config.t1 + " A " + config.join + " " + config.t2 + " B ON " + config.on + " WHERE " + config.where; } else { config.sql = "SELECT TOP " + config.psize + " * FROM (SELECT ROW_Number() OVER(" + config.order + ") RowNum," + config.fields + " FROM " + config.t1 + " WHERE " + config.where + ") " + alias + " WHERE RowNum>" + preSize; config.countsql = "SELECT COUNT(*) From " + config.t1 + " WHERE " + config.where; } config.DealWithAlias(); config.itemCount = DataConvert.CLng(ExecuteScala(new SqlModel(config.countsql, config.sp))); config.pageCount = GetPageCount(config.itemCount, config.psize); if (config.debug) { throw new Exception(config.sql); } return(SqlHelper.ExecuteTable(this.ConnectionString, CommandType.Text, config.sql)); }
//--------------分页与连结 public override DataTable SelPage(PageSetting config) { if (config.pageMethod.Equals("row")) { return(SelPageByRow(config)); } if (!string.IsNullOrEmpty(config.t2)) { config.sql = "SELECT TOP " + config.psize + config.fields + " FROM " + config.t1 + " A " + config.join + " " + config.t2 + " B ON " + config.on + " WHERE " + config.pk + " Not In(SELECT TOP " + config.cursize + " " + config.pk + " FROM " + config.t1 + " A LEFT JOIN " + config.t2 + " B ON " + config.on + " WHERE " + config.where + config.order + ") AND " + config.where + config.order; config.countsql = "SELECT COUNT(*) FROM " + config.t1 + " A LEFT JOIN " + config.t2 + " B ON " + config.on + " WHERE " + config.where; } else { config.sql = "SELECT TOP " + config.psize + config.fields + " FROM " + config.t1 + " A WHERE " + config.pk + " Not In(Select Top " + config.cursize + " " + config.pk + " FROM " + config.t1 + " WHERE " + config.where + config.order + ") And " + config.where + config.order; config.countsql = "SELECT COUNT(*) FROM " + config.t1 + " WHERE " + config.where; } config.DealWithAlias(); config.itemCount = DataConvert.CLng(SqlHelper.ExecuteScalar(config.countsql, config.sp)); config.pageCount = GetPageCount(config.itemCount, config.psize); if (config.debug) { throw new Exception(config.sql); } return(SqlHelper.ExecuteTable(this.ConnectionString, CommandType.Text, config.sql, config.sp)); }
//-------------- public override DataTable SelPage(PageSetting config) { SqlToOracle(config); config.sql = "SELECT T.*,ROWNUM FROM ({0}) T WHERE ROWNUM>" + (config.cpage - 1) * config.psize + " AND ROWNUM<=" + config.cpage * config.psize; string innersql = ""; if (!string.IsNullOrEmpty(config.t2))//双表查询 { innersql = "SELECT " + config.fields + " FROM " + config.t1 + " A LEFT JOIN " + config.t2 + " B ON " + config.on; config.countsql = "SELECT COUNT(*) FROM " + config.t1 + " A LEFT JOIN " + config.t2 + " B ON " + config.on; } else//单表查询 { innersql = "SELECT " + config.fields + " FROM " + config.t1 + " A "; config.countsql = "SELECT COUNT(*) FROM " + config.t1; } if (!string.IsNullOrEmpty(config.where)) { innersql += " WHERE " + config.where; config.countsql += " WHERE " + config.where; } if (!string.IsNullOrEmpty(config.order)) { innersql += config.order; } config.sql = string.Format(config.sql, innersql); config.DealWithAlias(); //------------------ SqlModel countMod = new SqlModel() { sql = config.countsql }; countMod.AddSpToList(config.sp); SqlModel model = new SqlModel() { sql = config.sql }; model.AddSpToList(config.sp); config.itemCount = DataConvert.CLng(ExecuteScala(countMod)); config.pageCount = GetPageCount(config.itemCount, config.psize); return(ExecuteTable(model)); }
public override DataTable JoinQuery(PageSetting config) { config.sql = "SELECT {0} FROM {1} A LEFT JOIN {2} B ON {3} "; config.sql = string.Format(config.sql, config.fields, config.t1, config.t2, config.on); if (!string.IsNullOrEmpty(config.where)) { config.sql += " WHERE " + config.where; } if (!string.IsNullOrEmpty(config.order)) { config.sql += config.order; } config.DealWithAlias(); SqlModel model = new SqlModel() { sql = config.sql, }; model.AddSpToList(config.sp); return(ExecuteTable(model)); }