对EnterpriseLibrary2.0进一步封装:DbHelperSQL2
/// Enterprise Library 2.0 数据访问进一步封装类
/// Copyright (C) 2006-2008 LiTianPing
/// All rights reserved
///
public abstract class DbHelperSQL2
{
public DbHelperSQL2()
{
}
#region 公用方法
///
/// 获取表某个字段的最大值
///
///
///
///
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());
}
}
///
/// 检测一个记录是否存在(SQL语句方式)
///
///
///
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;
}
}
///
/// 检测一个记录是否存在(SqlParameter语句方式)
///
///
///
///
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;
}
}
///
/// 加载参数
///
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语句
///
/// 执行SQL语句，返回影响的记录数
///
/// SQL语句
///
public static int ExecuteSql(string strSql)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand(strSql);
return db.ExecuteNonQuery(dbCommand);
}
///
/// 执行SQL语句，返回影响的记录数(对于长时间查询的语句，设置等待时间避免查询超时)
///
///
///
///
public static int ExecuteSqlByTime(string strSql,int Times)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand(strSql);
dbCommand.CommandTimeout = Times;
return db.ExecuteNonQuery(dbCommand);
}
///
/// 执行多条SQL语句，实现数据库事务。
///
/// 多条SQL语句
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 执行一个特殊字段带参数的语句
///
/// 执行带一个存储过程参数的的SQL语句。
///
/// SQL语句
/// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加
///
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);
}
///
/// 执行带一个存储过程参数的的SQL语句。
///
/// SQL语句
/// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加
///
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;
}
}
///
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
///
/// SQL语句
/// 图像字节,数据库的字段类型为image的情况
///
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
///
/// 执行一条计算查询结果语句，返回查询结果（object）。
///
/// 计算查询结果语句
///
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;
}
}
///
/// 执行查询语句，返回SqlDataReader ( 注意：使用后一定要对SqlDataReader进行Close )
///
/// 查询语句
///
public static SqlDataReader ExecuteReader(string strSql)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand(strSql);
SqlDataReader dr = (SqlDataReader)db.ExecuteReader(dbCommand);
return dr;
}
///
/// 执行查询语句，返回DataSet
///
/// 查询语句
///
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语句
///
/// 执行SQL语句，返回影响的记录数
///
/// SQL语句
///
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);
}
///
/// 执行多条SQL语句，实现数据库事务。
///
/// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）
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();
}
}
}
///
/// 执行一条计算查询结果语句，返回查询结果（object）。
///
/// 计算查询结果语句
///
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;
}
}
///
/// 执行查询语句，返回SqlDataReader ( 注意：使用后一定要对SqlDataReader进行Close )
///
/// 查询语句
///
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;
}
///
/// 执行查询语句，返回DataSet
///
/// 查询语句
///
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 存储过程操作
///
/// 执行存储过程，返回影响的行数
///
public static int RunProcedure(string storedProcName)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand(storedProcName);
return db.ExecuteNonQuery(dbCommand);
}
///
/// 执行存储过程，返回输出参数的值和影响的行数
///
/// 存储过程名
/// 存储过程参数
/// 输出参数名称
/// 影响的行数
///
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); //得到输出参数的值
}
///
/// 执行存储过程，返回SqlDataReader ( 注意：使用后一定要对SqlDataReader进行Close )
///
/// 存储过程名
/// 存储过程参数
///
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);
}
///
/// 执行存储过程，返回DataSet
///
/// 存储过程名
/// 存储过程参数
/// DataSet结果中的表名
///
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);
}
///
/// 执行存储过程，返回DataSet(设定等待时间)
///
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);
}
///
/// 构建SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
///
/// 数据库连接
/// 存储过程名
/// 存储过程参数
///
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;
}
///
/// 创建SqlCommand 对象实例(用来返回一个整数值)
///
/// 存储过程名
/// 存储过程参数
///
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代码生成，希望在以后有时间会加进去，以方便大家不同方式的开发，同时该类源码和具体使用方法在范例项目Codematicdemo里完整提供免费下载。(作者：李天平)
