2025년, 코딩은 선택이 아닌 필수!

2025년 모든 학교에서 코딩이 시작 됩니다. 먼저 준비하는 사람만이 기술을 선도해 갑니다~

강의자료/C#

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

원당컴1 2021. 2. 18. 08:35
목표

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

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

 

 

 

준비

 

 

 

폼구성


panel - Dock : Bottom

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

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

ContextMenuStrip : 삽입/삭제 추가

datagridview - Dock : Fill / ContextMenuStrip : ContextMenuStrip1

 

소스코드

- 데이터베이스 생성

 

1
2
3
4
5
6
7
8
9
10
11
12
        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();
        }
cs

 

- 테이블 존재 유무

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
        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[] { nullnullnull"TABLE" });
 
            foreach(DataRow row in schemaTable.Rows)
            {
                if (row["TABLE_NAME"].ToString() == tableName)
                {
                    res = true;
                    break;
                }
            }
            
 
            Conn.Close();
            return res;
           
cs

 

- 테이블 생성

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
        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();
        }
cs

 

- 테이블 로딩

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
        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// 읽기 전용으로
        }
cs

 

- 전체코드

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
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[] { nullnullnull"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이면 롤백
        }
    }
}
 
cs

 

 

동작

 

 

 

활용

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

 

 

 

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

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

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

 

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

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

 

사업자 정보 표시
원당컴퓨터학원 | 기희경 | 인천 서구 당하동 1028-2 장원프라자 502호 | 사업자 등록번호 : 301-96-83080 | TEL : 032-565-5497 | Mail : icon001@naver.com | 통신판매신고번호 : 호 | 사이버몰의 이용약관 바로가기