F018_PhoneBook

OleDbConnection conn = null;  // connection

OleDbCommand comm = null;  // command

OleDbDataReader reader = null;  // DataReader 또는 // ExecuteNonQuery

SELECT * FROM 테이블이름

SELECT * FROM 테이블이름 WHERE SId=…

UPDATE 테이블이름 SET ID=…, SId=…, SName=…, Phone=…
WHERE ID=…

이때 숫자는 따옴표 없이, 문자열은 ‘문자열’ 처럼 작은 따옴표를 넣어주어야 한다

INSERT INTO 테이블이름 VALUES ( … , … , … , …)

DELETE FROM 테이블이름 WHERE ID=…

---

 

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;

namespace F018_PhoneBook
{
    public partial class Form1 : Form
    {
        OleDbConnection conn = null;
        OleDbCommand comm = null;
        OleDbDataReader reader = null;
        string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\azc27\Documents\StudentDB.accdb";
        public Form1()
        {
            InitializeComponent();
            DisplayStudents();
        }

 

DisplayStudents()

 private void DisplayStudents()
        {
            if (conn == null)
            {
                conn = new OleDbConnection(connStr);
                conn.Open();

                //데이터 베이스에서 작동할 명령어
                //SQL 명령어
                string sql = "SELECT*FROM StudentTable";
                comm = new OleDbCommand(sql, conn);

                //읽어오는 작업

                ReadAndAddToListBox();

                reader.Close();
                conn.Close();
                conn = null;
            }
        }

 

listBox1 설정

private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            ListBox lb = sender as ListBox;

            if (lb.SelectedItem == null)
                return;

            string[] s = lb.SelectedItem.ToString().Split('\t');

            txtID.Text = s[0];
            txtSID.Text = s[1];
            txtName.Text = s[2];
            txtPhone.Text = s[3];

        }

 

ConnectionOpen()

private void ConnectionOpen()
        {
            if (conn == null)
            {
                conn = new OleDbConnection(connStr);
                conn.Open();
            }
        }

 

ConnectionClose()

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

 

ReadAndAddToListBox()

private void ReadAndAddToListBox()
        {
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                string x = "";
                x += reader[0] + "\t";//ID
                x += reader[1] + "\t";//SID
                x += reader[2] + "\t";//SName
                x += reader[3] + "\t";//Phone

                listBox1.Items.Add(x);
            }
        }

 

추가버튼

private void btnInsert_Click(object sender, EventArgs e)
        {
            if (txtName.Text == "" || txtSID.Text == "" || txtPhone.Text == "")
                return;

            //다시 DB를 연결하고 Open한다

            ConnectionOpen();

            string sql = string.Format("INSERT INTO" +
                "StrudentTable(SId,SName,Phone)" +
                "VALUES({0},'{1}','{2},)",
                txtSID.Text, txtName.Text, txtPhone.Text);

            MessageBox.Show(sql);

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


            ConnectionClose();

            listBox1.Items.Clear();
            DisplayStudents();

        }

 

검색버튼

private void btnSearch_Click(object sender, EventArgs e)
        {
            if (txtSID.Text == "" && txtName.Text == "" && txtPhone.Text == "")
                return;
            ConnectionOpen();

            string sql = "";
            if (txtSID.Text != "")
            {
                sql = string.Format("SELECT * FROM StudentTable WHERE SID={0}",
                    txtSID.Text);
            }
            else if (txtName.Text != "")
            {
                sql = string.Format("SELECT * FROM StudentTable WHERE S='{0}'",
                    txtName.Text);
            }
            else if (txtPhone.Text != "")
            {
                sql = string.Format("SELECT * FROM StudentTable WHERE SID='{0}'",
                    txtPhone.Text);
            }

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

            
            ReadAndAddToListBox();

            ConnectionClose();
        }

 

삭제버튼

private void button5_Click(object sender, EventArgs e)
        {
            ConnectionOpen();

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

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

            ConnectionClose();
            listBox1.Items.Clear();
            DisplayStudents();
        }

 

수정버튼

private void btnUpdate_Click(object sender, EventArgs e)
        {
            ConnectionOpen();

            string sql = string.Format("UPDATE StudentTable ET SID={0}." +
                "SName='{1}',Phone='{2}' WHERE ID={3}",
                txtID.Text, txtName.Text, txtPhone.Text, txtSID.Text);

            MessageBox.Show(sql);
            comm = new OleDbCommand(sql, conn);

            if (comm.ExecuteNonQuery() == 1)
                MessageBox.Show("수정 성공!");
            ConnectionClose();

            listBox1.Items.Clear();
            DisplayStudents();
        }

 

텍스박스의 내용 지우기

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

 

종료버튼

private void btnQuit_Click(object sender, EventArgs e)
        {
            this.Close();
        }