// 用Reader方式
private DataTable GetDataTableByDataReader(string sql, CommandType type, int pageIndex, int pageSize, ParameterCollection parameters)
{
DataTable ret = new DataTable();
int startIndex = 0;
int endIndex = 0;
int rowIndex = 0;
DataRow row = null;
OracleDataReader dr = null;
OracleCommand oleCmd = new OracleCommand();
try
{
startIndex = pageSize * (pageIndex - 1);
endIndex = startIndex + pageSize - 1;
//Open();
oleCmd.Connection = _oleConn;
oleCmd.CommandText = sql;
oleCmd.CommandType = type;
// 设置参数
for (int i = 0; i < parameters.Count; i++)
{
OracleParameter param = new OracleParameter();
param.ParameterName = parameters[i].ParameterName;
param.Value = parameters[i].ParameterValue;
oleCmd.Parameters.Add(param);
}
dr = oleCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
ret.Columns.Add(dr.GetName(i), dr.GetFieldType(i));
}
while (dr.Read())
{
rowIndex++;
if (rowIndex > startIndex)
{
row = ret.NewRow();
for (int i = 0; i < ret.Columns.Count; i++)
{
row[i] = dr.GetValue(i);
}
ret.Rows.Add(row);
}
if (rowIndex > endIndex)
{
break;
}
}
}
catch (System.Exception ex)
{
throw new TechException(ex.Message, sql, ex);
}
finally
{
//Close();
dr = null;
oleCmd = null;
}
return ret;
}
/// <summary>
/// 获取数据集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="type">SQL语句类型</param>
/// <param name="pageIndex">当前页数</param>
/// <param name="pageSize">当前页显示的记录数</param>
/// <param name="parameters">条件参数</param>
/// <returns>数据集</returns>
public DataTable GetDataTable(string sql, CommandType type, int pageIndex, int pageSize, ParameterCollection parameters)
{
if (type == CommandType.Text)
{
return GetDataTableByRowNum(sql, type, pageIndex, pageSize, parameters);
}
else
{
return GetDataTableByDataReader(sql, type, pageIndex, pageSize, parameters);
}
}
// 用rownum方式
private DataTable GetDataTableByRowNum(string sql, CommandType type, int pageIndex, int pageSize, ParameterCollection parameters)
{
// 对于Oracle，如果执行的是Sql语句，则采用Oracle数据库分页方式，从而减少DataReader占用的资源
int startIndex = pageSize * (pageIndex - 1);
int endIndex = startIndex + pageSize;
string sqlCommand = @"SELECT * FROM
(
SELECT A.*, rownum R
FROM
(
{0}
) A
WHERE rownum<={1}
) B
WHERE R>{2}";
return GetDataTable(string.Format(sqlCommand, sql, endIndex, startIndex));
}
/// <summary>
/// 获取数据集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="type">SQL语句类型</param>
/// <param name="parameters">条件参数</param>
/// <returns>数据集</returns>
public DataSet GetDataSet(string sql, CommandType type, ParameterCollection parameters)
{
DataSet ret = new DataSet();
OracleDataAdapter da = new OracleDataAdapter();
OracleCommand oleCmd = new OracleCommand();
try
{
//Open();
oleCmd.Connection = _oleConn;
oleCmd.CommandText = sql;
oleCmd.CommandType = type;
// 设置参数
for (int i = 0; i < parameters.Count; i++)
{
OracleParameter param = new OracleParameter();
param.ParameterName = parameters[i].ParameterName;
param.Value = parameters[i].ParameterValue;
oleCmd.Parameters.Add(param);
}
da.SelectCommand = oleCmd;
da.Fill(ret);
}
catch (System.Exception ex)
{
throw new TechException(ex.Message, sql, ex);
}
finally
{
//Close();
oleCmd = null;
da = null;
}
return ret;
}
/// <summary>
/// 获取数据集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="type">SQL语句类型</param>
/// <param name="parameters">条件参数</param>
/// <returns>数据集</returns>
public DataTable GetDataTable(string sql, CommandType type, ParameterCollection parameters)
{
return GetDataSet(sql, type, parameters).Tables[0];
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="type">SQL语句的类型</param>
/// <param name="parameters">相关参数</param>
/// <returns>第一行第一列的值</returns>
public object ExecuteScalar(string sql, CommandType type, ParameterCollection parameters)
{
object ret = null;
OracleCommand oleCmd = new OracleCommand();
try
{
//Open();
oleCmd.Connection = _oleConn;
oleCmd.CommandText = sql;
oleCmd.CommandType = type;
// 设置参数
for (int i = 0; i < parameters.Count; i++)
{
OracleParameter param = new OracleParameter();
param.ParameterName = parameters[i].ParameterName;
param.Value = parameters[i].ParameterValue;
oleCmd.Parameters.Add(param);
}
ret = oleCmd.ExecuteScalar();
//// 获取参数返回值
//for (int i = 0; i < parameters.Count; i++)
//{
// parameters[i].ParameterValue = oleCmd.Parameters[parameters[i].ParameterName].Value;
//}
}
catch (System.Exception ex)
{
throw new TechException(ex.Message, sql, ex);
}
finally
{
//Close();
oleCmd = null;
}
return ret;
}
/// <summary>
/// 执行数据库操作(Insert,Update,Delete)
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="type">SQL语句的类型</param>
/// <param name="parameters">相关参数</param>
/// <returns>SQL语句所影响的记录数</returns>
public int ExecuteNonQuery(string sql, CommandType type, ParameterCollection parameters)
{
int ret = 0;
OracleCommand oleCmd = new OracleCommand();
try
{
//Open();
oleCmd.Connection = _oleConn;
if (_oleTrans != null) oleCmd.Transaction = _oleTrans;
oleCmd.CommandText = sql;
oleCmd.CommandType = type;
// 设置参数
for (int i = 0; i < parameters.Count; i++)
{
OracleParameter param = new OracleParameter();
param.ParameterName = parameters[i].ParameterName;
param.Value = parameters[i].ParameterValue;
oleCmd.Parameters.Add(param);
}
ret = oleCmd.ExecuteNonQuery();
// 获取参数返回值
for (int i = 0; i < parameters.Count; i++)
{
parameters[i].ParameterValue = oleCmd.Parameters[parameters[i].ParameterName].Value;
}
}
catch (System.Exception ex)
{
throw new TechException(ex.Message, sql, ex);
}
finally
{
oleCmd.Dispose();
oleCmd = null;
//Close();
}
return ret;
}
// 用rownum方式
private DataTable GetDataTableByRowNum(string sql, CommandType type, int pageIndex, int pageSize, ParameterCollection parameters)
{
//SELECT *
//FROM (select *,ROW_NUMBER() Over(order by id) as rowNum from table_info )as myTable
//where rowNum between 50 and 60;
//注：SQL Server 的ROW_NUMBER()函数性能，通用性都不够，暂不使用
return null;
}
/// <summary>
/// 获取数据集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="type">SQL语句类型</param>
/// <param name="parameters">条件参数</param>
/// <returns>数据集</returns>
public DataSet GetDataSet(string sql, CommandType type, ParameterCollection parameters)
{
DataSet ret = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand oleCmd = new SqlCommand();
try
{
oleCmd.Connection = _oleConn;
oleCmd.CommandText = sql;
oleCmd.CommandType = CommandType.Text;
// 设置参数
for (int i = 0; i < parameters.Count; i++)
{
SqlParameter param = new SqlParameter();
param.ParameterName = parameters[i].ParameterName;
param.Value = parameters[i].ParameterValue;
if (parameters[i].ParameterValue is Guid)
{
param.DbType = DbType.Guid;
}
oleCmd.Parameters.Add(param);
}
da.SelectCommand = oleCmd;
da.Fill(ret);
}
catch (System.Exception ex)
{
throw new TechException(ex.Message, sql, ex);
}
finally
{
oleCmd.Dispose();
oleCmd = null;
da.Dispose();
da = null;
}
return ret;
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="type">SQL语句的类型</param>
/// <param name="parameters">相关参数</param>
/// <returns>第一行第一列的值</returns>
public object ExecuteScalar(string sql, CommandType type, ParameterCollection parameters)
{
object ret = null;
SqlCommand oleCmd = new SqlCommand();
try
{
oleCmd.Connection = _oleConn;
oleCmd.CommandText = sql;
oleCmd.CommandType = CommandType.Text;
// 设置参数
for (int i = 0; i < parameters.Count; i++)
{
SqlParameter param = new SqlParameter();
param.ParameterName = parameters[i].ParameterName;
param.Value = parameters[i].ParameterValue;
if (parameters[i].ParameterValue is Guid)
{
param.DbType = DbType.Guid;
}
oleCmd.Parameters.Add(param);
}
ret = oleCmd.ExecuteScalar();
//// 获取参数返回值
//for (int i = 0; i < parameters.Count; i++)
//{
// parameters[i].ParameterValue = oleCmd.Parameters[parameters[i].ParameterName].Value;
//}
}
catch (System.Exception ex)
{
throw new TechException(ex.Message, sql, ex);
}
finally
{
oleCmd.Dispose();
oleCmd = null;
}
return ret;
}