C# with SQLite - 데이터베이스 연동 3

C# WinForm과 SQLite를 이용해 간단한 회원관리 프로그램을 만들어 보자.

 

DataGridView, Button등을 윈폼에 적당히 배치한다.

 

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.SQLite;
 
namespace Manager
{
    public partial class Form1 : Form
    {
        SQLiteConnection connection;
        SQLiteCommand command;
        DataTable dataTable;
 
        public Form1()
        {
            InitializeComponent();
 
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView1.ReadOnly = true;
            dataGridView1.AllowUserToAddRows = false;
 
            comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;
            comboBox1.Items.Add("아이디로");
            comboBox1.Items.Add("이름으로");
            comboBox1.Items.Add("생일로");
            comboBox1.SelectedIndex = 0;
 
            try
            {
                connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "/member.db");
                connection.Open();
 
                command = new SQLiteCommand(connection);
                command.CommandText = "CREATE TABLE IF NOT EXISTS member (" +
                    "id TEXT UNIQUE NOT NULL, " +
                    "name TEXT NOT NULL, " +
                    "birthday TEXT NOT NULL)";
                command.ExecuteNonQuery();
 
                command.CommandText = "SELECT * FROM member";
                SQLiteDataReader reader = command.ExecuteReader();
                dataTable = new DataTable();
                dataTable.Load(reader);
                dataGridView1.DataSource = dataTable;
                reader.Close();
 
                dataGridView1.Columns[0].HeaderText = "아이디";
                dataGridView1.Columns[1].HeaderText = "이름";
                dataGridView1.Columns[2].HeaderText = "생일";
 
                connection.Close();
 
                label4.Text = "총 회원수: " + dataGridView1.Rows.Count;
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            try // 회원 정보 수정
            {
                foreach (DataGridViewRow row in dataGridView1.Rows) // 아이디(변경불가 상황 가정)를 바꾸려 시도하는 경우 체크
                {
                    if (row.Cells[0].Value.ToString() == textBox1.Text.Trim()) // 아이디 비교
                    {
                        if (MessageBox.Show(row.Cells[1].Value + "의 데이터를 수정 합니다. 수정하시겠습니까?",
                            "수정", MessageBoxButtons.YesNo) == DialogResult.Yes)
                        {
                            connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "/member.db");
                            connection.Open();
 
                            command = new SQLiteCommand(connection);
                            command.CommandText = "UPDATE member set name=:name, birthday=:birthday WHERE id=:id";
                            command.Parameters.Add("name", DbType.String).Value = textBox2.Text.Trim();
                            command.Parameters.Add("birthday", DbType.String).Value = textBox3.Text.Trim();
                            command.Parameters.Add("id", DbType.String).Value = textBox1.Text.Trim();
                            command.ExecuteNonQuery();
 
                            connection.Close();
 
                            row.Cells[0].Value = textBox1.Text.Trim();
                            row.Cells[1].Value = textBox2.Text.Trim();
                            row.Cells[2].Value = textBox3.Text.Trim();
 
                            return;
                        }
                        else
                        {
                            //MessageBox.Show("바뀌지 않았습니다.");
                            return;
                        }
                    }
                }
                MessageBox.Show(textBox1.Text + "는 존재하지 않는 ID 입니다.");
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }
 
        private void dataGridView1_SelectionChanged(object sender, EventArgs e)
        {
            // 프로그램 시작 시 인덱스 에러 방지
            if (dataGridView1.SelectedRows.Count < 1)
                return;
 
            DataGridViewRow row = dataGridView1.SelectedRows[0];
            textBox1.Text = row.Cells[0].Value.ToString();
            textBox2.Text = row.Cells[1].Value.ToString();
            textBox3.Text = row.Cells[2].Value.ToString();
        }
 
        private void button2_Click(object sender, EventArgs e)
        {
            try // 회원 추가
            {
                connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "/member.db");
                connection.Open();
 
                command = new SQLiteCommand(connection);
                command.CommandText = $"INSERT INTO member (id, name, birthday) VALUES (" +
                    $"'{textBox1.Text.Trim()}', '{textBox2.Text.Trim()}', '{textBox3.Text.Trim()}')";
                command.ExecuteNonQuery();
 
                connection.Close();
 
                dataTable.Rows.Add(textBox1.Text.Trim(), textBox2.Text.Trim(), textBox3.Text.Trim());
                label4.Text = "총 회원수: " + dataGridView1.Rows.Count;
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }
 
        private void button3_Click(object sender, EventArgs e)
        {
            try // 회원 삭제
            {
                if (MessageBox.Show($"{dataGridView1.SelectedRows[0].Cells[1].Value}의 데이터를 정말 삭제 하시겠습니까?",
                    "삭제", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "/member.db");
                    connection.Open();
 
                    command = new SQLiteCommand(connection);
                    command.CommandText = "DELETE from member WHERE id=:id";
                    command.Parameters.Add("id", DbType.String).Value = textBox1.Text.Trim();
                    command.ExecuteNonQuery();
 
                    connection.Close();
 
                    DataGridViewRow row = dataGridView1.SelectedRows[0];
                    dataGridView1.Rows.Remove(row);
 
                    label4.Text = "총 회원수: " + dataGridView1.Rows.Count;
                }
                else
                {
                    //MessageBox.Show("삭제하지 않았습니다");
                }
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }
 
        private void button4_Click(object sender, EventArgs e)
        {
            try // 검색
            {
                string filter;
                switch (comboBox1.SelectedIndex)
                {
                    case 0:
                        filter = "id";
                        break;
 
                    case 1:
                        filter = "name";
                        break;
 
                    case 2:
                        filter = "birthday";
                        break;
 
                    default:
                        filter = "id";
                        break;
                }
 
                connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "/member.db");
                connection.Open();
                command = new SQLiteCommand(connection);
 
                if (textBox4.Text.Trim().Length == 0)
                {
                    command.CommandText = $"SELECT * FROM member";
                }
                else
                {
                    command.CommandText = $"SELECT * FROM member where {filter}='{textBox4.Text.Trim()}'";
                }
                SQLiteDataReader reader = command.ExecuteReader();
                dataTable = new DataTable();
                dataTable.Load(reader);
                dataGridView1.DataSource = dataTable;
                reader.Close();
 
                connection.Close();
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }
 
        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            // 항목 번호 그리기
            String rowIndex = (e.RowIndex + 1).ToString();
 
            StringFormat centerFormat = new StringFormat()
            {
                Alignment = StringAlignment.Center,
                LineAlignment = StringAlignment.Center
            };
 
            Rectangle headerBound = new Rectangle(e.RowBounds.Left, e.RowBounds.Top,
                dataGridView1.RowHeadersWidth, e.RowBounds.Height);
            e.Graphics.DrawString(rowIndex, Font, SystemBrushes.ControlText, headerBound, centerFormat);
        }
    }
}

 

소스를 입력하고 빌드한다.

 

프로그램을 실행하면 빈 데이터베이스가 생성된다.

 

회원 추가, 수정, 삭제, 검색등을 해 본다.