对EnterpriseLibrary2.0进一步封装:DbHelperSQL2

爱被打了一巴掌 2022-03-29 05:58 143阅读 0赞

///<?XML:NAMESPACE PREFIX = O />

/// Enterprise Library 2.0 数据访问进一步封装类

///

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）。

///

///计算查询结果语句

///查询结果（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 )

///

///查询语句

///SqlDataReader

public static SqlDataReader ExecuteReader(string strSql)

Database db = DatabaseFactory.CreateDatabase();

DbCommand dbCommand = db.GetSqlStringCommand(strSql);

SqlDataReader dr = (SqlDataReader)db.ExecuteReader(dbCommand);

return dr;

///

///执行查询语句，返回DataSet

///

///查询语句

///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）。

///

///计算查询结果语句

///查询结果（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 )

///

///查询语句

///SqlDataReader

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

///

///查询语句

///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 )

///

///存储过程名

///存储过程参数

///SqlDataReader

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结果中的表名

///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 对象(用来返回一个结果集，而不是一个整数值)

///

///数据库连接

///存储过程名

///存储过程参数

///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 对象实例(用来返回一个整数值)

///

///存储过程名

///存储过程参数

///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][]里完整提供[免费下载][Codematicdemo]。(作者：[李天平][Link 1])

[Codematic]: http://www.maticsoft.com/Softlist.aspx
[Codematicdemo]: http://www.maticsoft.com/softdown.aspx
[Link 1]: http://ltp.cnblogs.com/