/// <summary>
/// Execute a database query which does not include a select
/// </summary>
/// <param name="connectionString">Connection string to database</param>
/// <param name="cmdType">Command type either stored procedure or SQL</param>
/// <param name="cmdText">Acutall SQL Command</param>
/// <param name="commandParameters">Parameters to bind to the command</param>
/// <returns></returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
// Create a new Oracle command
OracleCommand cmd = new OracleCommand();
//Create a connection
using (OracleConnection connection = new OracleConnection(connectionString))
{
try
{
//Prepare the command
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
//Execute the command
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return(val);
}
catch (Exception e)
{
string _errmsg = string.Format("执行SQL语句出错,错误信息为:{0}!\n类型:{1}\n查询语句为:{2}\n参数:",
e.Message, cmdType, cmdText);
foreach (OracleParameter _p in commandParameters)
{
_errmsg += string.Format("{0}={1}\n", _p.ParameterName, _p.Value.ToString());
}
OralceLogWriter.WriteSystemLog(_errmsg, "ERROR");
throw e;
}
}
}
/// <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="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</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[] commandParameters)
{
try
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connectionString, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return(val);
}
catch (Exception e)
{
string _errmsg = string.Format("执行SQL语句出错,错误信息为:{0}!\n类型:{1}\n查询语句为:{2}\n参数:",
e.Message, cmdType, cmdText);
if (commandParameters != null)
{
foreach (OracleParameter _p in commandParameters)
{
_errmsg += string.Format("{0}={1}\n", _p.ParameterName, _p.Value.ToString());
}
}
OralceLogWriter.WriteSystemLog(_errmsg, "ERROR");
throw e;
}
}
/// <summary>
/// Execute a select query that will return a result set
/// </summary>
/// <param name="connectionString">Connection string</param>
/// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="cmdText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns></returns>
public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
//Create the command and connection
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(connectionString);
try
{
//Prepare the command to execute
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
//Execute the query, stating that the connection should close when the resulting datareader has been read
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return(rdr);
}
catch (Exception e)
{
string _errmsg = string.Format("执行SQL语句出错,错误信息为:{0}!\n类型:{1}\n查询语句为:{2}\n参数:",
e.Message, cmdType, cmdText);
foreach (OracleParameter _p in commandParameters)
{
_errmsg += string.Format("{0}={1}\n", _p.ParameterName, _p.Value.ToString());
}
OralceLogWriter.WriteSystemLog(_errmsg, "ERROR");
//If an error occurs close the connection as the reader will not be used and we expect it to close the connection
conn.Close();
throw e;
}
}
/// <summary>
/// 通过查询语句填充表
/// </summary>
/// <param name="connectionString"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static DataTable FillDataTable(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
// Create a new Oracle command
DataTable _dt = new DataTable();
//Create a connection
using (OracleConnection connection = new OracleConnection(connectionString))
{
try
{
OracleCommand cmd = new OracleCommand();
//Prepare the command
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
//Execute the command
using (OracleDataReader rdr = cmd.ExecuteReader())
{
FillTableByReader(_dt, rdr);
rdr.Close();
}
cmd = null;
}
catch (Exception e)
{
string _errmsg = string.Format("执行SQL语句出错,错误信息为:{0}!\n类型:{1}\n查询语句为:{2}\n参数:",
e.Message, cmdType, cmdText);
if (commandParameters != null)
{
foreach (OracleParameter _p in commandParameters)
{
_errmsg += string.Format("{0}={1}\n", _p.ParameterName, _p.Value.ToString());
}
}
OralceLogWriter.WriteSystemLog(_errmsg, "ERROR");
connection.Close();
throw;
}
finally
{
connection.Close();
}
}
return(_dt);
}
/// <summary>
/// 通过查询语句取数据到DataSet
/// </summary>
/// <param name="_selectStr"></param>
/// <param name="_tableName"></param>
/// <returns></returns>
public static DataSet FillDataSet(string _selectStr, string _tableName)
{
OracleDataReader rdr;
DataSet _ds = new DataSet();
using (OracleConnection cn = OpenConnection())
{
try
{
OracleCommand _cmd = new OracleCommand(_selectStr, cn);
DataTable _dt = new DataTable(_tableName);
using (rdr = _cmd.ExecuteReader())
{
try
{
FillTableByReader(_dt, rdr);
rdr.Close();
}
catch
{
rdr.Close();
throw;
}
}
_ds.Tables.Add(_dt);
_ds.AcceptChanges();
}
catch (Exception e)
{
string _errmsg = string.Format("执行SQL语句出错,错误信息为:{0}!\n查询语句为:{1}\n:",
e.Message, _selectStr);
OralceLogWriter.WriteSystemLog(_errmsg, "ERROR");
throw e;
}
finally
{
cn.Close();
}
}
return(_ds);
}
/// <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 transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (transaction != null && transaction.Connection == null)
{
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
}
// Create a command and prepare it for execution
try
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return(retval);
}
catch (Exception e)
{
string _errmsg = string.Format("执行SQL语句出错,错误信息为:{0}!\n类型:{1}\n查询语句为:{2}\n参数:",
e.Message, commandType, commandText);
if (commandParameters != null)
{
foreach (OracleParameter _p in commandParameters)
{
_errmsg += string.Format("{0}={1}\n", _p.ParameterName, _p.Value.ToString());
}
}
OralceLogWriter.WriteSystemLog(_errmsg, "ERROR");
throw e;
}
}
