public static DataTable QueryDT(string SQLString) { using (IDbConnection connection = new OracleConnection(connStr)) { DataTable dt = null; DataSet ds = new DataSet(); DbDataAdapter command = null; try { connection.Open(); command = defaultPrv.getDataAdapter(SQLString, connection); PrintSqlTool.printSqlToText(SQLString, dsr); command.SelectCommand.CommandTimeout = 1200; command.Fill(ds, "temp"); dt = ds.Tables["temp"]; } catch (DbException ex) { throw new Exception(ex.Message); } finally { command.Dispose(); connection.Close(); } return(dt); } }
public static DataTable Query(string sql, int start, int limit, ref int total) { try { string Qurey_sql = string.Format(@" Select * from ( Select t.*,rownum rn From ( Select Count(1) over() emarine_total_cnt,ECNT.* From ({0}) ECNT )t Where rownum<{2}) Where rn > {1}", sql, start, start + limit + 1); using (OracleConnection conn = new OracleConnection(connStr)) { IDbCommand cmd = conn.CreateCommand(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DbDataAdapter sda; conn.Open(); //执行分页查询语句 prepare_command(cmd, conn, null, CommandType.Text, Qurey_sql, null); PrintSqlTool.printSqlToText(cmd.CommandText, dsr); sda = defaultPrv.getDataAdapter(); sda.SelectCommand = (DbCommand)cmd; sda.Fill(ds, "temp"); dt = ds.Tables["temp"]; if (dt.Rows.Count > 0) { //获取总记录数 if (dt.Columns.Contains("EMARINE_TOTAL_CNT")) { total = Convert.ToInt32(dt.Rows[0]["EMARINE_TOTAL_CNT"]); } else { total = dt.Rows.Count; } } cmd.Parameters.Clear(); cmd.Dispose(); sda.Dispose(); return(dt); } } catch (Exception e) { throw e; } }