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")
