/// <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 记录总数%页长) }