c#实现的操作oracle通用类

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   #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  //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝  //　＝＝＝＝＝＝＝＝＝内部调用函数完＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝  //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ } }