public static void initQuery(List <string> sqlKeyList, List <string> sqlValueList)
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = MjSql.createSqlConnection();
cmd.CommandText = "select sqlKey from SQLQuery";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
sqlKeyList.Add(reader["sqlKey"] as string);
}
reader.Close();
cmd.CommandText = "select sqlValue from SQLQuery";
reader = cmd.ExecuteReader();
while (reader.Read())
{
sqlValueList.Add(reader["sqlValue"] as string);
}
reader.Close();
}
Program.SQLDic.Clear();
for (int i = 0; i < sqlKeyList.Count; i++)
{
Program.SQLDic.Add(sqlKeyList[i], sqlValueList[i]);
}
}
private void btnKeyOk_Click(object sender, EventArgs e)
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = MjSql.createSqlConnection();
cmd.CommandText =
$"Update SQLQuery set sqlKey ='{tbKey.Text}' where sqlKey ='{Program.sqlKey}'";
cmd.ExecuteNonQuery();
}
}
private void btnRefrush_Click(object sender, EventArgs e)
{
lblSqlValueList.Items.Clear();
sqlKeyList.Clear();
sqlValueList.Clear();
MjSql.initQuery(sqlKeyList, sqlValueList);
foreach (var sqllist in Program.SQLDic)
{
lblSqlKeyList.Items.Add($"키 : {sqllist.Key} ");
if (lblSqlValueList.Items.Count == 0)
{
lblSqlValueList.Items.Add("아직 값이 없습니다.");
}
}
lblSqlValueList.SelectedIndex = lblSqlValueList.Items.Count - 1;
}
private void btnCheckText_Click(object sender, EventArgs e)
{
bCheck = false;
//keylist에서 같은키가 있는지 검사
MjSql.initQuery(sqlKeyList, sqlValueList);
for (int j = 0; j < sqlKeyList.Count; j++)
{
if (tbKey.Text.Trim() == sqlKeyList[j].Trim())
{
bCheck = true;
}
}
if (bCheck == false)
{
cbCheck.Checked = true;
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (tbKey.Text == string.Empty || tbValue.Text == string.Empty)
{
MessageBox.Show("빈 값이 있습니다.");
return;
}
//체크박스가 활성화 되어야만 진행가능
if (bCheck == true)
{
MessageBox.Show("Key 값이 중복됩니다.");
return;
}
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = MjSql.createSqlConnection();
cmd.CommandText =
$"insert into SQLQuery values((select count(sequence) from SQLQuery)+1 ,'{tbKey.Text}','{tbValue.Text}')";
cmd.ExecuteNonQuery();
}
}
private void Form1_Load(object sender, EventArgs e)
{
this.Size = new Size(622, 530);
MjSql.initQuery(sqlKeyList, sqlValueList);
foreach (var sqllist in Program.SQLDic)
{
//키 값만 보여주고 키 값을 선택하면 해당 키에대한 쿼리문을 보여주는 형식으로 변경하기.
lblSqlKeyList.Items.Add($"키 : {sqllist.Key} ");
// lblSqlValueList.Items.Add($" 쿼리문 : {sqllist.Value}");
if (lblSqlValueList.Items.Count == 0)
{
lblSqlValueList.Items.Add("아직 값이 없습니다.");
}
}
// for (int i = 0; i < Program.SQLDic.Count; i++)
// {
// lblSqlList.Items.Add(Program.SQLDic.Keys.ToList()[i]);
// }
lblSqlValueList.SelectedIndex = lblSqlValueList.Items.Count - 1;
//테이블 모두 검색후 리스트박스에 추가
}