/// <summary>
/// 生成删除的SQL声明
/// </summary>
/// <param name="dbTranslator"></param>
/// <param name="entityInfo"></param>
/// <param name="expr"></param>
/// <returns></returns>
public static SqlStatement BuildDeleteSqlStatement(IDbTranslator dbTranslator, IEntityMapper entityInfo, Expression whereExpr)
{
IExpressionParser parser = new ExpressionParser(entityInfo);
//设置查询条件
var dpc = new DataParameterCollection();
string strWhere = "";
if (whereExpr != null)
{
strWhere = parser.ToSQL(whereExpr, dbTranslator, dpc);
}
if (strWhere.Length > 0)
{
strWhere = "WHERE " + strWhere;
}
string sqlString = string.Format("DELETE FROM {0} {1}",
dbTranslator.Quote(entityInfo.TableName),
strWhere);
return(new SqlStatement(System.Data.CommandType.Text, sqlString, dpc));
}
/// <summary>
/// 分页查询（MySQL）
/// </summary>
/// <param name="dbTranslator"></param>
/// <param name="entityInfo"></param>
/// <param name="whereExpr"></param>
/// <param name="range"></param>
/// <param name="order"></param>
/// <returns></returns>
private static SqlStatement rangeSelectMySQL(IDbTranslator dbTranslator, IEntityMapper entityInfo, Expression whereExpr, Range range, Dictionary <string, FieldSearchOrder> order = null)
{
IExpressionParser parser = new ExpressionParser(entityInfo);
//设置查询条件
var dpc = new DataParameterCollection();
string strWhere = "";
if (whereExpr != null)
{
strWhere = parser.ToSQL(whereExpr, dbTranslator, dpc);
}
if (strWhere.Length > 0)
{
strWhere = "WHERE " + strWhere;
}
//查询字段
string strFieldInfo = getSelectText(dbTranslator, entityInfo);
//排序规则
string strOrder = getOrderInfo(dbTranslator, entityInfo, order, "", true);
//SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
//LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。
//参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
//初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。
//mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
string temp = "SELECT {0} FROM {1} {2} {3} LIMIT {4},{5}";
string sqlString = string.Format(temp,
strFieldInfo,
dbTranslator.Quote(entityInfo.TableName),
strWhere,
strOrder,
range.StartIndex - 1,
(range.EndIndex - range.StartIndex + 1));
return(new SqlStatement(System.Data.CommandType.Text, sqlString, dpc));
}
/// <summary>
/// 分页查询（Sqlserver2005 以上版本）
/// </summary>
/// <param name="dbTranslator"></param>
/// <param name="entityInfo"></param>
/// <param name="whereExpr"></param>
/// <param name="range"></param>
/// <param name="order"></param>
/// <returns></returns>
private static SqlStatement rangeSelectSql2005(IDbTranslator dbTranslator, IEntityMapper entityInfo, Expression whereExpr, Range range, Dictionary <string, FieldSearchOrder> order = null)
{
IExpressionParser parser = new ExpressionParser(entityInfo);
//设置查询条件
var dpc = new DataParameterCollection();
string strWhere = "";
if (whereExpr != null)
{
strWhere = parser.ToSQL(whereExpr, dbTranslator, dpc);
}
if (strWhere.Length > 0)
{
strWhere = "WHERE " + strWhere;
}
//查询字段
string strFieldInfo = getSelectText(dbTranslator, entityInfo);
//排序规则
string strOrder = getOrderInfo(dbTranslator, entityInfo, order, "", true);
string temp = "SELECT * FROM (SELECT * FROM (SELECT {0},ROW_NUMBER() OVER({3}) AS [row_number] FROM {1} {2}) p WHERE p.[row_number] >= {4}) q WHERE q.[row_number] <= {5}";
string sqlString = string.Format(temp,
strFieldInfo,
dbTranslator.Quote(entityInfo.TableName),
strWhere,
strOrder, range.StartIndex, range.EndIndex);
return(new SqlStatement(System.Data.CommandType.Text, sqlString, dpc));
//Linq提供了Skip和Take的API可以用于分页，由于使用的是Entity Framework，在好奇的驱使下用EFProfiler查看生成的SQL，才知道这样以下分页更好。
//主要就是使用了row_numer()over()这样的分析函数，可以直接找到那第5000行开始的地方，然后在取出30行就行了。
// select *
// from (select *
// from (select t.*, row_number() OVER(ORDER BY null) AS "row_number"
// from yz_bingrenyz t) p
// where p."row_number" > 5000) q
// where rownum <= 300
}
/// <summary>
/// SQL语句
/// </summary>
/// <param name="sqlCommandText">CommandType</param>
/// <param name="sqlCommandText">执行文本（SQL语句或者存储过程名称）(默认是SQL语句)</param>
/// <param name="commandParameters">参数集合</param>
public SqlStatement(CommandType sqlCommandType, string sqlCommandText, DataParameterCollection commandParameters)
: this(sqlCommandType, sqlCommandText, commandParameters, DatabaseCommon.DbCommandTimeOut, DatabaseCommon.DbSqlNeedLog)
{
}
/// <summary>
/// 分页查询（Sqlserver2000）
/// </summary>
/// <param name="dbTranslator"></param>
/// <param name="entityInfo"></param>
/// <param name="whereExpr"></param>
/// <param name="range"></param>
/// <param name="order"></param>
/// <returns></returns>
private static SqlStatement rangeSelectSql2000(IDbTranslator dbTranslator, IEntityMapper entityInfo, Expression whereExpr, Range range, Dictionary <string, FieldSearchOrder> order = null)
{
//采用改良的方法三
//SELECT * FROM (
// SELECT TOP 页长 * FROM (
// SELECT TOP 页长*页数 * FROM {表名}
// ) ORDER BY {排序字段} ASC
//) ORDER BY {排序字段} DESC
IExpressionParser parser = new ExpressionParser(entityInfo);
//设置查询条件
var dpc = new DataParameterCollection();
string strWhere = "";
if (whereExpr != null)
{
strWhere = parser.ToSQL(whereExpr, dbTranslator, dpc);
}
if (strWhere.Length > 0)
{
strWhere = "WHERE " + strWhere;
}
//查询字段
string strFieldInfo = getSelectText(dbTranslator, entityInfo);
//排序规则
string strOrder = getOrderInfo(dbTranslator, entityInfo, order, "", true);
string strOrder2 = getOrderInfo(dbTranslator, entityInfo, order, "", true, false);
string temp = "SELECT {8} FROM (SELECT TOP {4} {9} FROM (SELECT TOP {5} {0} FROM {1} {2} {3}) A {6}) B {7}";
string sqlString = string.Format(temp,
strFieldInfo,
dbTranslator.Quote(entityInfo.TableName),
strWhere,
strOrder, range.Rows, range.EndIndex, strOrder2, strOrder, strFieldInfo, strFieldInfo);
return(new SqlStatement(System.Data.CommandType.Text, sqlString, dpc));
//[SQLServer]SQL Server 2000的分页方法(SQL篇)
//不像SQL Server 2005提供ROW_NUMBER()和Oracle的ROWNUM伪列，SQL Server 2000本身无提供方便的分页方法，但可用select top的混合SQL语句实现。
//方法A：结合not in和select top
//SELECT TOP 页长 * FROM {表名}
//WHERE {字段} NOT IN (
// SELECT TOP 页长*(页数-1) {字段} FROM {表名} ORDER BY {排序字段}
//)
//ORDER BY {排序字段}
//点评：语句简单，但not in的字段不能有重复数据
//性能：页数越靠后越慢
//限制：只适合使用主键字段not in
//--------------------------------------------------
//方法B：结合>和select top
//SELECT TOP 页长 * FROM {表名}
//WHERE {字段} > (
// SELECT MAX({字段}) FROM (
// SELECT TOP 页长*(页数-1) {字段} FROM {表名} ORDER BY {排序字段}
// )
//)
//ORDER BY {排序字段}
//点评：跟方法A类似，但语句比方法A多一层查询。
//性能：页数越靠后越慢，但由于使用数字型字段，比方法A快
//限制：只适合主键字段是数字型并且最好是自增的
//--------------------------------------------------
//方法C：结合双select top和相反排序
//SELECT * FROM (
// SELECT TOP 页长 * FROM (
// SELECT TOP 页长*页数 * FROM {表名}
// ) ORDER BY {排序字段} ASC
//) ORDER BY {排序字段} DESC
//点评：与上面两种方法不一样，此方法对字段类型无任何要求，且最里层语句结构可随意(需添加top 页长*页数)，此方法依赖互斥的排序
//性能：页数越靠后越慢，但可改进为，当查询数据位置位于数据总数后半部分时，前一个排序改为倒序，后一个排序改为顺序
//限制：必须排序，且需要特别处理最后一页情况(最后一页时最里层是top 记录总数%页长)
}
