廢話不多說直接開始
在開啟一個專案放以下兩個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();
這樣首頁的功能就可以正常執行啦!
下篇見~
iThome鐵人賽
看影片追技術