SQLHelper

 /// <summary>
    /// Sql helper.
    /// </summary>
    public static class SqlHelper {

        #region Get Connection String

        /// <summary>
        /// Get connection string.
        /// </summary>
        /// <returns>Connection string</returns>
        public static string GetConnectionString() {
            string settingName = ConfigurationManager.AppSettings["Db_Setting"];
            if (!String.IsNullOrEmpty(settingName)) {
                if (ConfigurationManager.ConnectionStrings[settingName] != null) {
                    string connectionString = ConfigurationManager.ConnectionStrings[settingName].ConnectionString;
                    if (!String.IsNullOrEmpty(connectionString))
                        return connectionString;
                }
            }

            DbItem settingItem = DatabaseEnvironment.GetCurrentDatabase();
            return GetConnectionString(
                settingItem.Server,
                settingItem.Account,
                settingItem.Password,
                settingItem.Database,
                settingItem.ConnectionTimeout
            );
        }

        private static string GetConnectionString(string server, string account, string password, string database, int connTimeout) {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = server;
            builder.UserID = account;
            builder.Password = password;
            builder.InitialCatalog = database;

            builder.Pooling = true;
            builder.MaxPoolSize = 10;
            builder.MinPoolSize = 1;
            builder.ConnectTimeout = connTimeout;

            return builder.ToString();
        }

        #endregion

        #region Get Connection

        private static SqlConnection GetConnection(string server, string account, string password, string database, int connTimeout) {
            return new SqlConnection(GetConnectionString(server, account, password, database, connTimeout));
        }

        /// <summary>
        /// Get a new sql connection.
        /// </summary>
        /// <returns></returns>
        public static SqlConnection GetConnection() {
            return new SqlConnection(GetConnectionString());
        }

        #endregion

        #region Command Timeout

        /// <summary>
        /// Get command timeout. <br/>
        /// Default is 30 seconds.
        /// </summary>
        public static int CommandTimeout {
            get {
                string temp = ConfigurationManager.AppSettings["Db_Cmd_Timeout"];
                int timeout = 30;
                if (!Int32.TryParse(temp, out timeout))
                    timeout = 30;

                return timeout;
            }
        }

        #endregion

        #region Execute

        /// <summary>
        /// Execute sql data reader.
        /// </summary>
        /// <param name="cmd">SqlCommand</param>
        /// <returns>Sql data reader</returns>
        public static SqlDataReader ExecuteReader(SqlCommand cmd) {
            SqlConnection conn = GetConnection();
            cmd.Connection = conn;
            // Set timeout
            cmd.CommandTimeout = CommandTimeout;

            try {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            } catch {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        /// Execute sql command.
        /// </summary>
        /// <param name="cmd">SqlCommand</param>
        /// <returns>Affacted rows</returns>
        public static int ExecuteCommand(SqlCommand cmd) {
            SqlConnection conn = GetConnection();
            cmd.Connection = conn;
            // Set timeout
            cmd.CommandTimeout = CommandTimeout;

            try {
                conn.Open();
                return cmd.ExecuteNonQuery();
            } finally {
                conn.Close();
            }
        }

        #endregion

        #region Test Connection

        /// <summary>
        /// Test connection
        /// </summary>
        /// <param name="server">SQL Server instance</param>
        /// <param name="account">Account</param>
        /// <param name="password">Password</param>
        /// <param name="database">Database</param>
        /// <param name="connTimeout">Connection connTimeout</param>
        /// <returns>Return true when successful</returns>
        public static bool TestConnection(string server, string account, string password, string database, int connTimeout) {
            SqlConnection conn = GetConnection(server, account, password, database, connTimeout);
            try {
                conn.Open();
                return true;
            } catch {
                return false;
            } finally {
                conn.Dispose();
            }
        }

        #endregion
    }