twitter
    Find out what I'm doing, Follow Me :)

Like Button Facebook

Sabtu, 29 Januari 2011

VB .NET Menggunakan Store Procedure

Buat Store Procedure di SQL Server :
CREATE PROCEDURE SP_tambahbuku(@kode_buku char(5),@judul varchar(50),@jenis varchar(30),@pengarang varchar(30),@penerbit varchar(30))
as
begin
insert into buku(kode_buku,judul,jenis,pengarang,penerbit)
values(@kode_buku,@judul,@jenis,@pengarang,@penerbit)
end

CREATE PROCEDURE SP_deletebuku(@kode_buku char(5))
as
DELETE FROM buku
WHERE kode_buku=@kode_buku
RETURN

CREATE PROCEDURE SP_updatebuku(@kode_buku char(5),@judul varchar(50),@jenis varchar(30),@pengarang varchar(30),@penerbit varchar(30))
AS
BEGIN
UPDATE buku SET kode_buku=@kode_buku,judul=@judul,jenis=@jenis,pengarang=@pengarang,penerbit=@penerbit
WHERE kode_buku=@kode_buku
END


Script ini berada di FormBuku :
Imports System.Data
Imports System.Data.SqlClient

Public Class FromBuku
    Dim koneksi As New SqlConnection("Data Source=GINONG;Initial Catalog=pustaka;Integrated Security=True")
    Private PosisiRecord As BindingManagerBase

#Region "umum"
    Sub FormClear()
        TxtKode.Clear()
        TxtJudul.Clear()
        CbJenis.Text = ""
        TxtPengarang.Clear()
        TxtPenerbit.Clear()
        TxtKode.Focus()
        Me.CbJenis.Text = "- Pilih -"
        Me.CbJenis.Items.Add("Teknologi")
        Me.CbJenis.Items.Add("Pemrograman")
        Me.CbJenis.Items.Add("Sistem Informasi")
        Me.CbJenis.Items.Add("Lainnya")
    End Sub
#End Region

#Region "Akses-Data"
    Sub ViewRecord()
        Dim sql As String = "SELECT * FROM buku"
        Dim da As SqlDataAdapter = New SqlDataAdapter(sql, koneksi)
        Dim ds As DataSet = New DataSet
        da.Fill(ds, "buku")
        PosisiRecord = MyBase.BindingContext(ds, "buku")
        TxtKode.DataBindings.Add("Text", ds, "buku.kode_buku")
        TxtJudul.DataBindings.Add("Text", ds, "buku.judul")
        CbJenis.DataBindings.Add("Text", ds, "buku.jenis")
        TxtPengarang.DataBindings.Add("Text", ds, "buku.pengarang")
        TxtPenerbit.DataBindings.Add("Text", ds, "buku.penerbit")
    End Sub

    Sub SimpanRecord()
        Dim sqltrans As SqlTransaction = koneksi.BeginTransaction
        Dim cmdtrans As SqlCommand = koneksi.CreateCommand
        If TxtKode.Text <> "" Then
            Try
                With cmdtrans
                    .CommandText = "SP_tambahbuku"
                    .CommandType = CommandType.StoredProcedure
                    .Connection = koneksi
                    .Transaction = sqltrans
                    .Parameters.Add("@kode_buku", SqlDbType.Char, 5).Value = TxtKode.Text
                    .Parameters.Add("@judul", SqlDbType.VarChar, 50).Value = TxtJudul.Text
                    .Parameters.Add("@jenis", SqlDbType.VarChar, 30).Value = CbJenis.Text
                    .Parameters.Add("@pengarang", SqlDbType.VarChar, 30).Value = TxtPengarang.Text
                    .Parameters.Add("@penerbit", SqlDbType.VarChar, 30).Value = TxtPenerbit.Text
                    .ExecuteNonQuery()
                End With
                sqltrans.Commit()
                MsgBox("Data Telah Tersimpan")
            Catch ex As Exception
                sqltrans.Rollback()
                MessageBox.Show(ex.Message, "Data Gagal Tersimpan", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
                sqltrans.Dispose()
                cmdtrans.Dispose()
                koneksi.Close()
            End Try
        End If
    End Sub

    Sub DeleteRecord()
        Dim sqltrans As SqlTransaction = koneksi.BeginTransaction
        Dim cmdtrans As SqlCommand = koneksi.CreateCommand
        Dim hapus As Int16
        If TxtKode.Text <> "" Then
            Try
                With cmdtrans
                    hapus = MsgBox("Benar record ini akan dihapus " & TxtKode.Text & "?", MsgBoxStyle.OkCancel, "Delete record")
                    Select Case hapus
                        Case vbCancel
                            Exit Sub
                        Case vbOK
                            .CommandText = "SP_deletebuku"
                            .CommandType = CommandType.StoredProcedure
                            .Connection = koneksi
                            .Transaction = sqltrans
                            .Parameters.Add("@kode_buku", SqlDbType.Char, 5).Value = TxtKode.Text
                            .ExecuteNonQuery()
                    End Select
                End With
                sqltrans.Commit()
                MsgBox("Data Telah TerHapus")
            Catch ex As Exception
                sqltrans.Rollback()
                MessageBox.Show(ex.Message, "Data Gagal Terhapus", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
                sqltrans.Dispose()
                cmdtrans.Dispose()
                koneksi.Close()
            End Try
        End If
    End Sub

    Sub UpdateRecord()
        Dim sqltrans As SqlTransaction = koneksi.BeginTransaction
        Dim cmdtrans As SqlCommand = koneksi.CreateCommand
        If TxtKode.Text <> "" Then
            Try
                With cmdtrans
                    .CommandText = "SP_updatebuku"
                    .CommandType = CommandType.StoredProcedure
                    .Connection = koneksi
                    .Transaction = sqltrans
                    .Parameters.Add("@kode_buku", SqlDbType.Char, 5).Value = TxtKode.Text
                    .Parameters.Add("@judul", SqlDbType.VarChar, 50).Value = TxtJudul.Text
                    .Parameters.Add("@jenis", SqlDbType.VarChar, 30).Value = CbJenis.Text
                    .Parameters.Add("@pengarang", SqlDbType.VarChar, 30).Value = TxtPengarang.Text
                    .Parameters.Add("@penerbit", SqlDbType.VarChar, 30).Value = TxtPenerbit.Text
                    .ExecuteNonQuery()
                End With
                sqltrans.Commit()
                MsgBox("Data Telah Terupdate")
            Catch ex As Exception
                sqltrans.Rollback()
                MessageBox.Show(ex.Message, "Data Gagal Terupdate", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
                sqltrans.Dispose()
                cmdtrans.Dispose()
                koneksi.Close()
            End Try
        End If
    End Sub
#End Region

    Private Sub FromBuku_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Text = "~~~ Master Data Buku ~~~"
        Me.BackColor = Color.AliceBlue
        ControlBox = False
        Me.WindowState = FormWindowState.Maximized
        Me.PanelLuar.Location = New System.Drawing.Size(250, 80)
        Me.PanelLuar.Size = New System.Drawing.Size(802, 519)
        Me.ViewRecord()
        koneksi.Open()
    End Sub

    Private Sub BtnTambah_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSimpan.Click
        SimpanRecord()
        FormClear()
    End Sub

    Private Sub BtnBersih_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        FormClear()
    End Sub

    Private Sub BtnSelesai_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnKeluar.Click
        End
    End Sub

    Private Sub BtnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPrev.Click
        PosisiRecord.Position -= 1
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnFirst.Click
        PosisiRecord.Position = 0
    End Sub

    Private Sub BtnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnNext.Click
        PosisiRecord.Position += 1
    End Sub

    Private Sub BtnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnLast.Click
        PosisiRecord.Position = PosisiRecord.Count - 1
    End Sub

    Private Sub BtnTambah_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnTambah.Click
        FormClear()
    End Sub

    Private Sub BtnHapus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnHapus.Click
        DeleteRecord()
    End Sub

    Private Sub BtnUbah_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUbah.Click
        UpdateRecord()
    End Sub
End Class

Untuk yang maw download scriptnya dan aplikasinya klik disini (StoreProcedure.txt dan Pustaka.rar)

0 komentar:

Posting Komentar