IT工程師數位筆記本

//數據庫操作
    public class DataBase
    {
        private SqlConnection conn;//數據庫連接對象

        #region 打開數據庫連接
        private void Open()
        {
            if (conn == null)
            {
                conn = new SqlConnection();
                //conn.ConnectionString = ConfigurationSettings.AppSettings["connString"].ToString();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ToString();
                conn.Open();
            }
            else
            {
                if (conn.State == System.Data.ConnectionState.Closed)
                {
                    conn.Open();
                }
            }
        }
        #endregion

        #region 關閉數據庫連接
        private void Close()
        {
            if (conn != null && conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
            }
        }
        #endregion

        #region 釋放連接資源
        public void Dispose()
        {
            if (conn != null)
            {
                conn.Dispose();
                conn = null;
            }
        }
        #endregion

        #region 傳入參數并轉換為SqlParameter類型
        /// <summary>
        /// 轉換參數
        /// </summary>
        /// <param name="ParamName">存儲過程名稱或命令文本</param>
        /// <param name="DbType">參數類型</param></param>
        /// <param name="Size">參數大小</param>
        /// <param name="Value">參數值</param>
        /// <returns>新的 parameter 對象</returns>
        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, Object Value)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }

        /// <summary>
        /// 初始化參數值
        /// </summary>
        /// <param name="ParamName">存儲過程名稱或命令文本</param>
        /// <param name="DbType">參數類型</param>
        /// <param name="Size">參數大小</param>
        /// <param name="Direction">參數方向</param>
        /// <param name="Value">參數值</param>
        /// <returns>新的 parameter 對象</returns>
        private SqlParameter MakeParam(string ParamName, SqlDbType DbType, int Size, ParameterDirection Direction, object Value)
        {
            SqlParameter param;
            if (Size>0)
            {
                param = new SqlParameter(ParamName, DbType, Size);
            }
            else
            {
                param = new SqlParameter(ParamName, DbType);
            }
            param.Direction = Direction;
            if (!(param.Direction == ParameterDirection.Output && Value == null))
            {
                param.Value = Value;
            }

            return param;
        }
        #endregion

        #region 執行參數命令文本(無數據庫中數據返回)

        /// <summary>
        /// 執行命令
        /// </summary>
        /// <param name="procName">命令文本</param>
        /// <param name="prams">參數對象</param>
        /// <returns></returns>
        public int RunProc(string procName, SqlParameter[] prams)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            cmd.ExecuteNonQuery();
            this.Close();
            //得到成功執行的返回值
            return (int)cmd.Parameters["ReturnValue"].Value;
        }

        /// <summary>
        /// 直接執行SQL語句
        /// </summary>
        /// <param name="procName">命令文本</param>
        /// <returns></returns>
        public int RunProc(string procName)
        {
            this.Open();
            SqlCommand cmd = new SqlCommand(procName, conn);
            int num = cmd.ExecuteNonQuery();
            this.Close();
            return num;
        }
        
        #endregion

        #region 執行參數命令文本(有返回值)

        /// <summary>
        /// 執行查詢命令文本，并且返回DataSet數據集
        /// </summary>
        /// <param name="procName">命令文本</param>
        /// <param name="prams">參數對象</param>
        /// <param name="tbName">數據表名稱</param>
        /// <returns></returns>
        public DataSet RunProcReturn(string procName, SqlParameter[] prams, string tbName)
        {
            SqlDataAdapter dap = CreateDataAdapter(procName, prams);
            DataSet ds = new DataSet();
            dap.Fill(ds, tbName);
            this.Close();
            return ds;
        }

        /// <summary>
        /// 執行命令文本，并且返回DataSet數據集
        /// </summary>
        /// <param name="procName">命令文本</param>
        /// <param name="tbName">數據表名稱</param>
        /// <returns>DataSet</returns>
        public DataSet RunProcReturn(string procName, string tbName)
        {
            SqlDataAdapter dap = CreateDataAdapter(procName, null);
            DataSet ds = new DataSet();
            dap.Fill(ds, tbName);
            return ds;
        }

        #endregion

        #region 將命令文本添加到SqlDataAdapter

        /// <summary>
        /// 創建一個SqlDataAdapter對象以此來執行命令文本
        /// </summary>
        /// <param name="procName">命令文本</param>
        /// <param name="prams">參數對象</param>
        /// <returns></returns>
        private SqlDataAdapter CreateDataAdapter(string procName, SqlParameter[] prams)
        {
            this.Open();
            SqlDataAdapter dap = new SqlDataAdapter(procName, conn);
            dap.SelectCommand.CommandType = CommandType.Text;// 執行類型：命令文本
            if (prams != null)
            {
                foreach (SqlParameter param in prams)
                {
                    dap.SelectCommand.Parameters.Add(param);// 為查詢語句插入參數
                }
            }

            //加入返回值
            dap.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                ParameterDirection.ReturnValue, false, 0, 0,
                String.Empty, DataRowVersion.Default, null));

            return dap;
        }

        #endregion

        #region 將命令文本添加到SqlCommand

        /// <summary>
        /// 創建一個SqlCommand對象以此來執行命令文本
        /// </summary>
        /// <param name="procName">命令文本</param>
        /// <param name="prams"命令文本所需參數</param>
        /// <returns>返回SqlCommand對象</returns>
        private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
        {
            this.Open();
            SqlCommand cmd = new SqlCommand(procName, conn);
            cmd.CommandType = CommandType.Text;// 執行類型：命令文本

            //一次把參數傳入命令文本
            if (prams != null)
            {
                foreach (SqlParameter param in prams)
                {
                    cmd.Parameters.Add(param);
                }
            }

            //加入返回參數
            cmd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                ParameterDirection.ReturnValue, false, 0, 0,
                string.Empty, DataRowVersion.Default, null));

            return cmd;
        }

        #endregion
    }