[C#] DB연결하여 전화번호부 만들기

class

  OleDbConnection conn = null;
  OleDbCommand comm = null;
  OleDbDataReader reader = null;

DB에서 학생정보를 읽어와 리스트박스에 표시

//ShowStudents()

 if (conn == null)
            {
                conn = new OleDbConnection(connstr);
                conn.Open();
            }
            string sql = "SELECT * FROM StudentTable";
            comm = new OleDbCommand(sql, conn);

            reader = comm.ExecuteReader();
            while (reader.Read()) //각각의 데이터하나하나마다
            {
                string x = "";
                x += reader["ID"] + "\t";
                x += reader["SId"] + "\t";
                x += reader["SName"] + "\t";
                x += reader["SPhone"] + "\t";
                lbStudent.Items.Add(x);
            }
            reader.Close();
            conn.Close();
            conn = null;

listbox의 선택지가 바뀔 때

ListBox lb = sender as ListBox;
            //ListBox lb =(ListBox)sender;

            if (lb.SelectedItem == null)
                return;
            string[] s = lb.SelectedItem.ToString().Split('\t');
            txtID.Text = s[0];
            txtSID.Text = s[1];
            txtName.Text = s[2];
            txtNumber.Text = s[3];

ConnOpen 과 ConnClose

        private void ConnOpen()
        {
            if (conn == null)
            {
                conn = new OleDbConnection(connstr);
                conn.Open();
            }
        }

        private void ConnClose()
        {
            conn.Close();
            conn = null;
        }

"추가" 버튼

 if (
                txtSID.Text == "" ||
                txtName.Text == "" ||
                txtNumber.Text == "")
                return;
            ConnOpen();

            string sql = string.Format(
                "INSERT INTO StudentTable(SId,SName,SPhone) VALUES( {0}, '{1}', '{2}')",
                 txtSID.Text, txtName.Text, txtNumber.Text);

            comm = new OleDbCommand(sql, conn);
            if (comm.ExecuteNonQuery()== 1)
                MessageBox.Show("정상 삽입!");

            ConnClose();

• sql문에서 문자는 ' ' 안에 써야한다.

"삭제" 버튼

 if (txtID.Text == "")
                return;
            ConnOpen();
            
            string sql = string.Format("DELETE FROM StudentTable WHERE ID={0}", txtID.Text);

            comm = new OleDbCommand(sql, conn);
            if (comm.ExecuteNonQuery() == 1)
                MessageBox.Show("삭제 성공!");

            ConnClose();
            lbStudent.Items.Clear();
            ShowStudents();

"수정" 버튼

            ConnOpen();

            string sql = string.Format(
                "UPDATE StudentTable SET SId={0}, SName='{1}', SPhone='{2}' WHERE ID={3}",
                txtSID.Text, txtName.Text, txtNumber.Text, txtID.Text);

            comm = new OleDbCommand(sql, conn);
            if (comm.ExecuteNonQuery() == 1)
                MessageBox.Show("수정 성공!");

            ConnClose();
            lbStudent.Items.Clear();
            ShowStudents();

"검색" 버튼

           if (txtSID.Text == "" &&
                txtName.Text == "" &&
                txtNumber.Text == "")
                return;

            ConnOpen();

            string sql = "SELECT * FROM StudentTable ";
            if (txtSID.Text != "")
                sql += "WHERE SId = " + txtSID.Text;
            else if (txtName.Text != "")
                sql += "WHERE SName= '" + txtName.Text + "'";
            else if (txtNumber.Text != "")
                sql += "WHERE SPhone = '" + txtNumber.Text + "'";

            lbStudent.Items.Clear();
            comm=new OleDbCommand(sql, conn);

            reader = comm.ExecuteReader();
            while (reader.Read()) //각각의 데이터하나하나마다
            {
                string x = "";
                x += reader["ID"] + "\t";
                x += reader["SId"] + "\t";
                x += reader["SName"] + "\t";
                x += reader["SPhone"] + "\t";
                lbStudent.Items.Add(x);
            }
            reader.Close();

            ConnClose();

Clear(textbox초기화), View All(모든 DB보이기), 종료 버튼

 private void btnClear_Click(object sender, EventArgs e)
        {
            txtID.Text = "";
            txtSID.Text = "";
            txtName.Text = "";
            txtNumber.Text = "";
        }

 private void btnView_Click(object sender, EventArgs e)
        {
            lbStudent.Items.Clear();
            ShowStudents();
        }

 private void btnExit_Click(object sender, EventArgs e)
        {
            Close();
        }
    }