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