C# Oracle数据库操作类实例详解

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
 }
 }