OracleHelper操作类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using System.IO;
using System.Configuration;
namespace xxx.DBHelper
{
public static class OracleTools
{
/// <summary>
/// 依据连接串名字connectionName返回数据连接字符串
/// </summary>
/// <param name="connectionName">key值</param>
/// <returns></returns>
public static string GetConnectionStringsConfig(string connectionName)
{
//指定config文件读取
//获取Configuration对象
//winform的
Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
//web的
//Configuration config = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~");
//根据Key读取<add>元素的Value
return config.AppSettings.Settings[connectionName].Value;
}
#region [ 连接对象 ]
/// <summary>
/// 连接对象 字段
/// </summary>
private static OracleConnection conn = null;
/// <summary>
/// 连接串 字段
/// </summary>
//private static string connstr = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));User Id=leaf;Password=leaf;";
public static string connstr = ConfigurationManager.AppSettings["OracleConStr"].ToString();
//public static string connstr = GetConnectionStringsConfig("OracleConStr");
/// <summary>
/// 取得连接串
/// </summary>
public static string GetConnectionString
{
get
{
return connstr;
}
}
/// <summary>
/// 取得连接对象, 没有打开
/// </summary>
public static OracleConnection GetOracleConnection
{
get
{
//return new OracleConnection(GetConnectionString);
string conn = ConfigurationManager.AppSettings["OracleConStr"].ToString();// GetConnectionStringsConfig("OracleConStr");
return new OracleConnection(conn);
}
}
/// <summary>
/// 取得连接对象， 并打开
/// </summary>
public static OracleConnection GetOracleConnectionAndOpen
{
get
{
OracleConnection conn = GetOracleConnection;
conn.Open();
return conn;
}
}
/// <summary>
/// 彻底关闭并释放 OracleConnection 对象，再置为null.
/// </summary>
/// <param name="conn">OracleConnection</param>
public static void CloseOracleConnection(OracleConnection conn)
{
if (conn == null)
return;
conn.Close();
conn.Dispose();
conn = null;
}
#endregion
#region [ ExecuteNonQuery ]
/// <summary>
/// 普通SQL语句执行增删改
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQuery(string cmdText, params OracleParameter[] commandParameters)
{
return ExecuteNonQuery(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 存储过程执行增删改
/// </summary>
/// <param name="cmdText">存储过程</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQueryByProc(string cmdText, params OracleParameter[] commandParameters)
{
return ExecuteNonQuery(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 执行增删改
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
{
int result = 0;
OracleConnection conn = null;
try
{
conn = GetOracleConnectionAndOpen;
OracleCommand command = new OracleCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
result = command.ExecuteNonQuery();
}
catch (Exception ex)
{
result = -1;
}
finally
{
if (conn != null)
CloseOracleConnection(conn);
}
return result;
}
#endregion
#region [ ExecuteReader ]
/// <summary>
/// SQL语句得到 OracleDataReader 对象
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>OracleDataReader 对象</returns>
public static OracleDataReader ExecuteReader(string cmdText, params OracleParameter[] commandParameters)
{
return ExecuteReader(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 存储过程得到 OracleDataReader 对象
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>OracleDataReader 对象</returns>
public static OracleDataReader ExecuteReaderByProc(string cmdText, params OracleParameter[] commandParameters)
{
return ExecuteReader(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 得到 OracleDataReader 对象
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>OracleDataReader 对象</returns>
public static OracleDataReader ExecuteReader(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
{
OracleDataReader result = null;
OracleConnection conn = null;
try
{
conn = GetOracleConnectionAndOpen;
OracleCommand command = new OracleCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
result = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
result = null;
}
finally
{
if (conn != null)
CloseOracleConnection(conn);
}
return result;
}
#endregion
#region [ ExecuteScalar ]
/// <summary>
/// 执行SQL语句, 返回Object
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> Object </returns>
public static Object ExecuteScalar(string cmdText, params OracleParameter[] commandParameters)
{
return ExecuteScalar(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 执行存储过程, 返回Object
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> Object </returns>
public static Object ExecuteScalarByProc(string cmdText, params OracleParameter[] commandParameters)
{
return ExecuteScalar(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 返回Object
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> Object </returns>
public static Object ExecuteScalar(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
{
Object result = null;
OracleConnection conn = null;
try
{
conn = GetOracleConnectionAndOpen;
OracleCommand command = new OracleCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
result = command.ExecuteScalar();
}
catch (Exception ex)
{
result = null;
}
finally
{
if (conn != null)
CloseOracleConnection(conn);
}
return result;
}
#endregion
#region [ ExecuteDataSet ]
/// <summary>
/// 执行SQL语句, 返回DataSet
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataSet </returns>
public static DataSet ExecuteDataSet(string cmdText, params OracleParameter[] commandParameters)
{
return ExecuteDataSet(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 执行存储过程, 返回DataSet
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataSet </returns>
public static DataSet ExecuteDataSetByProc(string cmdText, params OracleParameter[] commandParameters)
{
return ExecuteDataSet(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataSet </returns>
public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
{
DataSet result = null;
OracleConnection conn = null;
try
{
conn = GetOracleConnectionAndOpen;
OracleCommand command = new OracleCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
OracleDataAdapter adapter = new OracleDataAdapter();
adapter.SelectCommand = command;
result = new DataSet();
adapter.Fill(result);
}
catch (Exception ex)
{
result = null;
//LogHelper.WriteLog(typeof(OracleTools), string.Format("连接oracle数据库出错：sql={2},Message={0},StackTrace={1}", ex.Message, ex.StackTrace, cmdText));
throw new Exception(string.Format("连接oracle数据库出错：sql={2},Message={0},StackTrace={1}", ex.Message, ex.StackTrace, cmdText));
}
finally
{
if (conn != null)
CloseOracleConnection(conn);
}
return result;
}
public static string GetYMD()
{
string day = DateTime.Now.Day.ToString();
return DateTime.Now.Year.ToString().Substring(2, 2) + (DateTime.Now.Month.ToString().Length == 1 ? "0" + DateTime.Now.Month.ToString() : DateTime.Now.Month.ToString()) + (day.Length == 1 ? "0" + day : day);
}
public static object ExecuteDataSet_self(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
{
string messge = string.Empty;
object result = null;
DataSet ds = new DataSet();
OracleConnection conn = null;
try
{
conn = GetOracleConnectionAndOpen;
OracleCommand command = new OracleCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
OracleDataAdapter adapter = new OracleDataAdapter();
adapter.SelectCommand = command;
//result = new DataSet();
adapter.Fill(ds);
}
catch (Exception ex)
{
messge = ex.Message;
}
finally
{
if (conn != null)
CloseOracleConnection(conn);
}
if (messge.Length > 0)
result = messge;
else
result = ds;
return result;
}
#endregion
#region [ ExecuteDataTable ]
/// <summary>
/// 执行SQL语句, 返回DataTable
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataTable </returns>
public static DataTable ExecuteDataTable(string cmdText, params OracleParameter[] commandParameters)
{
return ExecuteDataTable(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 执行存储过程, 返回DataTable
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataTable </returns>
public static DataTable ExecuteDataTableByProc(string cmdText, params OracleParameter[] commandParameters)
{
return ExecuteDataTable(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 返回DataTable
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataTable </returns>
public static DataTable ExecuteDataTable(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
{
DataTable dtResult = null;
DataSet ds = ExecuteDataSet(cmdText, cmdType, commandParameters);
if (ds != null && ds.Tables.Count > 0)
{
dtResult = ds.Tables[0];
}
return dtResult;
}
#endregion
#region [ PrepareCommand ]
/// <summary>
/// Command对象执行前预处理
/// </summary>
/// <param name="command"></param>
/// <param name="connection"></param>
/// <param name="trans"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="commandParameters"></param>
private static void PrepareCommand(OracleCommand command, OracleConnection connection, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
{
if (connection.State != ConnectionState.Open) connection.Open();
command.Connection = connection;
command.CommandText = cmdText;
command.CommandType = cmdType;
if (commandParameters != null)
{
foreach (OracleParameter parm in commandParameters)
command.Parameters.Add(parm);
}
}
#endregion
/// <summary>
/// 事务执行，返回字符串空时表示成功
/// </summary>
/// <param name="comList"></param>
/// <returns></returns>
public static string ExecuteSqlWithTrans(List<OracleCommandInfo> comList)
{
using (OracleConnection conn = GetOracleConnectionAndOpen)
{
OracleTransaction trans = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.Transaction = trans;
try
{
int count = 0;
for (int i = 0; i < comList.Count; i++)
{
cmd.CommandText = comList[i].CommandText;
//存储过程 or Sql语句
if (comList[i].IsProc)
cmd.CommandType = CommandType.StoredProcedure;
else
cmd.CommandType = CommandType.Text;
if (comList[i].Parameters.Length > 0)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(comList[i].Parameters);
}
count += cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
return string.Empty;
}
catch (Exception ex)
{
trans.Rollback();
return string.Format("Oracle执行事务出现异常，exception={0}", ex.ToString());
}
}
}
}
public class OracleCommandInfo
{
public string CommandText { get; set; }
public bool IsProc { get; set; }
public OracleParameter[] Parameters { get; set; }
public OracleCommandInfo(string commandText, bool isProc)
{
CommandText = commandText;
IsProc = isProc;
}
}
}
<!--oracle数据库连接字符串-->
<add key="OracleConStr" value="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.1.2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));Persist Security Info=True;User ID=xxx;Password=xxx;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5; Decr Pool Size=1;"/>
使用示例：
public ResultModel AddOutBound(List<b_DataSync_OutBoundByTJDX> list)
{
var lstOracleCommandInfo = new List<OracleCommandInfo>();
var sql = @"insert into TPB_GWO_DX(
TGD_ID
,TGD_PI_CODE
,TGD_PD_NAME
,TGD_PI_SPEC
,TGD_PI_SPEC1
,TGD_DATE
,TGD_LOT
,TGD_EXP_DATE
,TGD_PRD_DATE
,TGD_UNIT
,TGD_QUAN
,TGD_PRICE
,TGD_AMOUNT
,TGD_DEPT_ID
,TGD_DEPT_NAME)
values(
:TGD_ID
,:TGD_PI_CODE
,:TGD_PD_NAME
,:TGD_PI_SPEC
,:TGD_PI_SPEC1
,:TGD_DATE
,:TGD_LOT
,:TGD_EXP_DATE
,:TGD_PRD_DATE
,:TGD_UNIT
,:TGD_QUAN
,:TGD_PRICE
,:TGD_AMOUNT
,:TGD_DEPT_ID
,:TGD_DEPT_NAME
)";
foreach (var item in list)
{
var lstParameter = new List<OracleParameter>();
lstParameter.Add(new OracleParameter(":TGD_ID", item.TGD_ID));
lstParameter.Add(new OracleParameter(":TGD_PI_CODE", item.TGD_PI_CODE));
lstParameter.Add(new OracleParameter(":TGD_PD_NAME", item.TGD_PD_NAME));
lstParameter.Add(new OracleParameter(":TGD_PI_SPEC", item.TGD_PI_SPEC));
lstParameter.Add(new OracleParameter(":TGD_PI_SPEC1", item.TGD_PI_SPEC1));
lstParameter.Add(new OracleParameter(":TGD_DATE", item.TGD_DATE));
lstParameter.Add(new OracleParameter(":TGD_LOT", item.TGD_LOT));
lstParameter.Add(new OracleParameter(":TGD_EXP_DATE", item.TGD_EXP_DATE));
lstParameter.Add(new OracleParameter(":TGD_PRD_DATE", item.TGD_PRD_DATE));
lstParameter.Add(new OracleParameter(":TGD_UNIT", item.TGD_UNIT));
lstParameter.Add(new OracleParameter(":TGD_QUAN", item.TGD_QUAN));
lstParameter.Add(new OracleParameter(":TGD_PRICE", item.TGD_PRICE));
lstParameter.Add(new OracleParameter(":TGD_AMOUNT", item.TGD_AMOUNT));
lstParameter.Add(new OracleParameter(":TGD_DEPT_ID", item.TGD_DEPT_ID));
lstParameter.Add(new OracleParameter(":TGD_DEPT_NAME", item.TGD_DEPT_NAME));
var oracleCommandInfo = new OracleCommandInfo(sql, false);
oracleCommandInfo.Parameters = lstParameter.ToArray();
lstOracleCommandInfo.Add(oracleCommandInfo);
}
var message = OracleTools.ExecuteSqlWithTrans(lstOracleCommandInfo);
return string.IsNullOrWhiteSpace(message) ? new ResultModel(1, "操作成功！") : new ResultModel(-1, message);
}
public DataTable GetPurchaseApplyData(DateTime startDate, DateTime? endDate)
{
string sql = @"select * from yxzb.vw_order
where ORDERDATE > :StartDate ";
if (endDate.HasValue)
sql += " and ORDERDATE <= :EndDate ";
List<OracleParameter> lstPt = new List<OracleParameter>();
lstPt.Add(new OracleParameter(":StartDate", startDate));
if (endDate.HasValue)
lstPt.Add(new OracleParameter(":EndDate", endDate));
OracleParameter[] parameters = lstPt.ToArray();
DataTable dt = OracleTools.ExecuteDataTable(sql, parameters);
return dt;
}
