你的位置:首页 > ASP.net教程

[ASP.net教程]OracleHelper数据库事务处理


    OracleHelper代码:

using System;using System.Collections;using System.Collections.Generic;using System.ComponentModel;using System.Configuration;using System.Data;using System.Data.OracleClient;using System.Linq;using System.Reflection;using System.Text;using System.Web;using System.using System.Data.Objects.DataClasses;using Models;namespace DBHelper{  /// <summary>  /// Oracle操作类  /// 2015年6月20日  /// 写程序之前,首先引用System.Data.OracleClient  /// </summary>  public class OracleHelper  {    #region 静态变量    /// <summary>    /// 数据库连接字符串    /// </summary>    private static string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();    #endregion    #region OracleConnection 获取数据库连接    /// <summary>    /// 获取数据库连接,此连接已Open    /// </summary>    private static OracleConnection GetConn()    {      OracleConnection connection = null;      string key = "Simpo2016_OracleConnection";      if (HttpContext.Current.Items[key] == null)      {        connection = new OracleConnection(connectionString);        connection.Open();        HttpContext.Current.Items[key] = connection;      }      else      {        connection = (OracleConnection)HttpContext.Current.Items[key];        if (connection.State != ConnectionState.Open) connection.Open();      }      return connection;    }    #endregion    #region OracleTransaction 获取事务对象    /// <summary>    /// 获取事务对象    /// </summary>    private static OracleTransaction GetTran()    {      OracleTransaction tran = null;      string key = "Simpo2016_OracleTransaction";      if (HttpContext.Current.Items[key] == null)      {        tran = GetConn().BeginTransaction();        HttpContext.Current.Items[key] = tran;      }      else      {        tran = (OracleTransaction)HttpContext.Current.Items[key];      }      return tran;    }    #endregion    #region 开起事务标志    /// <summary>    /// 事务标志    /// </summary>    private static string tranFlagKey = "Simpo2016_OracleTransaction_Flag";    /// <summary>    /// 添加事务标志    /// </summary>    public static void AddTranFlag()    {      HttpContext.Current.Items[tranFlagKey] = true;    }    /// <summary>    /// 移除事务标志    /// </summary>    public static void RemoveTranFlag()    {      HttpContext.Current.Items[tranFlagKey] = false;    }    /// <summary>    /// 事务标志    /// </summary>    public static bool TranFlag    {      get      {        bool tranFlag = false;        if (HttpContext.Current.Items[tranFlagKey] != null)        {          tranFlag = (bool)HttpContext.Current.Items[tranFlagKey];        }        return tranFlag;      }    }    #endregion    #region 用于查询的数据库连接    /// <summary>    /// 用于查询的数据库连接    /// </summary>    private OracleConnection m_Conn;    #endregion    #region 构造函数    public OracleHelper()    {      m_Conn = new OracleConnection(connectionString);    }    #endregion    #region 基础方法    #region 执行简单SQL语句    #region Exists    public bool Exists(string sqlString)    {      using (OracleCommand cmd = new OracleCommand(sqlString, m_Conn))      {        try        {          m_Conn.Open();          object obj = cmd.ExecuteScalar();          if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))          {            return false;          }          else          {            return true;          }        }        catch (Exception ex)        {          throw ex;        }        finally        {          cmd.Dispose();          m_Conn.Close();        }      }    }    #endregion    #region 执行SQL语句,返回影响的记录数    /// <summary>    /// 执行SQL语句,返回影响的记录数    /// </summary>    /// <param name="sqlString">SQL语句</param>    /// <returns>影响的记录数</returns>    public int ExecuteSql(string sqlString)    {      OracleConnection connection = GetConn();      using (OracleCommand cmd = new OracleCommand(sqlString, connection))      {        try        {          if (TranFlag) cmd.Transaction = GetTran();          int rows = cmd.ExecuteNonQuery();          return rows;        }        catch (Exception ex)        {          throw new Exception(ex.Message);        }        finally        {          cmd.Dispose();          if (!TranFlag) connection.Close();        }      }    }    #endregion    #region 执行一条计算查询结果语句,返回查询结果    /// <summary>    /// 执行一条计算查询结果语句,返回查询结果(object)    /// </summary>    /// <param name="sqlString">计算查询结果语句</param>    /// <returns>查询结果(object)</returns>    public object GetSingle(string sqlString)    {      using (OracleCommand cmd = new OracleCommand(sqlString, m_Conn))      {        try        {          m_Conn.Open();          object obj = cmd.ExecuteScalar();          if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))          {            return null;          }          else          {            return obj;          }        }        catch (Exception ex)        {          throw ex;        }        finally        {          cmd.Dispose();          m_Conn.Close();        }      }    }    #endregion    #region 执行查询语句,返回SQLiteDataReader    /// <summary>    /// 执行查询语句,返回SQLiteDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )    /// </summary>    /// <param name="sqlString">查询语句</param>    /// <returns>SQLiteDataReader</returns>    public OracleDataReader ExecuteReader(string sqlString)    {      OracleConnection connection = new OracleConnection(connectionString);      OracleCommand cmd = new OracleCommand(sqlString, connection);      try      {        connection.Open();        OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);        return myReader;      }      catch (Exception ex)      {        throw ex;      }    }    #endregion    #region 执行查询语句,返回DataSet    /// <summary>    /// 执行查询语句,返回DataSet    /// </summary>    /// <param name="sqlString">查询语句</param>    /// <returns>DataSet</returns>    public 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 (Exception ex)        {          throw ex;        }        finally        {          connection.Close();        }        return ds;      }    }    #endregion    #endregion    #region 执行带参数的SQL语句    #region 执行SQL语句,返回影响的记录数    /// <summary>    /// 执行SQL语句,返回影响的记录数    /// </summary>    /// <param name="SQLString">SQL语句</param>    /// <returns>影响的记录数</returns>    public int ExecuteSql(string SQLString, params OracleParameter[] cmdParms)    {      OracleConnection connection = GetConn();      using (OracleCommand cmd = new OracleCommand())      {        try        {          PrepareCommand(cmd, connection, null, SQLString, cmdParms);          if (TranFlag) cmd.Transaction = GetTran();          int rows = cmd.ExecuteNonQuery();          cmd.Parameters.Clear();          return rows;        }        catch (Exception ex)        {          throw ex;        }        finally        {          cmd.Dispose();          if (!TranFlag) connection.Close();        }      }    }    #endregion    #region 执行查询语句,返回SQLiteDataReader    /// <summary>    /// 执行查询语句,返回SQLiteDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )    /// </summary>    /// <param name="strSQL">查询语句</param>    /// <returns>SQLiteDataReader</returns>    public OracleDataReader ExecuteReader(string sqlString, params OracleParameter[] cmdParms)    {      OracleCommand cmd = new OracleCommand();      try      {        PrepareCommand(cmd, m_Conn, null, sqlString, cmdParms);        OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);        cmd.Parameters.Clear();        return myReader;      }      catch (Exception ex)      {        throw ex;      }    }    #endregion    #region 执行查询语句,返回DataSet    /// <summary>    /// 执行查询语句,返回DataSet    /// </summary>    /// <param name="sqlString">查询语句</param>    /// <returns>DataSet</returns>    public DataSet Query(string sqlString, params OracleParameter[] cmdParms)    {      OracleCommand cmd = new OracleCommand();      PrepareCommand(cmd, m_Conn, null, sqlString, cmdParms);      using (OracleDataAdapter da = new OracleDataAdapter(cmd))      {        DataSet ds = new DataSet();        try        {          da.Fill(ds, "ds");          cmd.Parameters.Clear();        }        catch (Exception ex)        {          throw ex;        }        finally        {          cmd.Dispose();          m_Conn.Close();        }        return ds;      }    }    #endregion    #region PrepareCommand    private 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;      if (cmdParms != null)      {        foreach (OracleParameter parm in cmdParms)        {          cmd.Parameters.Add(parm);        }      }    }    #endregion    #endregion    #endregion    #region 增删改查    #region 获取最大编号    /// <summary>    /// 获取最大编号    /// </summary>    /// <typeparam name="T">实体Model</typeparam>    /// <param name="key">主键</param>    public int GetMaxID<T>(string key)    {      Type type = typeof(T);      string sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);      using (OracleCommand cmd = new OracleCommand(sql, m_Conn))      {        try        {          m_Conn.Open();          object obj = cmd.ExecuteScalar();          if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))          {            return 1;          }          else          {            return int.Parse(obj.ToString()) + 1;          }        }        catch (Exception ex)        {          throw ex;        }        finally        {          cmd.Dispose();          m_Conn.Close();        }      }    }    #endregion    #region 添加    /// <summary>    /// 添加    /// </summary>    public void Insert(object obj)    {      StringBuilder strSql = new StringBuilder();      Type type = obj.GetType();      strSql.Append(string.Format("insert into {0}(", type.Name));      PropertyInfo[] propertyInfoList = GetEntityProperties(type);      List<string> propertyNameList = new List<string>();      foreach (PropertyInfo propertyInfo in propertyInfoList)      {        propertyNameList.Add(propertyInfo.Name);      }      strSql.Append(string.Format("{0})", string.Join(",", propertyNameList.ToArray())));      strSql.Append(string.Format(" values ({0})", string.Join(",", propertyNameList.ConvertAll<string>(a => ":" + a).ToArray())));      OracleParameter[] parameters = new OracleParameter[propertyInfoList.Length];      for (int i = 0; i < propertyInfoList.Length; i++)      {        PropertyInfo propertyInfo = propertyInfoList[i];        object val = propertyInfo.GetValue(obj, null);        OracleParameter oracleParameter = new OracleParameter(":" + propertyInfo.Name, val == null ? DBNull.Value : val);        parameters[i] = oracleParameter;      }      ExecuteSql(strSql.ToString(), parameters);    }    #endregion    #region 修改    /// <summary>    /// 修改    /// </summary>    public void Update(object obj)    {      object oldObj = Find(obj);      if (oldObj == null) throw new Exception("无法获取到旧数据");      StringBuilder strSql = new StringBuilder();      Type type = obj.GetType();      strSql.Append(string.Format("update {0} ", type.Name));      PropertyInfo[] propertyInfoList = GetEntityProperties(type);      List<string> propertyNameList = new List<string>();      int savedCount = 0;      foreach (PropertyInfo propertyInfo in propertyInfoList)      {        object oldVal = propertyInfo.GetValue(oldObj, null);        object val = propertyInfo.GetValue(obj, null);        if (!object.Equals(oldVal, val))        {          propertyNameList.Add(propertyInfo.Name);          savedCount++;        }      }      strSql.Append(string.Format(" set "));      OracleParameter[] parameters = new OracleParameter[savedCount];      StringBuilder sbPros = new StringBuilder();      int k = 0;      for (int i = 0; i < propertyInfoList.Length; i++)      {        PropertyInfo propertyInfo = propertyInfoList[i];        object oldVal = propertyInfo.GetValue(oldObj, null);        object val = propertyInfo.GetValue(obj, null);        if (!object.Equals(oldVal, val))        {          sbPros.Append(string.Format(" {0}=:{0},", propertyInfo.Name));          OracleParameter oracleParameter = new OracleParameter(":" + propertyInfo.Name, val == null ? DBNull.Value : val);          parameters[k++] = oracleParameter;        }      }      if (sbPros.Length > 0)      {        strSql.Append(sbPros.ToString(0, sbPros.Length - 1));      }      strSql.Append(string.Format(" where {0}='{1}'", GetIdName(obj.GetType()), GetIdVal(obj).ToString()));      if (savedCount > 0)      {        ExecuteSql(strSql.ToString(), parameters);      }    }    #endregion    #region 删除    /// <summary>    /// 根据Id删除    /// </summary>    public void Delete<T>(int id)    {      Type type = typeof(T);      StringBuilder sbSql = new StringBuilder();      sbSql.Append(string.Format("delete from {0} where {2}='{1}'", type.Name, id, GetIdName(type)));      ExecuteSql(sbSql.ToString());    }    /// <summary>    /// 根据Id集合删除    /// </summary>    public void BatchDelete<T>(string ids)    {      if (string.IsNullOrWhiteSpace(ids)) return;      Type type = typeof(T);      StringBuilder sbSql = new StringBuilder();      sbSql.Append(string.Format("delete from {0} where {2} in ({1})", type.Name, ids, GetIdName(type)));      ExecuteSql(sbSql.ToString());    }    /// <summary>    /// 根据条件删除    /// </summary>    public void Delete<T>(string conditions)    {      if (string.IsNullOrWhiteSpace(conditions)) return;      Type type = typeof(T);      StringBuilder sbSql = new StringBuilder();      sbSql.Append(string.Format("delete from {0} where {1}", type.Name, conditions));      ExecuteSql(sbSql.ToString());    }    #endregion    #region 获取实体    #region 根据实体获取实体    /// <summary>    /// 根据实体获取实体    /// </summary>    private object Find(object obj)    {      Type type = obj.GetType();      object result = Activator.CreateInstance(type);      bool hasValue = false;      IDataReader rd = null;      string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, GetIdVal(obj), GetIdName(obj.GetType()));      try      {        rd = ExecuteReader(sql);        PropertyInfo[] propertyInfoList = GetEntityProperties(type);        int fcnt = rd.FieldCount;        List<string> fileds = new List<string>();        for (int i = 0; i < fcnt; i++)        {          fileds.Add(rd.GetName(i).ToUpper());        }        while (rd.Read())        {          hasValue = true;          IDataRecord record = rd;          foreach (PropertyInfo pro in propertyInfoList)          {            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)            {              continue;            }            pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);          }        }      }      catch (Exception ex)      {        throw ex;      }      finally      {        if (rd != null && !rd.IsClosed)        {          rd.Close();          rd.Dispose();        }      }      if (hasValue)      {        return result;      }      else      {        return null;      }    }    #endregion    #region 根据Id获取实体    /// <summary>    /// 根据Id获取实体    /// </summary>    private object FindById(Type type, int id)    {      object result = Activator.CreateInstance(type);      IDataReader rd = null;      bool hasValue = false;      string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));      try      {        rd = ExecuteReader(sql);        PropertyInfo[] propertyInfoList = GetEntityProperties(type);        int fcnt = rd.FieldCount;        List<string> fileds = new List<string>();        for (int i = 0; i < fcnt; i++)        {          fileds.Add(rd.GetName(i).ToUpper());        }        while (rd.Read())        {          hasValue = true;          IDataRecord record = rd;          foreach (PropertyInfo pro in propertyInfoList)          {            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)            {              continue;            }            pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);          }        }      }      catch (Exception ex)      {        throw ex;      }      finally      {        if (rd != null && !rd.IsClosed)        {          rd.Close();          rd.Dispose();        }      }      if (hasValue)      {        return result;      }      else      {        return null;      }    }    #endregion    #region 根据Id获取实体    /// <summary>    /// 根据Id获取实体    /// </summary>    public T FindById<T>(string id) where T : new()    {      Type type = typeof(T);      T result = (T)Activator.CreateInstance(type);      IDataReader rd = null;      bool hasValue = false;      string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));      try      {        rd = ExecuteReader(sql);        PropertyInfo[] propertyInfoList = GetEntityProperties(type);        int fcnt = rd.FieldCount;        List<string> fileds = new List<string>();        for (int i = 0; i < fcnt; i++)        {          fileds.Add(rd.GetName(i).ToUpper());        }        while (rd.Read())        {          hasValue = true;          IDataRecord record = rd;          foreach (PropertyInfo pro in propertyInfoList)          {            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)            {              continue;            }            pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);          }        }      }      catch (Exception ex)      {        throw ex;      }      finally      {        if (rd != null && !rd.IsClosed)        {          rd.Close();          rd.Dispose();        }      }      if (hasValue)      {        return result;      }      else      {        return default(T);      }    }    #endregion    #region 根据sql获取实体    /// <summary>    /// 根据sql获取实体    /// </summary>    public T FindBySql<T>(string sql) where T : new()    {      Type type = typeof(T);      T result = (T)Activator.CreateInstance(type);      IDataReader rd = null;      bool hasValue = false;      try      {        rd = ExecuteReader(sql);        PropertyInfo[] propertyInfoList = GetEntityProperties(type);        int fcnt = rd.FieldCount;        List<string> fileds = new List<string>();        for (int i = 0; i < fcnt; i++)        {          fileds.Add(rd.GetName(i).ToUpper());        }        while (rd.Read())        {          hasValue = true;          IDataRecord record = rd;          foreach (PropertyInfo pro in propertyInfoList)          {            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)            {              continue;            }            pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);          }        }      }      catch (Exception ex)      {        throw ex;      }      finally      {        if (rd != null && !rd.IsClosed)        {          rd.Close();          rd.Dispose();        }      }      if (hasValue)      {        return result;      }      else      {        return default(T);      }    }    #endregion    #endregion    #region 获取列表    /// <summary>    /// 获取列表    /// </summary>    public List<T> FindListBySql<T>(string sql) where T : new()    {      List<T> list = new List<T>();      object obj;      IDataReader rd = null;      try      {        rd = ExecuteReader(sql);        if (typeof(T) == typeof(int))        {          while (rd.Read())          {            list.Add((T)rd[0]);          }        }        else if (typeof(T) == typeof(string))        {          while (rd.Read())          {            list.Add((T)rd[0]);          }        }        else        {          PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();          int fcnt = rd.FieldCount;          List<string> fileds = new List<string>();          for (int i = 0; i < fcnt; i++)          {            fileds.Add(rd.GetName(i).ToUpper());          }          while (rd.Read())          {            IDataRecord record = rd;            obj = new T();            foreach (PropertyInfo pro in propertyInfoList)            {              if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)              {                continue;              }              pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);            }            list.Add((T)obj);          }        }      }      catch (Exception ex)      {        throw ex;      }      finally      {        if (rd != null && !rd.IsClosed)        {          rd.Close();          rd.Dispose();        }      }      return list;    }    #endregion    #region 获取列表    /// <summary>    /// 获取列表    /// </summary>    public List<T> FindListBySql<T>(string sql, params OracleParameter[] cmdParms) where T : new()    {      List<T> list = new List<T>();      object obj;      IDataReader rd = null;      try      {        rd = ExecuteReader(sql, cmdParms);        if (typeof(T) == typeof(int))        {          while (rd.Read())          {            list.Add((T)rd[0]);          }        }        else if (typeof(T) == typeof(string))        {          while (rd.Read())          {            list.Add((T)rd[0]);          }        }        else        {          PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();          int fcnt = rd.FieldCount;          List<string> fileds = new List<string>();          for (int i = 0; i < fcnt; i++)          {            fileds.Add(rd.GetName(i).ToUpper());          }          while (rd.Read())          {            IDataRecord record = rd;            obj = new T();            foreach (PropertyInfo pro in propertyInfoList)            {              if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)              {                continue;              }              pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);            }            list.Add((T)obj);          }        }      }      catch (Exception ex)      {        throw ex;      }      finally      {        if (rd != null && !rd.IsClosed)        {          rd.Close();          rd.Dispose();        }      }      return list;    }    #endregion    #region 分页获取列表    /// <summary>    /// 分页(任意entity,尽量少的字段)    /// </summary>    public PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage) where T : new()    {      PagerModel pagerModel = new PagerModel();      using (OracleConnection connection = new OracleConnection(connectionString))      {        connection.Open();        string commandText = string.Format("select count(*) from ({0}) T", sql);        IDbCommand cmd = new OracleCommand(commandText, connection);        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());        int startRow = pageSize * (currentPage - 1);        int endRow = startRow + pageSize;        StringBuilder sb = new StringBuilder();        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");        sb.Append(sql);        if (!string.IsNullOrWhiteSpace(orderby))        {          sb.Append(" ");          sb.Append(orderby);        }        sb.Append(" ) row_limit where rownum <= ");        sb.Append(endRow);        sb.Append(" ) where rownum_ >");        sb.Append(startRow);        List<T> list = FindListBySql<T>(sb.ToString());        pagerModel.result = list;      }      return pagerModel;    }    #endregion    #region 分页获取列表    /// <summary>    /// 分页(任意entity,尽量少的字段)    /// </summary>    /// <typeparam name="T"></typeparam>    /// <param name="sql"></param>    /// <returns></returns>    public PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage, params OracleParameter[] cmdParms) where T : new()    {      PagerModel pagerModel = new PagerModel();      using (OracleConnection connection = new OracleConnection(connectionString))      {        connection.Open();        string commandText = string.Format("select count(*) from ({0}) T", sql);        OracleCommand cmd = new OracleCommand(commandText, connection);        PrepareCommand(cmd, connection, null, commandText, cmdParms);        pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());        cmd.Parameters.Clear();        int startRow = pageSize * (currentPage - 1);        int endRow = startRow + pageSize;        StringBuilder sb = new StringBuilder();        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");        sb.Append(sql);        if (!string.IsNullOrWhiteSpace(orderby))        {          sb.Append(" ");          sb.Append(orderby);        }        sb.Append(" ) row_limit where rownum <= ");        sb.Append(endRow);        sb.Append(" ) where rownum_ >");        sb.Append(startRow);        List<T> list = FindListBySql<T>(sb.ToString(), cmdParms);        pagerModel.result = list;      }      return pagerModel;    }    #endregion    #region 分页获取列表    /// <summary>    /// 分页(任意entity,尽量少的字段)    /// </summary>    public DataSet FindPageBySql(string sql, string orderby, int pageSize, int currentPage, out int totalCount, params OracleParameter[] cmdParms)    {      DataSet ds = null;      using (OracleConnection connection = new OracleConnection(connectionString))      {        connection.Open();        string commandText = string.Format("select count(*) from ({0}) T", sql);        IDbCommand cmd = new OracleCommand(commandText, connection);        totalCount = int.Parse(cmd.ExecuteScalar().ToString());        int startRow = pageSize * (currentPage - 1);        int endRow = startRow + pageSize;        StringBuilder sb = new StringBuilder();        sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");        sb.Append(sql);        if (!string.IsNullOrWhiteSpace(orderby))        {          sb.Append(" ");          sb.Append(orderby);        }        sb.Append(" ) row_limit where rownum <= ");        sb.Append(endRow);        sb.Append(" ) where rownum_ >");        sb.Append(startRow);        ds = Query(sql, cmdParms);      }      return ds;    }    #endregion    #region getReaderValue 转换数据    /// <summary>    /// 转换数据    /// </summary>    private Object getReaderValue(Object rdValue, Type ptype)    {      if (ptype == typeof(double))        return Convert.ToDouble(rdValue);      if (ptype == typeof(decimal))        return Convert.ToDecimal(rdValue);      if (ptype == typeof(int))        return Convert.ToInt32(rdValue);      if (ptype == typeof(long))        return Convert.ToInt64(rdValue);      if (ptype == typeof(DateTime))        return Convert.ToDateTime(rdValue);      if (ptype == typeof(Nullable<double>))        return Convert.ToDouble(rdValue);      if (ptype == typeof(Nullable<decimal>))        return Convert.ToDecimal(rdValue);      if (ptype == typeof(Nullable<int>))        return Convert.ToInt32(rdValue);      if (ptype == typeof(Nullable<long>))        return Convert.ToInt64(rdValue);      if (ptype == typeof(Nullable<DateTime>))        return Convert.ToDateTime(rdValue);      return rdValue;    }    #endregion    #region 获取主键名称    /// <summary>    /// 获取主键名称    /// </summary>    public string GetIdName(Type type)    {      PropertyInfo[] propertyInfoList = GetEntityProperties(type);      foreach (PropertyInfo propertyInfo in propertyInfoList)      {        if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0)        {          return propertyInfo.Name;        }      }      return "Id";    }    #endregion    #region 获取主键值    /// <summary>    /// 获取主键名称    /// </summary>    public object GetIdVal(object val)    {      string idName = GetIdName(val.GetType());      if (!string.IsNullOrWhiteSpace(idName))      {        return val.GetType().GetProperty(idName).GetValue(val, null);      }      return 0;    }    #endregion    #region 获取实体类属性    /// <summary>    /// 获取实体类属性    /// </summary>    private PropertyInfo[] GetEntityProperties(Type type)    {      List<PropertyInfo> result = new List<PropertyInfo>();      PropertyInfo[] propertyInfoList = type.GetProperties();      foreach (PropertyInfo propertyInfo in propertyInfoList)      {        if (propertyInfo.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), false).Length == 0          && propertyInfo.GetCustomAttributes(typeof(BrowsableAttribute), false).Length == 0)        {          result.Add(propertyInfo);        }      }      return result.ToArray();    }    #endregion    #endregion    #region 事务    #region 开始事务    /// <summary>    /// 开始事务    /// </summary>    public static void BeginTransaction()    {      GetTran();      AddTranFlag();    }    #endregion    #region 提交事务    /// <summary>    /// 提交事务    /// </summary>    public static void CommitTransaction()    {      try      {        GetTran().Commit();        RemoveTranFlag();      }      catch (Exception ex)      {        GetTran().Rollback();        RemoveTranFlag();      }      finally      {        GetConn().Close();      }    }    #endregion    #region 回滚事务(出错时调用该方法回滚)    /// <summary>    /// 回滚事务(出错时调用该方法回滚)    /// </summary>    public static void RollbackTransaction()    {      GetTran().Rollback();      RemoveTranFlag();      GetConn().Close();    }    #endregion    #endregion  }}

View Code

    在MVC4项目的FilterConfig.cs文件中,添加ActionFilter拦截器:

using System.Web;using System.Web.Mvc;using Common;namespace TechReport.Web{  public class FilterConfig  {    public static void RegisterGlobalFilters(GlobalFilterCollection filters)    {      filters.Add(new HandleErrorAttribute());      filters.Add(new ActionFilter());    }  }}

View Code

    ActionFilter拦截器代码:

using System;using System.Collections.Generic;using System.Linq;using System.Reflection;using System.Text;using System.Web.Mvc;using DBHelper;namespace Common{  /// <summary>  /// Action拦截器  /// </summary>  public class ActionFilter : FilterAttribute, IActionFilter  {    //在执行操作方法之前调用    public void OnActionExecuting(ActionExecutingContext filterContext)    {      MethodInfo method = filterContext.Controller.GetType().GetMethod(filterContext.ActionDescriptor.ActionName);      object[] transactionAttributes = method.GetCustomAttributes(typeof(TransactionAttribute), false);      if (transactionAttributes.Length > 0)      {        OracleHelper.BeginTransaction();      }    }    //在执行操作方法后调用    public void OnActionExecuted(ActionExecutedContext filterContext)    {      MethodInfo method = filterContext.Controller.GetType().GetMethod(filterContext.ActionDescriptor.ActionName);      object[] transactionAttributes = method.GetCustomAttributes(typeof(TransactionAttribute), false);      if (transactionAttributes.Length > 0)      {        if (filterContext.Exception == null)        {          OracleHelper.CommitTransaction();        }        else        {          OracleHelper.RollbackTransaction();          filterContext.ExceptionHandled = true;          ContentResult contentResult = new ContentResult();          contentResult.Content = filterContext.Exception.Message;          filterContext.Result = contentResult;        }      }    }  }}

View Code

    TransactionAttribute类:

using System;using System.Collections.Generic;using System.Linq;using System.Text;namespace Common{  /// <summary>  /// 开启事务,添加此特性的方法不要使用try catch,若要使用,catch中应将错误再次抛出  /// </summary>  [Serializable, AttributeUsage(AttributeTargets.Method)]  public class TransactionAttribute : Attribute  {  }}

View Code

    在Action上添加[Transaction]标签:

[Transaction]public ActionResult Submit(string reportCode){  string strParams = Request["params"];  REPORTAUDIT reportAudit = new REPORTAUDIT();  REPORTFLOW reportFlow = new REPORTFLOW();  SYS_USER empdetail = m_UserDal.Get(strParams.Get("AUDITUSERNAME"));  reportAudit.AUDITUSERNAME = strParams.Get("AUDITUSERNAME");  reportAudit.AUDITEMPNAME = empdetail.EMPNAME;  reportAudit.AUDITTYPE = (int)Enums.AuditType.审核;  reportAudit.ISFINISHED = 0;  reportAudit.REPORTCODE = reportCode;  reportAudit.TASKTIME = DateTime.Now;  long auditSID = m_ReportAudit_DAL.GetAuditSID(reportCode, 2);  if (m_ReportAudit_DAL.Exists(auditSID))  {    if (m_ReportAudit_DAL.Get(auditSID).ISFINISHED == 0)    {      return Content("正在提交,请稍后!");    }    else    {      reportAudit.AUDITSID = auditSID;      m_ReportAudit_DAL.Update(reportAudit);    }  }  else  {    reportAudit.AUDITSID = m_ReportAudit_DAL.GetMaxID();    m_ReportAudit_DAL.Insert(reportAudit);  }  reportFlow.FLOWSERIALID = m_ReportFlow_DAL.GetMaxID();  reportFlow.OPERATOREMPNAME = AdminUtil.LoginUser.EMPNAME;  reportFlow.OPRATORUSERNAME = AdminUtil.LoginUser.USERNAME;  reportFlow.OPERATORTIME = DateTime.Now;  reportFlow.OPERATORTYPE = 4;  reportFlow.OPINION = strParams.Get("OPINION");  REPORT report = m_Report_DAL.GetByReportCode(reportCode);  report.CALLBACKSTATE = 0;   report.REPORTSTATEID = (long)Enums.ReportState.待审核;   report.SYSTAR = int.Parse(strParams.Get("SYSTAR"));   report.SECLEVELID = int.Parse(strParams.Get("SECLEVELID"));   report.COVERID = long.Parse(strParams.Get("COVERID"));   m_Report_DAL.Update(report);  int flag = Convert.ToInt32(report.COVERID);  REPORTFILE reportFile = m_ReportFile_DAL.Get(reportCode);  if (reportFile != null)  {    string filename = Server.MapPath(m_ReportFile_DAL.Get(reportCode).FILEADDR.ToString());    if ((flag != 6) && (flag != 7))    {      AddPageInfo(filename, flag, reportCode);    }  }  m_ReportFlow_DAL.Insert(reportFlow);   return Content("OK");}

View Code

    DAL示例:

using System;using System.Collections.Generic;using System.Linq;using System.Text;using DBHelper;using Models;namespace DAL{  /// <summary>  ///   /// </summary>  public class ReportAudit_DAL  {    #region 变量    private OracleHelper dbHelper = new OracleHelper();    #endregion    #region 添加    public void Insert(REPORTAUDIT model)    {      dbHelper.Insert(model);    }    #endregion      }}

View Code