using System;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Collections.Generic;
using System.Text;
namespace Best.Biz.Repository.Base
{
/// <summary>
/// 数据库访问类
/// </summary>
public static class OracleDbHelper
{
/// <summary>
/// 默认数据库连接字符串,在程序配置文件中的ConnectionStrings段定义,名称是OracleDbConnectionString
/// </summary>
private static readonly string dbConnectionString = ConfigurationManager.ConnectionStrings["OracleDbConnectionString"].ToString();
/// <summary>
/// 用指定的连接字符串执行SQL,返回DataSet
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="connectionString">连接字符串</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataSet(string sql, string connectionString)
{
using (OracleConnection oraConn = new OracleConnection(connectionString))
{
using (OracleDataAdapter oda = new OracleDataAdapter(sql, oraConn))
{
DataSet ds = new DataSet();
oda.Fill(ds);
return ds;
}
}
}
/// <summary>
/// 用默认连接字符串执行SQL,返回DataSet
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataSet(string sql)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleDataAdapter oda = new OracleDataAdapter(sql, oraConn))
{
DataSet ds = new DataSet();
oda.Fill(ds);
return ds;
}
}
}
/// <summary>
/// 用默认连接字符串执行查询语句,返回DataSet
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataSet(string sql, params OracleParameter[] parameters)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand())
{
PrepareCommand(oraCmd, oraConn, null, sql, parameters);
using (OracleDataAdapter oda = new OracleDataAdapter(oraCmd))
{
DataSet ds = new DataSet();
oda.Fill(ds, "ds");
oraCmd.Parameters.Clear();
return ds;
}
}
}
}
/// <summary>
/// 用默认连接字符串执行查询语句,返回DataSet
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parametersCollection">参数集合</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string sql, OracleParameterCollection parametersCollection)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand())
{
PrepareCommand(oraCmd, oraConn, null, sql, parametersCollection);
using (OracleDataAdapter oda = new OracleDataAdapter(oraCmd))
{
DataSet ds = new DataSet();
oda.Fill(ds, "ds");
oraCmd.Parameters.Clear();
return ds;
}
}
}
}
public static int ExecuteSqlCount(string sql, OracleParameterCollection parametersCollection)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
PrepareCommand(oraCmd, oraConn, null, sql, parametersCollection);
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
return oraCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用默认连接字符串执行SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数</param>
public static void ExecuteSql(string sql, params OracleParameter[] parameters)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
PrepareCommand(oraCmd, oraConn, null, sql, parameters);
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
oraCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用默认连接字符串执行SQL
/// </summary>
/// <param name="sql">SQL语句</param>
public static void ExecuteSql(string sql)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
oraCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用指定连接字符串执行SQL
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="connectionString">连接字符串</param>
public static void ExecuteSql(string sql, string connectionString)
{
using (OracleConnection oraConn = new OracleConnection(connectionString))
{
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
oraCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用指定连接字符串执行SQL
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="connectionString">连接字符串</param>
/// <param name="parameters">参数</param>
public static void ExecuteSql(string sql, string connectionString, params OracleParameter[] parameters)
{
using (OracleConnection oraConn = new OracleConnection(connectionString))
{
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
PrepareCommand(oraCmd, oraConn, null, sql, parameters);
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
oraCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用指定连接字符串执行SQL,返回受影响的行数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="connectionString">连接字符串</param>
/// <returns>受影响的行数</returns>
public static int ExecuteSqlCount(string sql, string connectionString)
{
using (OracleConnection oraConn = new OracleConnection(connectionString))
{
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
return oraCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用默认连接字符串执行SQL,返回受影响的行数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>受影响的行数</returns>
public static int ExecuteSqlCount(string sql)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
return oraCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用默认连接字符串执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="params">参数列表</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlCount(string sql, params OracleParameter[] @params)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand())
{
PrepareCommand(oraCmd, oraConn, null, sql, @params);
int affectedRows = oraCmd.ExecuteNonQuery();
oraCmd.Parameters.Clear();
return affectedRows;
}
}
}
/// <summary>
/// 为准备执行的命令绑定参数
/// </summary>
/// <param name="cmd">OracleCommand</param>
/// <param name="conn">OracleConnection</param>
/// <param name="trx">OracleTransaction</param>
/// <param name="cmdText">要执行的命令</param>
/// <param name="parameters">参数数组</param>
private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trx, string cmdText, OracleParameter[] parameters)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trx != null)
cmd.Transaction = trx;
cmd.CommandType = CommandType.Text;
if (parameters != null)
{
foreach (OracleParameter param in parameters)
{
if (param.Value == null)
{
param.Value = DBNull.Value;
}
cmd.Parameters.Add(param);
}
}
}
/// <summary>
/// 为准备执行的命令绑定参数
/// </summary>
/// <param name="cmd">OracleCommand</param>
/// <param name="conn">OracleConnection</param>
/// <param name="trx">OracleTransaction</param>
/// <param name="cmdText">要执行的命令</param>
/// <param name="parametersCollection">参数集合</param>
private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trx, string cmdText, OracleParameterCollection parametersCollection)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trx != null)
cmd.Transaction = trx;
cmd.CommandType = CommandType.Text;//cmdType;
if (parametersCollection != null)
{
foreach (OracleParameter param in parametersCollection)
{
if (param.Value == null)
{
param.Value = DBNull.Value;
}
//赋值,避免"The OracleParameter is already contained by another OracleParameterCollection. "错误
if (param.Direction != ParameterDirection.Output)
cmd.Parameters.Add(param.ParameterName, param.OracleType, param.Size).Value = param.Value;
else
cmd.Parameters.Add(param.ParameterName, param.OracleType, param.Size).Direction = param.Direction;
}
}
}
/// <summary>
/// 使用Oracle的特性进行分页,使用默认连接字符串
/// </summary>
/// <param name="tableName">查询的表名</param>
/// <param name="condition">查询条件</param>
/// <param name="selectColumns">查询的列</param>
/// <param name="size">分页大小</param>
/// <param name="offset">开始位置</param>
/// <param name="sortDirection">排序方式</param>
/// <param name="sortColumn">排序列</param>
/// <returns>DataSet</returns>
public static DataSet ExecutePagedResult(string tableName, string condition, string selectColumns, int size, int offset, string sortDirection, string sortColumn)
{
offset = offset / size;
offset += 1;
StringBuilder sql = new StringBuilder();
sql.Append("select ");
sql.Append(selectColumns);
sql.Append(" from ");
sql.Append(tableName);
sql.Append(" where rowid in (select rid from (select rownum rn,rid from(select rowid rid,");
sql.Append(sortColumn);
sql.Append(" from ");
sql.Append(tableName);
sql.Append(" where 1=1 ");
sql.Append(condition);
sql.Append(" order by ");
sql.Append(sortColumn);
sql.Append(" ");
sql.Append(sortDirection);
sql.Append(") where rownum<=");
sql.Append(offset * size);
sql.Append(") where rn>=");
sql.Append(((offset - 1) * size + 1));
sql.Append(") order by ");
sql.Append(sortColumn);
sql.Append(" ");
sql.Append(sortDirection);
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleDataAdapter oda = new OracleDataAdapter(sql.ToString(), oraConn))
{
DataSet ds = new DataSet();
oda.Fill(ds);
return ds;
}
}
}
/// <summary>
/// 普通分页方法,使用默认连接字符串
/// </summary>
/// <param name="sql"></param>
/// <param name="size"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public static DataSet ExecutePagedResult(string sql, int size, int pageIndex)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleDataAdapter oda = new OracleDataAdapter(sql, oraConn))
{
DataSet ds = new DataSet();
oda.Fill(ds, size * pageIndex, size, "PagedTable");
return ds;
}
}
}
/// <summary>
/// 使用默认连接字符串执行SQL,返回指定字段的值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="dataField">要返回的字段名</param>
/// <returns>指定字段的值</returns>
public static string ExecuteDataField(string sql, string dataField)
{
DataSet ds = new DataSet();
using (OracleConnection oConn = new OracleConnection(dbConnectionString))
{
using (OracleDataAdapter da = new OracleDataAdapter(sql, oConn))
{
da.Fill(ds);
}
}
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0][dataField] != DBNull.Value)
{
return ds.Tables[0].Rows[0][dataField].ToString();
}
return null;
}
return null;
}
/// <summary>
/// 使用默认连接字符串执行SQL,返回指定字段的值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="dataField">要返回的字段名</param>
/// <param name="parametersCollection">参数集合</param>
/// <returns>指定字段的值</returns>
public static string ExecuteDataField(string sql, string dataField, OracleParameterCollection parametersCollection)
{
DataSet ds = new DataSet();
using (OracleConnection oConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand())
{
PrepareCommand(oraCmd, oConn, null, sql, parametersCollection);
using (OracleDataAdapter oda = new OracleDataAdapter(oraCmd))
{
oda.Fill(ds, "ds");
oraCmd.Parameters.Clear();
}
}
}
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0][dataField] != DBNull.Value)
{
return ds.Tables[0].Rows[0][dataField].ToString();
}
return null;
}
return null;
}
/// <summary>
/// 使用指定连接字符串执行SQL,返回指定字段的值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="dataField">要返回的字段名</param>
/// <param name="connectionString">连接字符串</param>
/// <returns>指定字段的值</returns>
public static string ExecuteDataField(string sql, string dataField, string connectionString)
{
DataSet ds = new DataSet();
using (OracleDataAdapter da = new OracleDataAdapter(sql, connectionString))
{
da.Fill(ds);
}
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0][dataField] != DBNull.Value)
{
return ds.Tables[0].Rows[0][dataField].ToString();
}
return null;
}
return null;
}
/// <summary>
/// 使用指定的连接字符串,返回符合查询条件的记录数
/// </summary>
/// <param name="tableName">要查询的表名</param>
/// <param name="condition">查询条件</param>
/// <param name="connectionString">连接字符串</param>
/// <returns>记录数</returns>
public static int ExecuteCount(string tableName, string condition, string connectionString)
{
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentOutOfRangeException("tableName", "TableName Can not be null or empty");
}
return int.Parse(ExecuteDataField("SELECT COUNT(*) COUNTER FROM " + tableName + " WHERE 1=1 " + condition, "COUNTER", connectionString));
}
/// <summary>
/// 使用默认的连接字符串,返回符合查询条件的记录数
/// </summary>
/// <param name="tableName">要查询的表名</param>
/// <param name="condition">查询条件</param>
/// <returns>记录数</returns>
public static int ExecuteCount(string tableName, string condition)
{
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentOutOfRangeException("tableName", "TableName Can not be null or empty");
}
return int.Parse(ExecuteDataField("SELECT COUNT(*) COUNTER FROM " + tableName + " WHERE 1=1 " + condition, "COUNTER"));
}
/// <summary>
/// 使用默认的连接字符串,返回Count Sql的查询结果。查询语句中COUNT(*)的别名必须是COUNTER。
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>查询结果</returns>
public static int ExecuteCount(string sql)
{
if (string.IsNullOrEmpty(sql))
{
throw new ArgumentOutOfRangeException("sql", "Sql Can not be null or empty");
}
if (!sql.Contains("COUNTER"))
{
throw new ArgumentOutOfRangeException("sql", "Sql must contains COUNTER");
}
return int.Parse(ExecuteDataField(sql, "COUNTER"));
}
/// <summary>
/// 使用默认的连接字符串执行SQL,返回DataReader,注意用完之后必须关闭DataReader。
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>DataReader</returns>
public static OracleDataReader ExecuteDataReader(string sql)
{
OracleConnection oraConn = new OracleConnection(dbConnectionString);
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
OracleDataReader dr = oraCmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
}
/// <summary>
/// 使用默认连接字符串执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="sqlList">Sql List</param>
/// <returns>返回0成功,否则失败</returns>
public static int ExecuteSqlTransaction(List<String> sqlList)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
OracleCommand cmd = new OracleCommand { Connection = oraConn };
OracleTransaction tx = oraConn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (string sql in sqlList)
{
if (!String.IsNullOrEmpty(sql))
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
return 0;
}
catch
{
tx.Rollback();
throw;
}
}
}
/// <summary>
/// 使用默认连接字符串执行存储过程,返回DataReader ( 注意:调用该方法后,一定要对DataReader进行Close )
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>DataReader</returns>
public static OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
OracleConnection oraConn = new OracleConnection(dbConnectionString);
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
OracleCommand command = BuildQueryCommand(oraConn, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
OracleDataReader returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return returnReader;
}
/// <summary>
/// 使用默认连接字符串执行存储过程,返回DataSet
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
DataSet dataSet = new DataSet();
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
OracleDataAdapter oda = new OracleDataAdapter
{
SelectCommand = BuildQueryCommand(oraConn, storedProcName, parameters)
};
oda.Fill(dataSet, tableName);
return dataSet;
}
}
/// <summary>
/// 使用默认连接字符串执行存储过程,指定超时时间,返回DataSet
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <param name="timeOut">超时时间</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int timeOut)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
DataSet dataSet = new DataSet();
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
OracleDataAdapter oda = new OracleDataAdapter
{
SelectCommand = BuildQueryCommand(oraConn, storedProcName, parameters)
};
oda.SelectCommand.CommandTimeout = timeOut;
oda.Fill(dataSet, tableName);
return dataSet;
}
}
/// <summary>
/// 构建 OracleCommand 对象(用来返回一个结果集,而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>OracleCommand</returns>
private static OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
{
OracleCommand oraCmd = new OracleCommand(storedProcName, connection)
{
CommandType = CommandType.StoredProcedure
};
foreach (OracleParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
oraCmd.Parameters.Add(parameter);
}
}
return oraCmd;
}
/// <summary>
/// 使用默认连接字符串执行存储过程,返回影响的行数。
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</param>
/// <returns></returns>
public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
OracleCommand command = BuildIntCommand(oraConn, storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
int result = (int)command.Parameters["ReturnValue"].Value;
return result;
}
}
/// <summary>
/// 使用默认连接字符串执行存储过程,返回结果。
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>存储过程执行的结果</returns>
public static string RunSimpleProcedure(string storedProcName, IDataParameter[] parameters)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
OracleCommand command = BuildStringCommand(oraConn, storedProcName, parameters);
command.ExecuteNonQuery();
return command.Parameters["ReturnValue"].Value.ToString();
}
}
/// <summary>
/// 创建 OracleCommand 对象实例(用来返回一个整数值)
/// </summary>
/// <param name="connection">连接字符串</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>OracleCommand 对象实例</returns>
private static OracleCommand BuildIntCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
{
OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.Parameters.Add(new OracleParameter("ReturnValue",
OracleType.Int32, 10, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
return command;
}
/// <summary>
/// 创建 OracleCommand 对象实例(用来返回一个整数值)
/// </summary>
/// <param name="connection">连接字符串</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>OracleCommand 对象实例</returns>
private static OracleCommand BuildStringCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
{
OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.Parameters.Add(new OracleParameter("ReturnValue",
OracleType.VarChar, 255, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
return command;
}
/// <summary>
/// 使用默认连接字符串进行分页查询,并将总记录数放在DataSet中名字为RecCount的表中。
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="selectColumns">查询结果列</param>
/// <param name="condition">查询条件</param>
/// <param name="size">分页大小</param>
/// <param name="pageIndex">查询页</param>
/// <param name="sortColumns">排序字段</param>
/// <param name="sortDirection">排序方向</param>
/// <returns>DataSet</returns>
public static DataSet QueryWithCount(string tableName, string selectColumns, string condition, int size, int pageIndex, string sortColumns, string sortDirection)
{
string sql = "select " + selectColumns + " from " + tableName;
if (condition != "")
{
sql += " where " + condition;
}
sql += " order by " + sortColumns + " " + sortDirection;
using (OracleConnection oarConn = new OracleConnection(dbConnectionString))
{
using (OracleDataAdapter oda = new OracleDataAdapter(sql, oarConn))
{
DataSet ds = new DataSet();
oda.Fill(ds, pageIndex * size, size, tableName);
sql = "select count(*) as COUNTER from " + tableName;
if (condition != "")
{
sql += " where " + condition;
}
oda.SelectCommand.CommandText = sql;
oda.Fill(ds, "RecCount");
return ds;
}
}
}
/// <summary>
/// 使用默认连接字符串进行分页查询,并将总记录数放在DataSet中名字为RecCount的表中。
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="selectColumns">查询结果列</param>
/// <param name="condition">查询条件</param>
/// <param name="groupBy">Group字段</param>
/// <param name="size">分页大小</param>
/// <param name="pageIndex">查询页</param>
/// <param name="sortColumns">排序字段</param>
/// <param name="sortDirection">排序方向</param>
/// <returns>DataSet</returns>
public static DataSet QueryWithCount(string tableName, string selectColumns, string condition, string groupBy, int size, int pageIndex, string sortColumns, string sortDirection)
{
string sql = "select " + selectColumns + " from " + tableName;
if (condition != "")
{
sql += " where " + condition;
}
if (groupBy != "")
{
sql += " Group by " + groupBy;
}
sql += " order by " + sortColumns + " " + sortDirection;
using (OracleConnection sConn = new OracleConnection(dbConnectionString))
{
using (OracleDataAdapter oda = new OracleDataAdapter(sql, sConn))
{
DataSet ds = new DataSet();
oda.Fill(ds, pageIndex * size, size, tableName);
if (groupBy != "")
{
sql = "select count(*) as COUNTER from " + "(Select " + selectColumns + " from " + tableName +
" where 1=1 and " + condition + " group by " + groupBy + ") a";
}
else
{
sql = "select count(*) as COUNTER from " + tableName;
if (condition != "")
{
sql += " where " + condition;
}
}
oda.SelectCommand.CommandText = sql;
oda.Fill(ds, "RecCount");
return ds;
}
}
}
}
}
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Collections.Generic;
using System.Text;
namespace Best.Biz.Repository.Base
{
/// <summary>
/// 数据库访问类
/// </summary>
public static class OracleDbHelper
{
/// <summary>
/// 默认数据库连接字符串,在程序配置文件中的ConnectionStrings段定义,名称是OracleDbConnectionString
/// </summary>
private static readonly string dbConnectionString = ConfigurationManager.ConnectionStrings["OracleDbConnectionString"].ToString();
/// <summary>
/// 用指定的连接字符串执行SQL,返回DataSet
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="connectionString">连接字符串</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataSet(string sql, string connectionString)
{
using (OracleConnection oraConn = new OracleConnection(connectionString))
{
using (OracleDataAdapter oda = new OracleDataAdapter(sql, oraConn))
{
DataSet ds = new DataSet();
oda.Fill(ds);
return ds;
}
}
}
/// <summary>
/// 用默认连接字符串执行SQL,返回DataSet
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataSet(string sql)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleDataAdapter oda = new OracleDataAdapter(sql, oraConn))
{
DataSet ds = new DataSet();
oda.Fill(ds);
return ds;
}
}
}
/// <summary>
/// 用默认连接字符串执行查询语句,返回DataSet
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataSet(string sql, params OracleParameter[] parameters)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand())
{
PrepareCommand(oraCmd, oraConn, null, sql, parameters);
using (OracleDataAdapter oda = new OracleDataAdapter(oraCmd))
{
DataSet ds = new DataSet();
oda.Fill(ds, "ds");
oraCmd.Parameters.Clear();
return ds;
}
}
}
}
/// <summary>
/// 用默认连接字符串执行查询语句,返回DataSet
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parametersCollection">参数集合</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string sql, OracleParameterCollection parametersCollection)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand())
{
PrepareCommand(oraCmd, oraConn, null, sql, parametersCollection);
using (OracleDataAdapter oda = new OracleDataAdapter(oraCmd))
{
DataSet ds = new DataSet();
oda.Fill(ds, "ds");
oraCmd.Parameters.Clear();
return ds;
}
}
}
}
public static int ExecuteSqlCount(string sql, OracleParameterCollection parametersCollection)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
PrepareCommand(oraCmd, oraConn, null, sql, parametersCollection);
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
return oraCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用默认连接字符串执行SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数</param>
public static void ExecuteSql(string sql, params OracleParameter[] parameters)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
PrepareCommand(oraCmd, oraConn, null, sql, parameters);
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
oraCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用默认连接字符串执行SQL
/// </summary>
/// <param name="sql">SQL语句</param>
public static void ExecuteSql(string sql)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
oraCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用指定连接字符串执行SQL
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="connectionString">连接字符串</param>
public static void ExecuteSql(string sql, string connectionString)
{
using (OracleConnection oraConn = new OracleConnection(connectionString))
{
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
oraCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用指定连接字符串执行SQL
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="connectionString">连接字符串</param>
/// <param name="parameters">参数</param>
public static void ExecuteSql(string sql, string connectionString, params OracleParameter[] parameters)
{
using (OracleConnection oraConn = new OracleConnection(connectionString))
{
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
PrepareCommand(oraCmd, oraConn, null, sql, parameters);
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
oraCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用指定连接字符串执行SQL,返回受影响的行数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="connectionString">连接字符串</param>
/// <returns>受影响的行数</returns>
public static int ExecuteSqlCount(string sql, string connectionString)
{
using (OracleConnection oraConn = new OracleConnection(connectionString))
{
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
return oraCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用默认连接字符串执行SQL,返回受影响的行数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>受影响的行数</returns>
public static int ExecuteSqlCount(string sql)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
return oraCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用默认连接字符串执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="params">参数列表</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlCount(string sql, params OracleParameter[] @params)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand())
{
PrepareCommand(oraCmd, oraConn, null, sql, @params);
int affectedRows = oraCmd.ExecuteNonQuery();
oraCmd.Parameters.Clear();
return affectedRows;
}
}
}
/// <summary>
/// 为准备执行的命令绑定参数
/// </summary>
/// <param name="cmd">OracleCommand</param>
/// <param name="conn">OracleConnection</param>
/// <param name="trx">OracleTransaction</param>
/// <param name="cmdText">要执行的命令</param>
/// <param name="parameters">参数数组</param>
private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trx, string cmdText, OracleParameter[] parameters)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trx != null)
cmd.Transaction = trx;
cmd.CommandType = CommandType.Text;
if (parameters != null)
{
foreach (OracleParameter param in parameters)
{
if (param.Value == null)
{
param.Value = DBNull.Value;
}
cmd.Parameters.Add(param);
}
}
}
/// <summary>
/// 为准备执行的命令绑定参数
/// </summary>
/// <param name="cmd">OracleCommand</param>
/// <param name="conn">OracleConnection</param>
/// <param name="trx">OracleTransaction</param>
/// <param name="cmdText">要执行的命令</param>
/// <param name="parametersCollection">参数集合</param>
private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trx, string cmdText, OracleParameterCollection parametersCollection)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trx != null)
cmd.Transaction = trx;
cmd.CommandType = CommandType.Text;//cmdType;
if (parametersCollection != null)
{
foreach (OracleParameter param in parametersCollection)
{
if (param.Value == null)
{
param.Value = DBNull.Value;
}
//赋值,避免"The OracleParameter is already contained by another OracleParameterCollection. "错误
if (param.Direction != ParameterDirection.Output)
cmd.Parameters.Add(param.ParameterName, param.OracleType, param.Size).Value = param.Value;
else
cmd.Parameters.Add(param.ParameterName, param.OracleType, param.Size).Direction = param.Direction;
}
}
}
/// <summary>
/// 使用Oracle的特性进行分页,使用默认连接字符串
/// </summary>
/// <param name="tableName">查询的表名</param>
/// <param name="condition">查询条件</param>
/// <param name="selectColumns">查询的列</param>
/// <param name="size">分页大小</param>
/// <param name="offset">开始位置</param>
/// <param name="sortDirection">排序方式</param>
/// <param name="sortColumn">排序列</param>
/// <returns>DataSet</returns>
public static DataSet ExecutePagedResult(string tableName, string condition, string selectColumns, int size, int offset, string sortDirection, string sortColumn)
{
offset = offset / size;
offset += 1;
StringBuilder sql = new StringBuilder();
sql.Append("select ");
sql.Append(selectColumns);
sql.Append(" from ");
sql.Append(tableName);
sql.Append(" where rowid in (select rid from (select rownum rn,rid from(select rowid rid,");
sql.Append(sortColumn);
sql.Append(" from ");
sql.Append(tableName);
sql.Append(" where 1=1 ");
sql.Append(condition);
sql.Append(" order by ");
sql.Append(sortColumn);
sql.Append(" ");
sql.Append(sortDirection);
sql.Append(") where rownum<=");
sql.Append(offset * size);
sql.Append(") where rn>=");
sql.Append(((offset - 1) * size + 1));
sql.Append(") order by ");
sql.Append(sortColumn);
sql.Append(" ");
sql.Append(sortDirection);
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleDataAdapter oda = new OracleDataAdapter(sql.ToString(), oraConn))
{
DataSet ds = new DataSet();
oda.Fill(ds);
return ds;
}
}
}
/// <summary>
/// 普通分页方法,使用默认连接字符串
/// </summary>
/// <param name="sql"></param>
/// <param name="size"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public static DataSet ExecutePagedResult(string sql, int size, int pageIndex)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
using (OracleDataAdapter oda = new OracleDataAdapter(sql, oraConn))
{
DataSet ds = new DataSet();
oda.Fill(ds, size * pageIndex, size, "PagedTable");
return ds;
}
}
}
/// <summary>
/// 使用默认连接字符串执行SQL,返回指定字段的值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="dataField">要返回的字段名</param>
/// <returns>指定字段的值</returns>
public static string ExecuteDataField(string sql, string dataField)
{
DataSet ds = new DataSet();
using (OracleConnection oConn = new OracleConnection(dbConnectionString))
{
using (OracleDataAdapter da = new OracleDataAdapter(sql, oConn))
{
da.Fill(ds);
}
}
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0][dataField] != DBNull.Value)
{
return ds.Tables[0].Rows[0][dataField].ToString();
}
return null;
}
return null;
}
/// <summary>
/// 使用默认连接字符串执行SQL,返回指定字段的值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="dataField">要返回的字段名</param>
/// <param name="parametersCollection">参数集合</param>
/// <returns>指定字段的值</returns>
public static string ExecuteDataField(string sql, string dataField, OracleParameterCollection parametersCollection)
{
DataSet ds = new DataSet();
using (OracleConnection oConn = new OracleConnection(dbConnectionString))
{
using (OracleCommand oraCmd = new OracleCommand())
{
PrepareCommand(oraCmd, oConn, null, sql, parametersCollection);
using (OracleDataAdapter oda = new OracleDataAdapter(oraCmd))
{
oda.Fill(ds, "ds");
oraCmd.Parameters.Clear();
}
}
}
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0][dataField] != DBNull.Value)
{
return ds.Tables[0].Rows[0][dataField].ToString();
}
return null;
}
return null;
}
/// <summary>
/// 使用指定连接字符串执行SQL,返回指定字段的值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="dataField">要返回的字段名</param>
/// <param name="connectionString">连接字符串</param>
/// <returns>指定字段的值</returns>
public static string ExecuteDataField(string sql, string dataField, string connectionString)
{
DataSet ds = new DataSet();
using (OracleDataAdapter da = new OracleDataAdapter(sql, connectionString))
{
da.Fill(ds);
}
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0][dataField] != DBNull.Value)
{
return ds.Tables[0].Rows[0][dataField].ToString();
}
return null;
}
return null;
}
/// <summary>
/// 使用指定的连接字符串,返回符合查询条件的记录数
/// </summary>
/// <param name="tableName">要查询的表名</param>
/// <param name="condition">查询条件</param>
/// <param name="connectionString">连接字符串</param>
/// <returns>记录数</returns>
public static int ExecuteCount(string tableName, string condition, string connectionString)
{
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentOutOfRangeException("tableName", "TableName Can not be null or empty");
}
return int.Parse(ExecuteDataField("SELECT COUNT(*) COUNTER FROM " + tableName + " WHERE 1=1 " + condition, "COUNTER", connectionString));
}
/// <summary>
/// 使用默认的连接字符串,返回符合查询条件的记录数
/// </summary>
/// <param name="tableName">要查询的表名</param>
/// <param name="condition">查询条件</param>
/// <returns>记录数</returns>
public static int ExecuteCount(string tableName, string condition)
{
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentOutOfRangeException("tableName", "TableName Can not be null or empty");
}
return int.Parse(ExecuteDataField("SELECT COUNT(*) COUNTER FROM " + tableName + " WHERE 1=1 " + condition, "COUNTER"));
}
/// <summary>
/// 使用默认的连接字符串,返回Count Sql的查询结果。查询语句中COUNT(*)的别名必须是COUNTER。
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>查询结果</returns>
public static int ExecuteCount(string sql)
{
if (string.IsNullOrEmpty(sql))
{
throw new ArgumentOutOfRangeException("sql", "Sql Can not be null or empty");
}
if (!sql.Contains("COUNTER"))
{
throw new ArgumentOutOfRangeException("sql", "Sql must contains COUNTER");
}
return int.Parse(ExecuteDataField(sql, "COUNTER"));
}
/// <summary>
/// 使用默认的连接字符串执行SQL,返回DataReader,注意用完之后必须关闭DataReader。
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>DataReader</returns>
public static OracleDataReader ExecuteDataReader(string sql)
{
OracleConnection oraConn = new OracleConnection(dbConnectionString);
using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
OracleDataReader dr = oraCmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
}
/// <summary>
/// 使用默认连接字符串执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="sqlList">Sql List</param>
/// <returns>返回0成功,否则失败</returns>
public static int ExecuteSqlTransaction(List<String> sqlList)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
OracleCommand cmd = new OracleCommand { Connection = oraConn };
OracleTransaction tx = oraConn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (string sql in sqlList)
{
if (!String.IsNullOrEmpty(sql))
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
return 0;
}
catch
{
tx.Rollback();
throw;
}
}
}
/// <summary>
/// 使用默认连接字符串执行存储过程,返回DataReader ( 注意:调用该方法后,一定要对DataReader进行Close )
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>DataReader</returns>
public static OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
OracleConnection oraConn = new OracleConnection(dbConnectionString);
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
OracleCommand command = BuildQueryCommand(oraConn, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
OracleDataReader returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return returnReader;
}
/// <summary>
/// 使用默认连接字符串执行存储过程,返回DataSet
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
DataSet dataSet = new DataSet();
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
OracleDataAdapter oda = new OracleDataAdapter
{
SelectCommand = BuildQueryCommand(oraConn, storedProcName, parameters)
};
oda.Fill(dataSet, tableName);
return dataSet;
}
}
/// <summary>
/// 使用默认连接字符串执行存储过程,指定超时时间,返回DataSet
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <param name="timeOut">超时时间</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int timeOut)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
DataSet dataSet = new DataSet();
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
OracleDataAdapter oda = new OracleDataAdapter
{
SelectCommand = BuildQueryCommand(oraConn, storedProcName, parameters)
};
oda.SelectCommand.CommandTimeout = timeOut;
oda.Fill(dataSet, tableName);
return dataSet;
}
}
/// <summary>
/// 构建 OracleCommand 对象(用来返回一个结果集,而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>OracleCommand</returns>
private static OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
{
OracleCommand oraCmd = new OracleCommand(storedProcName, connection)
{
CommandType = CommandType.StoredProcedure
};
foreach (OracleParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
oraCmd.Parameters.Add(parameter);
}
}
return oraCmd;
}
/// <summary>
/// 使用默认连接字符串执行存储过程,返回影响的行数。
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</param>
/// <returns></returns>
public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
OracleCommand command = BuildIntCommand(oraConn, storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
int result = (int)command.Parameters["ReturnValue"].Value;
return result;
}
}
/// <summary>
/// 使用默认连接字符串执行存储过程,返回结果。
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>存储过程执行的结果</returns>
public static string RunSimpleProcedure(string storedProcName, IDataParameter[] parameters)
{
using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
{
if (oraConn.State != ConnectionState.Open)
oraConn.Open();
OracleCommand command = BuildStringCommand(oraConn, storedProcName, parameters);
command.ExecuteNonQuery();
return command.Parameters["ReturnValue"].Value.ToString();
}
}
/// <summary>
/// 创建 OracleCommand 对象实例(用来返回一个整数值)
/// </summary>
/// <param name="connection">连接字符串</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>OracleCommand 对象实例</returns>
private static OracleCommand BuildIntCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
{
OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.Parameters.Add(new OracleParameter("ReturnValue",
OracleType.Int32, 10, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
return command;
}
/// <summary>
/// 创建 OracleCommand 对象实例(用来返回一个整数值)
/// </summary>
/// <param name="connection">连接字符串</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>OracleCommand 对象实例</returns>
private static OracleCommand BuildStringCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
{
OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.Parameters.Add(new OracleParameter("ReturnValue",
OracleType.VarChar, 255, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
return command;
}
/// <summary>
/// 使用默认连接字符串进行分页查询,并将总记录数放在DataSet中名字为RecCount的表中。
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="selectColumns">查询结果列</param>
/// <param name="condition">查询条件</param>
/// <param name="size">分页大小</param>
/// <param name="pageIndex">查询页</param>
/// <param name="sortColumns">排序字段</param>
/// <param name="sortDirection">排序方向</param>
/// <returns>DataSet</returns>
public static DataSet QueryWithCount(string tableName, string selectColumns, string condition, int size, int pageIndex, string sortColumns, string sortDirection)
{
string sql = "select " + selectColumns + " from " + tableName;
if (condition != "")
{
sql += " where " + condition;
}
sql += " order by " + sortColumns + " " + sortDirection;
using (OracleConnection oarConn = new OracleConnection(dbConnectionString))
{
using (OracleDataAdapter oda = new OracleDataAdapter(sql, oarConn))
{
DataSet ds = new DataSet();
oda.Fill(ds, pageIndex * size, size, tableName);
sql = "select count(*) as COUNTER from " + tableName;
if (condition != "")
{
sql += " where " + condition;
}
oda.SelectCommand.CommandText = sql;
oda.Fill(ds, "RecCount");
return ds;
}
}
}
/// <summary>
/// 使用默认连接字符串进行分页查询,并将总记录数放在DataSet中名字为RecCount的表中。
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="selectColumns">查询结果列</param>
/// <param name="condition">查询条件</param>
/// <param name="groupBy">Group字段</param>
/// <param name="size">分页大小</param>
/// <param name="pageIndex">查询页</param>
/// <param name="sortColumns">排序字段</param>
/// <param name="sortDirection">排序方向</param>
/// <returns>DataSet</returns>
public static DataSet QueryWithCount(string tableName, string selectColumns, string condition, string groupBy, int size, int pageIndex, string sortColumns, string sortDirection)
{
string sql = "select " + selectColumns + " from " + tableName;
if (condition != "")
{
sql += " where " + condition;
}
if (groupBy != "")
{
sql += " Group by " + groupBy;
}
sql += " order by " + sortColumns + " " + sortDirection;
using (OracleConnection sConn = new OracleConnection(dbConnectionString))
{
using (OracleDataAdapter oda = new OracleDataAdapter(sql, sConn))
{
DataSet ds = new DataSet();
oda.Fill(ds, pageIndex * size, size, tableName);
if (groupBy != "")
{
sql = "select count(*) as COUNTER from " + "(Select " + selectColumns + " from " + tableName +
" where 1=1 and " + condition + " group by " + groupBy + ") a";
}
else
{
sql = "select count(*) as COUNTER from " + tableName;
if (condition != "")
{
sql += " where " + condition;
}
}
oda.SelectCommand.CommandText = sql;
oda.Fill(ds, "RecCount");
return ds;
}
}
}
}
}