SP di MS Sql Sever 2008

Pada kesempatan kali ini, saya ingin berbagi masalah Stored Procedure di MS Sql Sever 2008 dan eksekusinya menggunanakan Visual Basic .Net Framework 3.5. Stored Procedure di MS Sql Server menurut saya emang mudah digunakan dan bahasanya manusiawi banget buat saya yang udah terbiasa dengan bahasa pemrograman dan khususnya produk-produk dari Microsoft, walau sebenarnya saya gak terlalu suka dengan produk microsoft terutama Windows Vista, seperti yang terpasang pada laptop Toshiba A200 yang sedang saya pakai ini.
Gak usah banyak ngomong masalah produk microsoft, langsung aja kita bahas kolaborasi Stored Procedure di MS Sql Sever 2008 dengan Visual Basic .Net Framework 3.5. Langsung ajah ikuti langkah berikut
1. Login ke MS Sql server menggunakan SQL Server Management Studio. Setelah berhasil masuk expand database yang sudah dibuat, kemudia masuk ke bagian Programmability, click kanan pada Stored Procedures dan pilih "New Stored Procedure".


2. Tuliskan Query untuk membuat Stored Procedure seperti berikut

USE [DataPegawai]
GO
/****** Object:  StoredProcedure [dbo].[InsertPropinsi]    Script Date: 01/28/2010 11:02:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Hendrawan Aprillia Ashari
-- Create date: 27 January 2010
-- Description: Insert Data Propinsi to Table
-- =============================================
CREATE PROCEDURE [dbo].[InsertPropinsi] 
 -- Add the parameters for the stored procedure here
 @NamaPropinsi   ntext,
 @KeteranganPropinsi  ntext
AS
BEGIN
 INSERT DataPegawai.dbo.Propinsi VALUES (@NamaPropinsi, @KeteranganPropinsi);
END

3. Setelah Stored Procedure berhasil dibuat, buka Visual Studio dan buatlah project baru dengan form baru yang didalamnya terdapat 2 textbox untuk memasukan data Nama Propinsi dan Keterangan Propinsi dan tambahkan satu tombol untuk memasukan data ke dalam database.



4. Kemudian ketikan code sebagai berikut.
- pada file DbConnection.vb

Imports System
Imports System.Configuration
Imports System.ComponentModel
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient

Public Class DbConnection
#Region "Class Variables"
    'Initialization Const

    'Initialization connection string
    Private Shared STRING_PROVIDER As String = "Network Library=DBMSSOCN;" & _
                                               "Data Source=127.0.0.1,1433;" & _
                                               "Initial Catalog=DataPegawai;" & _
                                               "User ID=bkd;" & _
                                               "Password=ajadah....."

    'Error messages   

    'Initialization protected variables
    Protected Shared _exception As Exception
    Protected dbConnection As SqlConnection
    Protected dbCommand As SqlCommand
    Protected dbDataAdapter As New SqlDataAdapter
    Protected dbDataSet As New DataSet
    'Protected _dbDataAdapter As OleDbDataAdapter
#End Region

#Region "Public Functions"
     _
    Public Shared ReadOnly Property ReadAccessException() As Exception
        Get
            Return _exception
        End Get
    End Property

    '*****************************************************
    '* OpenDb : get resource of DbConnection
    '*****************************************************
    Public Function OpenDb() As Boolean
        'create object OleDbConnection
        Try
            dbConnection = New SqlConnection()
            dbConnection.ConnectionString = STRING_PROVIDER
            dbConnection.Open()
        Catch ex As Exception
            _exception = New Exception(ex.Message.ToString)
            Return False
        End Try

        Return True
    End Function

    '*****************************************************
    '* CloseDb : get resource of DbConnection
    '*****************************************************
    Public Function CloseDb() As Boolean
        'create object OleDbConnection
        Try
            dbConnection.Close()
            dbConnection = Nothing
        Catch ex As Exception
            _exception = New Exception(ex.Message.ToString)
            Return False
        End Try

        Return True
    End Function


    '*****************************************************
    '* ConnResource : get resource of DbConnection
    '*****************************************************
    Public Function ConnResource() As SqlConnection
        Return dbConnection
    End Function

    '*****************************************************
    '* ExecuteCmd : Execute Sql command
    '*****************************************************
    Public Function ExecuteCmd(ByVal CmdString As String) As Boolean
        'Insert data
        Try
            '** Instantiate a command object and set the object properties
            dbCommand = New SqlCommand
            dbCommand.Connection = dbConnection
            dbCommand.CommandText = CmdString

            '** Open the database connection, execute the SQL command, close the
            '** database connection

            dbCommand.ExecuteNonQuery()
            dbCommand.Dispose()
            dbCommand = Nothing
        Catch ex As Exception
            _exception = New Exception(ex.Message.ToString)
            Return False
        End Try

        Return True
    End Function

#End Region

#Region "Shared Functions"

#End Region

#Region "Utility Functions"

#End Region
End Class

- Pada file Propinsi.vb

Imports System
Imports System.Configuration
Imports System.ComponentModel
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports DevExpress.XtraGrid.GridControl

Public Class Propinsi
    Inherits DbConnection

#Region "Class Variables"
    Private IdPropinsi As String
    Private NamaPropinsi As String
    Private KeteranganPropinsi As String

    Private _stringCommand As String
    Private _dbDataReader As SqlDataReader
#End Region

#Region "Private Functions"
     _
    Public Shared ReadOnly Property DataException() As Exception
        Get
            Return _exception
        End Get
    End Property

    Function Data(ByVal _NamaPropinsi As String, ByVal _KeteranganPropinsi As String) As Boolean
        Try
            NamaPropinsi = _NamaPropinsi
            KeteranganPropinsi = _KeteranganPropinsi

            Return True
        Catch ex As Exception
            _exception = New Exception(ex.Message.ToString)
            Return False
        End Try
    End Function    

    Function Add() As Boolean
        Try
            If OpenDb() Then
                dbCommand = New SqlCommand("DataPegawai.dbo.InsertPropinsi", ConnResource)

                dbCommand.CommandType = CommandType.StoredProcedure
                dbCommand.Parameters.Add("@NamaPropinsi", SqlDbType.NText)
                dbCommand.Parameters("@NamaPropinsi").Value = NamaPropinsi
                dbCommand.Parameters.Add("@KeteranganPropinsi", SqlDbType.NText)
                dbCommand.Parameters("@KeteranganPropinsi").Value = KeteranganPropinsi

                dbCommand.ExecuteNonQuery()

                dbCommand.Dispose()
                dbCommand = Nothing
                CloseDb()
                Return True
            Else
                _exception = New Exception("Fail execute InsertPropinsi Command")
                Return False
            End If
        Catch ex As Exception
            _exception = New Exception(ex.Message.ToString)
            Return False
        End Try
    End Function
#End Region

End Class

- pada Code Form Propinsi

Public Class FrmPropinsi 
    Private PropinsiConn As New Propinsi

    Private Sub btnTambahPropinsi_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTambahPropinsi.Click
        Dim namaPropinsi As String = Trim(txtNamaPropinsi.Text)
        Dim keteranganPropinsi As String = Trim(memoKeteranganPropinsi.Text)

        PropinsiConn.Data(namaPropinsi, keteranganPropinsi)
        If PropinsiConn.Add Then
            PropinsiConn.GetData(GridControl1)
            'MessageBox.Show("Data berhasil dimasukan")
        Else
            MessageBox.Show(Propinsi.DataException.Message.ToString)
        End If
    End Sub
End Class

Sekian saja obrolan kita kali ini, semoga bermanfaat
Tapi omong-omong bahasanya kok kurang enak yaa.... ditulisan ini. Heheheheh iya nich perasaan lagi agak gak nyaman, lagi kurang rasa humornya. Tapi yang penting ntuh kode jalan buat saya.