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
    }

我们首先来做登入的功能吧!

首先先设置全域变数:

  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();

这样首页的功能就可以正常执行啦!
下篇见~