Store Procedure

Stored procedure adalah satu atau lebih perintah-perintah SQL yang disimpan dalam sebuah DBMS. Pemrograman SQL ini sering disebut dengan T-SQL (Transact SQL). Stored procedure ini mirip dengan sebuah fungsi yang umum dalam pemrograman. Stored procedure bisa menerima inputan dari parameter dan dapat juga bisa mengembalikan data lewat result sets maupun output parameternya. Adapun keuntungan dari stored procedure ini adalah :
• Mudah untuk dikelola. Contohnya kita bisa mengoptimalkan serta merubah perintah dalam stored procedure tanpa mengkompilasi ulang aplikasi yang menggunakannya.
• Meningkatkan performa DBMS. Karena stored procedure merupakan kumpulan dari banyak perintah, kita dapat menyelesaikan banyak perintah hanya dalam satu koneksi ke DBMS, dan jika DBMS terletak pada komputer yang terpisah stored procedure akan mengurangi total waktu untuk menyelesaikan tugas yang kompleks secara drastis.
• Meningkatkan keamanan DBMS. Contohnya kita bisa mengijinkan Windows account untuk menjalankan perintah-perintah dalam aplikasi yang dibuat untuk menggunakan beberapa stored procedure tapi Windows account tersebut tidak boleh mengakses tabel secara langsung.

Ex:
CREATE PROCEDURE TransferDana
@rekeningAsal char(10),
@rekeningTujuan char(10),
@jumlahDana money
AS
BEGIN TRANSACTION
/* Kurangi Saldo dari rekening asal */
UPDATE DataNasabah
SET Saldo = Saldo - @jumlahDana
WHERE no_rekening = @rekeningAsal
/* Tambah Saldo pada rekening tujuan */
UPDATE DataNasabah
SET Saldo = Saldo + @jumlahDana
WHERE no_rekening = @rekeningTujuan
IF (@@ERROR > 0)
ROLLBACK
ELSE
COMMIT
GO

Menggunakan select case procedure:

CREATE PROCEDURE getNumber
@OrderNo char(6)OUTPUT
as
SELECT @OrderNo=Max(cOrderNo) FROM Orders
SELECT @OrderNo=
CASE
WHEN @OrderNo >=0 and @OrderNo < 9
Then '00000'+Convert(char,@OrderNo+1)
WHEN @OrderNo >=9 and @OrderNo < 99
Then '0000'+Convert(char,@OrderNo+1)
WHEN @OrderNo >=99 and @OrderNo < 999
Then '000'+Convert(char,@OrderNo+1)
WHEN @OrderNo >=999 and @OrderNo < 9999
Then '00'+Convert(char,@OrderNo+1)
WHEN @OrderNo >=9999 and @OrderNo < 99999
Then '0'+Convert(char,@OrderNo+1)
WHEN @OrderNo >=99999
Then Convert(char,@OrderNo+1)
END
RETURN

Export Data from Grid to Excel File Using DataSet

Dim loSavefd As New SaveFileDialog
Dim loResult As New DialogResult
Dim lsFilePath As String = String.Empty
Dim lsAssessment As String = String.Empty
Dim lsFileName As String = String.Empty
Dim lsCharacter As String = "/\*:?<>~|'"""
Dim liData As Integer
Dim lbOpen As Boolean = False
Dim liMergeAss, liMergeAss2 As Integer
Dim liCount, liCount2 As Integer
Dim loXlApp As New Excel.Application
Dim loXlWorkBook As Excel.Workbook = loXlApp.Workbooks.Add
Dim loXlWorkSheet As Excel.Worksheet = loXlWorkBook.ActiveSheet()

Try
liMergeAss = 3
liMergeAss2 = 3
liCount = 0
liCount2 = 0

Dim frmMsgBx As New Form
Dim lblMsgText As New System.Windows.Forms.Label
Dim btnMsgOpen, btnMsgSave, btnMsgCancel As New System.Windows.Forms.Button

With frmMsgBx
.StartPosition = FormStartPosition.CenterScreen
.MinimizeBox = False
.MaximizeBox = False
.Width = 380
.Height = 150
.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedToolWindow
.ShowIcon = False
.ShowInTaskbar = False
.Text = "Exporting to Excel"
End With

With lblMsgText
.Parent = frmMsgBx
.Left = 30
.Top = 30
.Width = 300
.Height = 13
.Text = "Would you like to open the file or save it to your computer?"
.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
End With

With btnMsgOpen
.Parent = frmMsgBx
.Left = 50
.Top = 80
.Width = 52
.Height = 23
.Text = "Open"
.DialogResult = System.Windows.Forms.DialogResult.OK
End With

With btnMsgSave
.Parent = frmMsgBx
.Left = 110
.Top = 80
.Width = 73
.Height = 23
.Text = "Save"
.DialogResult = System.Windows.Forms.DialogResult.Yes
End With

With btnMsgCancel
.Parent = frmMsgBx
.Left = 250
.Top = 80
.Width = 75
.Height = 23
.Text = "Cancel"
.DialogResult = System.Windows.Forms.DialogResult.Cancel
End With

frmMsgBx.ShowDialog()
lsFileName = txtCompItemId.Text.Trim.Replace("&", "and")
If lsFileName.Substring(0, 1).Equals(".") Then
lsFileName = "Dot " & lsFileName.Remove(0, 1)
End If

For liCtr As Integer = 1 To Len(lsCharacter)
lsFileName = Replace(lsFileName, Mid(lsCharacter, liCtr, 1), " ")
Next liCtr

If frmMsgBx.DialogResult = System.Windows.Forms.DialogResult.Yes Then
With loSavefd
.Filter = "Excel Files|*.xls"
.Title = "Download to Excel File"
.FileName = lsFileName
.ValidateNames = True
loResult = .ShowDialog()
lsFilePath = loSavefd.FileName
End With

loSavefd.Dispose()
End If

If loResult = System.Windows.Forms.DialogResult.OK OrElse frmMsgBx.DialogResult = System.Windows.Forms.DialogResult.OK OrElse frmMsgBx.DialogResult = System.Windows.Forms.DialogResult.Yes Then
If lsFileName.Length > 30 Then
loXlWorkSheet.Name = lsFileName.Substring(0, 30)
Else
loXlWorkSheet.Name = lsFileName
End If

'add the title
With loXlWorkSheet.Cells(1, 1)
.Font.Name = "Arial"
.Font.Bold = True
.Font.ColorIndex = 2
.Font.Size = 18
.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
End With
loXlWorkSheet.Range(loXlWorkSheet.Cells(1, 1), loXlWorkSheet.Cells(1, 4)).Merge()
loXlWorkSheet.Cells(1, 1).Value = txtCompItemId.Text.Trim
loXlWorkSheet.Range(loXlWorkSheet.Cells(1, 1), loXlWorkSheet.Cells(1, 1)).Interior.ColorIndex = 37

'add the definition competency item
With loXlWorkSheet.Cells(2, 1)
.Font.Name = "Arial"
.Font.Bold = True
.VerticalAlignment = XlVAlign.xlVAlignJustify
loXlWorkSheet.Cells(2, 1).Value = "Definition"
End With

With loXlWorkSheet.Cells(2, 2)
.RowHeight = 80
.Font.Italic = True
.Font.Name = "Arial"
.WrapText = True
.VerticalAlignment = XlVAlign.xlVAlignTop
loXlWorkSheet.Cells(2, 2).Value = txtDefinition.Text.Trim
End With
loXlWorkSheet.Range(loXlWorkSheet.Cells(2, 2), loXlWorkSheet.Cells(2, 4)).Merge()

SwapDataSet()

'add the headers table column
Dim i As Integer = moPniDataSetSwap.Tables(0).Columns.Count - 1
For c As Integer = 0 To i
With loXlWorkSheet.Cells(3, c + 1)
.Borders.Weight = XlBorderWeight.xlMedium
.Font.Bold = True
.Font.Size = 12
.WrapText = True
.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
.VerticalAlignment = XlVAlign.xlVAlignTop
loXlWorkSheet.Cells(3, c + 1).Value = moPniDataSetSwap.Tables(0).Columns(c).ColumnName
End With
Next
loXlWorkSheet.Range(loXlWorkSheet.Cells(3, 1), loXlWorkSheet.Cells(3, i + 1)).Interior.ColorIndex = 34

'add the values table
liData = moPniDataSetSwap.Tables(0).Rows.Count - 1

For r As Integer = 0 To moPniDataSetSwap.Tables(0).Rows.Count - 1
'column 1
With loXlWorkSheet.Cells(r + 4, 1)
.VerticalAlignment = XlVAlign.xlVAlignTop
.ColumnWidth = 15
.WrapText = True
.Font.Bold = True
.Borders.Weight = XlBorderWeight.xlThin
If moPniDataSetSwap.Tables(0).Rows(liData)(0).ToString.Trim.Equals("") Then
liCount = liCount + 1
lsAssessment = moPniDataSetSwap.Tables(0).Rows(liData)(0).ToString.Trim
loXlWorkSheet.Range(loXlWorkSheet.Cells(liMergeAss, 1), loXlWorkSheet.Cells(liMergeAss + liCount, 1)).Merge()
Else
liMergeAss = liMergeAss + liCount + 1
liCount = 0
lsAssessment = moPniDataSetSwap.Tables(0).Rows(liData)(0).ToString.Trim
loXlWorkSheet.Cells(r + 4, 1).Value = lsAssessment
End If
End With

'column 2
With loXlWorkSheet.Cells(r + 4, 2)
.VerticalAlignment = XlVAlign.xlVAlignTop
.ColumnWidth = 40
.WrapText = True
.Borders.Weight = XlBorderWeight.xlThin
If moPniDataSetSwap.Tables(0).Rows(liData)(1).ToString.Trim.Equals("") AndAlso lsAssessment.Equals("") Then
liCount2 = liCount2 + 1
loXlWorkSheet.Range(loXlWorkSheet.Cells(liMergeAss2, 2), loXlWorkSheet.Cells(liMergeAss2 + liCount2, 2)).Merge()
Else
liMergeAss2 = liMergeAss2 + liCount2 + 1
liCount2 = 0
loXlWorkSheet.Cells(r + 4, 2).Value = moPniDataSetSwap.Tables(0).Rows(liData)(1).ToString.Replace(";", "").Trim
End If
End With

'column 3,4
For c As Integer = 2 To i
With loXlWorkSheet.Cells(r + 4, c + 1)
.VerticalAlignment = XlVAlign.xlVAlignTop
.ColumnWidth = 40
.WrapText = True
.Borders.Weight = XlBorderWeight.xlThin
loXlWorkSheet.Cells(r + 4, c + 1).Value = moPniDataSetSwap.Tables(0).Rows(liData)(c).ToString.Replace(";", "").Trim
End With
Next
liData -= 1
Next
liCount = loXlWorkBook.Sheets.Count

For no As Integer = liCount To 2 Step -1
loXlWorkSheet = loXlWorkBook.Sheets.Item(no)
loXlWorkSheet.Delete()
Next

If frmMsgBx.DialogResult = System.Windows.Forms.DialogResult.OK Then
lbOpen = True
loXlApp.Visible = True
loXlWorkBook.Activate()
ElseIf loResult = System.Windows.Forms.DialogResult.OK Then
loXlApp.DisplayAlerts = False
loXlWorkBook.SaveAs(lsFilePath)
loXlWorkBook.Close(True)
loXlApp.DisplayAlerts = True
loXlApp.Quit()
MsgBox("Download Complete", MsgBoxStyle.Information, "Information")
End If
frmMsgBx.Dispose()
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Information")
Finally
moPniDataSetSwap.Clear()
Runtime.InteropServices.Marshal.ReleaseComObject(loXlApp)
Runtime.InteropServices.Marshal.ReleaseComObject(loXlWorkBook)
Runtime.InteropServices.Marshal.ReleaseComObject(loXlWorkSheet)
loXlApp = Nothing
loXlWorkBook = Nothing
loXlWorkSheet = Nothing
End Try

Import Excel File to Grid Using DataSet

untuk mengambil file excel kita menggunakan file dialog dan dialog result.

Dim fd As New OpenFileDialog
Dim result As New DialogResult
Dim loXlApp As New Excel.ApplicationClass
Dim loXlWorkBook As New Excel.Worksheet
Dim loXlWorkSheet As Excel.Workbook = Nothing

Try
With fd
.Filter = "Excel Files|*.xls"
.Title = "Browse Excel File"
result = .ShowDialog()
End With
If result = System.Windows.Forms.DialogResult.OK Then
grdPnisEntry.Columns.Clear()
grdPnisEntry.Clear()

Dim column As PowerGridColumn
Dim row As DataRow

loXlWorkSheet = loXlApp.Workbooks.Open(fd.FileName)
loXlWorkBook = loXlWorkSheet.Worksheets(1)

Dim range As Excel.Range
range = loXlWorkBook.UsedRange
Dim rCnt, cCnt, i As New Integer
Dim Obj As New Object
Dim value As String = String.Empty
Dim mopniDataSet As New System.Data.DataSet
mopniDataSet.Clear()
mopniDataSet.Tables.Add(New System.Data.DataTable)

For rCnt = 3 To range.Rows.Count
row = mopniDataSet.Tables(0).NewRow
For cCnt = 1 To range.Columns.Count
Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
If rCnt = 3 Then
If cCnt = 1 Then
column = New PowerGridColumn()
column.Name = "clmDefinition"
column.FieldName = "Definition"
column.ReadOnly = True
grdPnisEntry.Columns.Add(column)
mopniDataSet.Tables(0).Columns.Add(column.Name)
column = New PowerGridColumn()
column.Name = "clmSysKey"
column.FieldName = "SysKey"
column.ReadOnly = False
column.Visible = False
grdPnisEntry.Columns.Add(column)
mopniDataSet.Tables(0).Columns.Add(column.Name)
miColumnCount = miColumnCount + 2
End If
column = New PowerGridColumn()
column.Name = Obj.value
column.FieldName = Obj.value
column.ReadOnly = True
grdPnisEntry.Columns.Add(column)
mopniDataSet.Tables(0).Columns.Add(column.Name)
miColumnCount = miColumnCount + 1
Else
If Obj.value = String.Empty AndAlso rCnt > 4 Then
row(0) = CType(range.Cells(2, 2), Excel.Range).Value
i = grdPnisEntry.Rows - 1
row(cCnt + 1) = grdPnisEntry.Cell((cCnt + 1), i)
Else
row(0) = CType(range.Cells(2, 2), Excel.Range).Value
row(cCnt + 1) = Obj.value
End If
If cCnt = range.Columns.Count Then
mopniDataSet.Tables(0).Rows.Add(row)
End If
End If
Next cCnt
Next

grdPnisEntry.DataSource = mopniDataSet.Tables(0)
grdPnisEntry.Columns(1).GroupIndex = 1
grdPnisEntry.Columns(1).Width = 100
grdPnisEntry.Columns(1).SetColumnVisibility(False, 2)
grdPnisEntry.Columns(4).Width = 300
grdPnisEntry.Columns(5).Width = 300
grdPnisEntry.Columns(6).Width = 300
grdPnisEntry.DoExpandAllGroups()
Dim liCount As Integer = 0
txtImportExcel.Text = fd.FileName
fd.Dispose()
loXlWorkSheet.Close()
loXlApp.Quit()
Me.lbImportExcel = True
txtAssessmentTypeDesc.Clear()
txtAssessmentTypeDesc.ReadOnly = True
mSetHyperlinks(False)
miCompItemId = CInt(Sync.Item(Me.Name, "COMPETENCY_ITEM_ID").Value)
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Information")
Finally
If result = System.Windows.Forms.DialogResult.OK Then
// untuk melepas resource dari object excel yg kita buat.
Runtime.InteropServices.Marshal.ReleaseComObject(loXlApp)
Runtime.InteropServices.Marshal.ReleaseComObject(loXlWorkBook)
Runtime.InteropServices.Marshal.ReleaseComObject(loXlWorkSheet)
loXlApp = Nothing
loXlWorkBook = Nothing
loXlWorkSheet = Nothing
End If
End Try

Create Custom Message Box VB.NET

Dim frmMsgBx As New Form
Dim lblMsgText As New System.Windows.Forms.Label
Dim btnMsgOpen, btnMsgSave, btnMsgCancel As New System.Windows.Forms.Button

With frmMsgBx
.StartPosition = FormStartPosition.CenterScreen
.MinimizeBox = False
.MaximizeBox = False
.Width = 380
.Height = 150
.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedToolWindow
.ShowIcon = False
.ShowInTaskbar = False
.Text = "Exporting to Excel"
End With

With lblMsgText
.Parent = frmMsgBx
.Left = 30
.Top = 30
.Width = 300
.Height = 13
.Text = "Would you like to open the file or save it to your computer?"
.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
End With

With btnMsgOpen
.Parent = frmMsgBx
.Left = 50
.Top = 80
.Width = 52
.Height = 23
.Text = "Open"
.DialogResult = System.Windows.Forms.DialogResult.OK
End With

With btnMsgSave
.Parent = frmMsgBx
.Left = 110
.Top = 80
.Width = 73
.Height = 23
.Text = "Save"
.DialogResult = System.Windows.Forms.DialogResult.Yes
End With

With btnMsgCancel
.Parent = frmMsgBx
.Left = 250
.Top = 80
.Width = 75
.Height = 23
.Text = "Cancel"
.DialogResult = System.Windows.Forms.DialogResult.Cancel
End With

frmMsgBx.ShowDialog()