今天的Playground範例程式碼與範本資料SQL以更新至Github
https://github.com/ted59438/EnglishVocabulary_MySQL
在「單字管理作業」,可以新增分類,如果分類已存在則不可以重複新增
在「單字管理作業」,可以在指定的分類底下新增單字
確認要新增的使用者是否已存在於資料庫
/// <summary>
/// 新增學生按鈕事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void insertStudentBtn_Click(object sender, EventArgs e)
{
string sql;
Dictionary<string, object> parameters;
sql = @"SELECT COUNT(StudentID)
FROM Student
WHERE Username = @Username";
parameters = new Dictionary<string, object>()
{
{"Username", studentUsernameTextBox.Text }
};
try
{
if (queryScalar(sql, parameters) != 0)
{
MessageBox.Show("學生帳號已經存在!", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
/// <summary>
/// 基本查詢:傳回單一值
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private int queryScalar(string sql, Dictionary<string, object> parameters)
{
MySqlCommand command = getAndBindCommand(sql, parameters);
try
{
command.Connection.Open();
int result = Convert.ToInt32(command.ExecuteScalar());
command.Connection.Close();
return result;
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 建立Connection→設定Command→綁定參數
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private MySqlCommand getAndBindCommand(string sql, Dictionary<string, object> parameters)
{
// 建立連線物件 (SqlConnection)
MySqlConnection connection = new MySqlConnection();
connection.ConnectionString = getConnectString();
// 建立指令物件,設定SQL語法 (SqlCommand)
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandText = sql;
// 將參數綁定到語法上
foreach (KeyValuePair<string, object> parameter in parameters)
{
command.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
return command;
}