Cara Memberi Akses User Dengan OOP VB.Net dan SQL2005

Dalam suatu perancangan sebuah aplikasi berbasis dekstop (VB.Net) pemberian akses pada user yang berinteraksi dengan sistem menjadi hal yang sensitif dan krusial. dan tentunya masih belum banyak cara pengoperasian yang dishare di dunia internet. 

so jangan khawatir, disini saya share untuk anda semua. diatas adalah salah satu akses form yang saya buat di VB.Net 2008 dengan database SQL2005 berbasis OOP.

asumsi saya, anda sudah membuat database dengan tabel data akses user. karena saya tidak mentutorialkan disini....

caranya adalah sebagai berikut ::

Kegiatan di SQL2005 ##

Buatlah STORED PROCEDURE untuk menyimpan data
 #
create procedure SP_SIMPAN_AKSESSISTEM
@IdGrupUser char(6),
@IdForm char(6),
@AksesForm char(1)
As Insert AksesSistemTabel
Values (@IdGrupUser, @IdForm, @AksesForm)

Buatlah STORED PROCEDURE untuk mengedit data
#
create procedure SP_EDIT_AKSESSISTEM
@IdGrupUser char(6),
@IdForm char(6),
@AksesForm char(1)
as update AksesSistemTabel
set AksesForm=@AksesForm
where IdGrupUser=@IdGrupUser and IdForm=@IdForm




Kegiatan di Visual Studio ##
Buatlah Modul untuk pendeklarasian data
#
Imports System.Data.SqlClient
Module UMUMModule

#Region "DEKLARASI"

    Public COMMAND As New SqlCommand
    Public DTA As New SqlDataAdapter
    Public DTR As SqlDataReader
    Public DTS As New DataSet
    Public DTT As New DataTable

#End Region


#Region "ENTITY & CONTROL"

    Public MeAksesEntity As New AKSESSISTEMEntityClass
    Public MeAksesControl As New AKSESSISTEMControlClass

#End Region

   
End Module


Buatlah Kelas KONEKSI DATABASE
#
Imports System.Data.SqlClient

Public Class CONNECTIONClass
    Dim KONEKSI As New SqlConnection("Data Source=.\; Initial Catalog=PM_DBASE; User ID=sa; Password=illuminator;")

    Public Function BUKAKONEKSI() As SqlConnection
        KONEKSI.Open()
        Return KONEKSI
    End Function

    Public Function TUTUPKONEKSI() As SqlConnection
        KONEKSI.Close()
        Return KONEKSI
    End Function
End Class

Buatlah Kelas ENTITY

#

Public Class AKSESSISTEMEntityClass

#Region "DEKLARASI"
    Private _IdGrupUser As String
    Private _IdForm As String
    Private _AksesForm As String
#End Region

#Region "PROPERTY"
    Public Overridable Sub DATA_AKSESSISTEM(ByVal IdGrupUser As String, ByVal IdForm As String, ByVal AksesForm As String)
        _IdGrupUser = IdGrupUser
        _IdForm = IdForm
        _AksesForm = AksesForm
    End Sub

    Public Property IdGrupUser() As String
        Get
            Return _IdGrupUser
        End Get
        Set(ByVal value As String)
            _IdGrupUser = value
        End Set
    End Property

    Public Property IdForm() As String
        Get
            Return _IdForm
        End Get
        Set(ByVal value As String)
            _IdForm = value
        End Set
    End Property

    Public Property AksesForm() As String
        Get
            Return _AksesForm
        End Get
        Set(ByVal value As String)
            _AksesForm = value
        End Set
    End Property
#End Region

End Class


Buatlah Kelas CONTROL
#
Imports System.Data.SqlClient

Public Class AKSESSISTEMControlClass
    Dim MeKONEKSI As New CONNECTIONClass

#Region "ACTION"
    Public Overridable Function SIMPAN_DATA(ByVal _AKSES As AKSESSISTEMEntityClass) As SqlCommand

        MeKONEKSI.TUTUPKONEKSI()
        COMMAND = New SqlCommand("SP_SIMPAN_AKSESSISTEM", MeKONEKSI.BUKAKONEKSI)
        COMMAND.CommandType = CommandType.StoredProcedure

        Dim _IdGrupUser As New SqlParameter("@IdGrupUser", SqlDbType.Char, 6)
        _IdGrupUser.Value = _AKSES.IdGrupUser

        Dim _IdForm As New SqlParameter("@IdForm", SqlDbType.Char, 6)
        _IdForm.Value = _AKSES.IdForm

        Dim _AksesForm As New SqlParameter("@AksesForm", SqlDbType.Char, 1)
        _AksesForm.Value = _AKSES.AksesForm

        With COMMAND.Parameters
            .Add(_IdGrupUser)
            .Add(_IdForm)
            .Add(_AksesForm)
        End With

        COMMAND.ExecuteNonQuery()
        COMMAND = New SqlCommand("", MeKONEKSI.TUTUPKONEKSI)
        Return COMMAND

    End Function

    Public Overridable Function EDIT_DATA(ByVal _AKSES As AKSESSISTEMEntityClass) As SqlCommand

        MeKONEKSI.TUTUPKONEKSI()
        COMMAND = New SqlCommand("SP_EDIT_AKSESSISTEM", MeKONEKSI.BUKAKONEKSI)
        COMMAND.CommandType = CommandType.StoredProcedure

        Dim _IdGrupUser As New SqlParameter("@IdGrupUser", SqlDbType.Char, 6)
        _IdGrupUser.Value = _AKSES.IdGrupUser

        Dim _IdForm As New SqlParameter("@IdForm", SqlDbType.Char, 6)
        _IdForm.Value = _AKSES.IdForm

        Dim _AksesForm As New SqlParameter("@AksesForm", SqlDbType.Char, 1)
        _AksesForm.Value = _AKSES.AksesForm

        With COMMAND.Parameters
            .Add(_IdGrupUser)
            .Add(_IdForm)
            .Add(_AksesForm)
        End With

        COMMAND.ExecuteNonQuery()
        COMMAND = New SqlCommand("", MeKONEKSI.TUTUPKONEKSI)
        Return COMMAND

    End Function
#End Region


#Region "QUERY"

    Public Overridable Function ISI_COMBO() As List(Of GRUPUSERAKSESEntityClass)
        Dim ItemS = New List(Of GRUPUSERAKSESEntityClass)
        MeKONEKSI.TUTUPKONEKSI()
        COMMAND = New SqlCommand("SELECT * FROM GrupUserTabel ORDER BY IdGrupUser ASC", MeKONEKSI.BUKAKONEKSI)
        DTR = COMMAND.ExecuteReader
        If DTR.HasRows = True Then
            While DTR.Read
                ItemS.Add(New GRUPUSERAKSESEntityClass(DTR("IdGrupUser"), DTR("IdGrupUser") & " - " & DTR("NamaGrupUser")))
            End While
        End If
        Return ItemS
    End Function

    Public Overridable Function CARI_AKSES(ByVal xAKSES As String, ByVal sAKSES As String) As String
        MeKONEKSI.TUTUPKONEKSI()
        COMMAND.Connection = MeKONEKSI.BUKAKONEKSI
        COMMAND.CommandText = ("SELECT IdGrupUser, IdForm FROM AksesSistemTabel WHERE IdGrupUser ='" & xAKSES & "' AND IdForm='" & sAKSES & "'")
        DTR = COMMAND.ExecuteReader

        Return DTR.Read()
    End Function
#End Region

End Class




Buatlah Kelas BOUNDARY (Design Form sesuai gambar diatas)
#

Public Class SISTEMAKSES

#Region "EVENT"

    Private Sub SISTEMAKSES_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, MyBase.Activated
        DgvAKSES.DataSource = MeFormSistemControl.ISI_GRID
        Call FORMAT_GRID()
        Call COMBOBOX()
    End Sub

    Private Sub CbAKSES_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CbAKSES.CheckedChanged
        If CbAKSES.CheckState = CheckState.Checked Then
            For iRow = 0 To DgvAKSES.Rows.Count - 1
                DgvAKSES.Rows(iRow).Cells("AksesBox").Value = True
            Next
        Else
            For iRow = 0 To DgvAKSES.Rows.Count - 1
                DgvAKSES.Rows(iRow).Cells("AksesBox").Value = False
            Next
        End If
    End Sub

    Private Sub BtnSIMPAN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSIMPAN.Click
        Call HASIL_AKSES()
    End Sub
#End Region


#Region "OTOMATIS"

    Private Sub COMBOBOX()
        CmbGRUPUSER.DataSource = MeAksesControl.ISI_COMBO
        CmbGRUPUSER.DisplayMember = "NamaGrupUser"
    End Sub

    Private Sub FORMAT_GRID()
        With DgvAKSES
            'Atur Lebar Kolom
            .Columns(0).Width = 50
            .Columns(1).Width = 60
            .Columns(2).Width = 200
            .Columns(3).Width = 250
            .Columns(4).Width = 60
            .Columns(5).Width = 200

            'Mengunci Grid
            .Columns(1).ReadOnly = True
            .Columns(2).ReadOnly = True
            .Columns(3).ReadOnly = True
            .Columns(4).ReadOnly = True
            .Columns(5).ReadOnly = True
        End With
    End Sub

#End Region


#Region "ACTION"

    Dim AKSES As String

    Private Sub SAVEDATA()

        For iRow = 0 To DgvAKSES.Rows.Count - 1
            'AksesBox = Nama CheckBox yang ada di DatagridView
            If DgvAKSES.Rows(iRow).Cells("AksesBox").Value Then
                AKSES = "1"
            Else
                AKSES = "0"
            End If
            'Class Proses DML (Simpan Data)
            MeAksesEntity.IdGrupUser = Mid(CmbGRUPUSER.Text, 1, 6)
            MeAksesEntity.IdForm = DgvAKSES.Rows(iRow).Cells(1).Value
            MeAksesEntity.AksesForm = AKSES
            MeAksesControl.SIMPAN_DATA(MeAksesEntity)
        Next iRow

        MsgBox("DATA HAS BEEN SAVED !!", MsgBoxStyle.Information, Me.Text)
        Me.DgvAKSES.DataSource = Nothing

        CbAKSES.CheckState = CheckState.Unchecked
        DgvAKSES.DataSource = MeFormSistemControl.ISI_GRID
        Call FORMAT_GRID()
    End Sub

    Private Sub EDITDATA()

        For iRow = 0 To DgvAKSES.Rows.Count - 1
            'AksesBox = Nama CheckBox yang ada di DatagridView
            If DgvAKSES.Rows(iRow).Cells("AksesBox").Value Then
                AKSES = "1"
            Else
                AKSES = "0"
            End If
            'Class Proses DML (Edit Data)
            MeAksesEntity.IdGrupUser = Mid(CmbGRUPUSER.Text, 1, 6)
            MeAksesEntity.IdForm = DgvAKSES.Rows(iRow).Cells(1).Value
            MeAksesEntity.AksesForm = AKSES
            MeAksesControl.EDIT_DATA(MeAksesEntity)
        Next iRow

        MsgBox("DATA HAS BEEN SAVED BY EDITED !!", MsgBoxStyle.Information, Me.Text)
        Me.DgvAKSES.DataSource = Nothing

        CbAKSES.CheckState = CheckState.Unchecked
        DgvAKSES.DataSource = MeFormSistemControl.ISI_GRID
        Call FORMAT_GRID()
    End Sub

    Private Sub HASIL_AKSES()
        Dim IdGrup = Mid(CmbGRUPUSER.Text, 1, 6)
        Dim IdForm = DgvAKSES.Rows(0).Cells(1).Value

        If MeAksesControl.CARI_AKSES(IdGrup, IdForm) = True Then
            Call EDITDATA()
        Else
            Call SAVEDATA()
        End If
    End Sub
#End Region

  
End Class