[VB.NET] 비주얼베이직에서 MS-Access의 데이터베이스와 바인딩하기

안녕하세요? HappyBono 입니다.

작년 하반기 11월 말 경, 제 Instagram 쪽지함에 한 통의 쪽지가 도착했습니다. 어느 대학생 몇 명의 학생들이 과제의 도움이 필요하다며 간절하게 도움을 요청해왔는데요, 사실 하루에도 제 이메일로 많은 프로그래밍 질문이 쏟아질 때도 있긴 합니다만, 그냥 형식적으로 과제를 해 달라고 부탁하는 성의 없는 질문은 들어주려고 하지 않습니다만 이번 학생들은 굉장히 열정적이고 노력하겠다는 의지가 확고했기에, 들어주어야겠다는 결심을 하고 도와주기로 했었습니다.

 

 

학생들의 과제는 “예식장 예약 프로그램” 을 개발하여 제출해야하며,  MS Access 데이터베이스와의 연동은 필수적으로 구현해야 하는 과제였었는데요. 이러한 과제 뿐 아니라 최근 들어 Naver 지식iN 에도 비슷한 데이터베이스 연동에 대한 질문들이 많이 올라오고 있음에 강좌를 작성해둡니다.

보통 데이터베이스와의 연동이라 하면, Access 형식으로 저장된 데이터베이스 파일의 내용을 Visual Basic .NET 에서 처리하기 위해 ADO.NET 을 사용하여 코드를 작성하는 것이 일반적인 방법인데요.

따라서, Visual Basic 코드 뿐 아니라 기본적인 SQL 데이터 구문까지 어느 정도 이해하고 계셔야 프로그램을 구현하는 데 큰 문제가 없습니다.

이번 강좌에서는 아래와 같은 내용을 다루어보도록 하겠습니다 :

• DataGridView 컨트롤을 사용하여 데이터베이스의 데이터를 얻어오는 방법
• ADO.NET 을 사용하여 MS Access 2016 형식의 데이터베이스 내의 항목을 삽입 / 삭제 / 업데이트 및 선택하는 방법.
• 데이터를 삽입 / 삭제 / 업데이트 및 선택 하는 데 SQL 구문을 이용하는 방법.

 

우선 Access 데이터베이스에서 주로 사용되는 SQL 구문들을 알아봅시다 :

SELECT 문
테이블이나 뷰에 있는 데이터를 조회할 때 사용하는 문장입니다.

문장 구문

SELECT * (또는 [열 제목]) : 결과 창에 나올 열 번호를 입력합니다.
( * 는 테이블의 모든 열)
FROM [테이블 명] : 어느 테이블이 검색 대상이 될 지를 입력합니다.
WHERE [조건] : 데이터에서 해당 조건에 해당하는 것들을 추출합니다.
GROUP BY [열 제목] 혹은 [표현 식] : 그룹으로 묶을 열 이름이나 표현 식을 이용해서 사용하는 대신,  GROUP BY 구문에 사용한 열 중에서만 결과창에 나올 열로 사용 할 수 있습니다.
HAVING BY [조건식] : 그룹으로 지정된 결과를 다시 한번 조건에 맞는 것들로 걸러냅니다.
ORDER BY [열 제목] : 정렬 방식을 결정합니다.
asc (기본 값) : 오름차순 / dsc : 내림차순

기본적으로 SELECT ~ FROM 까지만 쓰면 사용할 수 있으며, 나머지는 선택적으로 사용이 가능합니다.
(HAVING BY 는 GROUP BY 절을 사용 하는 경우에 한해 사용할 수 있습니다.)

FROM 절에는 테이블이 하나만 올 수 있지만 그 이상도 올 수 있습니다.
(당연하겠지만, 추가한 테이블의 경우 조건이나 결과 창에 나올 열 제목란에도 쓸 수 있습니다.)

WHERE 구문에서는 AND, OR 연산자를 넣어 조건을 추가 할 수 있습니다.
AND : 조건이 둘 모두 성립하는 경우
OR : 조건 중 하나만 성립하는 경우

 

INSERT 문
테이블에 데이터를 입력할 때 사용하는 문장입니다.

문장 구문

INSERT INTO [테이블 명([열 제목 ①], [열 제목 ②], …) VALUES ([값 ①], [값 ②]);

열 제목을 입력하지 않으면 SELECT 문을 통해 필터링 되는 열 순서대로 VALUES 괄호 안에 값을 입력하면 됩니다. 위 구문을 쓸 경우 한번에 한 개의 항목을 내 보낼 수 있습니다.

INSERT INTO [테이블 명([열 제목 ①], [열 제목 ②], …) SELECT 문
위 문장을 쓸 경우 여러개의 데이터가 입력 될 수 있습니다. 하지만 테이블에 기본 키와 유일 키 (Unique Key) 가 겹칠 경우 오류가 뜨면서 데이터 전체가 들어가지 않습니다.

SELECT 문은 당연히 입력되는 열 순서에 맞게 데이터 형식을 맞추어야 합니다.

 

UPDATE 문
테이블에 존재하는 기존 데이터들을 수정 할 때 사용하는 문장입니다.

문장 구문

Update [테이블 명] : 데이터를 변경할 테이블의 이름을 지정합니다.
SET [열 제목 ①] = [변경 값 ①], [열 제목 ②] = [변경 값 ②] : 조건 만족 시 변경할 열제목과 변경될 값을 입력합니다.
WHERE [조건] ; : 변경할 때의 조건을 입력합니다.  

 

MERGE 문
조건을 비교해서 테이블에 해당 조건이 있으면 UPDATE 문, 없으면 INSERT 문을 수행하는 구문입니다.

문장 구문

MERGE INTO [테이블 명]

USING [비교할 데이터 객체]

ON [비교할 조건]

WHEN MATCHED THEN
조건이 맞을 경우 수행할 문장.

WHEN NOT MATCHED THEN
조건이 맞지 않는 경우 수행할 문장.

 

DELETE 문
테이블에 존재하는 데이터를 삭제할 때 사용하는 문장입니다.

문장 구문

DELETE [테이블 명]
WHERE DELETE[조건];
조건을 입력하지 않을 경우 테이블에 존재하는 모든 데이터를 삭제합니다.

그럼 이제 앱을 직접 개발해볼까요? 이번 시간에 개발해 볼 앱은 주소록 데이터베이스와 연동되는 앱으로, 실질적으로 활용이 가능한 앱입니다.

 

[ 데이터베이스 생성하기 ]

1. Microsoft Access 를 실행하고 새로운 데이터베이스 파일을 생성합니다.

 

 

2. 데이터베이스 파일 명을 AddressBook.accdb 로 지정하고 [만들기] 버튼을 클릭합니다.

 

 

3. 테이블에 필드 (카테고리) 를 추가합니다. 클릭하여 필드를 추가하신 후 “Short Text” 속성을 선택합니다.

 

 

4. 추가된 필드에 명칭을 부여합니다. “성명” 을 입력하신 후 [Enter] 키를 누르세요.

 

 

5. 같은 방법으로 “연락처” 필드 (카테고리) 도 추가합니다. 속성은 동일하게 “Short Text” 로 설정합니다.

 

6. 새로운 필드 (카테고리) 를 추가합니다. 속성은 “Long Text” 로 설정해줍니다.

 

 

7. 추가한 필드에 “Email” 명칭을 부여합니다.

 

 

8. 같은 방법으로 “주소” 필드 (카테고리) 도 추가합니다. 속성은 “Email” 필드와 동일하게 “Long Text” 로 설정합니다.

 

9. 해당 테이블을 “People” 이라는 명칭으로 저장합니다. [Ctrl] + [S] 를 눌러 명칭 변경 후 [확인] 버튼을 눌러 저장하실 수 있습니다.

 

 

10. 데이터베이스 파일을 닫고 마무리합니다.

 

[Form 디자인 하기]

아래와 같이 Form 을 디자인합니다.

 

 

[소스 코드 코딩하기]

Imports System.Data.OleDb

Public Class FrmPeople
Dim cnn As New OleDb.OleDbConnection

'--- DataGridView 의 데이터 항목을 새로 고침 (업데이트)
Private Sub RefreshData()

Dim cmd As New OleDb.OleDbCommand

If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If

Dim da As New OleDb.OleDbDataAdapter("SELECT ID, " &
"성명, 연락처, Email, 주소 " &
" FROM People ORDER BY 성명", cnn)
Dim dt As New DataTable

da.Fill(dt)

PeopleGridView.DataSource = dt

cnn.Close()
End Sub

'--- 입력된 데이터를 추가하는 추가 버튼 클릭 이벤트
Private Sub AddButton_Click(sender As Object, e As EventArgs) Handles AddButton.Click

Dim cmd As New OleDb.OleDbCommand
Dim PhoneNumber As String
Dim EmailAddress As String

If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If

cmd.Connection = cnn

PhoneNumber = PhoneNumberComboBox1.Text & "-" & PhoneNumberTextBox2.Text & "-" & PhoneNumberTextBox3.Text
EmailAddress = EmailTextBox1.Text & "@" & EmailComboBox2.Text

If Not CBool(IsNumeric(PhoneNumberTextBox2.Text)) Or Not CBool(IsNumeric(PhoneNumberTextBox3.Text)) Then
MessageBox.Show("연락처에는 숫자만 입력하실 수 있습니다.", "주소록", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If

If Not PhoneNumberTextBox2.Text.Length = 4 Or Not PhoneNumberTextBox2.Text.Length = 4 Then
MessageBox.Show("연락처 입력 형식이 잘못되었습니다.", "주소록", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If

If NameTextBox.Text = String.Empty Or PhoneNumberTextBox2.Text = String.Empty Or PhoneNumberTextBox3.Text = String.Empty Or EmailTextBox1.Text = String.Empty Or AddressTextBox.Text = String.Empty Then
MessageBox.Show("필수 입력 항목을 모두 입력하셔야 등록이 가능합니다.", "주소록", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If

If NameTextBox.Tag & "" = "" Then
cmd.CommandText = "INSERT INTO People(성명, 연락처, Email, 주소) " &
"VALUES('" & NameTextBox.Text & "','" & PhoneNumber & "','" & EmailAddress & "','" & AddressTextBox.Text & "')"
cmd.ExecuteNonQuery()
EditButton.Enabled = True
Else
cmd.CommandText = "UPDATE People " &
" SET 성명='" & NameTextBox.Text & "'" &
", 연락처='" & PhoneNumber.ToString & "'" &
", Email='" & EmailAddress.ToString & "'" &
", 주소='" & AddressTextBox.Text & "'" &
" WHERE ID=" & NameTextBox.Tag
EditButton.Enabled = True
cmd.ExecuteNonQuery()
End If

RefreshData()
InitButton.PerformClick()

cnn.Close()
End Sub

'--- FrmPeople 로드 이벤트
Private Sub FrmPeople_Load(sender As Object, e As EventArgs) Handles MyBase.Load
cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\AddressBook.accdb"

PhoneNumberComboBox1.SelectedIndex = 0
EmailComboBox2.SelectedIndex = 0

Me.RefreshData()
InitButton.PerformClick()
End Sub

'--- 데이터 항목 편집 / 수정 버튼 클릭 이벤트
Private Sub EditButton_Click(sender As Object, e As EventArgs) Handles EditButton.Click
Dim PhoneNumber As String
Dim EmailID As String
Dim EmailDomain As String

If PeopleGridView.Rows.Count > 0 Then
If PeopleGridView.SelectedRows.Count > 0 Then
Dim Index As Integer = PeopleGridView.SelectedRows(0).Cells("ID").Value
If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If

Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM People " & " WHERE ID=" & Index, cnn)
Dim dt As New DataTable
da.Fill(dt)

PhoneNumber = dt.Rows(0).Item("연락처")
EmailID = dt.Rows(0).Item("Email")
EmailID = EmailID.Split("@")(0)

EmailDomain = dt.Rows(0).Item("Email")
EmailDomain = EmailDomain.Split("@")(1)

IDTextBox.Text = Index
NameTextBox.Text = dt.Rows(0).Item("성명")
PhoneNumberComboBox1.SelectedItem = PhoneNumber.Substring(0, 3)
PhoneNumberTextBox2.Text = PhoneNumber.Substring(4, 4)
PhoneNumberTextBox3.Text = PhoneNumber.Substring(9, 4)
EmailTextBox1.Text = EmailID
EmailComboBox2.Text = EmailDomain
AddressTextBox.Text = dt.Rows(0).Item("주소")

NameTextBox.Tag = Index.ToString
AddButton.Text = "수정 완료"
EditButton.Enabled = False
cnn.Close()
End If
End If
End Sub

'--- 선택된 데이터를 삭제하는 삭제 버튼 클릭 이벤트
Private Sub RemoveButton_Click(sender As Object, e As EventArgs) Handles RemoveButton.Click
If PeopleGridView.Rows.Count > 0 Then
If PeopleGridView.SelectedRows.Count > 0 Then
Dim ID As Integer = PeopleGridView.SelectedRows(0).Cells("ID").Value
Dim results As Integer = MessageBox.Show("선택하신 데이터를 삭제하시면 복구하실 수 없습니다." & vbCrLf & "정말 삭제하시겠습니까?", "주소록 데이터 삭제 확인", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation)

If results = DialogResult.No Then
Exit Sub
Else
If Not cnn.State = ConnectionState.Open Then
Dim cmd As New OleDb.OleDbCommand
cnn.Open()
cmd.Connection = cnn
cmd.CommandText = "DELETE FROM People WHERE ID=" & ID

cmd.ExecuteNonQuery()
InitButton.PerformClick()
Me.RefreshData()
cnn.Close()
End If
End If
End If
End If
End Sub

'--- 입력된 내용을 초기화 하는 초기화 버튼 클릭 이벤트
Private Sub InitButton_Click(sender As Object, e As EventArgs) Handles InitButton.Click
IDTextBox.Text = String.Empty
NameTextBox.Tag = String.Empty
NameTextBox.Text = String.Empty
PhoneNumberComboBox1.SelectedIndex = 0
PhoneNumberTextBox2.Text = String.Empty
PhoneNumberTextBox3.Text = String.Empty
EmailTextBox1.Text = String.Empty
EmailComboBox2.SelectedIndex = 0
AddressTextBox.Text = String.Empty
EditButton.Enabled = True

AddButton.Text = "추가"
Me.RefreshData()

NameTextBox.Select()

'--- 종료 버튼 클릭 이벤트
Private Sub ExitButton_Click(sender As Object, e As EventArgs) Handles ExitButton.Click
Application.Exit()
End Sub

'--- 데이터베이스의 내용을 통합 검색하는 검색 버튼 클릭 이벤트
Private Sub SearchButton_Click(sender As Object, e As EventArgs) Handles SearchButton.Click
Dim keyword As String = SearchTextBox.Text
Dim cmd As OleDbCommand = New OleDbCommand("SELECT ID, 성명, 연락처, Email, 주소 FROM People WHERE 성명 Like '%" & keyword & "%' or 연락처 Like '%" & keyword & "%' or Email Like '%" & keyword & "%' or 주소 Like '%" & keyword & "%' ", cnn)

If SearchTextBox.Text = String.Empty Then
Me.RefreshData()
End If

cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\AddressBook.accdb"
cnn.Open()

Dim MyDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim PeopleDataSet As DataSet = New DataSet()

MyDA.Fill(PeopleDataSet, "People")
PeopleGridView.DataSource = PeopleDataSet.Tables("People").DefaultView

cnn.Close()
SearchTextBox.Text = String.Empty
End Sub

'--- 키보드 입력에 따른 자동 커서 이동 이벤트
Private Sub PhoneNumberComboBox1_KeyDown(sender As Object, e As KeyEventArgs) Handles PhoneNumberComboBox1.KeyDown
If e.KeyData = Keys.Enter Then PhoneNumberTextBox2.Select()
End Sub

Private Sub PhoneNumberTextBox2_KeyDown(sender As Object, e As KeyEventArgs) Handles PhoneNumberTextBox2.KeyDown
If e.KeyData = Keys.Enter Then PhoneNumberTextBox3.Select()
End Sub

Private Sub PhoneNumberTextBox3_KeyDown(sender As Object, e As KeyEventArgs) Handles PhoneNumberTextBox3.KeyDown
If e.KeyData = Keys.Enter Then EmailTextBox1.Select()
End Sub

Private Sub EmailTextBox1_KeyDown(sender As Object, e As KeyEventArgs) Handles EmailTextBox1.KeyDown
If e.KeyData = Keys.Enter Then EmailComboBox2.Select()
End Sub

Private Sub AddressTextBox_KeyDown(sender As Object, e As KeyEventArgs) Handles AddressTextBox.KeyDown
If e.KeyData = Keys.Enter Then AddButton.PerformClick()
End Sub

Private Sub SearchTextBox_KeyDown(sender As Object, e As KeyEventArgs) Handles SearchTextBox.KeyDown
If e.KeyData = Keys.Enter Then SearchButton.PerformClick()
End Sub
End Class

 

샘플 소스 코드 다운로드