[C#] C#에서 MDB(Access DataBase) 다루기

목표

- MDB(Access DataBase) 파일이 없을때 생성해 보기

- MDB(Access DataBase) 를 이용해서 데이터베이스 명령어(Sql문 - insert,select,update 등)을 확인하기

 

 

 

준비

 

 

 

폼구성

panel - Dock : Bottom

label : 4개 - 아이디/이름/핸드폰/학교/학년

textbox : 4개 - 아이디/이름/핸드폰/학교/학년

ContextMenuStrip : 삽입/삭제 추가

datagridview - Dock : Fill / ContextMenuStrip : ContextMenuStrip1

 

소스코드

- 데이터베이스 생성

 

        private void mdbFileCreate(string strDBName)
        {
            String strDBCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDBName + ";Jet OLEDB:Database Password=1234";
 
            ADOX.CatalogClass adoxCC = new ADOX.CatalogClass();
            adoxCC.Create(strDBCon);
 
            adoxCC.ActiveConnection = null;
            adoxCC = null;
 
            GC.Collect();
        }

 

- 테이블 존재 유무

 

        private bool isMDBTable(string tableName)
        {
            Boolean res = false;            
            String strDBCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ strDBName + ";Jet OLEDB:Database Password=1234";
            
            Conn.ConnectionString = strDBCon;
            Conn.Open();
 
            DataTable schemaTable = Conn.GetOleDbSchemaTable(
                                    OleDbSchemaGuid.Tables,
                                    new object[] { null, null, null, "TABLE" });
 
            foreach(DataRow row in schemaTable.Rows)
            {
                if (row["TABLE_NAME"].ToString() == tableName)
                {
                    res = true;
                    break;
                }
            }
            
 
            Conn.Close();
            return res;
           

 

- 테이블 생성

 

        private void createMDBTable(string tableName)
        {
            String strDBCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDBName + ";Jet OLEDB:Database Password=1234";
 
            Conn.ConnectionString = strDBCon;
            Conn.Open();
            String stSql="";
            if (tableName == "Test") // Test 테이블 생성하자.
            {
                stSql = "Create Table Test(id int,name nvarchar(50),handphone varchar(30),school nvarchar(100),grade int, Primary Key(id))";
                //아이디,이름,핸드폰,학교,학년 (키값은 아이디) 로 테이블 생성하자. 
            }
            if(stSql != "")
            {
                ExecSql(stSql);
            }
            Conn.Close();
        }

 

- 테이블 로딩

 

        private void mdbTableLoad(string tableName)
        {
 
 
            String strSql = "Select * From " + tableName;
            OleDbDataAdapter m_daDataAdapter = new OleDbDataAdapter(strSql, Conn);
 
            OleDbCommandBuilder m_cbCommandBuilder = new OleDbCommandBuilder(m_daDataAdapter);
            DataTable m_dtStudent = new DataTable();
 
            m_daDataAdapter.Fill(m_dtStudent);
            dataGridView1.DataSource = m_dtStudent;
            dataGridView1.MultiSelect = false; //한개의 열만 선택하자.
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; //셀선택시 한행 전체 선택
            dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.WhiteSmoke;//행의 색상을 다르게
            dataGridView1.ReadOnly = true; // 읽기 전용으로
        }

 

- 전체코드

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
 
namespace MDBManager
{
   
    public partial class Form1 : Form
    {
        static String strDBName = "TEST.MDB";
        static int selectRowIndex = 0;
        OleDbConnection Conn;
        public Form1()
        {
            InitializeComponent();
            FileInfo fileInfo = new FileInfo(strDBName);
            if(!fileInfo.Exists)
            {
                //데이터 파일이 없으면 생성해 주자.
                mdbFileCreate(strDBName);
            }
            Conn = new OleDbConnection();
 
            if (!isMDBTable("Test"))
            {
                //Test 테이블이 있는지 체크해서 없으면 
                //엑셀에서는 Sheet 개념
                createMDBTable("Test"); // Test 테이블을 생성하자.
            }
 
            mdbConnect();
            
            mdbTableLoad("Test"); //Test Table을 DataGridView에 연결하자.
        }
 
        private void mdbConnect()
        {
            String strDBCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDBName + ";Jet OLEDB:Database Password=1234";
 
            Conn.ConnectionString = strDBCon;
            Conn.Open();
        }
 
        private void mdbTableLoad(string tableName)
        {
 
 
            String strSql = "Select * From " + tableName;
            OleDbDataAdapter m_daDataAdapter = new OleDbDataAdapter(strSql, Conn);
 
            OleDbCommandBuilder m_cbCommandBuilder = new OleDbCommandBuilder(m_daDataAdapter);
            DataTable m_dtStudent = new DataTable();
 
            m_daDataAdapter.Fill(m_dtStudent);
            dataGridView1.DataSource = m_dtStudent;
            dataGridView1.MultiSelect = false; //한개의 열만 선택하자.
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; //셀선택시 한행 전체 선택
            dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.WhiteSmoke;//행의 색상을 다르게
            dataGridView1.ReadOnly = true; // 읽기 전용으로
        }
 
        private void createMDBTable(string tableName)
        {
            String strDBCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDBName + ";Jet OLEDB:Database Password=1234";
 
            Conn.ConnectionString = strDBCon;
            Conn.Open();
            String stSql="";
            if (tableName == "Test") // Test 테이블 생성하자.
            {
                stSql = "Create Table Test(id int,name nvarchar(50),handphone varchar(30),school nvarchar(100),grade int, Primary Key(id))";
                //아이디,이름,핸드폰,학교,학년 (키값은 아이디) 로 테이블 생성하자. 
            }
            if(stSql != "")
            {
                ExecSql(stSql);
            }
            Conn.Close();
        }
 
        private bool isMDBTable(string tableName)
        {
            Boolean res = false;            
            String strDBCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ strDBName + ";Jet OLEDB:Database Password=1234";
            
            Conn.ConnectionString = strDBCon;
            Conn.Open();
 
            DataTable schemaTable = Conn.GetOleDbSchemaTable(
                                    OleDbSchemaGuid.Tables,
                                    new object[] { null, null, null, "TABLE" });
 
            foreach(DataRow row in schemaTable.Rows)
            {
                if (row["TABLE_NAME"].ToString() == tableName)
                {
                    res = true;
                    break;
                }
            }
            
 
            Conn.Close();
            return res;
           
        }
 
        private void mdbFileCreate(string strDBName)
        {
            String strDBCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDBName + ";Jet OLEDB:Database Password=1234";
 
            ADOX.CatalogClass adoxCC = new ADOX.CatalogClass();
            adoxCC.Create(strDBCon);
 
            adoxCC.ActiveConnection = null;
            adoxCC = null;
 
            GC.Collect();
        }
 
        private void button2_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
 
        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            Conn.Close();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            if(idCheck(txt_ID.Text))
            {
                updateTestTable(txt_ID.Text, txt_Name.Text, txt_HandPhone.Text, txt_School.Text, txt_Grade.Text);
            }
            else
            {
                InsertTestTable(txt_ID.Text, txt_Name.Text, txt_HandPhone.Text, txt_School.Text, txt_Grade.Text);
            }
            mdbTableLoad("Test");
        }
 
        private int InsertTestTable(string stID, string stName,string stHandphone, string stSchool, string stGrade)
        {
            //id int,name nvarchar(50),handphone varchar(30),school nvarchar(100),grade int
            
            String stSql = "insert into Test(id,name,handphone,school,grade) values( " + stID + ",'" + stName + "','" + stHandphone + "','" + stSchool + "'," + stGrade + ")";
            return ExecSql(stSql); //-1이면 롤백
 
        }
 
        private int ExecSql(string stSql)
        {
            OleDbCommand cmd = new OleDbCommand(stSql, Conn);
            
            return cmd.ExecuteNonQuery();
        }
 
        private int updateTestTable(string stID, string stName, string stHandphone, string stSchool, string stGrade)
        {
            String stSql = "update Test set name = '" + stName + "',handphone = '" + stHandphone + "',school = '" + stSchool + "',grade = " + stGrade + " where id = " + stID;
            return ExecSql(stSql); //-1이면 롤백
        }
 
        private bool idCheck(string stID)
        {
            String strSql = "Select * From Test where id = " + stID;
            OleDbCommand OLECmd = new OleDbCommand(strSql, Conn);
            OLECmd.CommandType = CommandType.Text;
            OleDbDataReader OLEReader = OLECmd.ExecuteReader(CommandBehavior.Default);
            bool res;
            res = OLEReader.Read();
            OLECmd.Dispose();
            OLEReader.Dispose();
            return res;
 
        }
 
        private void 삽입ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            txt_ID.Text = "";
            txt_Name.Text = "";
            txt_HandPhone.Text = "";
            txt_School.Text = "";
            txt_Grade.Text = "";
        }
 
        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            selectRowIndex = e.RowIndex;
            txt_ID.Text = dataGridView1.Rows[e.RowIndex].Cells[0].FormattedValue.ToString();
            txt_Name.Text = dataGridView1.Rows[e.RowIndex].Cells[1].FormattedValue.ToString();
            txt_HandPhone.Text = dataGridView1.Rows[e.RowIndex].Cells[2].FormattedValue.ToString();
            txt_School.Text = dataGridView1.Rows[e.RowIndex].Cells[3].FormattedValue.ToString();
            txt_Grade.Text = dataGridView1.Rows[e.RowIndex].Cells[4].FormattedValue.ToString();
        }
 
        private void 삭제ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DeleteTestTable(dataGridView1.Rows[selectRowIndex].Cells[0].FormattedValue.ToString());
            mdbTableLoad("Test");
        }
 
        private int DeleteTestTable(string stID)
        {
            String stSql = "Delete from Test where id = " + stID;
            return ExecSql(stSql); //-1이면 롤백
        }
    }
}
 

 

 

동작

 

 

 

활용

데이터베이스의 기본을 알고 데이터베이스를 활용하여 구현하는 응용프로그램 구현

 

 

 

=====================================================

이 자료는 학생들과 특강시간에 만들어 보는 프로젝트입니다.

=====================================================

 

오늘도 최선을 다하는 우리 학생들을 응원합니다.

인천 서구 검단신도시 원당컴퓨터학원