Oracle帮助类

Oracle帮助类

///

/// Copyright (C) Maticsoft /// 数据访问基础类(基于Oracle) /// 可以用户可以修改满足自己项目的需要。 ///

public abstract class DbHelperOra { //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. public static string connectionString = PubConstant.ConnectionString; 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) { object obj = GetSingle(strSql); 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; } } public static bool Exists(string strSql, params OracleParameter[] 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; } } public static int GetSequence(string SequenceName) { string strsql = "select " + SequenceName + ".nextval from dual"; object obj = GetSingle(strsql); if (obj == null) { return 0; } else { return int.Parse(obj.ToString()); } } #endregion public static string GetServerDateTime() { //string SQLString = "Select To_Char(SYSDATE，'yyyyMMddHHmmss') from dual"; string datetime = ""; string SQLString = "select SYSDATE from dual"; using (OracleConnection connection = new OracleConnection(connectionString)) { using (OracleCommand cmd = new OracleCommand(SQLString, connection)) { try { connection.Open(); OracleDataReader dataReader = cmd.ExecuteReader(); if (dataReader.Read()) { datetime = dataReader[0].ToString(); } dataReader.Close(); } catch (System.Data.OracleClient.OracleException E) { connection.Close(); throw new Exception(E.Message); } } } return datetime; } #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 bool 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(); return true; } catch //(System.Data.OracleClient.OracleException E) { tx.Rollback(); return false; //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", OracleType.NVarChar); 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", OracleType.LongRaw); 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 ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) ///

/// 查询语句 /// 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(CommandBehavior.CloseConnection); 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 bool ExecuteSqlTran(System.Collections.Generic.List SQLStringList) { using (OracleConnection conn = new OracleConnection(connectionString)) { conn.Open(); using (OracleTransaction trans = conn.BeginTransaction()) { OracleCommand cmd = new OracleCommand(); try { //循环 foreach (Hashtable item in SQLStringList) { foreach (DictionaryEntry myDE in item) { 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(); return true; } catch { trans.Rollback(); //throw; return false; } finally { conn.Close(); conn.Dispose();//打开后需要关闭释放 } } } } public static bool Manange(System.Collections.Hashtable hash) { bool b = false; foreach (System.Collections.DictionaryEntry entry in hash) { OracleParameter[] parameters = (OracleParameter[])entry.Value; int rows = ExecuteSql(entry.Key.ToString(), parameters); if (rows > 0) { b = true; } else { b = false; } } return b; } ///

/// 执行多条SQL语句，实现数据库事务。 ///

/// SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]） public static bool 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(); return true; } catch { trans.Rollback(); //throw; return false; } finally { conn.Close(); conn.Dispose();//打开后需要关闭释放 } } } } ///

/// 执行一条计算查询结果语句，返回查询结果（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 ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) ///

/// 查询语句 /// 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(CommandBehavior.CloseConnection); 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 存储过程操作 ///

/// 执行存储过程 返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) ///

/// 存储过程名 /// 存储过程参数 /// 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(CommandBehavior.CloseConnection); 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 parameters) { command.Parameters.Add( parameter ); } return command; } ///

/// 执行存储过程，返回影响的行数 ///

/// 存储过程名 /// 存储过程参数 /// 影响的行数 /// public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected ) { using (OracleConnection connection = new OracleConnection(connectionString)) { int result; connection.Open(); OracleCommand command = BuildIntCommand(connection,storedProcName, parameters ); rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["ReturnValue"].Value; //Connection.Close(); return result; } } ///

/// 创建 OracleCommand 对象实例(用来返回一个整数值) ///

/// 存储过程名 /// 存储过程参数 /// OracleCommand 对象实例 private static OracleCommand BuildIntCommand(OracleConnection connection,string storedProcName, IDataParameter[] parameters) { OracleCommand command = BuildQueryCommand(connection,storedProcName, parameters ); command.Parameters.Add( new OracleParameter ( "ReturnValue", OracleType.Int32, 4, ParameterDirection.ReturnValue, false,0,0,string.Empty,DataRowVersion.Default,null )); return command; } #endregion }

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。