c#操作oracle，有没有类似sqlhelp之类的通用操作类

#region 根据相关条件对数据库进行更新操作 用法：Update("test","Id=:Id",ht); 

   public int Update(string TableName,string ht_Where, Hashtable ht)

   {

    OracleParameter[] Parms=new OracleParameter[ht.Count];

    IDictionaryEnumerator et = ht.GetEnumerator();

    DataTable dt=GetTabType(TableName);

    System.Data.OracleClient.OracleType otype;

    int size=0;

    int i=0;

    // 作哈希表循环

    while ( et.MoveNext() )

    {

     GetoType(et.Key.ToString().ToUpper(),dt,out otype,out size);

     System.Data.OracleClient.OracleParameter op=MakeParam(":"+et.Key.ToString(),otype,size,et.Value.ToString());

     Parms[i]=op; // 添加SqlParameter对象

     i=i+1;

    }

    string str_Sql=GetUpdateSqlbyHt(TableName,ht_Where,ht); // 获得插入sql语句

    int val=ExecuteNonQuery(str_Sql,Parms);

    return val;

   }

   #endregion

  

   #region del操作,注意此处条件个数与hash里参数个数应该一致 用法：Del("test","Id=:Id",ht)

   public int Del(string TableName,string ht_Where,Hashtable ht)

   {

    OracleParameter[] Parms=new OracleParameter[ht.Count];

    IDictionaryEnumerator et = ht.GetEnumerator();

    DataTable dt=GetTabType(TableName);

    System.Data.OracleClient.OracleType otype;

    int i=0;

    int size=0;

    // 作哈希表循环

    while ( et.MoveNext() )

    {

     GetoType(et.Key.ToString().ToUpper(),dt,out otype,out size);

     System.Data.OracleClient.OracleParameter op=MakeParam(":"+et.Key.ToString(),et.Value.ToString());

     Parms[i]=op; // 添加SqlParameter对象

     i=i+1;

    }

    string str_Sql=GetDelSqlbyHt(TableName,ht_Where,ht); // 获得删除sql语句

    int val=ExecuteNonQuery(str_Sql,Parms);

    return val;

   }

   #endregion 



   //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

   //　＝＝＝＝＝＝＝＝上面三个操作的内部调用函数＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

   //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ 



   #region 根据哈稀表及表名自动生成相应insert语句(参数类型的)

   /// <summary>

   /// 根据哈稀表及表名自动生成相应insert语句

   /// </summary>

   /// <param name="TableName">要插入的表名</param>

   /// <param name="ht">哈稀表</param>

   /// <returns>返回sql语句</returns>

   public static string GetInsertSqlbyHt(string TableName,Hashtable ht)

   {

    string str_Sql="";

    int i=0;

    int ht_Count=ht.Count; // 哈希表个数

    IDictionaryEnumerator myEnumerator = ht.GetEnumerator();

    string before="";

    string behide="";

    while ( myEnumerator.MoveNext() )

    {

     if (i==0)

     {

      before="("+myEnumerator.Key;

     }

     else if (i+1==ht_Count)

     {

      before=before+","+myEnumerator.Key+")";

     }

     else

     {

      before=before+","+myEnumerator.Key;

     }

     i=i+1;

    }

    behide=" Values"+before.Replace(",",",:").Replace("(","(:");

    str_Sql="Insert into "+TableName+before+behide;

    return str_Sql;

   }

   #endregion



   #region 根据表名，where条件，哈稀表自动生成更新语句(参数类型的)

   public static string GetUpdateSqlbyHt(string Table,string ht_Where,Hashtable ht)

   {

    string str_Sql="";

    int i=0;

    int ht_Count=ht.Count; // 哈希表个数

    IDictionaryEnumerator myEnumerator = ht.GetEnumerator();

    while ( myEnumerator.MoveNext() )

    {

     if (i==0)

     {

      if (ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key+"=:"+myEnumerator.Key).ToLower())==-1)

      {

       str_Sql=myEnumerator.Key+"=:"+myEnumerator.Key;

      }

     }

     else

     {

      if (ht_Where.ToString().ToLower().IndexOf((":"+myEnumerator.Key+" ").ToLower())==-1)

      {

       str_Sql=str_Sql+","+myEnumerator.Key+"=:"+myEnumerator.Key;

      }

     

     }

     i=i+1;

    }

    if (ht_Where==null || ht_Where.Replace(" ","")=="")  // 更新时候没有条件

    {

     str_Sql="update "+Table+" set "+str_Sql;

    }

    else

    {

     str_Sql="update "+Table+" set "+str_Sql+" where "+ht_Where;

    }

    str_Sql=str_Sql.Replace("set ,","set ").Replace("update ,","update ");

    return str_Sql;

   }

   #endregion



   #region 根据表名，where条件，哈稀表自动生成del语句(参数类型的)

   public static string GetDelSqlbyHt(string Table,string ht_Where,Hashtable ht)

   {

    string str_Sql="";

    int i=0;

   

    int ht_Count=ht.Count; // 哈希表个数

    IDictionaryEnumerator myEnumerator = ht.GetEnumerator();

    while ( myEnumerator.MoveNext() )

    {

     if (i==0)

     {

      if (ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key+"=:"+myEnumerator.Key).ToLower())==-1)

      {

       str_Sql=myEnumerator.Key+"=:"+myEnumerator.Key;

      }

     }

     else

     {

      if (ht_Where.ToString().ToLower().IndexOf((":"+myEnumerator.Key+" ").ToLower())==-1)

      {

       str_Sql=str_Sql+","+myEnumerator.Key+"=:"+myEnumerator.Key;

      }

     

     }

     i=i+1;

    }

    if (ht_Where==null || ht_Where.Replace(" ","")=="")  // 更新时候没有条件

    {

     str_Sql="Delete "+Table;

    }

    else

    {

     str_Sql="Delete "+Table+" where "+ht_Where;

    }

    return str_Sql;

   }

   #endregion



   #region 生成oracle参数

   /// <summary>

   /// 生成oracle参数

   /// </summary>

   /// <param name="ParamName">字段名</param>

   /// <param name="otype">数据类型</param>

   /// <param name="size">数据大小</param>

   /// <param name="Value">值</param>

   /// <returns></returns>

   public static OracleParameter MakeParam(string ParamName,System.Data.OracleClient.OracleType otype,int size,Object Value) 

   {

    OracleParameter para=new OracleParameter(ParamName,Value);

    para.OracleType=otype;

    para.Size=size;

    return para;

   }

   #endregion



   #region 生成oracle参数

   public static OracleParameter MakeParam(string ParamName,string Value) 

   {

    return new OracleParameter(ParamName, Value);

   }

   #endregion



   #region 根据表结构字段的类型和长度拼装oracle sql语句参数

   public static void GetoType(string key,DataTable dt,out System.Data.OracleClient.OracleType otype,out int size)

   {



    DataView dv=dt.DefaultView;

    dv.RowFilter="column_name='"+key+"'";

    string fType=dv[0]["data_type"].ToString().ToUpper();

    switch (fType)

    {

     case "DATE":

      otype= OracleType.DateTime;

      size=int.Parse(dv[0]["data_length"].ToString());

      break;

     case "CHAR":

      otype= OracleType.Char;

      size=int.Parse(dv[0]["data_length"].ToString());

      break;

     case "LONG":

      otype= OracleType.Double;

      size=int.Parse(dv[0]["data_length"].ToString());

      break;

     case "NVARCHAR2":

      otype= OracleType.NVarChar;

      size=int.Parse(dv[0]["data_length"].ToString());

      break;

     case "VARCHAR2":

      otype= OracleType.NVarChar;

      size=int.Parse(dv[0]["data_length"].ToString());

      break;

     default:

      otype= OracleType.NVarChar;

      size=100;

      break;

    }

   }

   #endregion



   #region动态 取表里字段的类型和长度,此处没有动态用到connstr,是默认的！by/文少

   public System.Data.DataTable GetTabType(string tabnale)

   {

    string sql="select  column_name,data_type,data_length from all_tab_columns where table_name='"+tabnale.ToUpper()+"'";

    OpenConn();

    return (ReturnDataSet(sql,"dv")).Tables[0];

   

   }

   #endregion



   #region 执行sql语句 

   public int ExecuteNonQuery(string cmdText, params OracleParameter[] cmdParms) 

   {

   

    OracleCommand cmd = new OracleCommand();

    OpenConn();  

    cmd.Connection=Connection;

    cmd.CommandText = cmdText;

    if (cmdParms != null) 

    {

     foreach (OracleParameter parm in cmdParms)

      cmd.Parameters.Add(parm);

    }

    int val = cmd.ExecuteNonQuery();

    cmd.Parameters.Clear();

    //   conn.CloseConn();

    return val;

   }

   #endregion



   //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

   //　＝＝＝＝＝＝＝＝＝内部调用函数完＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝



   //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

  }

 } 



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/lee576/archive/2008/12/04/3445823.aspx

using System;

 using System.Data;

 using System.Data.OracleClient;

 using System.Collections;

 using System.Reflection;

 namespace MyOraComm

 {

  /// <summary>

  /// ConnDbForOracle 的摘要说明。

  /// </summary>

  public class ConnForOracle

  {

   protected OracleConnection Connection;

   private string connectionString;

   public ConnForOracle()

   {

    string connStr;

    connStr = System.Configuration.ConfigurationSettings.AppSettings["connStr"].ToString();

    connectionString = connStr;

    Connection = new OracleConnection(connectionString);

   }

  

   #region 带参数的构造函数

   /// <summary>

   /// 带参数的构造函数

   /// </summary>

   /// <param name="ConnString">数据库联接字符串</param>

   public ConnForOracle(string ConnString)

   {

    string connStr;

    connStr = System.Configuration.ConfigurationSettings.AppSettings[ConnString].ToString();

    Connection = new OracleConnection(connStr);

   }

   #endregion



   #region 打开数据库

   /// <summary>

   /// 打开数据库

   /// </summary>

   public void  OpenConn()

   {

    if(this.Connection.State!=ConnectionState.Open)

     this.Connection.Open();

   }

   #endregion

   #region 关闭数据库联接

   /// <summary>

   /// 关闭数据库联接

   /// </summary>

   public void CloseConn()

   {

    if(Connection.State==ConnectionState.Open)

     Connection.Close();

   }

   #endregion



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

   /// <summary>

   /// 执行SQL语句，返回数据到DataSet中

   /// </summary>

   /// <param name="sql">sql语句</param>

   /// <param name="DataSetName">自定义返回的DataSet表名</param>

   /// <returns>返回DataSet</returns>

   public DataSet ReturnDataSet(string sql,string DataSetName)

   {

    DataSet dataSet=new DataSet();

    OpenConn();

    OracleDataAdapter OraDA=new OracleDataAdapter(sql,Connection);

    OraDA.Fill(dataSet,DataSetName);

    //   CloseConn();

    return dataSet;

   }

   #endregion



   #region 执行Sql语句,返回带分页功能的dataset

   /// <summary>

   /// 执行Sql语句,返回带分页功能的dataset

   /// </summary>

   /// <param name="sql">Sql语句</param>

   /// <param name="PageSize">每页显示记录数</param>

   /// <param name="CurrPageIndex"><当前页/param>

   /// <param name="DataSetName">返回dataset表名</param>

   /// <returns>返回DataSet</returns>

   public DataSet ReturnDataSet(string sql,int PageSize,int CurrPageIndex,string DataSetName)

   {

    DataSet dataSet=new DataSet();

    OpenConn();

    OracleDataAdapter OraDA=new OracleDataAdapter(sql,Connection);

    OraDA.Fill(dataSet,PageSize * (CurrPageIndex - 1), PageSize,DataSetName);

    //   CloseConn();

    return dataSet;

   }

   #endregion



   #region 执行SQL语句，返回 DataReader,用之前一定要先.read()打开,然后才能读到数据

   /// <summary>

   /// 执行SQL语句，返回 DataReader,用之前一定要先.read()打开,然后才能读到数据

   /// </summary>

   /// <param name="sql">sql语句</param>

   /// <returns>返回一个OracleDataReader</returns>

   public OracleDataReader ReturnDataReader(String sql)

   {

    OpenConn();

    OracleCommand command = new OracleCommand(sql,Connection);

    return command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

   }

   #endregion



   #region 执行SQL语句，返回记录总数数

   /// <summary>

   /// 执行SQL语句，返回记录总数数

   /// </summary>

   /// <param name="sql">sql语句</param>

   /// <returns>返回记录总条数</returns>

   public int GetRecordCount(string sql)

   {

    int recordCount = 0;

    OpenConn();

    OracleCommand command = new OracleCommand(sql,Connection);

    OracleDataReader dataReader = command.ExecuteReader();

    while(dataReader.Read())

    {

     recordCount++;

    }

    dataReader.Close();

    //   CloseConn();

    return recordCount;

   }

   #endregion



   #region 取当前序列,条件为seq.nextval或seq.currval

   /// <summary>

   /// 取当前序列

   /// </summary>

   /// <param name="seqstr"></param>

   /// <param name="table"></param>

   /// <returns></returns>

   public decimal GetSeq(string seqstr)

   {

    decimal seqnum = 0;

    string sql="select "+seqstr+" from dual";

    OpenConn();

    OracleCommand command = new OracleCommand(sql,Connection);

    OracleDataReader dataReader = command.ExecuteReader();

    if(dataReader.Read())

    {

     seqnum=decimal.Parse(dataReader[0].ToString());

    }

    dataReader.Close();

    //   CloseConn();

    return seqnum;

   }

   #endregion



   #region 执行SQL语句,返回所影响的行数

   /// <summary>

   /// 执行SQL语句,返回所影响的行数

   /// </summary>

   /// <param name="sql"></param>

   /// <returns></returns>

   public int  ExecuteSQL(string sql)

   {

    int Cmd=0;

    OpenConn();

    OracleCommand command = new OracleCommand(sql,Connection);

    try

    {

     Cmd =command.ExecuteNonQuery(); 

    }

    catch

    {

    

    }

    finally

    {

     //    CloseConn();

    }



    return Cmd;

   }

   #endregion



   //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ 

   //　＝＝用hashTable对数据库进行insert,update,del操作,注意此时只能用默认的数据库连接"connstr"＝＝

   //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝



   #region 根据表名及哈稀表自动插入数据库 用法：Insert("test",ht)

   public int Insert(string TableName,Hashtable ht)

   {

    OracleParameter[] Parms=new OracleParameter[ht.Count];

    IDictionaryEnumerator et = ht.GetEnumerator();

    DataTable dt=GetTabType(TableName);

    System.Data.OracleClient.OracleType otype;

    int size=0;

    int i=0;

   

    while ( et.MoveNext() ) // 作哈希表循环

    {

     GetoType(et.Key.ToString().ToUpper(),dt,out otype,out size);

     System.Data.OracleClient.OracleParameter op=MakeParam(":"+et.Key.ToString(),otype,size,et.Value.ToString());

     Parms[i]=op; // 添加SqlParameter对象

     i=i+1;

    }

    string str_Sql=GetInsertSqlbyHt(TableName,ht); // 获得插入sql语句

    int val=ExecuteNonQuery(str_Sql,Parms);

    return val;

   }

   #endregion







本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/lee576/archive/2008/12/04/3445823.aspx

using System;

using System.Data;

using System.Data.OracleClient; 

class Sample

{

    public static void Main() 

    {

        string myConnString = "user id=zk;data source=zkqx;password=*****";

        OracleConnection myConnection = new OracleConnection(myConnString);



        OracleCommand catCMD = myConnection.CreateCommand();

        catCMD.CommandText = " SELECT CZYDM,CZYMC,CZYMM,CZYQX FROM ZC_CZY ";



        myConnection.Open();



        OracleDataReader myReader = catCMD.ExecuteReader();



        while (myReader.Read())

        {

            Console.WriteLine("\t{0}\t{1}\t{2}\t{3}", myReader.GetString(0),myReader.GetString(1),myReader.GetString(2),myReader.GetString(3));

        }



        myReader.Close();



        myConnection.Close();

    }

}

        /// <summary>

        /// 更新数据集. 

        /// 过程:客户层(dataSet.GetChanges()) -- 修改 --> 数据服务层(hasChangesDataSet.update()) -- 更新--> 数据层(hasChangesDataSet) ...

        ///  数据层(hasChangesDataSet) -- 新数据 --> 数据服务层 (hasChangesDataSet) -- 合并 -- > 客户层(dataSet.Merge(hasChangesDataSet))

        /// </summary>

        /// <param name="hasChangeDataSet"></param>

        /// <returns></returns>

        public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet)

        {

            if (sql == null || sql == string.Empty)

            {

                throw new Exception("抱歉，SQL 语句为空...");

            }

            using (oracleConnection = this.GetOracleConnection())

            {

                if (oracleConnection == null)

                    return null;

                using (OracleDataAdapter da = new OracleDataAdapter(sql, oracleConnection))

                {

                    try

                    {

                        OracleCommandBuilder cb = new OracleCommandBuilder(da);

                        da.Update(hasChangesDataSet);

                    }

                    catch (Exception ex)

                    {

                        throw ex;

                    }

                    return hasChangesDataSet;

                }

            }

        }



        /// <summary>

        /// 将一组 UPDATE、INSERT 和 DELETE 语句以事务执行

        /// </summary>

        /// <param name="sqls"></param>

        /// <returns>是否执行成功</returns>

        public bool ExecuteTransaction(string[] sqls)

        {

            if (sqls == null || sqls.Length == 0)

            {

                throw new Exception("抱歉，SQL 语句为空...");

            }

            using (oracleConnection = this.GetOracleConnection())

            {

                if (oracleConnection == null)

                    return false;

                OracleTransaction oracleTransaction = null;

                try

                {

                    if (oracleConnection.State == System.Data.ConnectionState.Closed)

                        oracleConnection.Open();

                    oracleCommand = oracleConnection.CreateCommand();

                    oracleTransaction = oracleConnection.BeginTransaction();

                    oracleCommand.Connection = oracleConnection;

                    oracleCommand.Transaction = oracleTransaction;



                    for (int i = 0; i < sqls.Length; i++)

                    {

                        oracleCommand.CommandText = sqls[i];

                        oracleCommand.ExecuteNonQuery();

                    }

                    oracleTransaction.Commit();



                    return true;

                }

                catch (Exception ex)

                {

                    if (oracleTransaction != null)

                        oracleTransaction.Rollback();



                    throw ex;

                }

            }

        }

        public bool ExecuteTransaction(string[] sqls, object[][] myParams)

        {

            throw new Exception("抱歉，未实现...");

        }



        /// <summary>

        ///  执行Sql数组语句查询，并将查询返回的结果作为一个数据读取器返回

        /// </summary>

        /// <param name="sql"></param>

        /// <returns>OracleDataReader</returns>

        public OracleDataReader RetriveDataReader(string sql)

        {

            if (sql == null || sql == string.Empty)

            {

                throw new Exception("抱歉，SQL 语句为空...");

            }

            using (oracleConnection = this.GetOracleConnection())

            {

                if (oracleConnection == null)

                    return null;

                using (oracleCommand = new OracleCommand(sql, oracleConnection))

                {

                    try

                    {

                        OracleDataReader oracleDataReader = oracleCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

                        return oracleDataReader;

                    }

                    catch (Exception ex)

                    {

                        throw ex;

                    }

                }

            }

        }



        /// <summary>

        /// 执行一个查询式的存贮过程,返回得到的数据集

        /// </summary>

        /// <param name="proceName">存贮过程名称</param>

        /// <param name="myParams">所有属性值</param>

        /// <returns></returns>

        public DataSet ExecStoredProcedure(string proceName, object[] myParams)

        {

            if (proceName == null || proceName == string.Empty)

            {

                throw new Exception("抱歉，存贮过程名称为空...");

            }



            using (oracleConnection = this.GetOracleConnection())

            {

                if (oracleConnection == null)

                    return null;



                DataSet ds = new DataSet();



                try

                {

                    if (oracleConnection.State == System.Data.ConnectionState.Closed)

                        oracleConnection.Open();

                    oracleCommand = oracleConnection.CreateCommand();

                    oracleCommand.CommandType = CommandType.StoredProcedure;

                    oracleCommand.CommandText = proceName;

                    if (myParams != null)

                    {

                        for (int i = 0; i < myParams.Length; i++)

                            oracleCommand.Parameters.Add((OracleParameter)myParams[i]);

                    }

                    using (OracleDataAdapter da = new OracleDataAdapter(oracleCommand))

                    {

                        int returnValue = da.Fill(ds);

                        if (returnValue < 0)

                            throw new Exception("存储过程执行错误:" + (returnValue >= -14 ?

                                ((StoreProcReturn)returnValue).ToString() : "ErrCode:" + returnValue));

                        return ds;

                    }

                }

                catch (Exception ex)

                {

                    throw ex;

                }

            }

        }



        /// <summary>

        /// 执行一个非查询式的存贮过程

        /// </summary>

        /// <param name="proceName">存贮过程名称</param>

        /// <param name="myParams">所有属性值</param>

        /// <returns>存储过程return值</returns>

        public int ExecNonQueryStoredProcedure(string proceName, ref object[] myParams)

        {

            if (proceName == null || proceName == string.Empty)

            {

                throw new Exception("抱歉，存贮过程名称为空...");

            }



            using (oracleConnection = this.GetOracleConnection())

            {

                if (oracleConnection == null)

                    throw new Exception("抱歉，数据库连接没有初始化...");



                try

                {

                    if (oracleConnection.State == System.Data.ConnectionState.Closed)

                        oracleConnection.Open();

                    oracleCommand = oracleConnection.CreateCommand();

                    oracleCommand.CommandType = CommandType.StoredProcedure;

                    oracleCommand.CommandText = proceName;

                    if (myParams != null)

                    {

                        for (int i = 0; i < myParams.Length; i++)

                            oracleCommand.Parameters.Add((OracleParameter)myParams[i]);

                    }

                    int returnValue = oracleCommand.ExecuteNonQuery();

                    if (returnValue < 0)

                        throw new Exception("存储过程执行错误:" + (returnValue >= -14 ?

                            ((StoreProcReturn)returnValue).ToString() : "ErrCode:" + returnValue));

                    return returnValue;

                }

                catch (Exception ex)

                {

                    throw ex;

                }

            }

        }



        public void Dispose()

        {

            this.connectionString = null;

            if (this.oracleCommand != null)

                this.oracleCommand.Dispose();

            if (this.oracleConnection != null)

                this.oracleConnection.Dispose();

        }

    }

}

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;



using System.Data.OracleClient;

using CommLB.wmj.AllExceptionsCatch.ExceptionObjs;



namespace CommLB.wmj.AllDataProdivers.Command

{

    /// <summary>

    /// OracleDataProvider 的摘要说明

    /// </summary>

    internal class OracleDataProvider : CommLB.wmj.AllDataProdivers.CommandOfInterface.IDataProvider

    {

        private System.Data.OracleClient.OracleConnection oracleConnection;

        private System.Data.OracleClient.OracleCommand oracleCommand;

        private string connectionString;

        public OracleDataProvider()

            : this(null)

        {

            //

            // TODO: 在此处添加构造函数逻辑

            //

        }



        public OracleDataProvider(string connectionString)

        {

            if (connectionString == null || connectionString.Trim() == string.Empty)

            {

                System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();

                this.connectionString = (string)(configurationAppSettings.GetValue("oracleConnectionString", typeof(string)));

            }

            else

            {

                this.connectionString = connectionString;

            }

        }



        /// <summary>

        /// Oracle 连接字符串 "User Id=southfence;Data Source=FENCEORA;Password=southfence;Persist Security Info=true;"    

        /// </summary>

        public string ConnectionString

        {

            get

            {

                return this.connectionString;

            }

            set

            {

                this.connectionString = value;

            }

        }



        /// <summary>

        /// 返回一个带有连接字符串的Oracle Connection.

        /// </summary>

        /// <returns>OracleConnection</returns>

        private OracleConnection GetOracleConnection()

        {

            try

            {

                return new OracleConnection(this.connectionString);

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }



        /// <summary>

        /// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1

        /// </summary>

        /// <param name="Sql">UPDATE、INSERT 和 DELETE 语句</param>

        public int ExecuteNonQuery(string sql)

        {

            using (oracleConnection = this.GetOracleConnection())

            {

                if (oracleConnection == null)

                    return -1;

                int rv = -1;

                OracleTransaction oracleTransaction = null;

                try

                {

                    if (oracleConnection.State == System.Data.ConnectionState.Closed)

                        oracleConnection.Open();

                    oracleCommand = new OracleCommand(sql, oracleConnection);

                    oracleTransaction = oracleConnection.BeginTransaction();

                    oracleCommand.Transaction = oracleTransaction;

                    rv = oracleCommand.ExecuteNonQuery();

                    oracleTransaction.Commit();

                }

                catch (Exception ex)

                {

                    oracleTransaction.Rollback();

                    rv = -1;

                    throw ex;

                }



                return rv;

            }

        }



        /// <summary>

        /// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。

        /// </summary>

        /// <param name="sql">SELECT 语句</param>

        /// <returns>.NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用</returns>

        public object ExecuteScalar(string sql)

        {

            using (oracleConnection = this.GetOracleConnection())

            {

                if (oracleConnection == null)

                    return null;

                try

                {

                    if (oracleConnection.State == System.Data.ConnectionState.Closed)

                        oracleConnection.Open();

                    oracleCommand = new OracleCommand(sql, oracleConnection);

                    return oracleCommand.ExecuteScalar();

                }

                catch (Exception ex)

                {

                    throw ex;

                }

            }

        }



        /// <summary>

        ///  执行单Sql语句查询，并将查询返回的结果作为一个数据集返回

        /// </summary>

        /// <param name="selectSql">SELECT 语句</param>

        /// <returns>数据集 DataSet</returns>

        public DataSet RetriveDataSet(string sql)

        {

            if (sql == null || sql == string.Empty)

            {

                throw new Exception("抱歉，SQL 语句为空...");

            }

            using (oracleConnection = this.GetOracleConnection())

            {

                if (oracleConnection == null)

                    return null;

                using (OracleDataAdapter da = new OracleDataAdapter(sql, oracleConnection))

                {

                    DataSet ds = new DataSet();

                    try

                    {

                        da.Fill(ds);

                    }

                    catch (Exception ex)

                    {

                        throw ex;

                    }

                    return ds;

                }

            }

        }



        /// <summary>

        /// 执行Sql数组语句查询，并将查询返回的结果作为一个数据集返回

        /// </summary>

        /// <param name="sql">Select 语句数组</param>

        /// <param name="tableName">TableName</param>

        /// <returns>数据集 DataSet</returns>

        public DataSet RetriveDataSet(string[] sqls, params string[] tableNames)

        {

            if (sqls == null || sqls.Length == 0)

            {

                throw new Exception("抱歉，SQL 语句为空...");

            }

            int sqlLength;

            sqlLength = sqls.Length;

            using (oracleConnection = this.GetOracleConnection())

            {

                if (oracleConnection == null)

                    return null;

                DataSet ds = new DataSet();

                int tableNameLength = tableNames.Length;

                for (int i = 0; i < sqlLength; i++)

                {

                    using (OracleDataAdapter da = new OracleDataAdapter(sqls[i], oracleConnection))

                    {

                        try

                        {

                            if (i < tableNameLength)

                                da.Fill(ds, tableNames[i]);

                            else

                                da.Fill(ds, "table" + i);

                        }

                        catch (Exception ex)

                        {

                            throw ex;

                        }

                    }

                }

                return ds;

            }

        }



        public DataSet RetriveDataSet(string[,] sql_tableNames)

        {

            if (sql_tableNames.GetLength(0) != 2)

            {

                throw new Exception("抱歉，SQL语句-表名 参数必须为二维数组...");

            }

            int length = sql_tableNames.GetLength(1);

            if (length <= 0)

            {

                throw new Exception("抱歉，SQL语句-表名 为空...");

            }



            string[] sqls = new string[length];

            string[] tableNames = new string[length];

            for (int i = 0; i < length; i++)

            {

                sqls[i] = sql_tableNames[i, 0];

                tableNames[i] = sql_tableNames[i, 1];

            }



            return RetriveDataSet(sqls, tableNames);

        }