Sabtu, 28 April 2012

Database Coding SQLClient di VB.NET

Membuat aplikasi database menggunakan SQLServer dapat dilakukan memakai Driver OLEDB (System.Data.OleDb) maupun SQLServer Native (System.Data.SqlClient). Jika menggunakan OleDb cara pemrogramannya mirip dengan OleDb saat membuat aplikasi database menggunakan Access, hanya saja ConnectionString-nya diganti sebagai berikut :

cn = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Persist Security Info=True;Initial Catalog=world;Integrated Security=True")

atau

cn = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Persist Security Info=True;User ID=sa;Initial Catalog=world;Password=")


Apabila menggunakan SQLClient, berikut program selengkapnya :

Imports System.Data.SqlClient

Public Class FSQLServer
    Public cn As SqlConnection
    Public ds As DataSet
    Public tb As DataTable
    Public WithEvents bs As BindingSource

    Private Sub FSQLServer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim i As Integer

        'cn = New SqlConnection("Data Source=localhost;Initial Catalog=world;Integrated Security=True")
        cn = New SqlConnection("Data Source=localhost;Initial Catalog=world;Persist Security Info=True;User ID=sa;Password=")

        cn.Open()

        Dim cm As New SqlCommand("SELECT name, address, phone FROM contacts", cn)
        Dim rd As SqlDataReader

        rd = cm.ExecuteReader()

        ds = New DataSet
        tb = New DataTable
        tb.TableName = "contacts"
        ds.Tables.Add(tb)

        ds.Load(rd, LoadOption.Upsert, tb)

        For i = 0 To (tb.Columns.Count - 1)
            tb.Columns(i).MaxLength = -1
        Next

        rd.Close()
        cn.Close()

        bs = New BindingSource()

        bs.DataSource = ds

        bs.DataMember = "contacts"

        gContacts.DataSource = bs
    End Sub

    Private Sub bs_CurrentChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bs.CurrentChanged
        If ((bs.Count > 0) And (bs.DataMember = "contacts")) Then
            tName.Text = bs.Item(bs.Position)("name").ToString
            tAddress.Text = bs.Item(bs.Position)("address").ToString
            tPhone.Text = bs.Item(bs.Position)("phone").ToString
        End If
    End Sub

    Private Sub bFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bFirst.Click
        bs.Position = 0
    End Sub

    Private Sub bPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bPrev.Click
        bs.Position = bs.Position - 1
    End Sub

    Private Sub bNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bNext.Click
        bs.Position = bs.Position + 1
    End Sub

    Private Sub bLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bLast.Click
        bs.Position = bs.Count - 1
    End Sub

    Private Sub bAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bAdd.Click
        bs.AddNew()
    End Sub

    Private Sub bEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bEdit.Click
        '
    End Sub

    Private Sub bSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bSave.Click
        Dim cm As New SqlCommand

        cn.Open()

        If bs.Item(bs.Position)("name").ToString() = "" Then
            cm.Dispose()

            cm = New SqlCommand("INSERT INTO contacts (name, address, phone) VALUES (@name, @address, @phone)", cn)
        Else
            cm.Dispose()

            cm = New SqlCommand("UPDATE contacts SET name = @name, address = @address, phone = @phone WHERE name = @Original_name", cn)
        End If

        cm.Parameters.Add("@name", SqlDbType.VarChar, 0, "").Value = tName.Text
        cm.Parameters.Add("@address", SqlDbType.VarChar, 0, "").Value = tAddress.Text
        cm.Parameters.Add("@phone", SqlDbType.VarChar, 0, "").Value = tPhone.Text

        If bs.Item(bs.Position)("name").ToString() <> "" Then
            cm.Parameters.Add("@Original_name", SqlDbType.VarChar, 0, "").Value = bs.Item(bs.Position)("name").ToString
        End If

        cm.ExecuteNonQuery()
        cn.Close()

        bs.Item(bs.Position)("name") = tName.Text
        bs.Item(bs.Position)("address") = tAddress.Text
        bs.Item(bs.Position)("phone") = tPhone.Text

        bs.EndEdit()
    End Sub

    Private Sub bCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bCancel.Click
        bs.CancelEdit()
    End Sub

    Private Sub bDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bDelete.Click
        cn.Open()
        Dim cm As New SqlCommand("DELETE FROM contacts WHERE name = @Original_name", cn)
        cm.Parameters.Add("@Original_name", SqlDbType.VarChar, 0, "").Value = bs.Item(bs.Position)("name").ToString
        cm.ExecuteNonQuery()
        cn.Close()

        bs.RemoveAt(bs.Position)
    End Sub

    Private Sub bRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bRefresh.Click
        Dim i As Integer

        gContacts.DataSource = Nothing
        bs.DataSource = Nothing
        ds.Dispose()
        tb.Dispose()

        cn.Open()

        Dim cm As New SqlCommand("SELECT name, address, phone FROM contacts WHERE name LIKE '%" & tFind.Text.Trim & "%'", cn)
        Dim rd As SqlDataReader

        rd = cm.ExecuteReader()

        ds = New DataSet
        tb = New DataTable
        tb.TableName = "contacts"
        ds.Tables.Add(tb)

        ds.Load(rd, LoadOption.Upsert, tb)

        For i = 0 To (tb.Columns.Count - 1)
            tb.Columns(i).MaxLength = -1
        Next

        rd.Close()
        cn.Close()

        bs = New BindingSource()

        bs.DataSource = ds

        bs.DataMember = "contacts"

        gContacts.DataSource = bs
    End Sub
End Class

Jika SQLServer yang digunakan adalah versi SQLExpress, maka databasenya berupa sebuah file *.MDF. ConnectionString-nya harus diubah menjadi seperti di bawah ini :

cn = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=" & Path.GetDirectoryName(Application.ExecutablePath) & "\world.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")