13장 ADO.NET 메모리상의 DB 이용하여 주소록 만들기

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.IO; using System.Data.SqlClient; namespace 주소록 {     public partial class MainForm : Form     {              private String connectinonString; //커넥션 스트링 저장용 필드 선언         public MainForm()         {             InitializeComponent();                      //데이터 베이스 연결문자열 지정             connectinonString = String.Format (                 "server=.;database={0};uid={0};pwd=1234;", "AddressBook");         }         private void MainForm_Load(object sender, EventArgs e)         {            DisplayData();         }         private void LoadData()         {                          DisplayData();         }

                                             ↓

//Dataset을 필드 레벨로 옮겨서 다른서 메서드에서 사용가능하도록 한다 .                 DataSet ds; //현패 폼에서 공통적으로 사용할 DataSet개체         // 출력         private void DisplayData()         {             SqlConnection con = new SqlConnection(connectinonString);             con.Open(); //연결             SqlCommand cmd = new SqlCommand                        ("Select *From AddressBook", con);             SqlDataAdapter da = new SqlDataAdapter(cmd);             ds = new DataSet();             da.Fill(ds, "AddressBook");             this.dgvAddress.DataSource = ds.Tables[0].DefaultView;                         con.Close(); //닫기             if (ds.Tables[0].Rows.Count > 0)             {                 ShowRecord(0); //첫번째 자료로 초기화                 currentIndex = 0;             }             sslCount.Text = ds.Tables[0].Rows.Count.ToString();         }

                                    ↓

private void btnSave_Click(object sender, EventArgs e)         {               SaveData();         } ----------------------------------------------------------------------        private void SaveData()         {             SqlConnection con = new SqlConnection(connectinonString);             con.Open();             SqlCommand cmd = new SqlCommand();             cmd.Connection = con;             cmd.CommandText = "Insert Into AddressBook Values                                          (@Name, @Mobile, @Email)";             cmd.CommandType = CommandType.Text;             cmd.Parameters.AddWithValue("@Name", txtName.Text);             cmd.Parameters.AddWithValue("@Mobile", txtMobile.Text);             cmd.Parameters.AddWithValue("@Email", txtEmail.Text);             cmd.ExecuteNonQuery() ; //저장             con.Close();             ClearTextBox();             MessageBox.Show("저장되었습니다.");             DisplayData(); //호출 하여 재 바인딩         }

                                     ↓

// 백업         private void miBackup_Click(object sender, EventArgs e)         {             try             {                 string dir = "C:\\AddressBook.xml";                 //해당 폴더에는 NTFS파일 시스템 권한이 필요                 ds.WriteXml(dir); //XML 형태로 저장                 MessageBox.Show("백업 완료");             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }         } ----------------------------------------------------------------------        // 종료          private void miExit_Click(object sender, EventArgs e)         {             Application.Exit();         }         private int currentIndex = -1;         //셀 클릭하였을 경우 선택한값 텍스트 박스에 표시         private void dgvAddress_CellClick(object sender,             DataGridViewCellEventArgs e)         {             currentIndex = e.RowIndex; // 현재 인덱스를 필드에 보관             if (e.RowIndex < ds.Tables[0].Rows.Count)             {                 ShowRecord(e.RowIndex);             }         }

                                      ↓ 

//상세         private void ShowRecord(int index)         {             DataRow dr = ds.Tables[0].Rows[index];                         this.txtNum.Text = dr["Num"].ToString();             this.txtName.Text = dr["Name"].ToString();             this.txtMobile.Text = dr["Mobile"].ToString();             this.txtEmail.Text = dr["Email"].ToString();                      txtGo.Text = txtNum.Text; //현재 선택 된 인덱스값 출력         }         //수정         private void btnModify_Click(object sender, EventArgs e)         {             SqlConnection con = new SqlConnection(connectinonString);             con.Open();             SqlCommand cmd = new SqlCommand();             cmd.Connection = con;             cmd.CommandText = "Update AddressBook                       Set Name = @Name, Mobile = @Mobile, Email = @Email                                         Where Num = @Num";             cmd.CommandType = CommandType.Text;             cmd.Parameters.AddWithValue("@Name", txtName.Text);             cmd.Parameters.AddWithValue("@Mobile", txtMobile.Text);             cmd.Parameters.AddWithValue("@Email", txtEmail.Text);             cmd.Parameters.AddWithValue("@Num", txtNum.Text);             cmd.ExecuteNonQuery();             con.Close();             MessageBox.Show("수정되었습니다");             ClearTextBox();             DisplayData();             ShowRecord(currentIndex);         }         //삭제         private void btnDelete_Click(object sender, EventArgs e)         {             if (txtNum.Text != "" && currentIndex != -1)             {                 DialogResult dr =                     MessageBox.Show("정말로 삭제하시겠습니까?",                            "삭제확인" , MessageBoxButtons.YesNo,                                            MessageBoxIcon.Question);                 if (dr != DialogResult.No)                 {                     // 실제 DB에서 삭제                     DeleteData(); //호출                     DisplayData();                 }             }         }         private void DeleteData()         {             SqlConnection con = new SqlConnection(connectinonString);             con.Open();             SqlCommand cmd = new SqlCommand();             cmd.Connection = con;             cmd.CommandText =                    "Delete AddressBook Where Num = @Num";             cmd.CommandType = CommandType.Text;                         //파라미터 추가             cmd.Parameters.AddWithValue("@Num", txtNum.Text);                         cmd.ExecuteNonQuery(); //삭제 실행             con.Close();             MessageBox.Show("삭제되었습니다");             ClearTextBox();                        }

                                   ↓

// 처음, 이전, 다음, 마지막 버튼에 대한 공통 이벤트         private void btnMove_Click(object sender, EventArgs e) {             Button btn = sender as Button;             if (btn == btnFirst) {                 if (currentIndex > 0) {                     currentIndex = 0; // 0번째 인덱스로 표시                     }                 MessageBox.Show("더이상 가실수 없습니다");                       }             else if (btn == btnPrev) {                 if (currentIndex > 0) {                     currentIndex--;                 }                 MessageBox.Show("더이상 가실수 없습니다");             }             else if (btn == btnNext) {                 if (currentIndex < ds.Tables[0].Rows.Count - 1) {                     currentIndex++;                 }             }             else if (btn == btnLast) {                 if (currentIndex != -1) {                     currentIndex = ds.Tables[0].Rows.Count - 1;                 }             }             ShowRecord(currentIndex); // 다시 데이터 표시         }         //이동         private void btnGo_Click(object sender, EventArgs e)         {             if (txtGo.Text != "" && Convert.ToInt32(txtGo.Text) > 0)              {                 int go = Convert.ToInt32(txtGo.Text);                 DataRow dr;                 bool flag = false ;                 for (int i = 0; i < ds.Tables[0].Rows.Count; i++)                  {                     dr= ds.Tables[0].Rows[i];                         if (go == Convert.ToInt32(dr["Num"]))                     {                         txtNum.Text = dr["Num"].ToString();                         txtName.Text = dr["Name"].ToString();                         txtMobile.Text = dr["Mobile"].ToString();                         txtEmail.Text = dr["Email"].ToString();                                               flag = true;                      }                  }                 if(flag == false)                 {                     MessageBox.Show("찾지못했습니다");                 }                 txtGo.Select();             }         }         // 검색         private void btnSearch_Click(object sender, EventArgs e)         {             DataView ddv = ds.Tables[0].DefaultView;             ddv.RowFilter =                     "Name Like '%" + txtSearch.Text + "%' Or "                     + "Mobile Like '%" + txtSearch.Text + "%' Or "                     + "Email Like '%" + txtSearch.Text + "%'";             this.dgvAddress.DataSource = ddv;                   }         //텍스트박스 클리어         private void button1_Click(object sender, EventArgs e)         {             ClearTextBox(); //텍스트박스 클리어         }     }

---

주소록0929.zip