一个 Oracle Data Provider For .NET （ODP.NET）调用的实例

在使用 ADO.NET 调用 Oracle 的代码中，除SQL语句和 SQL Server 有一些差异外，主要注意如果要一次执行多条SQL语句，前面要加Begin，最后加End和分号，每条SQL语句间要加分号，麻烦一点。

代码如下：

using System;
using System.Collections.Generic;
using System.Data;

using Oracle.DataAccess.Client;

namespace ODPDotNetDemo.DAL
{
    public class ProductService
    {
        public static DataTable GetAllEntity()
        {
            string safeSql = "select * from \"category\"";
            return new OracleHelper().ExecuteFillToDataTable(safeSql);
        }

        public static DataTable GetPartialEntity()
        {
            string safeSql = "select * from \"category\" where \"category\"=:category1 or \"category\"=:category2"; //为什么最后不能加 ; 号呢？
            OracleParameter[] paramValues = new OracleParameter[]
            {
                new OracleParameter(":category1","007"),
                new OracleParameter(":category2","319")
            };
            return new OracleHelper().ExecuteFillToDataTable(safeSql, paramValues);
        }

        public static DataTable GetEntityByPager(int pageNumber, int pageSize)
        {
            int beginNumber = (pageNumber - 1) * pageSize + 1;
            int endNumber = pageNumber * pageSize;
            string safeSql = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM \"category\") A WHERE ROWNUM <= :endNumber) WHERE RN >= :beginNumber";
            OracleParameter[] paramValues = new OracleParameter[]
            {
                /* 注意：在ODP.NET的参数化查询中，除非制定 OracleCommand.BindByName = true，否则参数的顺序必须一直。例如 :endNumber 参数一定要再 :beginNumber 之前 */
                /* BindByName 的默认值为 false, 表示按位置绑定 */
                new OracleParameter(":endNumber", endNumber),
                new OracleParameter(":beginNumber", beginNumber)
            };
            return new OracleHelper().ExecuteFillToDataTable(safeSql, paramValues);
            //string safeSql = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM \"category\") A WHERE ROWNUM <= {0}) WHERE RN >= {1}";
            //safeSql = string.Format(safeSql, endNumber, beginNumber);
            //return new OracleHelper().ExecuteFillToDataTable(safeSql);
        }

        public static List<ProductCategory> GetAllEntity2()
        {
            List<ProductCategory> result = new List<ProductCategory>();
            string safeSql = "select * from \"category\"";
            using(OracleDataReader reader = new OracleHelper().ExecuteReader(safeSql))
            {
                while (reader.Read())
                {
                    ProductCategory p = new ProductCategory();
                    p.CategoryId = Convert.ToString(reader["category"]);
                    p.DescI = Convert.ToString(reader["desci"]);
                    p.LongDesc = Convert.ToString(reader["longdesc"]);
                    p.Dept = Convert.ToInt32(reader["dept"]);
                    result.Add(p);
                }
                reader.Close();
            }
            return result;
        }
    }
}

    public class ProductCategory
    {
        public string CategoryId { get; set; }

        public string DescI { get; set; }

        public string LongDesc { get; set; }

        public int Dept { get; set; }
    }

using System;
using System.Data;
using Oracle.DataAccess.Client;
using System.Configuration;

namespace ODPDotNetDemo.DAL
{
    /// <summary>
    /// 数据库通用操作类
    /// </summary>
    public class OracleHelper
    {
        private static readonly string connString = ConfigurationManager.ConnectionStrings["oracleConnStrings"].ConnectionString;

        protected OracleConnection con;//连接对象

        public OracleHelper() : this(connString)
        {
            
        }

        /// <summary>
        /// 带连接字符串的构造函数
        /// </summary>
        /// <param name="constr"></param>
        public OracleHelper(string constr)
        {
            con = new OracleConnection(constr);
        }

        /* 注意：在ODP.NET的参数化查询中，除非制定 OracleCommand.BindByName = true，否则参数的顺序必须一直。例如 :endNumber 参数一定要再 :beginNumber 之前 */
        /* BindByName 的默认值为 false, 表示按位置绑定 */

        #region 打开数据库连接
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private void Open()
        {
            //打开数据库连接
            if (con.State == ConnectionState.Closed || con.State == ConnectionState.Broken)
            {
                try
                {
                    if(con.State == ConnectionState.Broken)
                    {
                        //先关闭，然后打开数据库连接
                        con.Close();
                    }
                    //打开数据库连接
                    con.Open();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
        #endregion

        #region 关闭数据库连接
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        private void Close()
        {
            //判断连接的状态是否已经打开
            if (con.State == ConnectionState.Open || con.State == ConnectionState.Broken)
            {
                con.Close();
            }
        }
        #endregion

        #region 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )
        
        /// <summary>   
        /// 执行查询语句，返回 OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )   
        /// </summary>   
        /// <param name="sql">SQL查询语句</param>   
        /// <returns></returns>   
        public OracleDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, null);
        }

        /// <summary>   
        /// 执行查询语句，返回 OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )   
        /// </summary>   
        /// <param name="sql">SQL查询语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns>  
        public OracleDataReader ExecuteReader(string sql, params OracleParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, con, null, sql, cmdParms);
            OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return myReader;
        }
        
        #endregion

        #region 执行SQL命令语句

        /// <summary>   
        /// 执行不带参数的SQL语句  
        /// </summary>   
        /// <param name="sql">SQL命令语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteCommand(string sql)
        {
            return ExecuteCommand(sql, null);
        }

        /// <summary>   
        /// 执行SQL语句，返回影响的记录数   
        /// </summary>   
        /// <param name="sql">SQL命令语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>影响的记录数</returns>   
        public int ExecuteCommand(string sql, params OracleParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();
            try
            {
                PrepareCommand(cmd, con, null, sql, cmdParms);
                int rowsAffected = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                Close();
                return rowsAffected;
            }
            catch (OracleException e)
            {
                throw e;
            }
        }
        #endregion

        #region 执行SQL查询语句，返回数据到 DataSet 中

        /// <summary>
        /// 执行SQL查询语句，返回数据到 DataSet 中
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <returns></returns>
        public DataSet ExecuteFillToDataSet(string sql)
        {
            return ExecuteFillToDataSet(sql, null);
        }

        /// <summary>
        /// 执行SQL查询语句，返回数据到 DataSet 中
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns>
        public DataSet ExecuteFillToDataSet(string sql, params OracleParameter[] cmdParms)
        {
            Open();//打开数据连接
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, con, null, sql, cmdParms);
            OracleDataAdapter adapter = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            Close();//关闭数据库连接
            return ds;
        }

        #endregion

        #region 执行SQL查询语句，返回数据到 DataTable 中

        /// <summary>
        /// 执行SQL查询语句，返回数据到 DataTable 中
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <returns></returns>
        public DataTable ExecuteFillToDataTable(string sql)
        {
            return ExecuteFillToDataSet(sql).Tables[0];
        }

        /// <summary>
        /// 执行SQL查询语句，返回数据到 DataTable 中
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns>
        public DataTable ExecuteFillToDataTable(string sql, params OracleParameter[] cmdParms)
        {
            return ExecuteFillToDataSet(sql, cmdParms).Tables[0];
        }

        #endregion

        #region 存储过程操作
        
        /// <summary>   
        /// 执行存储过程，返回 OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )   
        /// </summary>   
        /// <param name="storedProcName">存储过程名</param>   
        /// <param name="parameters">存储过程参数</param>   
        /// <returns></returns>   
        public OracleDataReader RunProcedureReturnDataReader(string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand command = BuildQueryCommand(con, storedProcName, parameters);
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>   
        /// 执行存储过程，返回受影响的行数
        /// </summary>   
        /// <param name="storedProcName">存储过程名</param>   
        /// <param name="parameters">存储过程参数</param>   
        /// <returns>受影响的行数</returns>   
        public int RunProcedureReturnRowsAffected(string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand command = BuildQueryCommand(con, storedProcName, parameters);
            int rowsAffected = command.ExecuteNonQuery();
            Close();
            return rowsAffected;
        }

        /// <summary>   
        /// 执行存储过程，返回 DataSet
        /// </summary>   
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns></returns>   
        public DataSet RunProcedureReturnDataSet(string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand cmd = BuildQueryCommand(con, storedProcName, parameters);
            OracleDataAdapter adapter = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            Close();//关闭数据库连接   
            return ds;
        }

        /// <summary>   
        /// 执行存储过程，返回 DataTable
        /// </summary>   
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns></returns>   
        public DataTable RunProcedureReturnDataTable(string storedProcName, IDataParameter[] parameters)
        {
            return RunProcedureReturnDataSet(storedProcName, parameters).Tables[0];
        }


        /// <summary>   
        /// 执行存储过程，返回存储过程的 ReturnValue  
        /// </summary>   
        /// <param name="storedProcName">存储过程名</param>   
        /// <param name="parameters">存储过程参数</param>   
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>   
        public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            int result;
            OracleCommand command = BuildIntCommand(con, storedProcName, parameters);
            rowsAffected = command.ExecuteNonQuery();
            result = (int)command.Parameters["ReturnValue"].Value;
            Close();
            return result;
        }

        #endregion

        #region 私有成员
        
        private void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;   
            if (cmdParms != null)
            {
                foreach (OracleParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && parameter.Value == null)
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        /// <summary>   
        /// 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值)   
        /// </summary>   
        /// <param name="conn">数据库连接</param>   
        /// <param name="storedProcName">存储过程名</param>   
        /// <param name="parameters">存储过程参数</param>   
        /// <returns>OracleCommand</returns>   
        private OracleCommand BuildQueryCommand(OracleConnection conn, string storedProcName, IDataParameter[] parameters)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            OracleCommand command = new OracleCommand(storedProcName, conn);
            command.CommandType = CommandType.StoredProcedure;
            foreach (OracleParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.   
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                     (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

        /// <summary>   
        /// 创建 OracleCommand 对象实例(用来返回一个整数值)    
        /// </summary>   
        /// <param name="storedProcName">存储过程名</param>   
        /// <param name="parameters">存储过程参数</param>   
        /// <returns>OracleCommand 对象实例</returns>   
        private OracleCommand BuildIntCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new OracleParameter("ReturnValue", OracleDbType.Int32, 8, ParameterDirection.ReturnValue,
             false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        #endregion
    }
}

附件下载：https://files.cnblogs.com/Music/ODPDotNetDemo.rar

谢谢浏览！