SQLHelper

 /// <summary>
    /// SQLServer 实用类，全部属性和方法都是静态的，可在程序中直接调用
    /// </summary>
    public abstract class SQLHelper
    {
        //获取SQLServer数据库连接串
        private static readonly string connectionString = ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString;

        #region 常用操作
        /// <summary>
        /// 对数据库的增删改操作
        /// </summary>
        /// <param name="strSql">要执行的SQL语句</param>
        /// <param name="commandType">要执行的SQL语句类型，如存储过程或者SQL文本</param>
        /// <param name="parameters">存储过程或者参数化SQL中的参数集合</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string strSql, CommandType commandType, params SqlParameter[] parameters)
        {
            SqlCommand cmd = new SqlCommand();

            //使用using关键字，确保Connection使用后关闭
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, commandType, strSql, parameters);

                int result = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear(); //清除命令参数
                return result;
            }
        }

        /// <summary>
        ///
        /// 使用参数化SQL方式对数据库执行增删改操作
        /// </summary>
        /// <param name="strSql">要执行的SQL语句</param>
        /// <param name="parameters">存储过程或者参数化SQL中的参数集合</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string strSql, params SqlParameter[] parameters)
        {
            return ExecuteNonQuery(strSql, CommandType.Text, parameters);
        }

        /// <summary>
        /// 执行一个查询，并返回一个DataTable结果集
        /// </summary>
        /// <param name="strSql">要执行的Sql语句或者存储过程</param>
        /// <param name="commandType">要执行的SQL语句类型，为存储过程或者SQL文本</param>
        /// <param name="parameters">存储过程或者参数化SQL中的参数集合</param>
        /// <returns>
        /// DataTable结果集
        /// </returns>
        public static DataTable ExecuteDataTable(string strSql,CommandType commandType, params SqlParameter[] parameters)
        {
            DataTable dataTable = new DataTable();
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, commandType, strSql, parameters);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                try { sda.Fill(dataTable);
                cmd.Parameters.Clear();
                }
                catch { }
            }
            return dataTable;
        }

        /// <summary>
        /// 使用参数化SQL方式执行一个查询，并返回一个DataTable结果集
        /// </summary>
        /// <param name="strSql">要执行的Sql语句或者存储过程</param>
        /// <param name="parameters">存储过程或者参数化SQL中的参数集合</param>
        /// <returns>
        /// DataTable结果集
        /// </returns>
        public static DataTable ExecuteDataTable(string strSql, params SqlParameter[] parameters)
        {
            return ExecuteDataTable(strSql, CommandType.Text, parameters);
        }

        /// <summary>
        /// 执行一个查询，并返回SqlDataReader实例
        /// </summary>
        /// <param name="strSql">要执行的Sql语句或者存储过程</param>
        /// <param name="commandType">要执行的SQL语句类型，为存储过程或者SQL文本</param>
        /// <param name="parameters">存储过程或者参数化SQL中的参数集合</param>
        /// <returns>
        /// 包含结果集的SqlDataReader对象
        /// </returns>
        public static SqlDataReader ExecuteReader(string strSql,CommandType commandType,params SqlParameter[] parameters)
        {
            SqlConnection connection = null;

            SqlCommand command = new SqlCommand();
            try
            {
                connection = new SqlConnection(connectionString);
                PrepareCommand(command, connection, null, commandType, strSql, parameters);

                //CommandBehavior.CloseConnection表示关闭DataReader时，与之关联的Connection对象也会相应关闭
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Parameters.Clear();
                return reader;
            }
            catch (Exception ex)
            {
                //出现异常时，也要记得关闭连接
                connection.Close();
                //向外抛出该异常
                throw ex;
            }
        }

        /// <summary>
        /// 执行一个查询，并返回SqlDataReader实例.设置CommandType = CommandType.Text
        /// </summary>
        /// <param name="strSql">要执行的Sql语句或者存储过程</param>
        /// <param name="parameters">存储过程或者参数化SQL中的参数集合</param>
        /// <returns>
        /// 包含结果集的SqlDataReader对象
        /// </returns>
        public static SqlDataReader ExecuteReader(string strSql, params SqlParameter[] parameters)
        {
            return ExecuteReader(strSql, CommandType.Text, parameters);
        }

        /// <summary>
        /// 执行一个查询，并返回结果集里面的第一行第一列
        /// </summary>
        /// <param name="strSql">要执行的Sql语句或者存储过程</param>
        /// <param name="commandType">要执行的SQL语句类型，为存储过程或者SQL文本</param>
        /// <param name="parameters">存储过程或者参数化SQL中的参数集合</param>
        /// <returns>
        /// 结果集中的第一行第一列
        /// </returns>
        public static Object ExecuteScalar(string strSql, CommandType commandType, params SqlParameter[] parameters)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, commandType, strSql, parameters);
                object result = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return result;
            }
        }

        /// <summary>
        /// 执行一个查询，并返回结果集里面的第一行第一列
        /// </summary>
        /// <param name="strSql">要执行的Sql语句或者存储过程</param>
        /// <param name="parameters">存储过程或者参数化SQL中的参数集合</param>
        /// <returns>
        /// 结果集中的第一行第一列
        /// </returns>
        public static Object ExecuteScalar(string strSql, params SqlParameter[] parameters)
        {
            return ExecuteScalar(strSql, CommandType.Text, parameters);
        }

        /// <summary>
        /// 为sql的执行配置SqlCommand对象
        /// </summary>
        /// <param name="cmd">待配置的SqlCommand对象</param>
        /// <param name="conn">指定到当前SqlCommand对象的SqlConnection对象</param>
        /// <param name="trans">指定到当前SqlCommand对象的SqlTransaction对象</param>
        /// <param name="cmdType">命令字符串类型</param>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="parameters">指定到SqlCommand的参数集合</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] parameters)
        {
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            if (parameters != null)
            {
                //添加Sql参数
                foreach (SqlParameter param in parameters)
                {
                    cmd.Parameters.Add(param);
                }
            }

            if (conn.State != ConnectionState.Open) //确保连接是打开的
            {
               conn.Open();
            }
        }

        /// <summary>
        /// 根据SQL语句查询，返回数组集合，一个数组对应表的一行记录
        /// </summary>
        public static List<string[]> ExecuteList(string sql)
        {
            List<string[]> li = new List<string[]>();
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand com = new SqlCommand(sql, con);
            con.Open();
            SqlDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                string[] str = new string[reader.VisibleFieldCount];
                for (int i = 0; i < reader.VisibleFieldCount; i++)
                {
                    str[i] = reader[i].ToString();
                }
                li.Add(str);
            }
            con.Close();
            return li;
        }

        /// <summary>
        /// 根据SQL执行增删改
        /// </summary>
        /// <returns>
        /// bool
        /// </returns>
        public static bool ExecuteNonQuerys(string sql)
        {
            bool bl = false;
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand com = new SqlCommand(sql, con);
            con.Open();
            if (com.ExecuteNonQuery() >= 1)
            {
                bl = true;
            }
            con.Close();
            return bl;
        }
        #endregion

        #region Sql Server 2005 基于通知的缓存依赖支持
        /// <summary>
        /// 执行一个查询，并返回一个DataTable结果集，在执行的同时向数据库注册一个基于通知的缓存依赖
        /// 注意：该方法只适用于Sql Server 2005 及以上的数据库且数据库必须开启缓存通知支持，
        /// 调用该方法后，必须显示把结果插入缓存中
        /// </summary>
        /// <param name="strSql">要执行的Sql语句或者存储过程</param>
        /// <param name="commandType">要执行的SQL语句类型，为存储过程或者SQL文本</param>
        /// <param name="parameters">存储过程或者参数化SQL中的参数集合</param>
        /// <param name="depend">输出参数，SqlCacheDependency实例</param>
        /// <returns>
        /// DataTable结果集
        /// </returns>
        public static DataTable ExecuteDataTable(string strSql, CommandType commandType, out System.Web.Caching.SqlCacheDependency depend, params SqlParameter[] parameters)
        {
            DataTable dataTable = new DataTable();
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, commandType, strSql, parameters);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);

                //创建缓存依赖
                System.Web.Caching.SqlCacheDependency sqlDepend = new System.Web.Caching.SqlCacheDependency(cmd);
                depend = sqlDepend;

                sda.Fill(dataTable);
                cmd.Parameters.Clear();
            }
            return dataTable;
        }

        /// <summary>
        /// 执行一个CommandType.Text的查询，并返回一个DataTable结果集，在执行的同时向数据库注册一个基于通知的缓存依赖
        /// 注意：该方法只适用于Sql Server 2005 及以上的数据库且数据库必须开启缓存通知支持，
        /// 调用该方法后，必须显示把结果插入缓存中
        /// </summary>
        /// <param name="strSql">要执行的Sql语句或者存储过程</param>
        /// <param name="parameters">存储过程或者参数化SQL中的参数集合</param>
        /// <param name="depend">输出参数，SqlCacheDependency实例</param>
        /// <returns>
        /// DataTable结果集
        /// </returns>
        public static DataTable ExecuteDataTable(string strSql, out System.Web.Caching.SqlCacheDependency depend, params SqlParameter[] parameters)
        {
            DataTable dataTable = new DataTable();
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, CommandType.Text, strSql, parameters);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);

                //创建缓存依赖
                System.Web.Caching.SqlCacheDependency sqlDepend = new System.Web.Caching.SqlCacheDependency(cmd);
                depend = sqlDepend;

                sda.Fill(dataTable);
                cmd.Parameters.Clear();
            }
            return dataTable;
        }

        /// <summary>
        /// 执行一个查询，并返回SqlDataReader实例在,
        /// 同时向数据库注册一个基于通知的缓存依赖,
        /// 注意：该方法只适用于Sql Server 2005 及以上的数据库且数据库必须已开启缓存通知支持，
        /// 调用该方法后，必须显示把结果插入缓存中
        /// </summary>
        /// <param name="strSql">要执行的Sql语句或者存储过程</param>
        /// <param name="commandType">要执行的SQL语句类型，为存储过程或者SQL文本</param>
        /// <param name="depend">输出参数，SqlCacheDependency实例</param>
        /// <param name="parameters">存储过程或者参数化SQL中的参数集合</param>
        /// <returns>
        /// 包含结果集的SqlDataReader对象
        /// </returns>
        public static SqlDataReader ExecuteReader(string strSql, CommandType commandType, out System.Web.Caching.SqlCacheDependency depend, params SqlParameter[] parameters)
        {
            SqlConnection connection = null;

            SqlCommand cmd = new SqlCommand();
            try
            {
                connection = new SqlConnection(connectionString);
                PrepareCommand(cmd, connection, null, commandType, strSql, parameters);

                //创建缓存依赖
                System.Web.Caching.SqlCacheDependency sqlDepend = new System.Web.Caching.SqlCacheDependency(cmd);
                depend = sqlDepend;

                //CommandBehavior.CloseConnection表示关闭DataReader时，与之关联的Connection对象也会相应关闭
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return reader;
            }
            catch (Exception ex)
            {
                //出现异常时，也要记得关闭连接
                connection.Close();
                //向外抛出该异常
                throw ex;
            }
        }

        /// <summary>
        /// 执行一个CommandType.Text的查询，并返回SqlDataReader实例,
        /// 同时向数据库注册一个基于通知的缓存依赖,
        /// 注意：该方法只适用于Sql Server 2005 及以上的数据库且数据库必须已开启缓存通知支持，
        /// 调用该方法后，必须显示把结果插入缓存中
        /// </summary>
        /// <param name="strSql">要执行的Sql语句或者存储过程</param>
        /// <param name="depend">输出参数，SqlCacheDependency实例</param>
        /// <param name="parameters">存储过程或者参数化SQL中的参数集合</param>
        /// <returns>
        /// 包含结果集的SqlDataReader对象
        /// </returns>
        public static SqlDataReader ExecuteReader(string strSql, out System.Web.Caching.SqlCacheDependency depend, params SqlParameter[] parameters)
        {
            SqlConnection connection = null;

            SqlCommand cmd = new SqlCommand();
            try
            {
                connection = new SqlConnection(connectionString);
                PrepareCommand(cmd, connection, null, CommandType.Text, strSql, parameters);

                //创建缓存依赖
                System.Web.Caching.SqlCacheDependency sqlDepend = new System.Web.Caching.SqlCacheDependency(cmd);
                depend = sqlDepend;

                //CommandBehavior.CloseConnection表示关闭DataReader时，与之关联的Connection对象也会相应关闭
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return reader;
            }
            catch (Exception ex)
            {
                //出现异常时，也要记得关闭连接
                connection.Close();
                //向外抛出该异常
                throw ex;
            }
        }
        #endregion
    }