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

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

程序员文章站 2024-02-20 22:51:11
本文所述为c#实现的oracle数据库操作类,可执行超多常用的oracle数据库操作,包含了基础数据库连接、关闭连接、输出记录集、执行sql语句,返回带分页功能的datas...

本文所述为c#实现的oracle数据库操作类,可执行超多常用的oracle数据库操作,包含了基础数据库连接、关闭连接、输出记录集、执行sql语句,返回带分页功能的dataset 、取表里字段的类型和长度等,同时还有哈稀表自动插入数据库等高级任务。需要特别指出的是:在执行sql语句,返回 datareader之前一定要先用.read()打开,然后才能读到数据,再用hashtable对数据库进行insert,update,del操作,注意此时只能用默认的数据库连接"connstr"。

完整的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
 }
 }

使用时可将上述代码保存成oracle_dbconn.cs文件,再进行调用。