public override DataTable SelTop(PageSetting config) { config.sql = "SELECT TOP " + config.psize + " * FROM " + config.t1; if (!string.IsNullOrEmpty(config.where)) { config.sql += " WHERE " + config.where; } if (!string.IsNullOrEmpty(config.order)) { config.sql += config.order; } return(ExecuteTable(new SqlModel() { sql = config.sql })); }
/// <summary> /// 联接查询,统一使用左连接 /// </summary> public override DataTable JoinQuery(PageSetting config) { string sql = "SELECT {0} FROM {1} A LEFT JOIN {2} B ON {3} "; if (!string.IsNullOrEmpty(config.where)) { sql += " WHERE " + config.where; } if (!string.IsNullOrEmpty(config.order)) { if (!config.order.ToUpper().Contains("ORDER BY ")) { config.order = " ORDER BY " + config.order; } sql += config.order; } sql = string.Format(sql, config.fields, config.t1, config.t2, config.on); return(ExecuteTable(new SqlModel(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 SelPage(PageSetting config) { //excel需要加判断为0时不生成子查询语句 config.sql = "SELECT TOP " + config.psize + " * FROM " + config.t1 + " 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 + ") "; } else { config.sql += " 1=1 "; } config.sql += " AND " + config.where + config.order; DataTable dt = ExecuteTable(new SqlModel(config.sql, null)); config.itemCount = dt.Rows.Count; config.pageCount = GetPageCount(config.itemCount, config.psize); return(dt); }
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)); }
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(ExecuteScalar(new SqlModel(config.countsql, config.sp))); config.pageCount = GetPageCount(config.itemCount, config.psize); if (config.debug) { throw new Exception(config.sql); } return(ExecuteTable(new SqlModel() { sql = config.sql, spList = config.spList })); }
/// <summary> /// 可不需要指定Order与主键,取前几条数据,主用于标签 /// </summary> public abstract DataTable SelTop(PageSetting config);
public abstract DataTable JoinQuery(PageSetting config);
//------------私有 /// <summary> /// 将SQL语法转为Oracle,用于set,where等地方 /// </summary> private void SqlToOracle(PageSetting setting) { setting.where = SqlToOracle(setting.where); }
public override DataTable JoinQuery(PageSetting config) { return(SqlHelper.JoinQuery(config.fields, config.t1, config.t2, config.on, config.where, config.order, config.sp)); }
public override DataTable JoinQuery(PageSetting config) { throw new NotImplementedException(); }
public override DataTable JoinQuery(PageSetting config) { config.cpage = 1; config.psize = int.MaxValue; return(SelPage(config)); }