OracleHelper--> Oracle数据库连接类及测试
请先下载对应的 odp.net 的dll, 放在 bin 目录下面。 下载地址:点击打开链接
1. OracleHelper.cs
- using System;
- using System.Data;
- using Oracle.DataAccess.Client;
-
- namespace Utils
- {
- /// <summary>
- /// Desciption: Oracle数据库访问类 (注:针对 odp.net ).
- /// Author : yenange
- /// Date : 2013-09-21
- /// </summary>
- public static class OracleHelper
- {
- #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;";
- private static string connstr = @"Data Source=localhost/ORCL;Persist Security Info=True;User ID=leaf;Password=leaf;";
-
- /// <summary>
- /// 取得连接串
- /// </summary>
- public static string GetConnectionString
- {
- get
- {
- return connstr;
- }
- }
-
- /// <summary>
- /// 取得连接对象, 没有打开
- /// </summary>
- public static OracleConnection GetOracleConnection
- {
- get
- {
- return new OracleConnection(GetConnectionString);
- }
- }
-
- /// <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;
- }
- finally
- {
- if (conn != null)
- CloseOracleConnection(conn);
- }
-
- 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)
- {
- try
- {
- if (connection.State != ConnectionState.Open) connection.Open();
-
- command.Connection = connection;
- command.CommandText = cmdText;
- command.CommandType = cmdType;
-
- //if (trans != null) command.Transaction = trans;
-
- if (commandParameters != null)
- {
- foreach (OracleParameter parm in commandParameters)
- command.Parameters.Add(parm);
- }
- }
- catch
- {
-
- }
- }
- #endregion
- }//end of class
- }//end of namespace
2. 测试类
- using System;
- using System.Collections.Generic;
- using System.Text;
- using Oracle.DataAccess.Client;
- using System.Data;
- using Utils;
-
- namespace OracleForNet
- {
- class Program
- {
- static void Main(string[] args)
- {
- try
- {
- using (OracleConnection conn = Utils.OracleHelper.GetOracleConnectionAndOpen)
- {
- if (conn.State == ConnectionState.Open)
- {
- Console.WriteLine("打开连接成功!");
- }
- }
- }
- catch (Exception ex)
- {
- Console.WriteLine("出现异常, 异常信息: " + ex.Message);
- }
- Console.Read();
- }
- }//end of class
- }//end of namespace