/// <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);
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>
/// 通过查询语句取数据到表
/// </summary>
/// <param name="_selectStr"></param>
/// <param name="_tableName"></param>
/// <returns></returns>
public static DataTable Get_Data(string _selectStr, string _tableName)
{
OracleDataReader rdr;
DataTable _dt = new DataTable(_tableName);
using (OracleConnection cn = OpenConnection())
{
try
{
OracleCommand _cmd = new OracleCommand(_selectStr, cn);
using (rdr = _cmd.ExecuteReader())
{
FillTableByReader(_dt, rdr);
}
}
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(_dt);
}
/// <summary>
/// 通过查询语句填充表
/// </summary>
/// <param name="connectionString"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static DataTable FillDataTable(OracleConnection cn, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleDataReader rdr;
// Create a new Oracle command
OracleCommand cmd = new OracleCommand();
DataTable _dt = new DataTable("ResultTable");
//Create a connection
try
{
//Prepare the command
PrepareCommand(cmd, cn, null, cmdType, cmdText, commandParameters);
//Execute the command
rdr = cmd.ExecuteReader();
FillTableByReader(_dt, rdr);
rdr.Close();
}
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;
}
return(_dt);
}
/// <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(OracleConnection cn, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
//Create the command and connection
OracleCommand cmd = new OracleCommand();
try
{
//Prepare the command to execute
PrepareCommand(cmd, cn, null, cmdType, cmdText, commandParameters);
//Execute the query, stating that the connection should close when the resulting datareader has been read
OracleDataReader rdr = cmd.ExecuteReader();
cmd.Parameters.Clear();
return(rdr);
}
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");
//If an error occurs close the connection as the reader will not be used and we expect it to close the connection
throw e;
}
}
public static string GetSystemParms(string _CSNAME)
{
string str = "";
try
{
OracleParameter[] _param =
{
new OracleParameter(":CSNAME", OracleDbType.Varchar2)
};
_param[0].Value = _CSNAME;
OracleDataReader dr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringProfile, System.Data.CommandType.Text, SQL_GetZHTJ_CSB, _param);
while (dr.Read())
{
str = dr.IsDBNull(0) ? "" : dr.GetString(0).ToString();
}
dr.Close();
}
catch (Exception ex)
{
OralceLogWriter.WriteSystemLog(string.Format("从zhtj_csb中取参数{0}时出错：{1}", _CSNAME, ex.Message), "ERROR");
str = "";
}
return(str);
}
/// <summary>
/// 写入日志
/// </summary>
/// <param name="Direction">访问方向 1.被访问 2.向外访问其它系统接口</param>
/// <param name="IcsType">接口类型</param>
/// <param name="SrcIP">来源IP地址</param>
/// <param name="DesIP">目标IP地址</param>
/// <param name="CallParam">调用参数</param>
/// <param name="LogMsg">日志消息内容</param>
/// <param name="CallResult">执行结果情况 0成功 1失败</param>
/// <returns></returns>
public static bool WirteLog(string Direction, string IcsType, string SrcIP, string DesIP, string CallParam, string LogMsg, string CallResult)
{
try
{
using (OracleConnection cn = OracleHelper.OpenConnection())
{
bool _ret = WirteLog(Direction, IcsType, SrcIP, DesIP, CallParam, LogMsg, CallResult, cn);
return(_ret);
}
}
catch (Exception ex)
{
OralceLogWriter.WriteSystemLog(string.Format("插入接口日志记录出错！{0}", ex.Message), "ERROR");
return(false);
}
}
/// <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;
}
}