private static readonly string connString; static SqlHelper() { var conn = ConfigurationManager.ConnectionStrings["connStr"]; if (conn != null) connString = conn.ConnectionString; } #region MyRegion 查询方法 + GetTable(string sql, CommandType type, params SqlParameter[] pars) /// <summary> /// 查询方法 /// </summary> /// <param name="sql">sql语句</param> /// <param name="type">类型：存储过程 OR 字符串?</param> /// <param name="pars">参数列表</param> /// <returns></returns> public static DataTable GetTable(string sql, CommandType type, params SqlParameter[] pars) { using (SqlConnection conn = new SqlConnection(connString)) { using (SqlDataAdapter apter = new SqlDataAdapter(sql, conn)) { apter.SelectCommand.CommandType = type; if (pars != null) { apter.SelectCommand.Parameters.AddRange(pars); } DataTable da = new DataTable(); apter.Fill(da); return da; } } } #endregion #region 查询方法，默认命令类型：字符串 +static DataTable GetTable(string sql, params SqlParameter[] pars) /// <summary> /// 查询方法，默认类型：字符串 /// </summary> /// <param name="sql">sql语句</param> /// <param name="pars">参数列表</param> /// <returns></returns> public static DataTable GetTable(string sql, params SqlParameter[] pars) { return GetTable(sql, CommandType.Text, pars); } #endregion #region 查询方法 + static SqlDataReader ExecuteReader(string sql, CommandType type, SqlParameter[] pars) /// <summary> /// 执行一个查询的T-SQL语句, 返回一个SqlDataReader对象 /// </summary> /// <param name="sql">要执行的T-SQL语句</param> /// <param name="type">命令类型</param> /// <param name="pars">参数列表</param> /// <returns>SqlDataReader对象</returns> public static SqlDataReader ExecuteReader(string sql, CommandType type, SqlParameter[] pars) { using (SqlConnection conn = new SqlConnection(connString)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { if (pars != null) { cmd.Parameters.AddRange(pars); } cmd.CommandType = type; if (conn.State != ConnectionState.Open) conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); return reader; } } } #endregion #region 查询方法 默认命令类型：字符串 + static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters) /// <summary> /// 执行一个查询的T-SQL语句, 返回一个SqlDataReader对象 /// </summary> /// <param name="sql">要执行的T-SQL语句</param> /// <param name="parameters">参数列表</param> /// <returns>SqlDataReader对象</returns> public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters) { return ExecuteReader(sql, CommandType.Text, parameters); } #endregion #region 修改、添加、删除方法 +ExecuteNonquery(string sql, CommandType type, params SqlParameter[] pars) /// <summary> /// 修改、添加、删除方法 /// </summary> /// <param name="sql"></param> /// <param name="type"></param> /// <param name="pars"></param> /// <returns></returns> public static int ExecuteNonquery(string sql, CommandType type, params SqlParameter[] pars) { using (SqlConnection conn = new SqlConnection(connString)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = type; if (pars != null) cmd.Parameters.AddRange(pars); if (conn.State != ConnectionState.Open) conn.Open(); return cmd.ExecuteNonQuery(); } } } #endregion #region 修改、添加、删除方法，默认字符串 + static int ExecuteNonquery(string sql, params SqlParameter[] pars) /// <summary> /// 修改、添加、删除方法，默认字符串 /// </summary> /// <param name="sql"></param> /// <param name="pars"></param> /// <returns></returns> public static int ExecuteNonquery(string sql, params SqlParameter[] pars) { return ExecuteNonquery(sql, CommandType.Text, pars); } #endregion #region ExecuteScalar +static object ExecuteScalar(string cmdText, CommandType type, params SqlParameter[] parameters) /// <summary> /// 执行一个查询的T-SQL语句，返回第一行第一列的结果 /// </summary> /// <param name="cmdText">要执行的T-SQL语句</param> /// <param name="type">命令类型</param> /// <param name="parameters">参数列表</param> /// <exception cref="链接数据库异常"></exception> /// <returns>返回第一行第一列的数据</returns> public static object ExecuteScalar(string cmdText, CommandType type, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connString)) { using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { if (parameters != null) { cmd.Parameters.Clear(); cmd.Parameters.AddRange(parameters); } cmd.CommandType = type; try { conn.Open(); object res = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return res; } catch (System.Data.SqlClient.SqlException e) { conn.Close(); throw e; } } } } #endregion #region ExecuteScalar +static object ExecuteScalar(string cmdText, params SqlParameter[] parameters) /// <summary> /// 执行一个查询的T-SQL语句，返回第一行第一列的结果 /// </summary> /// <param name="cmdText">要执行的T-SQL语句</param> /// <param name="parameters">参数列表</param> /// <exception cref="链接数据库异常"></exception> /// <returns>返回第一行第一列的数据</returns> public static object ExecuteScalar(string cmdText, params SqlParameter[] parameters) { return ExecuteScalar(cmdText, CommandType.Text, parameters); } #endregion #region 公共方法 #region 将一个SqlDataReader转换为实体类对象 + static TEntity MapEntity<TEntity>(SqlDataReader reader) where TEntity : class, new() /// <summary> /// 将一个SqlDataReader转换为实体类对象 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> /// <param name="reader">当前指向的reader</param> /// <returns>实体对象</returns> public static TEntity MapEntity<TEntity>(SqlDataReader reader) where TEntity : class, new() { try { var props = typeof(TEntity).GetProperties(); var entity = new TEntity(); foreach (var prop in props) { if (prop.CanWrite) { try { var index = reader.GetOrdinal(prop.Name); var data = reader.GetValue(index); if (data != DBNull.Value) { prop.SetValue(entity, Convert.ChangeType(data, prop.PropertyType), null); } } catch (IndexOutOfRangeException) { continue; } } } return entity; } catch { return null; } } #endregion #region 将dataTable转换为实体 + static List<TEntity> MapEntity<TEntity>(DataTable table) where TEntity : class, new() /// <summary> /// 将dataTable转换为实体 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="table"></param> /// <returns></returns> public static List<TEntity> MapEntity<TEntity>(DataTable table) where TEntity : class, new() { try { List<TEntity> Entitys = new List<TEntity>(); if (table.Rows.Count == 0) return Entitys; var props = typeof(TEntity).GetProperties(); foreach (DataRow dr in table.Rows) { var entity = new TEntity(); foreach (var prop in props) { if (prop.CanWrite && table.Columns.Contains(prop.Name)) { var value = dr[prop.Name]; if (value != DBNull.Value) { prop.SetValue(entity, Convert.ChangeType(value, prop.PropertyType), null); } } } Entitys.Add(entity); } return Entitys; } catch { return null; } } #endregion #endregion #region 判断某张表的某个字段是否存在某个值 + static bool ColumnExistsValue(string table, string column, string value) /// <summary> /// 判断某张表的某个字段是否存在某个值 /// </summary> /// <param name="table">表名称</param> /// <param name="column">列名称</param> /// <param name="value">要判断的值</param> /// <returns>是否存在</returns> public static bool ColumnExistsValue(string table, string column, string value) { string sql = "SELECT count(1) FROM [" + table + "] WHERE [" + column + "] =@Value;"; object res = ExecuteScalar(sql, new SqlParameter("@Value", value)); if (res == null) { return false; } return Convert.ToInt32(res) > 0; } #endregion #region 构建分页查询 /// <summary> /// 构建分页的T-SQL语句 /// </summary> /// <param name="page"></param> /// <param name="rows"></param> /// <param name="sql"></param> /// <returns></returns> public static string CreateQuerySql(int? page, int? rows, StringBuilder sbSql) { int? iBeginIndex = 1; int? iEndIndex = 1; string strPageWhere = string.Empty; StringBuilder sbSqlPager = null; try { sbSqlPager = new StringBuilder(); page = page == null ? 1 : page; rows = rows == null ? 20 : rows; iBeginIndex = (page - 1) * rows + 1; iEndIndex = (page) * rows; strPageWhere = "where t.SN between " + iBeginIndex + " and " + iEndIndex; sbSqlPager.AppendLine("select * from ("); sbSqlPager.AppendLine(sbSql.ToString()); sbSqlPager.AppendLine(")t " + strPageWhere); } catch { sbSqlPager = sbSql; } return sbSqlPager.ToString(); } #endregion