.net C# SqlHelper for Oracle
.net C# SqlHelper for Oracle适用于Oracle的sqlhelper
需要使用ODP.Net，引用Oracle.Dataaccess.dll推荐安装ODAC
代码如下：
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Reflection;using System.Data;using System.Configuration;using Oracle.DataAccess.Client;namespace DAL{ public static class SqlHelper { #region 样本 //标准连接-SSPI PRivate static readonly string defaultConnectString = "Data Source=ORCL;Integrated Security=SSPI;"; //标准连接 //private static readonly string defaultConnectString = "Data Source=ORCL;User Id=UPDM;PassWord=1234;"; //标准链接 //private static readonly string defaultConnectString = "Data Source=(DESCRipTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));User Id=system;Password=1234;"; #endregion #region 变量 private static OracleConnection _con = null; public static string _constr = ConfigurationManager.ConnectionStrings["orcl"].ToString(); #endregion #region 属性 public static string constr { get { if (_constr == null||_constr.Equals(String.Empty)) { _constr = defaultConnectString; } return _constr; } set { _constr = value; } } /// <summary> /// 获取或设置数据库连接对象 /// </summary> public static OracleConnection Con { get { if (SqlHelper._con == null) { SqlHelper._con = new OracleConnection(); } if (SqlHelper._con.ConnectionString == null || SqlHelper._con.ConnectionString.Equals(string.Empty)) { SqlHelper._con.ConnectionString = SqlHelper.constr; } return SqlHelper._con; } set { SqlHelper._con = value; } } #endregion #region 方法 /// <summary> /// 执行并返回第一行第一列的数据库操作 /// </summary> /// <param name="commandText">Sql语句或存储过程名</param> /// <param name="commandType">Sql命令类型</param> /// <param name="param">Oracle命令参数数组</param> /// <returns>第一行第一列的记录</returns> public static int ExecuteScalar(string commandText, CommandType commandType, params OracleParameter[] param) { int result = 0; try{ using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con)) { try { cmd.CommandType = commandType; if (param!=null) { cmd.Parameters.AddRange(param); } SqlHelper.Con.Open(); string x = cmd.CommandText; result = Convert.ToInt32(cmd.ExecuteScalar()); } catch { result = -1; } } } finally { if (SqlHelper.Con.State != ConnectionState.Closed) { SqlHelper.Con.Close(); } } return result; } /// <summary> /// 执行不查询的数据库操作 /// </summary> /// <param name="commandText">Oracle语句或存储过程名</param> /// <param name="commandType">Oracle命令类型</param> /// <param name="param">Oracle命令参数数组</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string commandText, CommandType commandType, params OracleParameter[] param) { int result = 0; try { using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con)) { try { cmd.CommandType = commandType; if (param!=null) { cmd.Parameters.AddRange(param); } SqlHelper.Con.Open(); result = cmd.ExecuteNonQuery(); } catch { result = -1; } } } finally { if (SqlHelper.Con.State != ConnectionState.Closed) { SqlHelper.Con.Close(); } } return result; } /// <summary> /// 获取数据表 /// </summary> /// <param name="commandText">select命令</param> /// <param name="param">参数表</param> /// <returns></returns> public static DataTable GetDataTable(string commandText,params OracleParameter[] param) { DataTable result = new DataTable(); try { using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con)) { cmd.Parameters.AddRange(param); try { OracleDataAdapter adapter = new OracleDataAdapter(cmd); adapter.Fill(result); } catch { result = null; } } } finally { if (SqlHelper.Con.State != ConnectionState.Closed) { SqlHelper.Con.Close(); } } return result; } public static int GetNextValueInSequence(string sequenceName) { if (ExecuteScalar("select count(*) from user_objects where OBJECT_NAME=:seqName", CommandType.Text, new OracleParameter(":seqName",sequenceName)) > 0) { return ExecuteScalar("select " + sequenceName + ".nextval from dual", CommandType.Text); } else { return -1; } } /// <summary> /// 事务模式执行多行非查询语句 /// </summary> /// <param name="commandText">sql语句</param> /// <param name="param">参数</param> /// <returns>受影响行数</returns> public static int ExecuteNonQueryTransaction(string commandText, List<OracleParameter[]> param) { int result = 0; try { using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con)) { SqlHelper.Con.Open(); cmd.Transaction = cmd.Connection.BeginTransaction(); try { foreach (OracleParameter[] par in param) { cmd.Parameters.Clear(); cmd.Parameters.AddRange(par); result += cmd.ExecuteNonQuery(); } cmd.Transaction.Commit(); } catch { result = -1; try { cmd.Transaction.Rollback(); } catch { result = -2; } } } } finally { if (SqlHelper.Con.State != ConnectionState.Closed)
