24.ADO.NET - CommandTest

WinForm 에서 ADO ConnectionString문자열 연결하여 DB에 값을 입력/출력/수정/삭제 하는 예제입니다.

---

Form1.Designer.cs

[그림24-1]        private System.Windows.Forms.ListBox listBox1;        private System.Windows.Forms.Button btnSelect;        private System.Windows.Forms.Button btUpdate;        private System.Windows.Forms.Button btnDelete;        private System.Windows.Forms.Button btnSum;        private System.Windows.Forms.TextBox txtCategoryName;        private System.Windows.Forms.TextBox txtSuperCategory;        private System.Windows.Forms.TextBox txtAlign;        private System.Windows.Forms.Button btnInsert;        private System.Windows.Forms.Button btnInsertPara;        private System.Windows.Forms.Label label1;        private System.Windows.Forms.Label label2;        private System.Windows.Forms.Label label3;

---

Form1.cs

namespace CommandTest {     public partial class Form1 : Form     {         private SqlConnection con;           public Form1()         {             InitializeComponent();         }           private void Form1_Load(object sender, EventArgs e)         {             con = new SqlConnection();             con.ConnectionString =                 "server=WINDOWS-XP\\SQLSERVER;database=Test;uid=Test;pwd=1234;           Integrated Security=true";             con.Open();         }           private void Form1_FormClosed(object sender, FormClosedEventArgs e)         {             con.Close();         }           private void btnSelect_Click(object sender, EventArgs e)         {             PrintTable();         }           private void PrintTable()         {             string Rec;             SqlCommand cmd = new SqlCommand("Select *From Categories", con);             SqlDataReader dr;             dr = cmd.ExecuteReader();             listBox1.Items.Clear();             while (dr.Read())             {                 Rec = string.Format("카테고리명 : {0}, 부모카테고리명 : {1}, 정렬 : {2}",                     dr["CategoryName"], dr["SuperCategory"], dr["Align"]);                                listBox1.Items.Add(Rec);             }             dr.Close();         }           private void btUpdate_Click(object sender, EventArgs e)         {             string Sql = "Update Categories Set CategoryName = 'Computer' Where Num = 3";             SqlCommand cmd = new SqlCommand(Sql, con);             cmd.ExecuteNonQuery();             PrintTable();         }           private void btnDelete_Click(object sender, EventArgs e)         {             string Sql = "Delete From Categories Where Num = 2";             SqlCommand cmd = new SqlCommand(Sql, con);             cmd.ExecuteNonQuery();             PrintTable();         }           private void btnSum_Click(object sender, EventArgs e)         {             string Sql = "Select Sum(ProductCount) From Products";             SqlCommand cmd = new SqlCommand(Sql, con);             int Sum = (int)cmd.ExecuteScalar();             MessageBox.Show("물건의 총 개수는" + Sum + "입니다.");         }           private void btnInsert_Click(object sender, EventArgs e)         {             string Sql = string.Format("Insert Into Categories Values('{0}', {1}, {2})",                 txtCategoryName.Text, txtSuperCategory.Text, txtAlign.Text);               SqlCommand cmd = new SqlCommand(Sql, con);             cmd.ExecuteNonQuery();             PrintTable();         }           private void btnInsertPara_Click(object sender, EventArgs e)         {             string Sql =                 "Insert Into Categories Values(@CategoryName, @SuperCategory, @Align)";               SqlCommand cmd = new SqlCommand(Sql, con);               cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 10);             cmd.Parameters.Add("@SuperCategory", SqlDbType.Int);             cmd.Parameters.Add("@Align", SqlDbType.Int);               cmd.Parameters["@CategoryName"].Value = txtCategoryName.Text;             cmd.Parameters["@SuperCategory"].Value = txtSuperCategory.Text;             cmd.Parameters["@Align"].Value = txtAlign.Text;               cmd.ExecuteNonQuery();             PrintTable();         }     } }

---

결과화면

[그림24-2]

CommandTest.zip