// 一个自用的 SqlHelper 类 利用了刚学习到的 扩展方法 http://technet.microsoft.com/zh-cn/bb383977
    /// <summary>

    /// The SqlHelper class is intended to encapsulate high performance,

    /// scalable best practices for common uses of SqlClient.

    /// </summary>

    public abstract class SqlHelper

    {

        //Database connection strings

        public static readonly string connStr = ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;

        public static int ExecuteNonQuery(string cmdText, params SqlParameter[] cmdParameters)

        {

            using (SqlConnection conn = new SqlConnection(connStr))

            {

                using (SqlCommand cmd = new SqlCommand(cmdText, conn))

                {

                    //if (conn.State != ConnectionState.Open) conn.Open();

                    conn.QuickOpen();

                    cmd.Parameters.Clear();

                    foreach (SqlParameter item in cmdParameters)

                    {

                        cmd.Parameters.Add(item);

                    }

                    return cmd.ExecuteNonQuery();

                }

            }

        }

        /// <summary>

        /// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction

        /// using the provided parameters.

        /// </summary>

        /// <remarks>

        /// e.g.:

        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));

        /// </remarks>

        /// <param name="trans">an existing sql transaction</param>

        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>

        /// <param name="commandText">the stored procedure name or T-SQL command</param>

        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>

        /// <returns>an int representing the number of rows affected by the command</returns>

        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

        {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);

            int val = cmd.ExecuteNonQuery();

            return val;

        }

        /// <summary>

        /// 执行一个查询，返回一个object

        /// </summary>

        /// <param name="cmdText">查询文本</param>

        /// <returns>结果</returns>

        public static object ExecuteScalar(string cmdText, params SqlParameter[] cmdParameters)

        {

            using (SqlConnection conn = new SqlConnection(connStr))

            {

                using (SqlCommand cmd = new SqlCommand(cmdText, conn))

                {

                    //if (conn.State != ConnectionState.Open) conn.Open();

                    conn.QuickOpen();

                    cmd.Parameters.Clear();

                    foreach (SqlParameter item in cmdParameters)

                    {

                        cmd.Parameters.Add(item);

                    }

                    return cmd.ExecuteScalar();

                }

            }

        }

        /// <summary>

        /// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction

        /// using the provided parameters.

        /// </summary>

        /// <remarks>

        /// e.g.:

        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));

        /// </remarks>

        /// <param name="trans">an existing sql transaction</param>

        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>

        /// <param name="commandText">the stored procedure name or T-SQL command</param>

        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>

        /// <returns>an int representing the number of rows affected by the command</returns>

        public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

        {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);

            return cmd.ExecuteScalar();

        }

        /// <summary>

        /// 执行一个查询，返回一个结果集

        /// </summary>

        /// <param name="cmdText">查询文本</param>

        /// <returns>结果集</returns>

        public static DataTable DB_Select(string cmdText, params SqlParameter[] parameters)

        {

            using (SqlConnection conn = new SqlConnection(connStr))

            {

                using (SqlDataAdapter da = new SqlDataAdapter())

                {

                    da.SelectCommand = BuildQueryCommand(conn, cmdText, CommandType.Text, parameters);

                    DataTable dt = new DataTable();

                    da.Fill(dt);

                    return dt;

                }

            }

        }

        /// <summary>

        /// 执行一个查询，返回一个结果集

        /// </summary>

        /// <param name="cmdText">查询文本</param>

        /// <returns>结果集</returns>

        public static DataTable DB_Select(string cmdText, CommandType type, params SqlParameter[] parameters)

        {

            using (SqlConnection conn = new SqlConnection(connStr))

            {

                using (SqlDataAdapter da = new SqlDataAdapter())

                {

                    da.SelectCommand = BuildQueryCommand(conn, cmdText, type, parameters);

                    DataTable dt = new DataTable();

                    da.Fill(dt);

                    return dt;

                }

            }

        }

        /// <summary>

        /// 执行存储过程

        /// </summary>

        /// <param name="storedProcName">存储过程名</param>

        /// <param name="parameters">存储过程参数</param>

        /// <param name="tableName">DataSet结果中的表名</param>

        /// <returns>DataSet</returns>

        public static DataTable RunProcedure(string storedProcName, params SqlParameter[] parameters)

        {

            using (SqlConnection conn = new SqlConnection(connStr))

            {

                conn.QuickOpen();

                DataTable dt = new DataTable();

                //conn.Open();

                SqlDataAdapter sqlDA = new SqlDataAdapter();

                sqlDA.SelectCommand = BuildQueryCommand(conn, storedProcName, parameters);

                sqlDA.Fill(dt);

                return dt;

            }

        }

        /// <summary>

        /// 执行存储过程

        /// </summary>

        /// <param name="storedProcName">存储过程名</param>

        /// <param name="parameters">存储过程参数</param>

        /// <param name="tableName">DataSet结果中的表名</param>

        /// <returns>DataSet</returns>

        public static DataSet RunProcedure(string storedProcName, string tableName, params SqlParameter[] parameters)

        {

            using (SqlConnection conn = new SqlConnection(connStr))

            {

                conn.QuickOpen();

                DataSet dataSet = new DataSet();

                //conn.Open();

                SqlDataAdapter sqlDA = new SqlDataAdapter();

                sqlDA.SelectCommand = BuildQueryCommand(conn, storedProcName, parameters);

                sqlDA.Fill(dataSet, tableName);

                conn.Close();

                return dataSet;

            }

        }

        /// <summary>

        /// 构建 SqlCommand 对象

        /// </summary>

        /// <param name="connection">数据库连接</param>

        /// <param name="storedProcName">存储过程名</param>

        /// <param name="parameters">存储过程参数</param>

        /// <returns>SqlCommand</returns>

        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, params SqlParameter[] parameters)

        {

            return BuildQueryCommand(connection, storedProcName, CommandType.StoredProcedure, parameters);

        }

        /// <summary>

        /// 构建 SqlCommand 对象

        /// </summary>

        /// <param name="connection">数据库连接</param>

        /// <param name="storedProcName">存储过程名</param>

        /// <param name="parameters">存储过程参数</param>

        /// <returns>SqlCommand</returns>

        private static SqlCommand BuildQueryCommand(SqlConnection connection, string cmdText, CommandType type, params SqlParameter[] parameters)

        {

            SqlCommand command = new SqlCommand(cmdText, connection);

            command.CommandType = type;

            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;

        }

        /// <summary>

        /// Prepare a command for execution

        /// </summary>

        /// <param name="cmd">SqlCommand object</param>

        /// <param name="conn">SqlConnection object</param>

        /// <param name="trans">SqlTransaction object</param>

        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>

        /// <param name="cmdText">Command text, e.g. Select * from Products</param>

        /// <param name="cmdParms">SqlParameters to use in the command</param>

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)

        {

            //if (conn.State != ConnectionState.Open)

            //    conn.Open();

            conn.QuickOpen();

            cmd.Connection = conn;

            cmd.CommandText = cmdText;

            if (trans != null)

                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            cmd.Parameters.Clear();

            if (cmdParms != null)

            {

                foreach (SqlParameter parameter in cmdParms)

                {

                    if (parameter != null)

                    {

                        // 检查未分配值的输出参数,将其分配以DBNull.Value.

                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))

                        {

                            parameter.Value = DBNull.Value;

                        }

                        cmd.Parameters.Add(parameter);

                    }

                }

            }

        }

    }

    public static class SqlExtensions

    {

        public static void QuickOpen(this SqlConnection conn)

        {

            int timeout = Generic.ToInt32(ConfigurationManager.AppSettings["TimeOut"]);

            timeout = Math.Max(Math.Min(, timeout), );

            // We'll use a Stopwatch here for simplicity. A comparison to a stored DateTime.Now value could also be used

            Stopwatch sw = new Stopwatch();

            bool connectSuccess = false;

            // Try to open the connection, if anything goes wrong, make sure we set connectSuccess = false

            Thread t = new Thread(delegate()

            {

                try

                {

                    sw.Start();

                    conn.Open();

                    connectSuccess = true;

                }

                catch { }

            });

            // Make sure it's marked as a background thread so it'll get cleaned up automatically

            t.IsBackground = true;

            t.Start();

            // Keep trying to join the thread until we either succeed or the timeout value has been exceeded

            while (timeout > sw.ElapsedMilliseconds)

                if (t.Join())

                    break;

            // If we didn't connect successfully, throw an exception

            if (!connectSuccess)

                throw new Exception("连接超时！\r\n未能连接到数据库！\r\n如有需要，可更改App.config 中的 TimeOut 值");

        }

    }