欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

c#实现的操作oracle通用类

程序员文章站 2023-12-17 22:10:46
修改整理的一个通用类,用来操作oracle数据库 十分的方便,支持直接操作sql语句和hash表操作.现在修补mis我都用这个类,节约了大 量的代码与工作良!在老孙的指点下...

修改整理的一个通用类,用来操作oracle数据库 十分的方便,支持直接操作sql语句和hash表操作.现在修补mis我都用这个类,节约了大 量的代码与工作良!在老孙的指点下,偶将操作oracle,sql server ,access三种数据库的通用类集成在一起写了个数据抽象工厂,同时支持三种数据库无缝切换...以后整理出来.

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

上一篇:

下一篇: