您的当前位置: 源码爱好者 » 网页制作教程(编程教程) » C#技术教程

C# Oracle连接 操作类

  • 标签:数据库连接类 Oracle  更新时间:2013-11-18
  • C# Oracle连接类,操作类代码,可执行超多常用的Oracle数据库操作,包含了基础数据库连接、关闭连接、输出记录集、执行Sql语句,返回带分页功能的dataset 、取表里字段的类型和长度等,同时还有哈稀表自动插入数据库等高级任务。需要说明的是:执行SQL语句,返回 DataReader,用之前一定要先.read()打开,然后才能读到数据,用hashTable对数据库进行insert,update,del操作,注意此时只能用默认的数据库连接"connstr"。

    using System;
    using System.Data;
    using System.Data.OracleClient;
    using System.Collections;
    using System.Reflection;
    namespace MyOraComm
    {
      /// ConnDbForOracle 的摘要说明。
      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 带参数的构造函数 
       /// 带参数的构造函数
       /// 数据库联接字符串
       public ConnForOracle(string ConnString)
       {
        string connStr;
        connStr = System.Configuration.ConfigurationSettings.AppSettings[ConnString].ToString();
        Connection = new OracleConnection(connStr);
       }
       #endregion
    
       #region 打开数据库 
       /// 打开数据库
       public void  OpenConn()
       {
        if(this.Connection.State!=ConnectionState.Open)
         this.Connection.Open();
       }
       #endregion
       #region 关闭数据库联接 
       /// 关闭数据库联接
       public void CloseConn()
       {
        if(Connection.State==ConnectionState.Open)
         Connection.Close();
       }
       #endregion
    
       #region 执行SQL语句,返回数据到DataSet中
       /// 执行SQL语句,返回数据到DataSet中
       /// sql语句
       /// 自定义返回的DataSet表名
       /// 返回DataSet
       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 
       /// 执行Sql语句,返回带分页功能的dataset
       /// Sql语句
       /// 每页显示记录数
       /// <当前页/param>
       /// 返回dataset表名
       /// 返回DataSet
       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()打开,然后才能读到数据
       /// 执行SQL语句,返回 DataReader,用之前一定要先.read()打开,然后才能读到数据 
       /// sql语句
       /// 返回一个OracleDataReader
       public OracleDataReader ReturnDataReader(String sql)
       {
        OpenConn();
        OracleCommand command = new OracleCommand(sql,Connection);
        return command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
       }
       #endregion
    
       #region 执行SQL语句,返回记录总数数
       /// 执行SQL语句,返回记录总数数
       /// sql语句
       /// 返回记录总条数
       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
       /// 
       /// 取当前序列
       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语句,返回所影响的行数
       /// 执行SQL语句,返回所影响的行数
       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
      
       #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语句(参数类型的)
       /// 根据哈稀表及表名自动生成相应insert语句 
       /// 要插入的表名
       /// 哈稀表
       /// 返回sql语句
       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参数
       /// 
       /// 生成oracle参数
       /// 字段名
       /// 数据类型
       /// 数据大小
       /// 值
       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
      }
     }

     建议可将上述代码复制成oracle_dbconn.cs文件,以方便调用。

  • 已读: 次 收藏本文关闭本文打印本文复制链接