用C# 来操作ORACLE 数据库.doc

用C# 来操作ORACLE 数据库using System;using System.Collections;using System.Collections.Specialized;using System.Data;using System.Data.OracleClient;using System.Configuration;namespace LiTianPing.OracleDAL /修改成实际项目的命名空间名称/ / Copyright (C) 2004-2008 LiTianPing / 数据访问基础类(基于Oracle)/ 可以用户可以修改满足自己项目的需要。/ public abstract class DbHelperOra /数据库连接字符串(web.config来配置) / protected static string connectionString = ConfigurationSettings.AppSettingsConnectionString; public DbHelperOra() #region 公用方法 public static int GetMaxID(string FieldName,string TableName) string strsql = select max( + FieldName + )+1 from + TableName; object obj = GetSingle(strsql); if (obj = null) return 1; else return int.Parse(obj.ToString(); public static bool Exists(string strSql, params SqlParameter cmdParms) object obj = GetSingle(strSql, cmdParms); int cmdresult; if (Object.Equals(obj, null) | (Object.Equals(obj, System.DBNull.Value) cmdresult = 0; else cmdresult = int.Parse(obj.ToString(); if (cmdresult = 0) return false; else return true; #endregion #region 执行简单SQL语句 / / 执行SQL语句，返回影响的记录数 / / SQL语句 / 影响的记录数 public static int ExecuteSql(string SQLString) using (OracleConnection connection = new OracleConnection(connectionString) using (OracleCommand cmd = new OracleCommand(SQLString,connection) try connection.Open(); int rows=cmd.ExecuteNonQuery(); return rows; catch(System.Data.OracleClient.OracleException E) connection.Close(); throw new Exception(E.Message); / / 执行多条SQL语句，实现数据库事务。 / / 多条SQL语句 public static void ExecuteSqlTran(ArrayList SQLStringList) using (OracleConnection conn = new OracleConnection(connectionString) conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection=conn; OracleTransaction tx=conn.BeginTransaction(); cmd.Transaction=tx; try for(int n=0;n1) cmd.CommandText=strsql; cmd.ExecuteNonQuery(); tx.Commit(); catch(System.Data.OracleClient.OracleException E) tx.Rollback(); throw new Exception(E.Message); / / 执行带一个存储过程参数的的SQL语句。 / / SQL语句 / 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 / 影响的记录数 public static int ExecuteSql(string SQLString,string content) using (OracleConnection connection = new OracleConnection(connectionString) OracleCommand cmd = new OracleCommand(SQLString,connection); System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter ( content, OracleDbType.NText); myParameter.Value = content ; cmd.Parameters.Add(myParameter); try connection.Open(); int rows=cmd.ExecuteNonQuery(); return rows; catch(System.Data.OracleClient.OracleException E) throw new Exception(E.Message); finally cmd.Dispose(); connection.Close(); / / 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) / / SQL语句 / 图像字节,数据库的字段类型为image的情况 / 影响的记录数 public static int ExecuteSqlInsertImg(string strSQL,byte fs) using (OracleConnection connection = new OracleConnection(connectionString) OracleCommand cmd = new OracleCommand(strSQL,connection); System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter ( fs, OracleDbType.Image); myParameter.Value = fs ; cmd.Parameters.Add(myParameter); try connection.Open(); int rows=cmd.ExecuteNonQuery(); return rows; catch(System.Data.OracleClient.OracleException E) throw new Exception(E.Message); finally cmd.Dispose(); connection.Close(); / / 执行一条计算查询结果语句，返回查询结果（object）。 / / 计算查询结果语句 / 查询结果（object） public static object GetSingle(string SQLString) using (OracleConnection connection = new OracleConnection(connectionString) using(OracleCommand cmd = new OracleCommand(SQLString,connection) try connection.Open(); object obj = cmd.ExecuteScalar(); if(Object.Equals(obj,null)|(Object.Equals(obj,System.DBNull.Value) return null; else return obj; catch(System.Data.OracleClient.OracleException e) connection.Close(); throw new Exception(e.Message); / / 执行查询语句，返回OracleDataReader / / 查询语句 / OracleDataReader public static OracleDataReader ExecuteReader(string strSQL) OracleConnection connection = new OracleConnection(connectionString); OracleCommand cmd = new OracleCommand(strSQL,connection); try connection.Open(); OracleDataReader myReader = cmd.ExecuteReader(); return myReader; catch(System.Data.OracleClient.OracleException e) throw new Exception(e.Message); / / 执行查询语句，返回DataSet / / 查询语句 / DataSet public static DataSet Query(string SQLString) using (OracleConnection connection = new OracleConnection(connectionString) DataSet ds = new DataSet(); try connection.Open(); OracleDataAdapter command = new OracleDataAdapter(SQLString,connection); command.Fill(ds,ds); catch(System.Data.OracleClient.OracleException ex) throw new Exception(ex.Message); return ds; #endregion #region 执行带参数的SQL语句 / / 执行SQL语句，返回影响的记录数 / / SQL语句 / 影响的记录数 public static int ExecuteSql(string SQLString,params OracleParameter cmdParms) using (OracleConnection connection = new OracleConnection(connectionString) using (OracleCommand cmd = new OracleCommand() try PrepareCommand(cmd, connection, null,SQLString, cmdParms); int rows=cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; catch(System.Data.OracleClient.OracleException E) throw new Exception(E.Message); / / 执行多条SQL语句，实现数据库事务。 / / SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter） public static void ExecuteSqlTran(Hashtable SQLStringList) using (OracleConnection conn = new OracleConnection(connectionString) conn.Open(); using (OracleTransaction trans = conn.BeginTransaction() OracleCommand cmd = new OracleCommand(); try /循环 foreach (DictionaryEntry myDE in SQLStringList) string cmdText=myDE.Key.ToString(); OracleParameter cmdParms=(OracleParameter)myDE.Value; PrepareCommand(cmd,conn,trans,cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); trans.Commit(); catch trans.Rollback(); throw; / / 执行一条计算查询结果语句，返回查询结果（object）。 / / 计算查询结果语句 / 查询结果（object） public static object GetSingle(string SQLString,params OracleParameter cmdParms) using (OracleConnection connection = new OracleConnection(connectionString) using (OracleCommand cmd = new OracleCommand() try PrepareCommand(cmd, connection, null,SQLString, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if(Object.Equals(obj,null)|(Object.Equals(obj,System.DBNull.Value) return null; else return obj; catch(System.Data.OracleClient.OracleException e) throw new Exception(e.Message); / / 执行查询语句，返回OracleDataReader / / 查询语句 / OracleDataReader public static OracleDataReader ExecuteReader(string SQLString,params OracleParameter cmdParms) OracleConnection connection = new OracleConnection(connectionString); OracleCommand cmd = new OracleCommand(); try PrepareCommand(cmd, connection, null,SQLString, cmdParms); OracleDataReader myReader = cmd.ExecuteReader(); cmd.Parameters.Clear(); return myReader; catch(System.Data.OracleClient.OracleException e) throw new Exception(e.Message); / / 执行查询语句，返回DataSet / / 查询语句 / DataSet public static DataSet Query(string SQLString,params OracleParameter cmdParms) using (OracleConnection connection = new OracleConnection(connectionString) OracleCommand cmd = new OracleCommand(); PrepareCommand(cmd, connection, null,SQLString, cmdParms); using( OracleDataAdapter da = new OracleDataAdapter(cmd) ) DataSet ds = new DataSet(); try da.Fill(ds,ds); cmd.Parameters.Clear(); catch(System.Data.OracleClient.OracleException ex) throw new Exception(ex.Message); return ds; private static void PrepareCommand(OracleCommand cmd,OracleConnection conn,OracleTransaction trans, string cmdText, OracleParameter cmdParms) if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;/cmdType; if (cmdParms != null) foreach (OracleParameter parm in cmdParms) cmd.Parameters.Add(parm); #endregion #region 存储过程操作 / / 执行存储过程 / / 存储过程名 / 存储过程参数 / OracleDataReader public static OracleDataReader RunProcedure(string storedProcName, IDataParameter parameters ) OracleConnection connection = new OracleConnection(connectionString); OracleDataReader returnReader; connection.Open(); OracleCommand command = BuildQueryCommand( connection,storedProcName, parameters ); command.CommandType = CommandType.StoredProcedure; returnReader = command.ExecuteReader(); return returnReader; / / 执行存储过程 / / 存储过程名 / 存储过程参数 / DataSet结果中的表名 / DataSet public static DataSet RunProcedure(string storedProcName, IDataParameter parameters, string tableName ) using (OracleConnection connection = new OracleConnection(connectionString) DataSet dataSet = new DataSet(); connection.Open(); OracleDataAdapter sqlDA = new OracleDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters ); sqlDA.Fill( dataSet, tableName ); connection.Close(); return dataSet; / / 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值) / / 数据库连接 / 存储过程名 / 存储过程参数 / OracleCommand private static OracleCommand BuildQueryCommand(OracleConnection connection,string storedProcName, IDataParameter parameters) OracleCommand command = new OracleCommand( storedProcName, connection ); command.CommandType = CommandType.StoredProcedure; foreach (OracleParameter parameter in para