using System;
using System.Data;
using System.Collections;
using System.Configuration;
using System.Data.OracleClient;
namespace DBhepler
{
/// <summary>
/// A helper class used to execute queries against an Oracle database||一个用于Oracle DB查询的帮助类
/// </summary>
public abstract class OracleHelper
{
//Database connection strings
private static readonly string connectionString =ConfigurationManager.AppSettings["OrcConnStr"];
//Create a hashtable for the parameter cached||为缓存中的参数创建一张hashtable
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// Internal function to prepare a command for execution by the database||关于command的数据库执行的内部函数
/// </summary>
/// <param name="cmd">Existing command object||现有的command(命令)对象</param>
/// <param name="conn">Database connection object数据库链接对象</param>
/// <param name="trans">Optional transaction object||可选事务对象</param>
/// <param name="cmdType">Command type,e.g. stored procedure||命令类型：例如：存储结构</param>
/// <param name="cmdText">Command text||命令文本</param>
/// <param name="commandParameters">Parameters for the command||命令参数</param>
private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
{
//Open the connection if required
if (conn.State != ConnectionState.Open)
conn.Open();
//Set up the command
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
//Bind it to the transaction if it exists
if (trans != null)
cmd.Transaction = trans;
// Bind the parameters passed in
if (commandParameters != null)
{
foreach (OracleParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
}
/// <summary>
/// Execute a database query which does not include a select||执行数据库查询，其中不包括一个选择
/// </summary>
/// <remark>
/// e.g.:
/// int result = ExecuteNonQuery(conn, CommandType.Text, sql, new OracleParameter(":prodid", 24));
/// </remark>
/// <param name="connString">Connection string to database||数据库链接字符串</param>
/// <param name="cmdType">the CommandType (stored procedure, text, etc.)||命令类型（SQL语句等）</param>
/// <param name="cmdText">the stored procedure name or PL/SQL command||存储过程或SQL语句</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command||用于执行命令oracleparamters数组</param>
/// <returns>an int representing the number of rows affected by the command||返回该命令影响的行数（int类型）</returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
{
OracleCommand cmd = new OracleCommand();
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// Execute an OracleCommand (that returns no resultset) against an existing database transaction||对现有的数据库事务执行OracleCommand（不返回结果集）
/// using the provided parameters.||使用提供的参数
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
/// </remarks>
/// <param name="trans">an existing database transaction||现有的数据库事务</param>
/// <param name="cmdType">the CommandType (stored procedure, text, etc.)||命令类型（存储过程等）</param>
/// <param name="cmdText">the stored procedure name or PL/SQL command||存储过程或SQL语句</param>
/// <param name="cmdParameters">an array of OracleParamters used to execute the command||用于执行命令oracleparamters数组</param>
/// <returns>an int representing the number of rows affected by the command||返回该命令影响的行数（int类型）</returns>
public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Execute an OracleCommand (that returns no resultset) against an existing database connection||对现有的数据库链接执行OracleCommand（不返回结果集）
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.TableDirect,, "TableName", new OracleParameter(":prodid", 24));
/// </remarks>
/// <param name="connectionString">an existing database connection||现有的数据库链接</param>
/// <param name="cmdType">the CommandType (stored procedure, text, etc.)||命令类型（表等）</param>
/// <param name="cmdText">the stored procedure name or PL/SQL command||存储过程或SQL语句</param>
/// <param name="cmdParameters">an array of OracleParamters used to execute the command||用于执行命令oracleparamters数组</param>
/// <returns>an int representing the number of rows affected by the command||返回该命令影响的行数（int类型）</returns>
public static int ExecuteNonQuery(OracleConnection connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connectionString, null, cmdType, cmdText, cmdParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Batch execution about ExecuteNonQuery||ExecuteNonQuery的批量执行
/// </summary>
/// <remarks>
/// Usage according to ExecuteNonQuery||用法参照ExecuteNonQuery
/// </remarks>
/// <param name="connectionString"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParameters"></param>
/// <returns></returns>
public static int ExecuteBatchNonQuery(string connectionString, CommandType cmdType, string[] cmdText, params OracleParameter[] cmdParameters)
{
int val = 0;
OracleCommand cmd = new OracleCommand();
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for(int i=0;i<cmdText.Length;i++)
{
cmd.CommandText = cmdText[i];
cmd.ExecuteNonQuery();
}
tx.Commit();
val = 1;
}
catch(System.Data.OracleClient.OracleException e)
{
tx.Rollback();
throw new Exception(e.Message);
}
finally
{
conn.Close();
}
return val;
}
}
/// <summary>
/// Execute a select query that will return a result set||执行一个查询，将返回结果集
/// </summary>
/// <param name="connString">Connection string</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns>return a result set(OracleDataReader)</returns>
public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
{
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParameters);
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
finally
{
}
}
/// <summary>
/// Execute a select query that will return a DataSet||执行一个查询，将返回数据集
/// </summary>
/// <param name="connString">Connection string</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns>return a result set(OracleDataReader)</returns>
public static DataSet ExecuteReadAdapter(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
{
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(connectionString);
DataSet ds = new DataSet();
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParameters);
OracleDataAdapter OrcAdapter = new OracleDataAdapter(cmd);
OrcAdapter.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
catch
{
conn.Close();
throw;
}
finally
{
}
}
/// <summary>
/// Execute an OracleCommand that returns the first column of the first record against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
{
OracleCommand cmd = new OracleCommand();
using(OracleConnection conn=new OracleConnection(connectionString))
{
PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
///<summary>
///Execute a OracleCommand (that returns a 1x1 resultset) against the specified SqlTransaction
///using the provided parameters.
///</summary>
///<param name="transaction">A valid SqlTransaction</param>
///<param name="commandType">The CommandType (stored procedure, text, etc.)</param>
///<param name="commandText">The stored procedure name or PL/SQL command</param>
///<param name="commandParameters">An array of OracleParamters used to execute the command</param>
///<returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
{
if (trans == null)
throw new ArgumentNullException("trans");
if (trans != null && trans.Connection == null)
throw new ArgumentException("The transaction was rollbacked or commited,please provide an open transacton.","trans");
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Execute an OracleCommand that returns the first column of the first record against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(OracleConnection connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connectionString, null, cmdType, cmdText, cmdParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Add a set of parameters to the cached||缓存中添加一个参数设置
/// </summary>
/// <param name="cacheKey">Key value to look up the parameters||查找参数的关键值</param>
/// <param name="cmdParameters">Actual parameters to cached||实际参数缓存</param>
public static void CacheParameters(string cacheKey,params OracleParameter[] cmdParameters)
{
parmCache[cacheKey] = cmdParameters;
}
/// <summary>
/// Fetch parameters form the cache||从缓存中取出参数
/// </summary>
/// <param name="cacheKey"> Key to look up the parameters||查找参数的关键字</param>
/// <returns></returns>
public static OracleParameter[] GetCachedParameters(string cacheKey)
{
OracleParameter[] cacheParms = (OracleParameter[])parmCache[cacheKey];
if (cacheParms == null)
return null;
OracleParameter[] clonedParms = new OracleParameter[cacheParms.Length];
for (int i = 0, j = cacheParms.Length; i < j; i++)
clonedParms[i] = (OracleParameter)((ICloneable)cacheParms[i]).Clone();
return clonedParms;
}
/// <summary>
/// Converter to use boolean data type with Oracle||Oracle中bool类型值转换成string类型
/// </summary>
/// <param name="value">Value to convert||转换的值</param>
/// <returns></returns>
public static string OraBit(bool value)
{
if (value)
return "Y";
else
return "N";
}
/// <summary>
/// Converter to use boolean data type with Oracle||Oracle中string类型值转换成bool类型
/// </summary>
/// <param name="value">Value to convert||转换的值</param>
/// <returns></returns>
public static bool OraBool(string value)
{
if (value.Equals("Y"))
return true;
else
return false;
}
}
}
