12:45 PM
Ridwan
Tabel Type adalah fitur yang baru diperkenalkan di SQL SERVER 2008, yang belum pernah ada di SQL SERVER versi-versi sebelumnya. Kegunaan fitur ini antara lain ketika:
Misalkan kita ingin menginsert isi tabel berikut
ke tabel :
1. Buat User Define Table Type di SQL Server 2008 seperti contoh berikut :
USE SITC
GO
CREATE TYPE SysAdmType AS TABLE
(
KODESEKOLAH VARCHAR(5),
NO_FORMULIR VARCHAR(20) NULL,
KODESYARAT VARCHAR(10) NULL,
SYARAT VARCHAR(50) NULL,
JUMLAH NUMERIC(6,2) NULL,
NILAI BIT NOT NULL,
TGLUPDATE DATE NULL,
USERID VARCHAR(10) NULL
)
GO
2. Buat Stored Procedure seperti berikut ini :
USE SITC
GO
CREATE PROCEDURE usp_InsSysAdmCalon
(
@SysAdmCalon SysAdmType READONLY
)
AS
BEGIN
INSERT INTO calonsiswa_syarat
(kodesekolah, no_formulir, kodesyarat, syarat, jumlah, nilai,
tglupdate, userid)
Select kodesekolah,no_formulir,kodesyarat,syarat,jumlah,nilai,
tglupdate, userid FROM @SysAdmCalon
END
3. Selanjutnya adalah menulis coding di Visual Basic seperti berikut ini. Oh ya, saya menggunakan VB 2010.
Private Sub UpdateSysAdm(cFormulirNo As String)
Dim clsSysAdm As clsSyaratAdm
Dim table1 As DataTable
Dim currentRow As DataRowView
Dim nCount As Integer
table1 = New DataTable
Try
clsSysAdm = New clsSyaratAdm
With clsSysAdm
.StoreProcName = "usp_psb_SysAdmIUD"
.Statement = "Select"
.KodeSekolah = cmbUnit.Text
.Scope = "KODESEKOLAH"
.FilterData()
End With
With table1
.Columns.Add("KODESEKOLAH", GetType(String))
.Columns.Add("NO_FORMULIR", GetType(String))
.Columns.Add("KODESYARAT", GetType(String))
.Columns.Add("SYARAT", GetType(String))
.Columns.Add("JUMLAH", GetType(Integer))
.Columns.Add("NILAI", GetType(Boolean))
.Columns.Add("TGLUPDATE", GetType(Date))
.Columns.Add("USERID", GetType(String))
End With
With clsSysAdm
nCount = .BDSSysAdm.Count
MsgBox(nCount)
If nCount > 0 Then
.BDSSysAdm.MoveFirst()
For i As Integer = 0 To nCount - 1
currentRow = DirectCast(clsSysAdm.BDSSysAdm.Current, DataRowView)
With currentRow
table1.Rows.Add(.Item(0), cFormulirNo, .Item(1), .Item(2), .Item(3), False, Date.Now, UserName)
End With
.BDSSysAdm.MoveNext()
Next
End If
End With
Catch ex As Exception
MsgBox(ex.Message, vbCritical)
clsSysAdm = Nothing
End Try
Try
Dim myConn = MyDB.Open
Dim myCommand As SqlCommand
Dim myParam As SqlParameter
myCommand = New SqlCommand
With myCommand
.Parameters.Clear()
.Connection = myConn
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_InsSysAdmCalon"
End With
myParam = New SqlParameter("@SysAdmCalon", SqlDbType.Structured)
myCommand.Parameters.Add(myParam)
myParam.Value = table1
myCommand.ExecuteNonQuery()
myConn.Close()
myCommand = Nothing
myConn = Nothing
myParam = Nothing
table1.Clear()
table1 = Nothing
Catch ex As Exception
MsgBox(ex.Message, vbCritical)
End Try
End Sub
Semoga bermanfaat.
- Mengirimkan data multiple rows ke Stored Procedure atau Function tidak perlu membuat temporary table
- Mengirimkan multple parameter ke Stored Prcedure atau Funtion dapat disederhanakan dengan adanya fitur ini.
Misalkan kita ingin menginsert isi tabel berikut
ke tabel :
1. Buat User Define Table Type di SQL Server 2008 seperti contoh berikut :
USE SITC
GO
CREATE TYPE SysAdmType AS TABLE
(
KODESEKOLAH VARCHAR(5),
NO_FORMULIR VARCHAR(20) NULL,
KODESYARAT VARCHAR(10) NULL,
SYARAT VARCHAR(50) NULL,
JUMLAH NUMERIC(6,2) NULL,
NILAI BIT NOT NULL,
TGLUPDATE DATE NULL,
USERID VARCHAR(10) NULL
)
GO
2. Buat Stored Procedure seperti berikut ini :
USE SITC
GO
CREATE PROCEDURE usp_InsSysAdmCalon
(
@SysAdmCalon SysAdmType READONLY
)
AS
BEGIN
INSERT INTO calonsiswa_syarat
(kodesekolah, no_formulir, kodesyarat, syarat, jumlah, nilai,
tglupdate, userid)
Select kodesekolah,no_formulir,kodesyarat,syarat,jumlah,nilai,
tglupdate, userid FROM @SysAdmCalon
END
3. Selanjutnya adalah menulis coding di Visual Basic seperti berikut ini. Oh ya, saya menggunakan VB 2010.
Private Sub UpdateSysAdm(cFormulirNo As String)
Dim clsSysAdm As clsSyaratAdm
Dim table1 As DataTable
Dim currentRow As DataRowView
Dim nCount As Integer
table1 = New DataTable
Try
clsSysAdm = New clsSyaratAdm
With clsSysAdm
.StoreProcName = "usp_psb_SysAdmIUD"
.Statement = "Select"
.KodeSekolah = cmbUnit.Text
.Scope = "KODESEKOLAH"
.FilterData()
End With
With table1
.Columns.Add("KODESEKOLAH", GetType(String))
.Columns.Add("NO_FORMULIR", GetType(String))
.Columns.Add("KODESYARAT", GetType(String))
.Columns.Add("SYARAT", GetType(String))
.Columns.Add("JUMLAH", GetType(Integer))
.Columns.Add("NILAI", GetType(Boolean))
.Columns.Add("TGLUPDATE", GetType(Date))
.Columns.Add("USERID", GetType(String))
End With
With clsSysAdm
nCount = .BDSSysAdm.Count
MsgBox(nCount)
If nCount > 0 Then
.BDSSysAdm.MoveFirst()
For i As Integer = 0 To nCount - 1
currentRow = DirectCast(clsSysAdm.BDSSysAdm.Current, DataRowView)
With currentRow
table1.Rows.Add(.Item(0), cFormulirNo, .Item(1), .Item(2), .Item(3), False, Date.Now, UserName)
End With
.BDSSysAdm.MoveNext()
Next
End If
End With
Catch ex As Exception
MsgBox(ex.Message, vbCritical)
clsSysAdm = Nothing
End Try
Try
Dim myConn = MyDB.Open
Dim myCommand As SqlCommand
Dim myParam As SqlParameter
myCommand = New SqlCommand
With myCommand
.Parameters.Clear()
.Connection = myConn
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_InsSysAdmCalon"
End With
myParam = New SqlParameter("@SysAdmCalon", SqlDbType.Structured)
myCommand.Parameters.Add(myParam)
myParam.Value = table1
myCommand.ExecuteNonQuery()
myConn.Close()
myCommand = Nothing
myConn = Nothing
myParam = Nothing
table1.Clear()
table1 = Nothing
Catch ex As Exception
MsgBox(ex.Message, vbCritical)
End Try
End Sub
Semoga bermanfaat.
