Create Koneksi Antara VB.NET dengan Database SQL Server

untuk bisa bekerja dengan database dari program vb.net, langkah pertama yang harus dilakukan adalah dengan membentuk koneksi antara program dengan database servernya. pada artikel ini, database yang digunakan adalah SQL Server 2008 dengan nama database yang akan digunakan adalah OMS dan untuk user SQL Servernya yang akan digunakan adalah sa dengan sandi misalkan juga sa.
karena database yang digunakan adalah SQL Server, namespace yang akan kita manfaatkan adalah namespace System.Data.SqlClient. object class yang bertanggung jawab untuk membentuk koneksi dengan database adalah object bertipe SqlConnection.
object SqlConnection sendiri dalam membentuk koneksi ke database SQL Server memerlukan parameter yang disebut sebagai ConnectionString. ConnectionString ini sendiri adalah sebuah string yang berisi parameter-parameter yang dibutuhkan untuk membentuk koneksi itu sendiri. adapun parameter-parameter tersebut adalah:
Data Source atau Server –> menyatakan nama komputer / ip address komputer di mana database SQL Servernya berada. jika database berada satu mesin dengan aplikasi, anda bisa menggunakan localhost\<sql server instance name> atau (local)\<sql server instance name>  atau .\<sql server instance name>
untuk mengecek sql server instance name, bisa dilakukan via menu sql server configuration manager seperti yang ditunjukkan pada gambar berikut.

pada laptop saya, sql server instance name yang digunakan adalah SQL2008
Initial Catalog atau Database –> menyatakan nama database yang akan diambil / digunakan dari mesin database SQL Server yang sudah berhasil dicontact. pada artikel ini database yang akan digunakan bernama OMS.

Integrated Security = True | SSPI atau UID = <UserID>; PWD = <Password> –> digunakan untuk memberitahukan cara program akan melakukan koneksi ke database SQL Server, dalam hal ini ada 2 teknik yang bisa digunakan, yaitu dengan menggunakan Windows Authentication yakni dengan menggunakan account windows (via Integrated Security = True | SSPI), atau dengan menggunakan SQL Server Authentication yakni dengan menggunakan account sql server yang sudah disediakan (via UID = <UserID>; PWD = <Password>).
ok, berikut ini detail bagaimana kode proses pembentukan koneksi ke database sql server via vb.net
misalkan rancangan programnya seperti berikut ini:

untuk proses pembentukan koneksi, sedikitnya ada dua pendekatan yang bisa digunakan, yaitu:
Early Open – Late Closed
contoh code ditunjukkan pada sample code berikut ini:
‘ import namespace yang dibutuhkan
Imports System.Data.SqlClient
Public Class Form1
    ‘ buat object bertipe SqlConnection
    Dim conn As SqlConnection = Nothing
    ‘ bentuk string ConnectionString
    ‘ jika menggunakan windows auth.
    Dim connString As String = _
              "Data Source = .\sql2008; Initial Catalog = OMS; Integrated Security = True"
    ‘ jika menggunakan sql server auth.
    ‘Dim connString2 As String = _
    ‘          "Data Source = .\sql2008; Initial Catalog = OMS; UID = sa; PWD = sa"
    ‘ Early open via constructor     Public Sub New()
        InitializeComponent()
        ‘ create object SqlConnection dengan melewatkan parameter 
        ‘ yang dibutuhkan via ConnectionString
        ‘ jika menggunakan windows auth.
        conn = New SqlConnection(connString)
        ‘ atau
        ‘ jika menggunakan sql server auth.
        ‘conn = New SqlConnection(connString2)
        ‘ open koneksinya
        conn.Open()
        ‘ tampilkan state dari koneksi
        ‘ jika berhasil dibuka, maka akan tercetak Status Koneksi: Open
        Me.lblStatus.Text = String.Format("Status Koneksi: {0}", conn.State.ToString())
    End Sub
    ‘ late closed via event FormClosed     Private Sub Form1_FormClosed(ByVal sender As Object, _
            ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        ‘ menutup koneksi jika ada yang sedang terbuka
        If conn IsNot Nothing Then conn.Close() : conn.Dispose()
    End Sub
End Class

Open When Needed – Closed As Soon As Possible
contoh code ditunjukkan pada sample code berikut ini:
‘ import namespace yang dibutuhkan
Imports System.Data.SqlClient
Public Class Form1
    ‘ buat object bertipe SqlConnection
    Dim conn As SqlConnection = Nothing
    ‘ bentuk string ConnectionString
    ‘ jika menggunakan windows auth.
    Dim connString As String = _
           "Data Source = .\sql2008; Initial Catalog = OMS; Integrated Security = True"
    ‘ jika menggunakan sql server auth.
    ‘Dim connString2 As String = _
    ‘        "Data Source = .\sql2008; Initial Catalog = OMS; UID = sa; PWD = sa"
    ‘ open when needed – close ASAP (As Soon As Possible)
    Private Sub btnOpenThenClosed_Click(ByVal sender As System.Object, _
                       ByVal e As System.EventArgs) Handles btnOpenThenClosed.Click
        ‘ create koneksi on demand
        conn = New SqlConnection(connString)
        conn.Open() ‘ open koneksi yang sudah terbentuk
        ‘ cetak status koneksi terkini
        Me.lblStatus.Text = String.Format("Status Koneksi: {0}", conn.State.ToString())
        Application.DoEvents()
        ‘ biarkan sistem idle selama 3 detik
        ‘ anda bisa menggantinya dengan proses-proses yang ingin dikerjakan
        System.Threading.Thread.Sleep(3000)
        ‘ close koneksi
        conn.Close()
        ‘ dispose koneksi
        conn.Dispose()
        ‘ cetak status koneksi terkini
        Me.lblStatus.Text = String.Format("Status Koneksi: {0}", conn.State.ToString())
    End Sub
End Class

Penggunaan Using
dengan menggunakan Using … End Using, programmer tidak perlu dipusingkan lagi dengan proses penutupan dan penghapusan object yang sudah tidak digunakan secara manual, karena hal tersebut akan otomatis akan ditangani oleh Using. syarat object yang bisa dipasang dengan Using … End Using adalah object yang mengimplementasikan interface IDisposable seperti object bertipe SqlConnection yang digunakan dicontoh ini. berikut bentuk perubahan code nya:
‘ import namespace yang dibutuhkan
Imports System.Data.SqlClient
Public Class Form1
    ‘ buat object bertipe SqlConnection
    Dim conn As SqlConnection = Nothing
    ‘ bentuk string ConnectionString
    ‘ jika menggunakan windows auth.
    Dim connString As String = _
            "Data Source = .\sql2008; Initial Catalog = OMS; Integrated Security = True"
   
    ‘ open when needed – close ASAP (As Soon As Possible)
    Private Sub btnOpenThenClosed_Click(ByVal sender As System.Object, _
                     ByVal e As System.EventArgs) Handles btnOpenThenClosed.Click
        ‘ create koneksi on demand
       Using conn = New SqlConnection(connString)             conn.Open() ‘ open koneksi yang sudah terbentuk
            ‘ cetak status koneksi terkini
            Me.lblStatus.Text = String.Format("Status Koneksi: {0}", conn.State.ToString())
            Application.DoEvents()
            ‘ biarkan sistem idle selama 3 detik
            ‘ anda bisa menggantinya dengan proses-proses yang ingin dikerjakan
            System.Threading.Thread.Sleep(3000)
        End Using         ‘ dengan penggunaan Using,
        ‘ programmer tidak perlu lagi menutup koneksi secara manual
        ‘ proses penutupan dan penghapusan object dari memori akan diurus
        ‘ oleh Using
        ‘ cetak status koneksi terkini
        Me.lblStatus.Text = String.Format("Status Koneksi: {0}", _
                            If(conn IsNot Nothing, conn.State.ToString(), "Nothing"))
    End Sub
End Class

Menggunakan MySetting properties untuk menyimpan ConnectionString
cara-cara yang dipakai pada code sebelumnya masih menggunakan penulisan ConnectionString secara hardcode. hal ini suatu saat akan menimbulkan keribetan tersendiri apabila suatu saat terjadi pemindahan server database ke lokasi dengan alamat yang berbeda dengan alamat yang digunakan saat ini. untuk itu kita bisa menyimpan string ConnectionString ke dalam file app.config via settingan properties dibagian Setting pada My Project Properties, seperti yang ditunjukkan pada gambar di bawah ini.

dengan menuliskan connectionstring ke dalam file app.config akan membuat proses perubahan terhadap nilai parameter dalam connectionstring lebih mudah untuk dimodifikasi, karena tidak perlu lagi menggantinya langsung dengan cara mengedit code, cuma perlu mengedit file app.config dari aplikasi yang bersangkutan saja.

perubahan terhadap code hanya terjadi pada proses pemanggilan connectionString saja, yaitu dengan memanggil dari object MySetting, seperti ditunjukkan oleh code di bawah ini:
‘ import namespace System.Data.SqlClient
Imports System.Data.SqlClient
Public Class Form1
    ‘ buat object bertipe SqlConnection
    Dim conn As SqlConnection = Nothing
    ‘ open when needed – close ASAP (As Soon As Possible)
    Private Sub btnOpenThenClosed_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenThenClosed.Click
        ‘ create koneksi on demand
        ‘ dengan menggunakan ConnectionString via MySetting Property
        Using conn = New SqlConnection(My.MySettings.Default.ConnString)              conn.Open() ‘ open koneksi yang sudah terbentuk
            ‘ cetak status koneksi terkini
            Me.lblStatus.Text = String.Format("Status Koneksi: {0}", conn.State.ToString())
            Application.DoEvents()
            ‘ biarkan sistem idle selama 3 detik
            ‘ anda bisa menggantinya dengan proses-proses yang ingin dikerjakan
            System.Threading.Thread.Sleep(3000)
        End Using
        ‘ dengan penggunaan Using,
        ‘ programmer tidak perlu lagi menutup koneksi secara manual
        ‘ proses penutupan dan penghapusan object dari memori akan diurus
        ‘ oleh Using
        ‘ cetak status koneksi terkini
        Me.lblStatus.Text = String.Format("Status Koneksi: {0}", _
                            If(conn IsNot Nothing, conn.State.ToString(), "Nothing"))
    End Sub
End Class

Membentuk string ConnectionString Secara Runtime
dan selain menggunakan cara-cara di atas, kita juga bisa membentuk string ConnectionString secara runtime via object SqlServerConnectionStringBuilder.
untuk sample pada aplikasi ini, kita akan menyesuaikan rancangan aplikasinya menjadi seperti di bawah ini:
 
dan code programnya adalah sebagai berikut:
‘ import namespace yang dibutuhkan
Imports System.Data.SqlClient
Public Class Form1
    ‘ buat object bertipe SqlConnection
    Dim conn As SqlConnection = Nothing
    ‘ buat object SqlConnectionStringBuilder
    Dim connString As SqlConnectionStringBuilder = Nothing
    Public Sub New()
        InitializeComponent()
        Me.cboAuthType.SelectedIndex = 0
    End Sub
    Private Sub cboAuthType_KeyPress(ByVal sender As Object, _
      ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cboAuthType.KeyPress
        e.Handled = True
    End Sub
    Private Sub cboAuthType_SelectedIndexChanged(ByVal sender As System.Object, _
                 ByVal e As System.EventArgs) Handles cboAuthType.SelectedIndexChanged
        If Me.cboAuthType.SelectedIndex = 0 Then
            Me.txtUserID.Enabled = False
            Me.txtPassword.Enabled = False
        Else
            Me.txtUserID.Enabled = True
            Me.txtPassword.Enabled = True
        End If
    End Sub
    Private Sub btnOpen_Click(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) Handles btnOpen.Click
        Try
            ‘ validasi
            If Me.txtServerName.Text.Trim().Equals("") Then _
                Throw New Exception("Isi Server Name Please …")
            If Me.txtDatabaseName.Text.Trim().Equals("") Then _
                Throw New Exception("Isi Database Name Please …")
            ‘ membentuk object ConnectionString secara run time
            ‘ via object SqlConnectionStringBuilder
            connString = New SqlConnectionStringBuilder()
            With connString
                .DataSource = Me.txtServerName.Text.Trim()
                .InitialCatalog = Me.txtDatabaseName.Text.Trim()
                If Me.cboAuthType.SelectedIndex = 0 Then
                    .IntegratedSecurity = True
                Else
                    .UserID = Me.txtUserID.Text.Trim()
                    .Password = Me.txtPassword.Text.Trim()
                End If
            End With
            ‘ membentuk koneksi
            conn = New SqlConnection(connString.ToString())
            conn.Open() ‘ open koneksi
            ‘ cetak status koneksi terkini
            Me.lblStatus.Text = String.Format("Status Koneksi: {0}", conn.State.ToString())
        Catch ex As Exception
            connString = Nothing
            conn = Nothing
            MessageBox.Show(ex.Message, Me.Text, _
                                             MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub
    Private Sub btnClosed_Click(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) Handles btnClosed.Click
        ‘ tutup koneksi
        If conn IsNot Nothing Then conn.Close() : conn.Dispose()
        ‘ cetak status koneksi terkini
        Me.lblStatus.Text = String.Format("Status Koneksi: {0}", _
                            If(conn IsNot Nothing, conn.State.ToString(), "Nothing"))
    End Sub
End Class