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