VB.Net에서 데이터베이스 사용하기 2

VB.Net에서 데이터베이스 사용하기 1에 이어 비베 닷넷(VB.Net)에서 다양한 데이터베이스에 접속해서 데이터 검색 및 저장, 갱신, 삭제등을 수행하기 위한 기본적인 ADO.NET 클래스들을 다루고자 합니다.

특정한 데이터베이스에 접속하여 동작하는 응용 프로그램을 본격적으로 개발하기에 앞서 중요한 검토사항은 해당 데이터베이스를 어떤 방식으로 접속할 것인가 하는 것입니다. DBMS 별로 제공하는 드라이버가 다를 수 있기 때문에 네이티브 ADO.NET 드라이버를 제공하는지 OLE DB 접속 방식을 사용하는지, 아니면 ODBC 방식을 사용해야 하는지를 먼저 확인하고 해당하는 드라이버의 클래스를 사용하여 코딩을 진행합니다.

그런데, ADO.NET에서는 드라이버의 종류와 관계없이 유사한 클래스를 제공하기 때문에 핵심 구조에 대해서 인지하고 있다면 드라이버 종류가 바뀌더라도 클래스 이름만 변경하면 서로 다른 드라이버에 대해서도 간편하게 대응할 수 있습니다.

VB.Net에서 데이터베이스 사용하기 1에서 언급한 것처럼 드라이버 종류별로 클래스 앞에 붙는 접두어만 바꾸어 주면 드라이버를 변경하더라도 손쉽게 새로운 드라이버를 적용할 수 있습니다. 지난번 포스팅에서 다룬 데이터어댑터와 데이터셋이 마치 엑셀 시트를 조작하듯 작업을 수행했다면 이번 포스팅에서 다룰 Command 오브젝트와 데이터리더 오브젝트는 보다 상세하고 복잡한 작업을 코드 차원에서 수행할수 있도록 지원 합니다.

예제는 특정 문자열을 입력하고 [검색]을 누르면 Command 오브젝트를 통해서 데이터베이스 질의를 수행하고 데이터리더 오브젝트를 활용하여 질의 결과를 받습니다. 한행씩 데이터리더를 통해서 읽어서 데이터 그리드에 출력합니다. 사용자가 특정 셀을 편집하면 해당 데이터를 데이터베이스에 업데이트 하는 과정입니다.

Imports System.Data.SqlClient

Public Class Form1
    Dim constr As String = "Server=localhost\MYTEST;Database=TESTDB;User Id=sa;Password=mytest;"
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        If findtext.Text.Trim().Length <= 0 Then
            MessageBox.Show("검색어를 입력하세요!")
            Return
        End If

        DataGridView1.Rows.Clear()
        Try
            Dim conn As SqlConnection = New SqlConnection(constr)
            conn.Open()
            Dim sqlstr As String = "FROM PRODUCTS WHERE NAME LIKE '%" + findtext.Text.Trim() + "%'"
            Dim cntcmd As SqlCommand = New SqlCommand("SELECT COUNT(*) " + sqlstr, conn)
            If Integer.Parse(cntcmd.ExecuteScalar()) <= 0 Then
                MessageBox.Show("검색어에 해당하는 자료가 없습니다!")
                Return
            End If
            Dim cmd As SqlCommand = New SqlCommand("SELECT ID, NAME " + sqlstr, conn)
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            Dim ri As Integer = 0
            While reader.Read()
                Dim rowary(2) As Object
                ri = ri + 1
                rowary(0) = reader.GetString(0)
                rowary(1) = reader.GetString(1)
                DataGridView1.Rows.Add(rowary)
                DataGridView1.Rows(ri - 1).HeaderCell.Value = ri.ToString()
            End While
            reader.Close()
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub DataGridView1_CellEndEdit(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        Dim newrow As DataGridViewRow = DataGridView1.Rows(e.RowIndex)
        Dim col_id As String = newrow.Cells(0).Value
        Dim col_name As String = newrow.Cells(1).Value
        If col_id = Nothing Or col_name = Nothing Then Return
        If col_id.Trim().Length > 0 And col_name.Trim().Length > 0 Then
            Try
                Dim conn As SqlConnection = New SqlConnection(constr)
                conn.Open()
                Dim cmd As SqlCommand = New SqlCommand("UPDATE PRODUCTS SET ID = '" + col_id + "', NAME = '" + col_name + "' WHERE ID = '" + col_id + "'", conn)
                If cmd.ExecuteNonQuery() < 1 Then
                    MsgBox("해당하는 행을 찾을 수 없습니다")
                End If
                conn.Close()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub
End Class

위의 코드는 SQL Server 기준이므로 다른 DBMS에 적용할 경우에는 클래스 이름만 적절하게 변경해주면 됩니다. 코드 예제처럼 Connection 오브젝트는 연결 스트링을 생성자의 인수로 받고 Open()으로 접속하고 Close()로 접속을 끊을 수 있습니다. 데이터베이스 작업이 많다면 Connection 오브젝트를 사용 시점마다 생성치 작업 전 과정을 하나의 Connection 오브젝트로 수행해서 작업을 효율성을 높일 수 있습니다.

Command 오브젝트의 경우 위의 예제에서는 SQL과 Connection 오브젝트를 인수로 오브젝트를 생성했지만 Connection 오브젝트와 SQL 모두 Connection, CommandText 속성에 의하여 변경할 수 있고 반복적으로 사용할 수 있습니다. Command 오브젝트를 실제로 수행시키는 메소드는 ExecuteNonQuery, ExecuteReader, ExecuteScalar가 있으며 INSERT, DELETE, UPDATE, SET과 같은 데이터 관리 SQL은 ExecuteNonQuery()를 통해서 수행시킵니다. ExecuteNonQuery()는 SQL 수행이후 영향을 받은(삽입, 갱신, 삭제등) 행의 개수를 리턴합니다. 위의 예제에서는 데이터그리드의 특정 셀을 편집했을때(DataGridView1.CellEndEdit) 해당 행의 유효성 검사이후 UPDATE문 수행과 수행 결과 확인에 사용했습니다.

질의문(SELECT)에 대해서는 ExecuteReader(), ExecuteScalar()를 사용할 수 있습니다. 단 한개의 행에 하나의 컬럼 값만을 가지는 질의하는 경우에는 ExecuteScalar()를 사용할 수 있으며 개수(COUNT)나 합계(SUM)등을 간편하게 추출하고 싶을 때 사용합니다. 위의 예제에서도 검색 결과의 개수를 먼저 확인할 때 사용했습니다. ExecuteReader()는 SELECT문을 수행한 결과를 가져오기는 하지만 실제 데이터를 가져오기 보다는 자료를 받기 위한 데이터리더를 리턴하여 데이터리더를 통해서 작업하도록 하고 있습니다. 데이터리더는 Read() 메소드 수행이후에 reader.Get... (1) 메소드를 활용하여 컬럼인덱스를 가지고 질의 결과를 데이터타입별로 얻을 수 있습니다.

위의 화면은 예제 코드를 실행한 결과화면입니다.