ORM框架-EntitysCodeGenerate自定义分页查询及快捷执行SQL(CreateSQL)示例

1、自定义分页查询(Skip/Take)

如下代码所示：

DbCore dbCore;   //Oracle 分页 dbCore = new DbCore(DatabaseType.Oracle, myOracle.BaseEntity.GetConnectionString()); DataSet dstOracle = dbCore.Select().Add(myOracle.DEPTColumn.DEPTNO)                     .Add(myOracle.DEPTColumn.DNAME).Add(myOracle.DEPTColumn.LOC)                     .Add(myOracle.EMPColumn.EMPNO).SelectColumn(myOracle.EMPColumn.ENAME)                 .From(myOracle.DEPTColumn.TableName).FromTable(myOracle.EMPColumn.TableName)                 .Where().ConditionColumnAndEqual(myOracle.DEPTColumn.DEPTNO, myOracle.EMPColumn.DEPTNO)                 .ConditionAndEqual(myOracle.DEPTColumn.DEPTNO,20)                 .OrderBy().Asc(myOracle.EMPColumn.DEPTNO).Skip(2).Take(3).ExecuteDataSet(); int countOracle = dstOracle.Tables[0].Rows.Count;   //未设置Oracle排序字段,默认数字1,但可能不是想要的顺序 dstOracle = dbCore.Select().Add(myOracle.DEPT.s_DEPTNO)                     .Add(myOracle.DEPT.s_DNAME).Add(myOracle.DEPT.s_LOC)                     .Add(myOracle.EMP.s_EMPNO).SelectColumn(myOracle.EMP.s_ENAME)                 .From(myOracle.DEPT.s_TableName).FromTable(myOracle.EMP.s_TableName)                 .Where().ConditionColumnAndEqual(myOracle.DEPT.s_DEPTNO, myOracle.EMP.s_DEPTNO)                 .ConditionAndGreatEqual(myOracle.DEPT.s_DEPTNO, 20)                 .OrderBy().Skip(2).Take(3).ExecuteDataSet(); countOracle = dstOracle.Tables[0].Rows.Count;   //SqlServer 分页,必须设置排序字段 dbCore = new DbCore(DatabaseType.SqlServer, mySqlServer2008.BaseEntity.GetConnectionString()); DataSet dstSqlServer = dbCore.Select().Add(mySqlServer2008.DEPT.s_DEPTNO)                     .Add(mySqlServer2008.DEPT.s_DNAME).Add(mySqlServer2008.DEPT.s_LOC)                     .Add(mySqlServer2008.EMP.s_EMPNO).SelectColumn(mySqlServer2008.EMP.s_ENAME)                 .From(mySqlServer2008.DEPT.s_TableName).FromTable(mySqlServer2008.EMP.s_TableName)                 .Where().ConditionColumnAndEqual(mySqlServer2008.DEPT.s_DEPTNO, mySqlServer2008.EMP.s_DEPTNO)                 .ConditionAndGreatEqual(myOracle.DEPT.s_DEPTNO, 20)                 .OrderBy().Asc(mySqlServer2008.DEPT.s_DEPTNO).Skip(1).Take(2).ExecuteDataSet(); int countSqlServer = dstSqlServer.Tables[0].Rows.Count;   //Sqlite 分页 dbCore = new DbCore(DatabaseType.SQLite, mySqlite.BaseEntity.GetConnectionString()); DataSet dstSqlite = dbCore.Select().Add(Entity.Common.SQLite.t_demo.s_z)                     .Add(Entity.Common.SQLite.t_demo.s_a).Add("b")                 .From(Entity.Common.SQLite.t_demo.s_TableName)                 .OrderBy().Asc("z").Skip(1).Take(2).ExecuteDataSet(); int countSqlite = dstSqlite.Tables[0].Rows.Count;   //MySql 分页 dbCore = new DbCore(DatabaseType.MySql, myMySql.BaseEntity.GetConnectionString()); DataSet dstMySql = dbCore.Select().Add(myMySql.dept.s_DEPTNO).Add("DNAME").Add("LOC")                 .From(myMySql.dept.s_TableName)                 .OrderBy().Desc(myMySql.dept.s_DEPTNO).Skip(1).Take(2).ExecuteDataSet(); int countMySql = dstMySql.Tables[0].Rows.Count;   //DB2 分页 dbCore = new DbCore(DatabaseType.DB2, myDB2.BaseEntity.GetConnectionString()); DataSet dstDB2 = dbCore.Select().Add(myDB2.TESTENTITY.s_A).Add(myDB2.TESTENTITY.s_B)                 .Add(myDB2.TESTENTITY.s_C).Add(myDB2.TESTENTITY.s_D)                 .From(myDB2.TESTENTITY.s_TableName)                 .OrderBy().Asc(myDB2.TESTENTITY.s_A).Skip(1).Take(2).ExecuteDataSet(); int countDB2 = dstDB2.Tables[0].Rows.Count;   //PostgreSQL分页 dbCore = new DbCore(DatabaseType.PostgreSQL, myPostgreSQL.BaseEntity.GetConnectionString()); DataSet dstPostgreSQL = dbCore.Select().Add(myPostgreSQL.t_tabledemoColumn.c_a)                 .Add(myPostgreSQL.t_tabledemoColumn.c_b).Add(myPostgreSQL.t_tabledemoColumn.c_c)                 .From(myPostgreSQL.t_tabledemoColumn.TableName)//.FromCustom("tt")                 .OrderBy().Asc(myPostgreSQL.t_tabledemoColumn.c_a).Skip(1).Take(2).ExecuteDataSet(); int countPostgreSQL = dstPostgreSQL.Tables[0].Rows.Count;     //DM(达梦)分页 dbCore = new DbCore(DatabaseType.Dm, myDM.BaseEntity.GetConnectionString()); DataSet dstDM = dbCore.Select().Add(myDM.T_DEMO.s_C_A).Add(myDM.T_DEMO.s_C_B)                 .Add(myDM.T_DEMO.s_C_C).Add(myDM.T_DEMO1.s_C_NAME)                 .From(myDM.T_DEMO.s_TableName).FromTable(myDM.T_DEMO1.s_TableName)                 .Where().ConditionColumnAndEqual(myDM.T_DEMO.s_C_ID, myDM.T_DEMO1.s_C_ID)                 .OrderBy().Asc(myDM.T_DEMO.s_C_A).Skip(1).Take(2).ExecuteDataSet(); int countDM = dstDM.Tables[0].Rows.Count;

Skip为跳过元素的数量，Take为所取连续元素的数量。

目前这种分页方式支持的数据库：Oracle/SqlServer/MySql/Sqlite/PostgreSQL/
DM/DB2/Sybase IQ/Informix/Firebird/

 

通用分页也可以使用System.Database.Extend.TableHelp类。

 

2.、DbCore+SQL快捷用法

DbCore dbCore = new DbCore(myOracle.BaseEntity.GetConnectionString()); DataSet dst = dbCore.CreateSQL("select * from emp where deptno = :deptno")     .AddInParameter("deptno", DbType.Int32, 20).ExecuteDataSet(); int intCount = dst.Tables[0].Rows.Count;

 

 

 

下载链接：

http://www.cnblogs.com/lxchutao/archive/2011/06/01/2065977.html