学步园

Enterprise Library 2.0很多人都已熟悉，有的人也在很多项目里做了应用，
很早以前也使用企业库的2.0做了一些项目，但感觉很多代码还是有些冗余和重复，
同时为了兼容现有的系统结构而不影响现有数据访问方式（包括代码生成器的DAL代码）不变，
对Enterprise Library 2.0 的调用进行了进一步封装(按我过去DbHelperSQL的思路)，在不改变现有的DAL的方式下来使用企业库。
当然，你完全可以直接使用企业库做一些更灵活的应用，这里只是抛砖引玉的一个解决方案。
 
     /// <summary>
    /// Enterprise Library 2.0 数据访问进一步封装类
     /// Copyright (C) 2006-2008 LiTianPing
     /// All rights reserved
     /// </summary>

     public abstract class DbHelperSQL2
     {       
         public DbHelperSQL2()
         {
        }
 
        #region 公用方法
        /// <summary>
        /// 获取表某个字段的最大值
        /// </summary>
        /// <param name="FieldName"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>

        public static int GetMaxID(string FieldName,string TableName)
        {
            string strSql = "select max(" + FieldName + ")+1 from " + TableName;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);          
            object obj = db.ExecuteScalar(dbCommand);
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }          
        }
        /// <summary>
        /// 检测一个记录是否存在(SQL语句方式)
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>

         public static bool Exists(string strSql)
         {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            object obj = db.ExecuteScalar(dbCommand);
              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;
              }
         }
        /// <summary>
        ///  检测一个记录是否存在(SqlParameter语句方式)
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>

        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            BuildDBParameter(db, dbCommand, cmdParms);           
            object obj = db.ExecuteScalar(dbCommand);          
            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;
            }
        }
       
        /// <summary>
        /// 加载参数
        /// </summary>

        public static void BuildDBParameter(Database db, DbCommand dbCommand, params SqlParameter[] cmdParms)
        {
            foreach (SqlParameter sp in cmdParms)
            {
                db.AddInParameter(dbCommand, sp.ParameterName, sp.DbType,sp.Value);
            }
        }
        #endregion
 
        #region  执行简单SQL语句
 
        /// <summary>
         /// 执行SQL语句，返回影响的记录数
         /// </summary>
         /// <param name="strSql">SQL语句</param>
         /// <returns>影响的记录数</returns>

        public static int ExecuteSql(string strSql)
         {   
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            return db.ExecuteNonQuery(dbCommand);
         }
        /// <summary>
        /// 执行SQL语句，返回影响的记录数(对于长时间查询的语句，设置等待时间避免查询超时)
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="Times"></param>
        /// <returns></returns>

         public static int ExecuteSqlByTime(string strSql,int Times)
         {          
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            dbCommand.CommandTimeout = Times;
            return db.ExecuteNonQuery(dbCommand);
         }
        
         /// <summary>
         /// 执行多条SQL语句，实现数据库事务。
         /// </summary>
         /// <param name="SQLStringList">多条SQL语句</param>    

         public static void ExecuteSqlTran(ArrayList SQLStringList)
         {
 
            Database db = DatabaseFactory.CreateDatabase();
            using (DbConnection dbconn = db.CreateConnection())
            {
                dbconn.Open();
                DbTransaction dbtran = dbconn.BeginTransaction();
                try
                {
                    //执行语句

                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            DbCommand dbCommand = db.GetSqlStringCommand(strsql);
                            db.ExecuteNonQuery(dbCommand);
                        }
                    }
                    //执行存储过程
                    //db.ExecuteNonQuery(CommandType.StoredProcedure, "InserOrders");
                    //db.ExecuteDataSet(CommandType.StoredProcedure, "UpdateProducts");

                    dbtran.Commit();
                }
                catch
                {
                    dbtran.Rollback();
                }
                finally
                {
                    dbconn.Close();
                }
            }
        }
 
        #region 执行一个特殊字段带参数的语句
        /// <summary>
         /// 执行带一个存储过程参数的的SQL语句。
         /// </summary>
         /// <param name="strSql">SQL语句</param>
         /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
         /// <returns>影响的记录数</returns>

         public static int ExecuteSql(string strSql,string content)
         {   
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            db.AddInParameter(dbCommand, "@content", DbType.String, content);
            return db.ExecuteNonQuery(dbCommand);
         }       
        
        /// <summary>
         /// 执行带一个存储过程参数的的SQL语句。
         /// </summary>
         /// <param name="strSql">SQL语句</param>
         /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
         /// <returns>返回语句里的查询结果</returns>

         public static object ExecuteSqlGet(string strSql,string content)
         {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            db.AddInParameter(dbCommand, "@content", DbType.String, content);
            object obj = db.ExecuteNonQuery(dbCommand);
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                return null;
            }
            else
            {
                return obj;
            }     
         }       
        
        /// <summary>
         /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
         /// </summary>
         /// <param name="strSql">SQL语句</param>
         /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
         /// <returns>影响的记录数</returns>

         public static int ExecuteSqlInsertImg(string strSql,byte[] fs)
         {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            db.AddInParameter(dbCommand, "@fs", DbType.Byte, fs);
            return db.ExecuteNonQuery(dbCommand);           
        }
        #endregion
 
        /// <summary>
         /// 执行一条计算查询结果语句，返回查询结果（object）。
         /// </summary>
         /// <param name="strSql">计算查询结果语句</param>
         /// <returns>查询结果（object）</returns>

         public static object GetSingle(string strSql)
         {           
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            object obj = db.ExecuteScalar(dbCommand);
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                return null;
            }
            else
            {
                return obj;
            }     
         }
        
        /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：使用后一定要对SqlDataReader进行Close )
         /// </summary>
         /// <param name="strSql">查询语句</param>
         /// <returns>SqlDataReader</returns>

        public static SqlDataReader ExecuteReader(string strSql)
         {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            SqlDataReader dr = (SqlDataReader)db.ExecuteReader(dbCommand);
            return dr;     
             
         }       
        
        /// <summary>
         /// 执行查询语句，返回DataSet
         /// </summary>
         /// <param name="strSql">查询语句</param>
         /// <returns>DataSet</returns>

         public static DataSet Query(string strSql)
         {           
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            return db.ExecuteDataSet(dbCommand);
           
         }
        //(对于长时间查询的语句，设置等待时间避免查询超时)

         public static DataSet Query(string strSql,int Times)
         {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            dbCommand.CommandTimeout = Times;
            return db.ExecuteDataSet(dbCommand);
         }
 
         #endregion
 
         #region 执行带参数的SQL语句
 
         /// <summary>
         /// 执行SQL语句，返回影响的记录数
         /// </summary>
         /// <param name="strSql">SQL语句</param>
         /// <returns>影响的记录数</returns>

         public static int ExecuteSql(string strSql,params SqlParameter[] cmdParms)
         {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            BuildDBParameter(db, dbCommand, cmdParms);   
            return db.ExecuteNonQuery(dbCommand);
         }
        
             
         /// <summary>
         /// 执行多条SQL语句，实现数据库事务。
         /// </summary>
         /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>

         public static void ExecuteSqlTran(Hashtable SQLStringList)
         {
            Database db = DatabaseFactory.CreateDatabase();
            using (DbConnection dbconn = db.CreateConnection())
            {
                dbconn.Open();
                DbTransaction dbtran = dbconn.BeginTransaction();
                try
                {
                    //执行语句

                    foreach (DictionaryEntry myDE in SQLStringList)
                    {
                        string strsql = myDE.Key.ToString();
                        SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;                       
                        if (strsql.Trim().Length > 1)
                        {
                            DbCommand dbCommand = db.GetSqlStringCommand(strsql);
                            BuildDBParameter(db, dbCommand, cmdParms);   
                            db.ExecuteNonQuery(dbCommand);
                        }
                    }
                    dbtran.Commit();
                }
                catch
                {
                    dbtran.Rollback();
                }
                finally
                {
                    dbconn.Close();
                }
            }
         }
    
                  
         /// <summary>
         /// 执行一条计算查询结果语句，返回查询结果（object）。
         /// </summary>
         /// <param name="strSql">计算查询结果语句</param>
         /// <returns>查询结果（object）</returns>

         public static object GetSingle(string strSql,params SqlParameter[] cmdParms)
         {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            BuildDBParameter(db, dbCommand, cmdParms);   
            object obj = db.ExecuteScalar(dbCommand);
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                return null;
            }
            else
            {
                return obj;
            }     
         }
        
         /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：使用后一定要对SqlDataReader进行Close )
         /// </summary>
         /// <param name="strSql">查询语句</param>
         /// <returns>SqlDataReader</returns>

         public static SqlDataReader ExecuteReader(string strSql,params SqlParameter[] cmdParms)
         {       
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            BuildDBParameter(db, dbCommand, cmdParms);
            SqlDataReader dr = (SqlDataReader)db.ExecuteReader(dbCommand);
            return dr;
             
         }       
        
         /// <summary>
         /// 执行查询语句，返回DataSet
         /// </summary>
         /// <param name="strSql">查询语句</param>
         /// <returns>DataSet</returns>

         public static DataSet Query(string strSql,params SqlParameter[] cmdParms)
         {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            BuildDBParameter(db, dbCommand, cmdParms);  
            return db.ExecuteDataSet(dbCommand);
         }
 
 
         private static void PrepareCommand(SqlCommand cmd,SqlConnection conn,SqlTransaction trans, string cmdText, SqlParameter[] 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 (SqlParameter parameter in cmdParms)
                   {
                       if ( ( parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input ) &&
                            (parameter.Value == null))
                       {
                            parameter.Value = DBNull.Value;
                       }
                       cmd.Parameters.Add(parameter);
                   }
              }
         }
 
         #endregion
 
         #region 存储过程操作
 
        /// <summary>
        /// 执行存储过程，返回影响的行数      
        /// </summary>      

        public static int RunProcedure(string storedProcName)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcName);
            return db.ExecuteNonQuery(dbCommand);
        }
 
        ///











														
							

返回

【上篇】建立知识结构是个人知识管理的基础活动
【下篇】Linux平台PThreads库多线程编程笔记汇总

作者: 86o9sostar

• 该日志由 86o9sostar 于11年前发表在综合分类下，最后更新于 2012年11月07日.
• 转载请注明: 保存李天平老师的一个数据库处理的类 | 学步园 +复制链接

抱歉!评论已关闭.