본문 바로가기

개발 Tip/Visual Basic

[VB.Net] PostgreSQL OleDB 연결하기

1. OleDB 연결을 위한 ConnectString
   - "Provider=PostgreSQL OLE DB Provider;Data Source=DB서버 주소;location=DB명;User ID=아이디;password=비밀번호"
 
2. DB 연결 함수
   Private gOleDBConn As OleDb.OleDbConnection
   Public objDA As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
   Public objDS As DataSet = New DataSet()
   Public strCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
 
   Public Function DB_CONNECT() As Boolean
        Dim strConn As String = "Provider=PostgreSQL OLE DB Provider;Data Source=DB서버 주소;location=DB명;User ID=아이디;password=비밀번호"
 
        Try
            If gOleDBConn Is Nothing Then
                gOleDBConn = New OleDb.OleDbConnection(strConn)
                gOleDBConn.Open()
            Else
                If gOleDBConn.State = ConnectionState.Open Then
                ElseIf gOleDBConn.State = ConnectionState.Closed Then
                    gOleDBConn = New OleDb.OleDbConnection(strConn)
 
                    gOleDBConn.Open()
                End If
            End If
        Catch ex As Exception
            Console.WriteLine(ex.Message)
 
            If gOleDBConn.State = ConnectionState.Closed Then
                DB_CONNECT = False
                MsgBox("데이터베이스 접속 실패!", MsgBoxStyle.Critical, "Error")
            Else
                DB_CONNECT = True
            End If
        End Try
 
        DB_CONNECT = True
    End Function
 
3. DB 연결 해제 함수
   Public Function DB_DISCONNECT() As Boolean
        Try
            If gOleDBConn.State = ConnectionState.Open Then
                objDA.Dispose()
                objDS.Dispose()
                strCmd.Dispose()
                gOleDBConn.Close()
            ElseIf gOleDBConn.State = ConnectionState.Closed Then
                MsgBox("데이터 베이스가 이미 연결 해제 상태입니다!", MsgBoxStyle.Critical, "Error")
            End If
        Catch ex As Exception
            Console.WriteLine(ex.Message)
 
            If gOleDBConn.State = ConnectionState.Open Then
                objDA.Dispose()
                objDS.Dispose()
                strCmd.Dispose()
                gOleDBConn.Close()
            End If
        End Try
 
        DB_DISCONNECT = True
    End Function