c#操作oracle总结
来源：互联网 发布：网络综艺节目定位论文 编辑：程序博客网 时间：2024/05/16 03:59
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 // ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ // ＝＝＝＝＝＝＝＝＝内部调用函数完＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ // ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ }} 其他oracle 的常用方法：
#region 初始化常用变量 /// <summary> /// 获取连接字符串 /// </summary> private readonly string constr = Constant.CON_DATABASE_DB; private OracleConnection con; //创建SQL连接 private OracleCommand cmd; //创建SQL命令对象 private OracleDataAdapter sda; //创建SQL数据适配器 private OracleDataReader sdr; //创建SQL数据阅读器 private OracleParameter param; //创建SQL参数 private DataTable dt; //创建数据表 #endregion #region 创建并打开数据库连接 /// <summary> /// 创建并打开数据库连接 /// </summary> private void OpenCon() { con = new OracleConnection(constr); con.Open(); } #endregion #region 关闭已打开的连接 /// <summary> /// 关闭已打开的连接 /// </summary> private void CloseCon() { if (con != null) { con.Close(); con.Dispose(); } } #endregion #region 返回数据集 /// <summary> /// 返回数据集 /// </summary> /// <param name="sqlstring">SQL语句</param> /// <returns>数据集</returns> public DataTable GetDt(string sqlString) { OpenCon(); sda = new OracleDataAdapter(sqlString, con); dt = new DataTable(); sda.Fill(dt); CloseCon(); return dt; } #endregion #region 获取数据视图 /// <summary> /// 获取数据视图 /// </summary> /// <param name="sqlstring">SQL语句</param> /// <returns>数据视图</returns> public DataView GetDv(string sqlString) { return GetDt(sqlString).DefaultView; } #endregion #region 获取阅读器 /// <summary> /// 获取阅读器 /// </summary> /// <param name="sqlString">SQL语句</param> /// <returns>阅读器</returns> public OracleDataReader GetDataReader(string sqlString) { OpenCon(); cmd = new OracleCommand(sqlString, con); sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return sdr; } #endregion #region 执行SQL语句 /// <summary> /// 执行SQL语句 /// </summary> /// <param name="sqlString">SQL语句</param> public int ExecSql(string sqlString) { OpenCon(); int flag = -20; cmd = new OracleCommand(sqlString, con); try { flag = (int)cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Source + ex.Message); } CloseCon(); return flag; } #endregion #region 执行带参SQL语句 /// <summary> /// 执行带参SQL语句 /// </summary> /// <param name="sqlString">SQL带参语句</param> /// <returns>执行SQL语句返回值</returns> public int ExecParmSql(string sqlString, OracleParameter[] parms) { cmd = BuildSqlCommand(sqlString, parms); cmd.ExecuteNonQuery(); CloseCon(); return (int)cmd.Parameters["ReturnValue"].Value; } #endregion #region 执行带参SQL语句,返回数据阅读对象 /// <summary> /// 执行带参SQL语句,返回数据阅读对象 /// </summary> /// <param name="sqlString">带参SQL语句</param> /// <param name="parms">SQL语句参数</param> /// <returns>数据阅读器</returns> public OracleDataReader ExecParmSqlReader(string sqlString, OracleParameter[] parms) { cmd = BuildSqlCommand(sqlString, parms); sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return sdr; } #endregion #region 执行带参SQL语句 /// <summary> /// 执行带参SQL语句 /// </summary> /// <param name="sqlString">SQL带参语句</param> /// <param name="parms">参数</param> /// <returns>数据集</returns> public DataTable ExecParmSqlDs(string sqlString, OracleParameter[] parms) { cmd = BuildSqlCommand(sqlString, parms); sda = new OracleDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); return dt; } #endregion #region 执行存储过程 /// <summary> /// 执行存储过程 /// </summary> /// <param name="procName">存储过程名</param> /// <returns>返回值</returns> public int ExecProc(string procName) { cmd = BulidProcCommand(procName, null); cmd.ExecuteNonQuery(); CloseCon(); return (int)cmd.Parameters["ReturnValue"].Value; } #endregion #region 执行带参数的存储过程 /// <summary> /// 执行带参数的存储过程 /// </summary> /// <param name="procName">存储过程名</param> /// <param name="parms">存储过程参数</param> /// <returns>存储过程返回值</returns> public int ExecProc(string procName, OracleParameter[] parms) { cmd = BulidProcCommand(procName, parms); cmd.ExecuteNonQuery(); CloseCon(); return (int)cmd.Parameters["ReturnValue"].Value; } #endregion #region 执行存储过程 /// <summary> /// 执行存储过程 /// </summary> /// <param name="procName">存储过程名</param> /// <param name="dataReader">数据阅读器</param> public void ExecProc(string procName, OracleDataReader dataReader) { cmd = BulidProcCommand(procName, null); dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); } #endregion #region 执行存储过程 /// <summary> ///执行存储过程 /// </summary> /// <param name="procName">过程名</param> /// <param name="parms">过程参数</param> /// <param name="dataReader">阅读器</param> public void ExecProc(string procName, OracleParameter[] parms, OracleDataReader dataReader) { cmd = BulidProcCommand(procName, parms); dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); } #endregion #region 执行存储过程 /// <summary> /// 执行存储过程 /// </summary> /// <param name="procName">过程名</param> /// <param name="parms">过程参数</param> /// <returns>返回数据集</returns> public DataTable ExecProcDs(string procName, OracleParameter[] parms) { cmd = BulidProcCommand(procName, parms); sda = new OracleDataAdapter(cmd); dt = new DataTable(); sda.Fill(dt); CloseCon(); return dt; } #endregion #region 生成存储过程命令对象 /// <summary> /// 生成存储过程命令对象 /// </summary> /// <param name="procName">存储过程名</param> /// <param name="parms">存储过程参数</param> /// <returns>命令对象</returns> public OracleCommand BulidProcCommand(string procName, OracleParameter[] parms) { OpenCon(); cmd = new OracleCommand(procName, con); cmd.CommandType = CommandType.StoredProcedure; if (parms != null) { foreach (OracleParameter parameter in parms) { cmd.Parameters.Add(parameter); } } cmd.Parameters.Add(new OracleParameter("ReturnValue", OracleType.Int16, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); return cmd; } #endregion #region 生成SQL命令对象 /// <summary> /// 生成SQL命令对象 /// </summary> /// <param name="sqlstr">SQL带参语句</param> /// <param name="parms">SQL语句参数</param> /// <returns>SQL命令对象</returns> public OracleCommand BuildSqlCommand(string sqlstr, OracleParameter[] parms) { OpenCon(); cmd = new OracleCommand(sqlstr, con); cmd.CommandType = CommandType.Text; if (parms != null) { foreach (OracleParameter parameter in parms) { cmd.Parameters.Add(parameter); } } cmd.Parameters.Add(new OracleParameter("ReturnValue", OracleType.Int16, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); return cmd; } /// <summary> /// 生成SQL命令对象 /// </summary> /// <param name="sqlstr">SQL带参语句</param> /// <param name="parms">SQL语句参数</param> /// <returns>SQL命令对象</returns> public OracleCommand BuildSqlCommand(string sqlstr) { OpenCon(); cmd = new OracleCommand(sqlstr, con); cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new OracleParameter("ReturnValue", OracleType.Int16, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); return cmd; } #endregion #region 生成过程参数 /// <summary> /// 生成过程参数 /// </summary> /// <param name="proName">过程名</param> /// <param name="dbTpye">数据类型</param> /// <param name="size">数据大小</param> /// <param name="direction">参数方向</param> /// <param name="value">参数值</param> /// <returns>过程参数</returns> private OracleParameter MakeParm(string parmName, OracleType dbTpye, Int32 size, ParameterDirection direction, object value) { if (size > 0) { param = new OracleParameter(parmName, dbTpye, size); } else { param = new OracleParameter(parmName, dbTpye); } param.Direction = direction; if (!(direction == ParameterDirection.Output && value == null)) { param.Value = value; } return param; } #endregion #region 生成存储过程输入参数 /// <summary> /// 生成存储过程输入参数 /// </summary> /// <param name="procName">过程名</param> /// <param name="dbType">参数类型</param> /// <param name="size">参数大小</param> /// <param name="value">参数值</param> /// <returns>过程参数</returns> public OracleParameter MakeInParm(string parmName, OracleType dbType, Int32 size, object value) { return MakeParm(parmName, dbType, size, ParameterDirection.Input, value); } #endregion #region 生成存储过程输出参数 /// <summary> /// 生成存储过程输出参数 /// </summary> /// <param name="procName">过程名</param> /// <param name="dbType">参数类型</param> /// <param name="size">参数大小</param> /// <param name="value">参数值</param> /// <returns>过程参数</returns> public OracleParameter MakOutParm(string parmName, OracleType dbType, Int32 size) { return MakeParm(parmName, dbType, size, ParameterDirection.Output, null); } #endregion- c#操作oracle总结
- C#操作Oracle
- C#操作ORACLE数据库
- C# Oracle基本操作
- C# oracle操作类
- C#操作数据库总结
- c# 操作注册表总结
- C# 文件操作总结
- C#操作数据库总结
- C# DateTime操作总结
- C#操作Excel总结
- C#操作Excel总结
- C#操作Excel总结
- C#操作Excel总结
- c# 操作excel 总结
- C#操作excel总结
- C#文件操作总结
- C#操作Excel总结
- poj_1915
- pgsql查询所有表名
- H.264码流结构
- 菜鸟晋级必修 智能手机越狱/解锁/刷机完全教程
- SAP CRM How to Create your own BOL Object for webclient
- c#操作oracle总结
- xubuntu下给php安装curl
- C#文件读写常用类介绍
- C++的虚函数和代码结构
- Android 悬浮Activity并可拖动(访悬浮歌词)
- CPU cache(上)
- cocos2d-x （四）粒子特效相关说明
- JSP简明教程
- debian 安装samba
