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文件,再进行调用。
上一篇: Java 生成随机字符串数组的实例详解
下一篇: 基于C#实现的HOOK键盘钩子实例代码