简单的OracleHelper Class，正在测试

正在测试，暂作备份。

using System;
using System.Data;
using System.Data.OleDb;
namespace Pierce.OracleHelper
{
    /**//// <summary>
    /// OracleHelper：完成常见的Oracle操作，
    /// 没有使用静态方法，目的是可以在多个线程中使用
    /// </summary>
    [LastModified("2005-07-26","建立OleDb Helper类")]
    [LastModified("2005-08-01","增加读取配置文件的方法，根据提供的参数，读取配置文件中对应的值")]
    [LastModified("2005-08-01","增加方法，处理用户指定的数据库连接，关闭或者打开")]
    [LastModified("2005-08-01","因为使用的是OleDb方式，增加对Access的处理")]
    public class OracleHelper
    {
        private OleDbConnection OraCon;                //连接类
        private string OracleUser;                    //Oracle用户名
        private string OraclePassword;                //Oracle密码
        private string OracleService;                //Oracle服务名
        private string oracleUser;                    //配置文件中的用户
        private string oraclePassword;                //配置文件中的密码
        private string oracleService;                //配置文件中的服务名
        private string fileSource;                    //配置文件中Access数据库文件路径
        private string Filesource;                    //数据库路径
        /**//// <summary>
        /// 数据库类型枚举
        /// </summary>
        public enum DbType
        {
            Oracle=0,
            AccessFree=1,
            AccessAuthen=2
        }
        /**//// <summary>
        /// 属性，只读，返回数据库联接类实例
        /// </summary>
        public OleDbConnection Con
        {
            get
            {
                return OraCon;
            }
        }
        /**//// <summary>
        /// 属性，只写，设置Oracle用户名
        /// </summary>
        public string OraUser
        {
            set{oracleUser=value;}
            
        }
        /**//// <summary>
        /// 属性，只写，设置Oracle密码
        /// </summary>
        public string OraPassword
        {
            set{oraclePassword=value;}
        }
        /**//// <summary>
        /// 属性，只写，设置Oralce服务名
        /// </summary>
        public string OraService
        {
            set{oracleService=value;}
        }
        /**//// <summary>
        /// 配置文件中，Access数据库文件路径的参数名
        /// </summary>
        public string FileSource
        {
            set{fileSource=value;}
        }
        /**//// <summary>
        /// 构造函数
        /// </summary>
        public OracleHelper()
        {
            Initialize();
        }
        /**//// <summary>
        /// 初始化实例
        /// </summary>
        private void Initialize()
        {
            this.OraCon=new OleDbConnection();
        }
        /**//// <summary>
        /// 给数据库连接类实例的联机字符赋值
        /// </summary>
        [LastModified("2005-07-26","给数据库连接字符赋值")]
        [LastModified("2005-08-01","读取配置文件中的信息")]
        public virtual void Init(DbType dbType)
        {
            string ConnectionString=null;
            switch(dbType)
            {
                case DbType.Oracle:

                    //判断用户名，密码，服务名是否为空，如果是，抛出异常
                    if(oracleUser==null)
                    {
                        throw new Exception("Oracle User is Null");
                    }
                    if(oraclePassword==null)
                    {
                        throw new Exception("Oracle Password is Null");
                    }
                    if(oracleService==null)
                    {
                        throw new Exception("Oracle Service is Null");
                    }
                    //读取配置文件
                    OracleUser=ReadConfig.ReadConfigValue(oracleUser);
                    OraclePassword=ReadConfig.ReadConfigValue(oraclePassword);
                    OracleService=ReadConfig.ReadConfigValue(oracleService);

                    //生成数据库联机字符串
                    ConnectionString=String.Concat
                        (
                        "Provider=\"OraOLEDB.Oracle.1\";User ID=",
                        OracleUser,
                        ";Data Source=",
                        OracleService,
                        ";Extended Properties=;Persist Security Info=True;Password=",
                        OraclePassword,
                        ";Pooling=true"
                        );
                    break;
                case DbType.AccessFree:                    

                    if(fileSource==null)
                    {
                        throw new Exception("File Source is Null");
                    }
                    //读取配置文件
                    Filesource=ReadConfig.ReadConfigValue(fileSource);
                    //生成数据库联机字符串
                    ConnectionString=String.Concat
                        (
                        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=",
                        Filesource,
                        ";Persist Security Info=False;User Id=;Password="
                        );
                    break;
                case DbType.AccessAuthen:
                    if(fileSource==null)
                    {
                        throw new Exception("File Source is Null");
                    }
                    if(OracleUser==null)
                    {
                        throw new Exception("User is null");
                    }
                    if(OraclePassword==null)
                    {
                        throw new Exception("Password is null");
                    }
                    //读取配置文件
                    FileSource=ReadConfig.ReadConfigValue(fileSource);
                    OracleUser=ReadConfig.ReadConfigValue(oracleUser);
                    OraclePassword=ReadConfig.ReadConfigValue(oraclePassword);

                    //生成数据库联机字符串
                    ConnectionString=String.Concat
                        (
                        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=",
                        fileSource,
                        ";Persist Security Info=False;User Id=",
                        OracleUser,
                        ";Password=",
                        OraclePassword
                        );
                    break;

            }
            //给数据库连接类实例赋连接字符串
            if(ConnectionString==null)
            {
                if(fileSource==null)
                {
                    throw new Exception("ConnectionString is Null");
                }
            }
            this.OraCon.ConnectionString=ConnectionString;
            
        }
        /**//// <summary>
        /// 根据数据库连接实例的使用状况，打开或者关闭连接
        /// </summary>
        /// <param name="ifBegin">是否是用于开始使用状态</param>
        [LastModified("2005-07-26","检查连接字符串是否为空，并且根据传递参数，处理数据库连接")]
        private void ConnectionPrepare(bool ifBegin)
        {
            //检查连接字符串
            if(OraCon.ConnectionString==null)
            {
                throw new Exception("OleDbConnection's ConnectionString is null,execute Init()");
                
            }
            //根据参数执行相关操作
            if(ifBegin==true)
            {
                if(OraCon.State==ConnectionState.Closed)
                {
                    OraCon.Open();
                }
            }
            else
            {
                if(OraCon.State==ConnectionState.Open)
                {
                    OraCon.Close();
                }
            }
        }
        /**//// <summary>
        /// 根据提供的OleDbConnection，关闭或者打开连接
        /// </summary>
        /// <param name="userCon">用户提供的OleDbConnection</param>
        /// <param name="ifBegin">用户使用状态</param>
        [LastModified("2005-08-01","对用户提供的连接进行操作，打开或者关闭")]
        public void ConnectionPrepare(OleDbConnection userCon,bool ifBegin)
        {
            //检查连接字符串
            if(userCon.ConnectionString==null)
            {
                throw new Exception("OleDbConnection's ConnectionString is null");
                
            }
            //根据参数执行相关操作
            if(ifBegin==true)
            {
                if(userCon.State==ConnectionState.Closed)
                {
                    userCon.Open();
                }
            }
            else
            {
                if(userCon.State==ConnectionState.Open)
                {
                    userCon.Close();
                }
            }
        }
      

/**//// <summary>
        /// 处理查询返回数值型数据的方法，
        /// 针对查询Count
        /// </summary>
        /// <param name="sqlString">查询字符串</param>
        /// <returns></returns>
        [LastModified("2005-07-26","执行Sql语句操作，针对count")]
        public virtual object TableCount(string sqlString)
        {
            object count=null;
            try
            {
                ConnectionPrepare(true);
                OleDbCommand Cmd=OraCon.CreateCommand();
                Cmd.CommandText=sqlString;
                //
                count=Cmd.ExecuteScalar();
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionPrepare(false);
            }
            //
            return count;
        }
        /**//// <summary>
        /// 处理返回Count的方法的重载，接收字符串数组作为参数
        /// </summary>
        /// <param name="sqlString">查询Sql</param>
        /// <param name="para">参数字符串</param>
        /// <returns></returns>
        [LastModified("2005-08-02","增加处理Count的方法重载，接收字符串参数")]
        public virtual object TableCount(string sqlString,string[] para)
        {
            object count=0;
            try
            {
                ConnectionPrepare(true);
                OleDbCommand Cmd=OraCon.CreateCommand();
                Cmd.CommandText=sqlString;
                //
                for(int i=0;i<para.Length;i++)
                {
                    OleDbParameter parameter=new OleDbParameter(@"para"+i.ToString(),OleDbType.VarChar,para[i].Length);
                    parameter.Value=para[i];
                    Cmd.Parameters.Add(parameter);
                }
                count=Cmd.ExecuteScalar();
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionPrepare(false);
            }
            //
            return count;
        }

        /**//// <summary>
        /// 执行Dml,Ddl操作，如update,insert,delete，返回影响的行数,
        /// 如果是Ddl操作，如建立表，那么返回-1
        /// </summary>
        /// <param name="dmlString">dml,ddl语句</param>
        /// <returns>受影响的行数</returns>
        [LastModified("2005-07-26","Dml,Ddl语句处理")]
        public virtual int DmlDdl(string dmlString)
        {
            int count=0;
            try
            {
                ConnectionPrepare(true);
                OleDbCommand Cmd=OraCon.CreateCommand();
                Cmd.CommandText=dmlString;
                //
                count=(int)Cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionPrepare(false);
            }
            //
            return count;
        }
        /**//// <summary>
        /// 执行Dml，ddl操作的方法的重载，处理参数为字符串数组的参数
        /// </summary>
        /// <param name="dmlString">Dml,ddl语句</param>
        /// <param name="para">字符串数组</param>
        /// <returns>返回值</returns>
        public virtual int DmlDdl(string dmlString,string[] para)
        {
            int count=0;
            try
            {
                ConnectionPrepare(true);
                OleDbCommand Cmd=OraCon.CreateCommand();
                Cmd.CommandText=dmlString;
                //
                for(int i=0;i<para.Length;i++)
                {
                    OleDbParameter parameter=new OleDbParameter(@"para"+i.ToString(),OleDbType.VarChar,para[i].Length);
                    parameter.Value=para[i];
                    Cmd.Parameters.Add(parameter);
                }
                //
                count=(int)Cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionPrepare(false);
            }
            //
            return count;
        }
        /**//// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="sqlString">查询字符</param>
        /// <returns>返回DataTable</returns>
        public DataTable GetDataTable(string sqlString)
        {
            DataTable dt=new DataTable();
            try
            {
                ConnectionPrepare(true);
                OleDbCommand Cmd=OraCon.CreateCommand();
                Cmd.CommandText=sqlString;
                //
                OleDbDataAdapter adapter=new OleDbDataAdapter(Cmd);
                adapter.Fill(dt);
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);            
            }
            finally
            {
                ConnectionPrepare(false);
            }
            return dt;
        }
        /**//// <summary>
        /// 返回DataTable，重载，接收字符串数组参数
        /// </summary>
        /// <param name="sqlString">查询命令</param>
        /// <param name="para">字符串数组参数</param>
        /// <returns>返回DataTable</returns>
        public DataTable GetDataTable(string sqlString,string[] para)
        {
            DataTable dt=new DataTable();
            try
            {
                ConnectionPrepare(true);
                OleDbCommand Cmd=OraCon.CreateCommand();
                Cmd.CommandText=sqlString;
                //parameter
                for(int i=0;i<para.Length;i++)
                {
                    OleDbParameter parameter=new OleDbParameter(@"para"+i.ToString(),OleDbType.VarChar,para[i].Length);
                    parameter.Value=para[i];
                    Cmd.Parameters.Add(parameter);
                }
                //
                OleDbDataAdapter adapter=new OleDbDataAdapter(Cmd);
                adapter.Fill(dt);
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);            
            }
            finally
            {
                ConnectionPrepare(false);
            }
            return dt;
        }
        /**//// <summary>
        /// 调用存储过程，针对没有参数的
        /// </summary>
        /// <param name="procedureName">过程名称</param>
        [LastModified("2005-07-27","调用没有参数的存储过程")]
        public virtual void CallProcedure(string procedureName)
        {
            try
            {
                ConnectionPrepare(true);
                OleDbCommand Cmd=OraCon.CreateCommand();
                Cmd.CommandType=CommandType.StoredProcedure;
                Cmd.CommandText=procedureName;
                //
                Cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionPrepare(false);
            }
        }
        /**//// <summary>
        /// 调用存储过程的重载，接收一个字符数组参数
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="para1">接收的参数</param>
        [LastModified("2005-07-27","调用参数是字符的存储过程，接收的参数是字符数组")]
        public virtual void CallProcedure(string procedureName,string[] para)
        {
            try
            {
                ConnectionPrepare(true);
                OleDbCommand Cmd=OraCon.CreateCommand();
                Cmd.CommandType=CommandType.StoredProcedure;
                Cmd.CommandText=procedureName;
                //
                for(int i=0;i<para.Length;i++)
                {
                    OleDbParameter Parameter=new OleDbParameter(@"para"+i,OleDbType.VarChar,para[i].Length);
                    Parameter.Value=para[i];
                    Cmd.Parameters.Add(Parameter);
                }
                                
                //
                Cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionPrepare(false);
            }
        }

        /**//// <summary>
        /// 调用存储过程重载，接收一个数值型数组参数
        /// </summary>
        /// <param name="procedureName">过程名</param>
        /// <param name="intValue">数值型数组参数</param>
        [LastModified("2005-07-27","接收数值型数组作为参数，调用存储过程")]
        public virtual void CallProcedure(string procedureName,int[] intValue)
        {
            try
            {
                ConnectionPrepare(true);
                OleDbCommand Cmd=OraCon.CreateCommand();
                Cmd.CommandType=CommandType.StoredProcedure;
                Cmd.CommandText=procedureName;
                //
                for(int i=0;i<intValue.Length;i++)
                {
                    OleDbParameter Parameter=new OleDbParameter(@"para"+i,OleDbType.Integer);
                    Parameter.Value=intValue[i];
                    Cmd.Parameters.Add(Parameter);
                }
                                
                //
                Cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionPrepare(false);
            }
        }
        /**//// <summary>
        /// 调用存储过程，重载，接收object数组，针对参数是字符，数值混合型的过程
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="para1"></param>
        /// <param name="para2"></param>
        [LastModified("2005-07-27","接收Object型数组作为参数，调用存储过程")]
        public virtual void CallProcedure(string procedureName,object[] objectValue)
        {
            try
            {
                ConnectionPrepare(true);
                OleDbCommand Cmd=OraCon.CreateCommand();
                Cmd.CommandType=CommandType.StoredProcedure;
                Cmd.CommandText=procedureName;
                //
                for(int i=0;i<objectValue.Length;i++)
                {
                    string valueType=objectValue[i].GetType().ToString();
                    if(valueType=="System.String")
                    {
                        OleDbParameter Parameter=new OleDbParameter(@"para"+i,OleDbType.VarChar,objectValue[i].ToString().Length);
                        Parameter.Value=objectValue[i].ToString();
                        Cmd.Parameters.Add(Parameter);
                    }
                    else if(valueType=="System.Int32")
                    {
                        OleDbParameter Parameter=new OleDbParameter(@"para"+i,OleDbType.Integer);
                        Parameter.Value=objectValue[i];
                        Cmd.Parameters.Add(Parameter);
                    }
                
                }
                //
                Cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionPrepare(false);
            }
        }
        /**//// <summary>
        /// 通用调用存储过程的方法，提供字符参数的支持
        /// 使用OleDbCommandBuider获取存储过程参数名称和类型
        /// 可以直接赋值使用，缺点：增加一次数据库访问，不适合在频繁访问中使用
        /// </summary>
        /// <param name="procedureName">存储过程名</param>
        /// <param name="paraValue">字符参数值的数组</param>    
        public virtual void CallProcedureCommon(string procedureName,string[] paraValue)
        {
            try
            {
                ConnectionPrepare(true);
                OleDbCommand Cmd=OraCon.CreateCommand();
                Cmd.CommandType=CommandType.StoredProcedure;
                Cmd.CommandText=procedureName;

                //获取过程的参数信息和类型，产生一次数据库访问
                OleDbCommandBuilder.DeriveParameters(Cmd);
            
                //
                for(int i=0;i<paraValue.Length;i++)
                {
                    Cmd.Parameters[i].Value=paraValue[i];                    
                }
                //
                Cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionPrepare(false);
            }
        }
    }
}