iT邦幫忙

2021 iThome 鐵人賽

DAY 27
0
自我挑戰組

菜鳥大學生資料庫學習記系列 第 27

Day 27 -資料庫應用小程式 首頁功能(內涵程式碼)

廢話不多說直接開始

在開啟一個專案放以下兩個cs
DBconnection.cs:

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Library
    {
    public class DBconnection
        {
        //---------- Global Data ----------//
        //private string dbHost = "localhost"; //"localhost";
        //private string dbPort = "3306";
        //private string dbUser = "root";
        //private string dbPassword = ""; //"";
        //private string dbName = "university"; //"testdb";
        //private string connStr = "protocol=tcp;pooling=false;Sslmode=none;charset=utf8;";
        //private string connStr = "charset=utf8;";

        //---------- Actions ---------//
        //----- Methods -----//
        //----- DB connection
        public static MySqlConnection connectMariaDB(string dbHost, string dbPort, string dbUser, string dbPassword, string dbName)
            {
            string connStr = "charset=utf8;";
            connStr += $"server={dbHost};port={dbPort};uid={dbUser};pwd={dbPassword};database={dbName}";
            //connStr += "server=" + dbHost + ";port=" + dbPort + ";uid=" + dbUser + ";pwd=" + dbPassword + ";database" + dbName;
            return (connectMariaDB(connStr));
            } // End of connectMariaDB with host, port, userID, pw, and dbName
        public static MySqlConnection connectMariaDB(string dbUser, string dbPassword, string dbName)
            {
            string connStr = "charset=utf8;";
            string dbHost = "localhost";
            string dbPort = "3306";
            connStr += $"server={dbHost};port={dbPort};uid={dbUser};pwd={dbPassword};database={dbName}";
            return (connectMariaDB(connStr));
            } // End of connectMariaDB with userID, pw, and dbName
        public static MySqlConnection connectMariaDB(string connStr)
            {
            MySqlConnection conn = null;

            try
                {
                conn = new MySqlConnection(connStr);
                if (conn.State != ConnectionState.Open) { conn.Open(); }
                }
            catch (MySql.Data.MySqlClient.MySqlException ee)
                {
                switch (ee.Number)
                    {
                    case 0: MessageBox.Show($"Cannot connect to DB -->\n   {ee.Number}:{ee.Message}", "MySQL connection fail"); break;
                    case 1045: MessageBox.Show($"Account or password not correct, try again -->\n   {ee.Number}:{ee.Message}"); break;
                    default: MessageBox.Show($"Error is -->\n   {ee.Number}:{ee.Message}"); break;
                    }
                conn = null;
                }
            catch (Exception ee) { conn = null; MessageBox.Show($"Error is --> \n   {ee.Message}"); }
            //if (conn != null) { MessageBox.Show("Connect to DB sucess!!"); }
            return conn;
            } // End of connDB

        //----- DB close connection
        public static bool closeConnection(MySqlConnection conn)
            {
            bool flag = true;
            try { conn.Close(); }
            catch (Exception ee) { flag = false; }
            return flag;
            } // End of connection close

        public static MySqlConnection connectMariaDB(object dbUser, object dbPassword, object dbName)
            {
            throw new NotImplementedException();
            }
        } // End of Class
    }

DBmanipulation.cs:

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Library
    {
    //===== Class =====//
    public class DBmanipulation
        {
        //---------- Actions ----------//
        //----- Methods -----//
        //----- Get table names inside a particular database
        public static bool getDBtables(MySqlConnection conn, string dbName, out List<String> lstTables)
            {
            bool flag = true;
            string sqlStr = $"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = \'BASE TABLE\' AND TABLE_SCHEMA = \'{dbName}\'";
            lstTables = new List<String>();
            try
                {
                MySqlCommand cmd = new MySqlCommand(sqlStr, conn);
                MySqlDataReader rr = cmd.ExecuteReader(); // execute query
                if (!rr.HasRows) { MessageBox.Show("No data."); }
                else { while (rr.Read()) { lstTables.Add(rr.GetString(0)); } }
                rr.Close();
                }
            catch (Exception ee) { flag = false; MessageBox.Show(ee.Message, "Get tables name fail!!"); }
            return flag;
            } // End of getDBtables
        public static bool getDBtables(MySqlConnection conn, string dbName, ListBox lbxTables)
            {
            bool flag = true;
            List<String> lstTables;
            lbxTables.Items.Clear();
            if (getDBtables(conn, dbName, out lstTables))
                {
                foreach (String ss in lstTables) { lbxTables.Items.Add(ss); }
                }
            else { flag = false; }
            return flag;
            } // End of getDBtables
        public static bool getDBtables(MySqlConnection conn, string dbName, ComboBox cbxTables)
            {
            bool flag = true;
            List<String> lstTables;
            cbxTables.Items.Clear();
            if (getDBtables(conn, dbName, out lstTables))
                {
                foreach (String ss in lstTables) { cbxTables.Items.Add(ss); }
                }
            else { flag = false; }
            return flag;
            } // End of getDBtables

        //----- Update changes in DataGridView.DataSource
        //      Commit the change in dataGridView (update DB too)
        //      make sure the property ReadOnly in dataGridView is false
        //      Committing all the data including the unchanged data is a waste of resources
        //      so extract only the changes 
        public static void updateDGV(DataGridView dgv, MySqlDataAdapter mda)
            {
            DataTable changes = ((DataTable)dgv.DataSource).GetChanges();
            if (changes != null) // the content of dataGridView has been changed
                {
                try
                    {
                    // create a command builder object with mySqlDataAdapter as a parameter
                    MySqlCommandBuilder mcb = new MySqlCommandBuilder(mda);
                    // create update command and assign to dataAdapter
                    mda.UpdateCommand = mcb.GetUpdateCommand();
                    // update the data set
                    mda.Update(changes);
                    // commit the update
                    ((DataTable)dgv.DataSource).AcceptChanges(); // commit
                    }
                catch (Exception ee) { MessageBox.Show(ee.Message, "DB update fail"); }
                }
            } // End of updateDGV
        } // End of class
    }

我們首先來做登入的功能吧!
https://ithelp.ithome.com.tw/upload/images/20211011/20141567G8hbwMPRNo.jpg
首先先設置全域變數:

  static public string loginusername;
        private string dbHost = "localhost"; //"localhost";
        private string dbPort = "3306";
        private string dbUser = "root";
        private string dbPassword = "";
        private string dbName = "db85cc";
        private string sqlStr = "";
        ordersearch fr2;

以下程式碼是登入按鈕內需要的!
程式碼包含判斷帳號及登入成功後顯示其他按鈕功能

 string sqlStr = $"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=\'BASE TABLE\'AND TABLE_SCHEMA = \'{dbName}\'";
            MySqlConnection conn = DBconnection.connectMariaDB(dbUser, dbPassword, dbName);

            string Account = tbxaccount.Text; //帳號
            string Password = tbxpassword.Text; //密碼
            string SQL = "SELECT * FROM guest where Guest_account in ('" + Account + "') and guest_password in ('" + Password + "')"; //sql指令
            MySqlCommand cmd = new MySqlCommand(SQL, conn); //mysql指令
            MySqlDataAdapter adp = new MySqlDataAdapter(cmd); //data偵測

            DataSet da = new DataSet(); //dataset
            adp.Fill(da, "info"); //資料存入dataset
            DataTable table = da.Tables["info"]; //資料存入datatble
            if (string.IsNullOrEmpty(tbxaccount.Text))
                {
                MessageBox.Show("請輸入帳號");
                return;
                }
            else if (string.IsNullOrEmpty(tbxpassword.Text))
                {
                MessageBox.Show("請輸入密碼");
                return;
                }
            else if (tbxaccount.Text.Trim().Length >= 4)
                {
                
                    //判定帳號是否存在
                    if (table.Rows.Count >= 1)
                        {
                        loginusername = Account; //全域變數紀錄使用者帳號
                        btnMenu.Visible = true;
                        btnDelete.Visible = true;
                        btnlogout.Visible = true;
                        btnoderseach.Visible = true;
                        MessageBox.Show("登入成功");

                        }
                    else
                        {
                        MessageBox.Show("帳號或密碼錯誤");
                        }
                    
                }
            else { MessageBox.Show("帳號長度不可少於四個字"); }
            

菜單按鈕
連結到我們上一篇做好的菜單頁面

   menu ff = new menu();
  
            ff.Show();

訂單查詢按鈕
連結到我們上一篇做好的訂單查詢

    fr2 = new ordersearch();
            fr2.show_MainEntrance_data(tbxaccount.Text);
            fr2.Show();

註冊按鈕
主要是連結到我們上一篇做好的註冊頁面

  GusetRegister ff = new GusetRegister();
        
            ff.Show();

會員刪除按鈕
連結到我們上一篇做好的刪除頁面

    Delete ff = new Delete();
            ff.Show();

登出按鈕主要是重製功能

 tbxaccount.Clear();
            tbxpassword.Clear();
         
            btnMenu.Visible = false;
            btnSignUp.Visible = false;
            btnDelete.Visible = false;
            btnMemberLogin.Visible = true;
            btnSignUp.Visible = true;
            btnoderseach.Visible = false;

離開按鈕則是關閉app

  Application.Exit();

這樣首頁的功能就可以正常執行啦!
下篇見~


上一篇
Day 26 -資料庫應用小程式 設計程式介面
下一篇
Day 28 -資料庫應用小程式 會員新增刪除(內涵程式碼)
系列文
菜鳥大學生資料庫學習記30

尚未有邦友留言

立即登入留言