using System;using System.Collections;using System.Collections.Specialized;using System.Data;using System.Data.OracleClient;using System.Configuration;namespace Joycb.GDInterface.Web.DALFactory{ /// /// Copyright (C) Maticsoft /// 数据访问基础类(基于Oracle) /// 可以用户可以修改满足自己项目的需要。 /// public abstract class DbHelperOra { //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. public static string connectionString = ConfigurationManager.ConnectionStrings["connStringOracle"].ToString(); 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; } } #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; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { 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", 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 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 ( 注意：调用该方法后，一定要对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 }}