[VB.NET] MS-SQL 연동 - 1. 서버에 연결

VB.NET에서는 당연히 MS-SQL을 연동해서 사용할 수 있다. MS-SQL 사용법은 번외로 다시 해보기로 하고, 이번 강좌에서는 VB.NET에서 MS-SQL을 연동하기 위해 어떻게 하면 되는지 알아보자.

편의를 위해 MS-SQL 서버는 아래 정보대로 이미 구축이 되어 있다고 가정하자.

Server IP	DB Name	User ID	User PW
123.456.789.012	TSQLDB	TUserID	TUserPW

이제 VB.NET에서 DB를 사용해보자.

1. 참조 추가
솔루션 탐색기 > 참조에서 마우스 오른쪽 버튼 > 참조 추가... > COM > Microsoft ActiveX Data Objects 6.1 Library 추가

참조관리자

2. 변수 선언

Public DBConnection As ADODB.Connection
Public DBRecordSet As ADODB.Recordset

MS-SQL을 사용할때마다 [연결 > 사용 > 끊기] 과정을 반복할 수도 있지만, 이건 많이 비효율적이라 보통 DB를 사용할때는 [시작시 연결 > 사용 > 종료시 끊기] 의 방법을 사용하게 된다. 그래서, 일반적으로 DB관련 변수는 최소한 클래스 단위변수, 혹은 프로젝트 전체 전역변수로 선언을 하게 된다.

여기서는 프로젝트에 모듈을 하나 추가(Module1.vb)해서 프로젝트 단위 전역변수로 사용해보자.

3. MS-SQL에 연결해서 변수를 메모리에 할당

Public Function ConnectDB(conn_str As String) As Boolean

    Try

        DBConnection = New ADODB.Connection
        DBRecordSet = New ADODB.Recordset

        DBConnection.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        DBConnection.Open(conn_str)

        DBRecordSet.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        DBRecordSet.CursorType = ADODB.CursorTypeEnum.adOpenKeyset
        DBRecordSet.LockType = ADODB.LockTypeEnum.adLockOptimistic

        Return True

    Catch ex As Exception
        Debug.Print("DB접속 오류 - " & ex.Message)
        Return False
    End Try

End Function

DBConnection = New ADODB.Connection
DBRecordSet = New ADODB.Recordset

DBConnection과 DBRecordSet 변수를 메모리에 할당한다.

DBConnection.CursorLocation = ADODB.CursorLocationEnum.adUseClient

커서위치를 설정한다. 우선 커서라는게 뭘까? DB에서의 커서는 현재 가리키고 있는 레코드의 위치를 뜻한다. 메모장같은 텍스트에디터에서 깜빡이는 커서와 일맥상통하다고 봐도 무방하다.
그렇다면 adUseClient는 무슨의미일까? 이건 커서를 클라이언트에 두겠다는 의미다. 반대 개념으로 adUseServer도 있다. 이건 커서를 서버에 두겠다는 의미겠지. 그렇다면, 커서를 서버에 두는것과 커서를 클라이언트에 두는것의 차이는 뭘까?
커서를 서버에 두면 MoveNext 등으로 커서를 움직여서 다른 레코드로 이동할때마다 서버에서 데이터를 가져오게 된다. 항상 최신 데이터를 유지한다는 장점이 있는 반면, 속도가 엄청 떨어진다는 단점이 있다.
커서를 클라이언트에 두면 모든 데이터를 클라이언트의 레코드셋으로 가져오게 되니, 처음 호출시에만 시간이 소요되고 그후에는 커서를 움직여도 속도가 거의 떨어지지 않는다는 장점이 있는 반면, 최신 데이터가 아닐수도 있다는 단점이 있다.
서로 상대방의 단점을 장점으로 갖고 있으니 다루는 데이터의 특성에 따라서 적당한 방식을 사용하면 되겠다. 데이터가 얼마 없지만 자주 변경된다면 서버로, 데이터는 많지만 자주 변경되지 않는다면 클라이언트 방식으로 사용하면 된다.
커서를 클라이언트에 두게되면 또하나의 큰 장점이 RecordCount를 구할 수가 있다는 것이다. 원리를 생각해보면 이해가 될텐데 커서를 서버에 두는순간 언제 어떻게 데이터가 변경될지 장담할 수 없기 때문에 전체 레코드 크기를 알수가 없다.
그래서 일반적으로 전체 레코드를 대상으로 루프를 돌때 adUseClient 방식은 For문으로 커서를 처음부터 끝까지 이동하며 돌리고, adUseServer 방식은 Do Loop 문으로 처음부터 하나씩 MoveNext 하다가 끝을 만났을때 EOF 확인후 루프를 종료하게 된다.

DBConnection.Open(conn_str)

파라미터로 지정된 문자열을 이용해 DB에 연결한다. DB연결 문자열은 아래 형식에 맞춰서 사용해야 한다.

Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=서버아이피;Initial Catalog=DB명;User ID=사용자ID;Password=사용자비번;

이 문자열을 정확히 채워야 하며, 저위의 표에 있는 속성으로 문자열을 생성하면 아래와 같다.

Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=123.456.789.012;Initial Catalog=TSQLDB;User ID=TUserID;Password=TUserPW;

연결 함수를 호출하는 쪽에서 위 형식으로 문자열을 생성후 파라미터로 넘겨주면 된다.

DBRecordSet.CursorLocation = ADODB.CursorLocationEnum.adUseClient

커서위치는 위 DBConnection과 동일하게 맞춰주면 된다.

DBRecordSet.CursorType = ADODB.CursorTypeEnum.adOpenKeyset

커서의 형식을 설정한다.

adOpenForwardOnly(0) - 커서가 앞으로만 갈 수 있음
adOpenKeyset(1) - 커서를 자유자재로 움직일 수는 있지만 변경된 내용에는 접근할 수 없다. 보통 adUseClient 방식의 커서와 함께 사용됨
adOpenDynamic(2) - 커서를 자유자재로 움직이며 변경된 내용에도 접근이 가능하다. 보통 adUseServer 방식의 커서와 함께 사용됨
adOpenStatic(3) - 커서를 앞뒤로만 이동할 수 있음

DBRecordSet.LockType = ADODB.LockTypeEnum.adLockOptimistic

레코드셋을 잠그는 형식을 지정한다.

adLockReadOnly(1) - 읽기만 가능
adLockPessimistic(2) - 수정이 가능하고, 사용중에는 다른 사용자나 프로그램이 변경할 수 없도록 레코드셋을 잠궈놓는다.
adLockOptimistic(3) - 수정 가능하고, 수정하는 순간에만 레코드셋을 잠근다.
adLockBatchOptimistic(4) - 여러개의 레코드를 업데이트할때만 잠근다.

자세히 설명하다보니 내용이 좀 길어지긴 했지만, 각 속성별로 하나 선택해서 사용하면 그다음부터는 거의 복사해서 사용하게 된다.

4. 접속 종료

Public Sub DisconnectDB()

    Try

        If DBConnection IsNot Nothing Then DBConnection.Close()

        DBRecordSet = Nothing
        DBConnection = Nothing

    Catch ex As Exception
        Debug.Print("DB접속종료 오류 - " & ex.Message)
    End Try

End Sub

If DBConnection IsNot Nothing Then DBConnection.Close()

DB에 접속중인 상태일때만 Close를 호출해서 접속을 끊는다. IsNot 이라는 재밌는 연산자가 사용됐는데 VB.NET에서 이런식으로 크게 필요는 없지만 있으면 좋은 정도의 기능들이 이렇게 눈에 띌때마다 기분이가 조으다.

이렇게 이번 강좌에서는 MS-SQL 서버에 연결하고 연결을 끊는 기능에 대해 알아봤다. 다음 강좌에서는 직접 쿼리문을 날려서 서버에서 데이터를 가져오고, 수정하는등의 SQL 작업을 해보자.