C# DbHelp类

using System; using System.Collections; using System.Collections.Specialized; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace NMJU.Web.DBUtility { /// /// 数据访问抽象基础类(MSSQL) /// Copyright (C) 2004-2008 NMJU.NET /// All rights reserved /// public abstract class DbHelperSQL { //数据库连接字符串(web.config来配置) public static string connectionString = ConfigurationManager.AppSettings["MSSQLConnectionString"]; // public static string connectionString = LemonJu.Common.DEncrypt.DESEncrypt.Decrypt(LemonJu.Common.ConfigHelper.GetConfigString("ConnectionString")); public DbHelperSQL() { } #region 公用方法 /// /// 获取某个表的数量 /// /// 主键 /// 表名 /// 条件 /// public static int GetDataRecordCount(string field, string tableName, string where) { string strsql = "select count(" + field + ") from " + tableName; if (where != "") { strsql += " where " + where; } object obj = DbHelperSQL.GetSingle(strsql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } public static int GetMaxID(string FieldName, string TableName) { string strsql = "select max(" + FieldName + ")+1 from " + TableName; object obj = DbHelperSQL.GetSingle(strsql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } public static bool Exists(string strSql) { object obj = DbHelperSQL.GetSingle(strSql); 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 bool Exists(string strSql, params SqlParameter[] cmdParms) { object obj = DbHelperSQL.GetSingle(strSql, cmdParms); 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; } } #endregion #region 执行简单SQL语句 /// /// 执行SQL语句，返回影响的记录数 /// /// SQL语句 /// 影响的记录数 public static int ExecuteSql(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException E) { connection.Close(); throw new Exception(E.Message); //ITNB.Base.Error.showError(E.Message.ToString()); } } } } /// /// 执行SQL语句，设置命令的执行等待时间 /// /// /// /// public static int ExecuteSqlByTime(string SQLString, int Times) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); cmd.CommandTimeout = Times; int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException E) { connection.Close(); throw new Exception(E.Message); // ITNB.Base.Error.showError(E.Message.ToString()); } } } } /// /// 执行多条SQL语句，实现数据库事务。 /// /// 多条SQL语句 public static void ExecuteSqlTran(ArrayList SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (System.Data.SqlClient.SqlException E) { tx.Rollback(); throw new Exception(E.Message); // ITNB.Base.Error.showError(E.Message.ToString()); } } } /// /// 执行带一个存储过程参数的的SQL语句。 /// /// SQL语句 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 /// 影响的记录数 public static int ExecuteSql(string SQLString, string content) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(SQLString, connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); // ITNB.Base.Error.showError(E.Message.ToString()); } finally { cmd.Dispose(); connection.Close(); } } } /// /// 执行带一个存储过程参数的的SQL语句。 /// /// SQL语句 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 /// 影响的记录数 public static object ExecuteSqlGet(string SQLString, string content) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(SQLString, connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); // ITNB.Base.Error.showError(E.Message.ToString()); } finally { cmd.Dispose(); connection.Close(); } } } /// /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// /// SQL语句 /// 图像字节,数据库的字段类型为image的情况 /// 影响的记录数 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(strSQL, connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image); myParameter.Value = fs; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); //ITNB.Base.Error.showError(E.Message.ToString()); } finally { cmd.Dispose(); connection.Close(); } } } /// /// 执行一条计算查询结果语句，返回查询结果（object）。 /// /// 计算查询结果语句 /// 查询结果（object） public static object GetSingle(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { connection.Close(); throw new Exception(e.Message); // ITNB.Base.Error.showError(e.Message.ToString()); } } } } /// /// 执行查询语句，返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接) /// /// 查询语句 /// SqlDataReader public static SqlDataReader ExecuteReader(string strSQL) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(strSQL, connection); try { connection.Open(); SqlDataReader myReader = cmd.ExecuteReader(); return myReader; } catch (System.Data.SqlClient.SqlException e) { throw new Exception(e.Message); // ITNB.Base.Error.showError(e.Message.ToString()); } //finally //不能在此关闭，否则，返回的对象将无法使用 //{ // cmd.Dispose(); // connection.Close(); //} } /// /// 执行查询语句，返回DataSet /// /// 查询语句 /// DataSet public static DataSet Query(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); // ITNB.Base.Error.showError(E.Message.ToString()); } return ds; } } /// /// 执行查询语句，返回DataSet,设置命令的执行等待时间 /// /// /// /// public static DataSet Query(string SQLString, int Times) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.SelectCommand.CommandTimeout = Times; command.Fill(ds, "ds"); } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); // ITNB.Base.Error.showError(ex.Message.ToString()); } return ds; } } #endregion #region 执行带参数的SQL语句 /// /// 执行SQL语句，返回影响的记录数 /// /// SQL语句 /// 影响的记录数 public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); // ITNB.Base.Error.showError(E.Message.ToString()); } } } } /// /// 执行多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） public static void ExecuteSqlTran(Hashtable SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); trans.Commit(); } } catch { trans.Rollback(); throw; } } } } /// /// 执行一条计算查询结果语句，返回查询结果（object）。 /// /// 计算查询结果语句 /// 查询结果（object） public static object GetSingle(string SQLString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { throw new Exception(e.Message); // ITNB.Base.Error.showError(e.Message.ToString()); } } } } /// /// 执行查询语句，返回SqlDataReader (使用该方法切记要手工关闭SqlDataReader和连接) /// /// 查询语句 /// SqlDataReader public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); SqlDataReader myReader = cmd.ExecuteReader(); cmd.Parameters.Clear(); return myReader; } catch (System.Data.SqlClient.SqlException e) { throw new Exception(e.Message); // ITNB.Base.Error.showError(e.Message.ToString()); } //finally //不能在此关闭，否则，返回的对象将无法使用 //{ // cmd.Dispose(); // connection.Close(); //} } /// /// 执行查询语句，返回DataSet /// /// 查询语句 /// DataSet public static DataSet Query(string SQLString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); // ITNB.Base.Error.showError(ex.Message.ToString()); } return ds; } } } 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 存储过程操作 /// /// 执行存储过程 (使用该方法切记要手工关闭SqlDataReader和连接) /// /// 存储过程名 /// 存储过程参数 /// SqlDataReader public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters) { SqlConnection connection = new SqlConnection(connectionString); SqlDataReader returnReader; connection.Open(); SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.CommandType = CommandType.StoredProcedure; returnReader = command.ExecuteReader(); //Connection.Close(); 不能在此关闭，否则，返回的对象将无法使用 return returnReader; } /// /// 执行存储过程 /// /// 存储过程名 /// 存储过程参数 /// DataSet结果中的表名 /// DataSet public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.Fill(dataSet, tableName); connection.Close(); return dataSet; } } public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.SelectCommand.CommandTimeout = Times; sqlDA.Fill(dataSet, tableName); connection.Close(); return dataSet; } } /// /// 执行存储过程后返回执行结果（标识） /// /// /// /// public static string RunProcedureState(string storedProcName, IDataParameter[] parameters) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); //增加存储过程的返回值参数 sqlDA.SelectCommand.ExecuteNonQuery(); connection.Close(); return sqlDA.SelectCommand.Parameters["ReturnValue"].Value.ToString(); } } /* @TableNames VARCHAR(200), --表名，可以是多个表，但不能用别名 @PrimaryKey VARCHAR(100), --主键，可以为空，但@Order为空时该值不能为空 @Fields VARCHAR(200), --要取出的字段，可以是多个表的字段，可以为空，为空表示select * @PageSize INT, --每页记录数 @CurrentPage INT, --当前页，0表示第1页 @Filter VARCHAR(200) = '', --条件，可以为空，不用填 where @Group VARCHAR(200) = '', --分组依据，可以为空，不用填 group by @Order VARCHAR(200) = '' --排序，可以为空，为空默认按主键升序排列，不用填 order by */ /// /// 关键字，显示字段，表，条件，排序，每页显示数，当前页 /// /// 主键 /// 要取出的字段 /// 表名 /// 条件 /// 排序 /// 每页记录数INT /// 当前页，INT /// public static DataSet GetPageDataList(string PrimaryKey, string Fields, string TableNames, string Filter, string Order, int PageSize, int CurrentPage) { string tableName = "viewPage"; string storedProcName = "P_viewPage"; IDataParameter[] p = new IDataParameter[8]; p[0] = new SqlParameter("TableNames", TableNames); p[1] = new SqlParameter("PrimaryKey", PrimaryKey); p[2] = new SqlParameter("Fields", Fields); p[3] = new SqlParameter("PageSize", PageSize); p[4] = new SqlParameter("CurrentPage", CurrentPage - 1); p[5] = new SqlParameter("Filter", Filter); p[6] = new SqlParameter("Group", ""); p[7] = new SqlParameter("Order", Order); return RunProcedure(storedProcName, p, tableName); } public static DataSet GetPageDataList(string PrimaryKey, string Fields, string TableNames, string Filter, string Order, int PageSize, int CurrentPage, string Group) { string tableName = "viewPage"; string storedProcName = "P_viewPage"; IDataParameter[] p = new IDataParameter[8]; p[0] = new SqlParameter("TableNames", TableNames); p[1] = new SqlParameter("PrimaryKey", PrimaryKey); p[2] = new SqlParameter("Fields", Fields); p[3] = new SqlParameter("PageSize", PageSize); p[4] = new SqlParameter("CurrentPage", CurrentPage - 1); p[5] = new SqlParameter("Filter", Filter); p[6] = new SqlParameter("Group", Group); p[7] = new SqlParameter("Order", Order); return RunProcedure(storedProcName, p, tableName); } /* @TableName VARCHAR(200), --表名 @FieldList VARCHAR(2000), --显示列名，如果是全部字段则为* @PrimaryKey VARCHAR(100), --单一主键或唯一值键 @Where VARCHAR(2000), --查询条件 不含'where'字符，如id>10 and len(userid)>9 @Order VARCHAR(1000), --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc --注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷 @SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法 @RecorderCount INT, --记录总数 0:会返回总记录 @PageSize INT, --每页输出的记录数 @PageIndex INT, --当前页数 @TotalCount INT OUTPUT, --记返回总记录 @TotalPageCount INT OUTPUT --返回总页数 */ public static DataSet GetPageDataList2(string PrimaryKey, string FieldList, string TableName, string Where, string Order, int PageSize, int PageIndex) { string tableName = "viewPage"; string storedProcName = "P_viewPage2"; IDataParameter[] p = new IDataParameter[11]; p[0] = new SqlParameter("TableName", TableName); p[1] = new SqlParameter("FieldList", FieldList); p[2] = new SqlParameter("PrimaryKey", PrimaryKey); p[3] = new SqlParameter("Where", Where); p[4] = new SqlParameter("Order", Order); p[5] = new SqlParameter("SortType", 3); p[6] = new SqlParameter("RecorderCount", 0); p[7] = new SqlParameter("PageSize", PageSize); p[8] = new SqlParameter("PageIndex", PageIndex); p[9] = new SqlParameter("TotalCount", 0); p[10] = new SqlParameter("TotalPageCount", 0); return RunProcedure(storedProcName, p, tableName); } /// /// 构建 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; } /// /// 执行存储过程，返回影响的行数 /// /// 存储过程名 /// 存储过程参数 /// 影响的行数 /// public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { using (SqlConnection connection = new SqlConnection(connectionString)) { int result; connection.Open(); SqlCommand command = BuildIntCommand(connection, storedProcName, parameters); rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["ReturnValue"].Value; //Connection.Close(); return result; } } /// /// 创建 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 #region SQL语句式分页 /// /// 智能返回SQL语句 /// /// 主键（不能为空） /// 提取字段（不能为空） /// 表（理论上允许多表） /// 条件（可以空） /// 排序，格式：字段名+""+ASC（可以空） /// 分页数（不能为空） /// 当前页，起始为：1（不能为空） /// public static DataSet GetPageDataListSQL(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex) { string strTmp = ""; //---strTmp用于返回的SQL语句 string SqlSelect = "", SqlPrimaryKeySelect = "", strOrderBy = "", strWhere = " where 1=1 ", strTop = ""; //0：分页数量 //1:提取字段 //2:表 //3:条件 //4:主键不存在的记录 //5:排序 SqlSelect = " select top {0} {1} from {2} {3} {4} {5}"; //0:主键 //1:TOP数量,为分页数*(排序号-1) //2:表 //3:条件 //4:排序 SqlPrimaryKeySelect = " and {0} not in (select {1} {0} from {2} {3} {4}) "; if (orderBy != "") strOrderBy = " order by " + orderBy; if (condition != "") strWhere += " and " + condition; int pageindexsize = (pageIndex - 1) * pageSize; if (pageindexsize > 0) { strTop = " top " + pageindexsize.ToString(); SqlPrimaryKeySelect = String.Format(SqlPrimaryKeySelect, primaryKey, strTop, tableName, strWhere, strOrderBy); strTmp = String.Format(SqlSelect, pageSize.ToString(), queryFields, tableName, strWhere, SqlPrimaryKeySelect, strOrderBy); } else { strTmp = String.Format(SqlSelect, pageSize.ToString(), queryFields, tableName, strWhere, "", strOrderBy); } return Query(strTmp); } #endregion #region 获取安全的SQL字符串 /// /// 获取安全的SQL字符串 /// /// /// public static string GetSafeSQLString(string sql) { sql = sql.Replace(",", "，"); sql = sql.Replace(".", "。"); sql = sql.Replace("(", "（"); sql = sql.Replace(")", "）"); sql = sql.Replace(">", "＞"); sql = sql.Replace("<", "＜"); sql = sql.Replace("-", "－"); sql = sql.Replace("+", "＋"); sql = sql.Replace("=", "＝"); sql = sql.Replace("?", "？"); sql = sql.Replace("*", "＊"); sql = sql.Replace("|", "｜"); sql = sql.Replace("&", "＆"); return sql; } #endregion } }