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

C# WinForm의 DataGridView와 SQLite를 연동해 보자.

 

윈폼에 DataGridView를 적당히 배치한다.

 

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 WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
 
            try
            {
                SQLiteConnection connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "/test.db");
                connection.Open();
 
                SQLiteCommand command = new SQLiteCommand(connection);
                command.CommandText = "CREATE TABLE IF NOT EXISTS test (" +
                    "id INTEGER PRIMARY KEY, " +
                    "name TEXT NOT NULL, " +
                    "birthday TEXT NOT NULL)";
                command.ExecuteNonQuery();
 
                command.CommandText = "INSERT OR IGNORE INTO test (id, name, birthday) VALUES (1, 'sean', '2020-01-20'), " +
                    "(2, 'david', '2021-11-03'), " +
                    "(3, 'john', '2022-05-17')";
                command.ExecuteNonQuery();
 
                command.CommandText = "SELECT * FROM test";
                SQLiteDataReader dataReader = command.ExecuteReader();
                DataTable dataTable = new DataTable();
                dataTable.Load(dataReader);
                dataGridView1.DataSource = dataTable;
                dataReader.Close();
 
                // 각 컬럼의 HeaderText를 변경하지 않으면 SQLite에 지정된 컬럼명 그대로 사용된다.
                dataGridView1.Columns[0].HeaderText = "아이디";
                dataGridView1.Columns[1].HeaderText = "이름";
                dataGridView1.Columns[2].HeaderText = "생일";
 
                connection.Close();
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }
 
        // DataGridView에 번호를 그려주는(DrawString) 함수.
        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            //DataGridView dataGridView = sender as DataGridView;
            // 'sender as DataGridView' 는 dataGridView1과 동일.
 
            String rowIndex = (e.RowIndex + 1).ToString();
            //String rowIdx = (e.RowIndex + 1).ToString("D4"); // 10진수 4자리 표기(0001~9999). 16진수4자리는 'X4'.
 
            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);
        }
    }
}

 

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

 

실행하면 데이터가 표시된다.