复制代码 代码如下: using System; using System.Collections; using System.Collections.Specialized; using System.Data; using System.Data.OracleClient; using System.Configuration; using System.Data.Common; using System.Collections.Generic;
/// <summary> /// 数据访问抽象基础类 /// /// </summary> public class DBBase {
//数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库. public static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString1"].ToString(); public DBBase() { }
#region 检查用户名是否存在 /// <summary> /// 检查用户名是否存在,存在返回true,不存在返回false /// </summary> /// <param name="strSql"></param> /// <returns></returns> public static bool Exists(string strSql) {
using (OracleConnection connection = new OracleConnection(connectionString)) { connection.Open(); OracleCommand myCmd = new OracleCommand(strSql, connection); try { object obj = myCmd.ExecuteScalar(); //返回结果的第一行一列 myCmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return false; } else { return true; } } catch (Exception ex) { throw ex; } } }
#endregion
#region 执行简单SQL语句 返回影响的记录数
/// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString) {
OracleConnection connection = null; OracleCommand cmd = null; try { connection = new OracleConnection(connectionString); cmd = new OracleCommand(SQLString, connection); connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } finally { if (cmd != null) { cmd.Dispose(); } if (connection != null) { connection.Close(); connection.Dispose(); } } } #endregion
#region 执行查询语句,返回SqlDataReader /// <summary> /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close ) /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>SqlDataReader</returns> 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 e; } finally { connection.Close();
} } #endregion
#region 执行SQL查询语句,返回DataTable数据表 /// <summary> /// 执行SQL查询语句 /// </summary> /// <param name="sqlStr"></param> /// <returns>返回DataTable数据表</returns> public static DataTable GetDataTable(string sqlStr) { OracleConnection mycon = new OracleConnection(connectionString); OracleCommand mycmd = new OracleCommand(sqlStr, mycon); DataTable dt = new DataTable(); OracleDataAdapter da = null; try { mycon.Open(); da = new OracleDataAdapter(sqlStr, mycon); da.Fill(dt);
} catch (Exception ex) {
throw new Exception(ex.ToString()); } finally { mycon.Close(); } return dt; } #endregion
#region 存储过程操作 /// <summary> /// 运行存储过程,返回datatable; /// </summary> /// <param name="storedProcName">存储过程名称</param> /// <param name="parameters">参数</param> /// <returns></returns> public static DataTable RunProcedureDatatable(string storedProcName, IDataParameter[] parameters) { using (OracleConnection connection = new OracleConnection(connectionString)) { DataSet ds = new DataSet(); connection.Open(); OracleDataAdapter sqlDA = new OracleDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.Fill(ds); connection.Close(); return ds.Tables[0]; } } /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">存储过程名称</param> /// <param name="parameters">参数</param> /// <returns></returns> public static int RunProcedure(string storedProcName, IDataParameter[] parameters) { using (OracleConnection connection = new OracleConnection(connectionString)) { try { connection.Open(); OracleCommand command = new OracleCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; foreach (OracleParameter parameter in parameters) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } command.Parameters.Add(parameter); } } int rows = command.ExecuteNonQuery(); return rows; }
finally { connection.Close(); } } }
/// <summary> /// 构建 OracleCommand 对象(用来返回一个结果集,而不是一个整数值) /// </summary> /// <param name="connection">数据库连接</param> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>OracleCommand</returns> 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) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } command.Parameters.Add(parameter); } } return command; }
#endregion
#region 事务处理
/// <summary> /// 执行多条SQL语句(list的形式),实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> /// 调用 Transaction 对象的 Commit 方法来完成事务,或调用 Rollback 方法来取消事务。 public static int ExecuteSqlTran(List<String> SQLStringList) { using (OracleConnection connection = new OracleConnection(connectionString)) { connection.Open(); // 为事务创建一个命令 OracleCommand cmd = new OracleCommand(); cmd.Connection = connection; OracleTransaction tx = connection.BeginTransaction();// 启动一个事务 cmd.Transaction = tx; try { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit();//用Commit方法来完成事务 return count;// } catch { tx.Rollback();//出现错误,事务回滚! return 0; } finally { cmd.Dispose(); connection.Close();//关闭连接 } } } #endregion #region 事务处理
/// <summary> /// 执行多条SQL语句(字符串数组形式),实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> /// 调用 Transaction 对象的 Commit 方法来完成事务,或调用 Rollback 方法来取消事务。 public static int ExecuteTransaction(string[] SQLStringList,int p) { using (OracleConnection connection = new OracleConnection(connectionString)) { connection.Open(); // 为事务创建一个命令 OracleCommand cmd = new OracleCommand(); cmd.Connection = connection; OracleTransaction tx = connection.BeginTransaction();// 启动一个事务 cmd.Transaction = tx; try { int count = 0; for (int n = 0; n < p; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit();//用Commit方法来完成事务 return count;// } catch { tx.Rollback();//出现错误,事务回滚! return 0; } finally { cmd.Dispose(); connection.Close();//关闭连接 } } }
#endregion /// <summary> /// 执行存储过程获取所需编号(各表主键) /// </summary> /// <param name="FlowName">存储过程参数</param> /// <param name="StepLen">存储过程参数(默认为1)</param> /// <returns>编号(各表主键)</returns> public static string Get_FlowNum(string FlowName, int StepLen = 1) { OracleConnection mycon = new OracleConnection(connectionString); try { mycon.Open(); OracleCommand MyCommand = new OracleCommand("ALARM_GET_FLOWNUMBER", mycon); MyCommand.CommandType = CommandType.StoredProcedure; MyCommand.Parameters.Add(new OracleParameter("I_FlowName", OracleType.VarChar, 50)); MyCommand.Parameters["I_FlowName"].Value = FlowName; MyCommand.Parameters.Add(new OracleParameter("I_SeriesNum", OracleType.Number)); MyCommand.Parameters["I_SeriesNum"].Value = StepLen; MyCommand.Parameters.Add(new OracleParameter("O_FlowValue", OracleType.Number)); MyCommand.Parameters["O_FlowValue"].Direction = ParameterDirection.Output; MyCommand.ExecuteNonQuery(); return MyCommand.Parameters["O_FlowValue"].Value.ToString(); } catch { return ""; } finally { mycon.Close(); } }
}
|