保存李天平老师的一个数据库处理的类

代码
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);

        }

 

        
/// <summary>

        
/// 执行存储过程,返回输出参数的值和影响的行数      

        
/// </summary>

        
/// <param name="storedProcName">存储过程名</param>

        
/// <param name="parameters">存储过程参数</param>

        
/// <param name="OutParameter">输出参数名称</param>

        
/// <param name="rowsAffected">影响的行数</param>

        
/// <returns></returns>

        
public static object RunProcedure(string storedProcName, IDataParameter[] InParameters, SqlParameter OutParameter, int rowsAffected)

        {

            Database db 
= DatabaseFactory.CreateDatabase();

            DbCommand dbCommand 
= db.GetStoredProcCommand(storedProcName);

            BuildDBParameter(db, dbCommand, (SqlParameter[])InParameters);

            db.AddOutParameter(dbCommand, OutParameter.ParameterName, OutParameter.DbType, OutParameter.Size);

            rowsAffected 
= db.ExecuteNonQuery(dbCommand);

            
return db.GetParameterValue(dbCommand,"@" + OutParameter.ParameterName);  //得到输出参数的值

        }

 

         
/// <summary>

        
/// 执行存储过程,返回SqlDataReader ( 注意:使用后一定要对SqlDataReader进行Close )

         
/// </summary>

         
/// <param name="storedProcName">存储过程名</param>

         
/// <param name="parameters">存储过程参数</param>

         
/// <returns>SqlDataReader</returns>

         
public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )

         {

            Database db 
= DatabaseFactory.CreateDatabase();

            DbCommand dbCommand 
= db.GetStoredProcCommand(storedProcName, parameters);           

            
//BuildDBParameter(db, dbCommand, parameters);

            
return (SqlDataReader)db.ExecuteReader(dbCommand);          

         }

                  

         
/// <summary>

        
/// 执行存储过程,返回DataSet

         
/// </summary>

         
/// <param name="storedProcName">存储过程名</param>

         
/// <param name="parameters">存储过程参数</param>

         
/// <param name="tableName">DataSet结果中的表名</param>

         
/// <returns>DataSet</returns>

         
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )

         {          

            Database db 
= DatabaseFactory.CreateDatabase();

            DbCommand dbCommand 
= db.GetStoredProcCommand(storedProcName, parameters);

            
//BuildDBParameter(db, dbCommand, parameters);

            
return db.ExecuteDataSet(dbCommand);

         }

        
/// <summary>

        
/// 执行存储过程,返回DataSet(设定等待时间)

        
/// </summary>

         
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName ,int Times)

         {          

            Database db 
= DatabaseFactory.CreateDatabase();

            DbCommand dbCommand 
= db.GetStoredProcCommand(storedProcName, parameters);

            dbCommand.CommandTimeout 
= Times;

            
//BuildDBParameter(db, dbCommand, parameters);

            
return db.ExecuteDataSet(dbCommand);

         }

 

        

         
/// <summary>

         
/// 构建SqlCommand 对象(用来返回一个结果集,而不是一个整数值)

         
/// </summary>

         
/// <param name="connection">数据库连接</param>

         
/// <param name="storedProcName">存储过程名</param>

         
/// <param name="parameters">存储过程参数</param>

         
/// <returns>SqlCommand</returns>

         
private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)

         {            

              SqlCommand command 
= new SqlCommand( storedProcName, connection );

              command.CommandType 
= CommandType.StoredProcedure;

              
foreach (SqlParameter 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;            

         }       

         
/// <summary>

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

         
/// </summary>

         
/// <param name="storedProcName">存储过程名</param>

         
/// <param name="parameters">存储过程参数</param>

         
/// <returns>SqlCommand 对象实例</returns>

         
private static SqlCommand BuildIntCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)

         {

              SqlCommand command 
= BuildQueryCommand(connection,storedProcName, parameters );

              command.Parameters.Add( 
new SqlParameter ( "ReturnValue",

                   SqlDbType.Int,
4,ParameterDirection.ReturnValue,

                   
false,0,0,string.Empty,DataRowVersion.Default,null ));

              
return command;

         }

         
#endregion   

 

     }

对使用现有Codematic代码生成的用户想使用企业库的话,可以直接替换此类。
同时,对于直接对企业库的DAL代码生成,希望在以后有时间会加进去,以方便大家不同方式的开发。(李天平)

 

posted @ 2011-01-17 20:10  ccczqh  阅读(509)  评论(1编辑  收藏  举报