今天的Playground範例程式碼與範本資料SQL以更新至Github
https://github.com/ted59438/EnglishVocabulary_MySQL
在「單字管理」選擇某項分類時,會將分類底下所有的單字帶入下方Grid
注意查詢的時候必須使用所有欄位,但是下方欄位只能顯示「單字、中文、詞性」。
將外部資料透過參數化的方式加到SQL語句,避免SQL Injection
/// <summary>
/// 基本查詢
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private DataTable queryDT(string sql, Dictionary<string, object> parameters)
{
// Step 1. 建立連線物件 (SqlConnection)
MySqlConnection connection = new MySqlConnection();
connection.ConnectionString = getConnectString();
// Step 2. 建立指令物件,設定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);
}
// Step 3. 建立撈取資料的物件 (Adapter)
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = command;
// Step 4. 開啟連線
connection.Open();
// Step 5. 執行SELECT查詢,取得資料後存放到DataTable
DataTable queryResultDT = new DataTable();
adapter.Fill(queryResultDT);
// Step 6. 關閉連線
connection.Close();
return queryResultDT;
}
/// <summary>
/// 取得特定學生的資料
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void queryOneStudentBtn_Click(object sender, EventArgs e)
{
// 撰寫查詢語句
string sql = @"SELECT *
FROM Student
WHERE StudentID = @StudentID";
// 要放到SQL的參數
Dictionary<string, object> parameters = new Dictionary<string, object>()
{
{"StudentID", selectStudentCombo.SelectedValue }
};
queryResultGrid.DataSource = queryDT(sql, parameters);
}
// 設定要查詢的SQL語句
command.CommandText = sql;
// 將參數綁定到語法上
foreach (KeyValuePair<string, object> parameter in parameters)
{
command.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
例如,綁定ID=123的學生:
string sql = @"SELECT *
FROM Student
WHERE StudentID = @StudentID";
Dictionary<string, object> parameters = new Dictionary<string, object>()
{
{"StudentID", "123" }
};
綁定完後的SQL
SELECT *
FROM Student
WHERE StudentID = '123'