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));
}
