SQL SERVER CLASS

PREVIEW :

DOWNLOAD CONTOH PROJECT 

CLASS :

Imports System.Data.SqlClient

Public Class clsSQL

    Public Enum XmlType As Short
        Normal = 0
        Schema = 1
    End Enum

    Private m_StrConnectionString As String = String.Empty
    Private m_Tag As String = String.Empty

    Public Event OnError(ByVal strProced As String, ByVal objEx As Exception)

    Public Sub New(ByVal strConnectionString As String)
        m_StrConnectionString = strConnectionString
    End Sub

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub

    Public ReadOnly Property About() As String
        Get
            Return "KABEH WONG BAKAL MATI" & vbNewLine & _
                   "GARI OPO SING AREP DITINGGAL" & vbNewLine & _
                   "ORA USAH NGAREP WONG ELING MARANG TINGGALANE DEWE" & vbNewLine & _
                   "NGAREPO PENGERAN BAKAL NOMPO" & vbNewLine & _
                   "MUNG KUI SING GAWE MESEM NALIKO SEDO"
        End Get
    End Property

    Public Property ConnectionString() As String
        Get
            Return m_StrConnectionString
        End Get
        Set(ByVal strValue As String)
            m_StrConnectionString = strValue
        End Set
    End Property

    Public Property Tag() As String
        Get
            Return m_Tag
        End Get
        Set(ByVal strValue As String)
            m_Tag = strValue
        End Set
    End Property

    Public Function TestConnection() As Boolean
        Try
            Dim objConnection As New SqlConnection(m_StrConnectionString)
            objConnection.Open()

            Select Case objConnection.State
                Case ConnectionState.Broken, ConnectionState.Closed : TestConnection = False
                Case Else : TestConnection = True
            End Select

            objConnection.Close()
            objConnection.Dispose()
            objConnection = Nothing

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Execute(ByVal strQuery As String) As Boolean
        Try
            Dim objConnection As New SqlConnection(m_StrConnectionString)
            Dim objCommand = New SqlCommand(strQuery, objConnection)

            objConnection.Open()

            objCommand.ExecuteNonQuery()

            objConnection.Close()
            objConnection.Dispose()
            objConnection = Nothing
            objCommand = Nothing

            Return True

        Catch ex As Exception
            RaiseEvent OnError("Execute", ex)
            Return False
        End Try
    End Function

    Public Function ToDataReader(ByVal strQuery As String) As SqlDataReader
        Try
            Dim objDR As System.Data.SqlClient.SqlDataReader
            Dim objConnection As New SqlConnection(m_StrConnectionString)
            'Abre a coneção
            objConnection.Open()
            'Define o comando
            Dim objSqlCommand As New System.Data.SqlClient.SqlCommand(strQuery, objConnection)
            'Executa o reader
            objDR = objSqlCommand.ExecuteReader
            Return objDR
        Catch ex As Exception
            RaiseEvent OnError("KeDataReader", ex)
            Return Nothing
        End Try
    End Function

    Public Function ToDataSet(ByVal strQuery As String, _
                     Optional ByVal strTable As String = "") As DataSet
        Try
            Dim objConnection As New SqlConnection(m_StrConnectionString)
            'Cria o objecto
            Dim objCommand = New SqlCommand(strQuery, objConnection)
            Dim objDataSet As New DataSet
            'Cria o sql DataAdapter
            Dim objSqlDataAdapter As SqlDataAdapter = New SqlDataAdapter(objCommand)

            'Verifica se foi defenido a tabela
            If strTable = "" Then _
                 objSqlDataAdapter.Fill(objDataSet) _
            Else objSqlDataAdapter.Fill(objDataSet, strTable)

            objConnection.Close()
            objConnection.Dispose()

            objConnection = Nothing
            objCommand = Nothing

            Return objDataSet

        Catch ex As Exception
            RaiseEvent OnError("KeDataSet", ex)
            Return Nothing
        End Try
    End Function

    Public Function ToDataSetFromXML(ByVal strPath As String, _
                                     ByVal iXmlType As XmlType) As DataSet
        Try
            'Cria o objecto
            Dim objDataSet As New DataSet

            If iXmlType = XmlType.Normal Then
                objDataSet.ReadXml(strPath)
            ElseIf iXmlType = XmlType.Schema Then
                objDataSet.ReadXmlSchema(strPath)
            End If

            Return objDataSet

        Catch ex As Exception
            RaiseEvent OnError("KeDataSetFromXML", ex)
            Return Nothing
        End Try
    End Function

    Public Function ToXML(ByVal strQuery As String, _
                          ByVal strOutPut As String, _
                          ByVal iXmlType As XmlType, _
                 Optional ByVal strTable As String = "", _
                 Optional ByVal strNamespace As String = "", _
                 Optional ByVal strDataSetName As String = "") As Boolean
        Try
            Dim objDataSet As New DataSet

            Dim objConnection As New SqlConnection(m_StrConnectionString)
            Dim objCommand = New SqlCommand(strQuery, objConnection)
            Dim objSqlDataAdapter As SqlDataAdapter = New SqlDataAdapter(objCommand)

            If Not strNamespace = "" Then objDataSet.Namespace = strNamespace
            If Not strDataSetName = "" Then objDataSet.DataSetName = strDataSetName

            'Verifica se foi defenido a tabela
            If strTable = "" Then _
                 objSqlDataAdapter.Fill(objDataSet) _
            Else objSqlDataAdapter.Fill(objDataSet, strTable)

            If iXmlType = XmlType.Normal Then
                objDataSet.WriteXml(strOutPut)
            ElseIf iXmlType = XmlType.Normal Then
                objDataSet.WriteXmlSchema(strOutPut)
            End If

            objConnection.Close()
            objDataSet.Dispose()
            objConnection.Dispose()

            objConnection = Nothing
            objCommand = Nothing
            objDataSet = Nothing

            Return True

        Catch ex As Exception
            RaiseEvent OnError("KEXML", ex)
            Return False
        End Try
    End Function

    Public Function ToDataGrid(ByVal objDataGrid As DataGridView, _
                               ByVal strQuery As String, _
                      Optional ByVal strTable As String = "") As Boolean
        Try
            Dim objConnection As New SqlConnection(m_StrConnectionString)
            Dim objCommand = New SqlCommand(strQuery, objConnection)
            Dim objSqlDataAdapter As SqlDataAdapter = New SqlDataAdapter(objCommand)
            Dim objDataSet As New DataSet

            'Verifica se foi defenido a tabela
            If strTable = "" Then _
                 objSqlDataAdapter.Fill(objDataSet) _
            Else objSqlDataAdapter.Fill(objDataSet, strTable)

            objDataGrid.DataSource = objDataSet.Tables(0)

            objConnection.Close()
            objConnection.Dispose()
            objDataSet.Dispose()

            objConnection = Nothing
            objCommand = Nothing
            objDataSet = Nothing

            Return True

        Catch ex As Exception
            RaiseEvent OnError("KEDataGrid", ex)
            Return False
        End Try

    End Function

    Public Function ToListView(ByVal objListView As ListView, _
                               ByVal strQuery As String, _
                      Optional ByVal strTable As String = "", _
                      Optional ByVal intDefautColumSize As Integer = 100) As Boolean
        Try
            Dim objConnection As New SqlConnection(m_StrConnectionString)
            Dim objCommand = New SqlCommand(strQuery, objConnection)
            Dim objSqlDataAdapter As SqlDataAdapter = New SqlDataAdapter(objCommand)
            Dim objDataSet As New DataSet

            'Verifica se foi defenido a tabela
            If strTable = "" Then _
                 objSqlDataAdapter.Fill(objDataSet) _
            Else objSqlDataAdapter.Fill(objDataSet, strTable)

            If objDataSet.Tables(0).Rows.Count > 0 Then
                objListView.Items.Clear()
                objListView.Columns.Clear()
                Dim i, y As Integer
                Dim intColCount As Integer
                intColCount = objDataSet.Tables(0).Columns.Count - 1
                'Adiciona as colunas
                For i = 0 To intColCount
                    objListView.Columns.Add(objDataSet.Tables(0).Columns(i).ToString, intDefautColumSize)
                Next
                'Adiciona os registos
                Dim objLVWItem As ListViewItem
                For i = 1 To objDataSet.Tables(0).Rows.Count - 1
                    'Tem em conta valores NULL
                    If Not IsDBNull(objDataSet.Tables(0).Rows.Item(i).Item(0)) Then _
                         objLVWItem = objListView.Items.Add(objDataSet.Tables(0).Rows.Item(i).Item(0).ToString) _
                    Else objLVWItem = objListView.Items.Add("")
                    'Tem em conta valores NULL
                    For y = 1 To intColCount
                        If Not IsDBNull(objDataSet.Tables(0).Rows.Item(i).Item(y).ToString) Then _
                             objLVWItem.SubItems.Add(objDataSet.Tables(0).Rows.Item(i).Item(y).ToString) _
                        Else objLVWItem.SubItems.Add("")
                    Next
                Next
            End If

            objConnection.Close()
            objDataSet.Dispose()
            objConnection.Dispose()
            objSqlDataAdapter.Dispose()

            objCommand = Nothing
            objDataSet = Nothing
            objConnection = Nothing
            objSqlDataAdapter = Nothing

            Return True

        Catch ex As Exception
            RaiseEvent OnError("KeListView", ex)
            Return False
        End Try
    End Function

    Public Function ToTextBox(ByVal objTextBox As TextBox, _
                              ByVal strQuery As String, _
                     Optional ByVal strTable As String = "", _
                     Optional ByVal intSepTabs As Integer = 1) As Boolean
        Try
            Dim objConnection As New SqlConnection(m_StrConnectionString)
            Dim objCommand = New SqlCommand(strQuery, objConnection)
            Dim objSqlDataAdapter As SqlDataAdapter = New SqlDataAdapter(objCommand)
            Dim objDataSet As New DataSet

            'Verifica se foi defenido a tabela
            If strTable = "" Then _
                 objSqlDataAdapter.Fill(objDataSet) _
            Else objSqlDataAdapter.Fill(objDataSet, strTable)

            Dim strTabs As String = String.Empty
            Dim strTemp As String = String.Empty
            Dim x As Integer

            For x = 1 To intSepTabs
                strTabs &= vbTab
            Next

            If objDataSet.Tables(0).Rows.Count > 0 Then
                Dim i, y As Integer
                Dim intColCount As Integer

                objTextBox.Text = ""

                intColCount = objDataSet.Tables(0).Columns.Count - 1
                'Adiciona as colunas
                For i = 0 To intColCount
                    strTemp &= objDataSet.Tables(0).Columns(i).ToString & strTabs
                Next
                strTemp &= vbNewLine
                'For i = 0 To intColCount
                '    strTemp &= "---" & strTabs & vbTab
                'Next
                strTemp &= vbNewLine

                'Adiciona os registos
                For i = 1 To objDataSet.Tables(0).Rows.Count - 1
                    'Tem em conta valores NULL
                    If Not IsDBNull(objDataSet.Tables(0).Rows.Item(i).Item(0)) Then _
                         strTemp &= objDataSet.Tables(0).Rows.Item(i).Item(0).ToString & strTabs _
                    Else strTemp &= " " & strTabs
                    'Tem em conta valores NULL
                    For y = 1 To intColCount
                        If Not IsDBNull(objDataSet.Tables(0).Rows.Item(i).Item(y).ToString) Then _
                             strTemp &= objDataSet.Tables(0).Rows.Item(i).Item(y).ToString & strTabs _
                        Else strTemp &= " " & strTabs
                    Next
                    strTemp &= vbNewLine
                Next
                objTextBox.Text = strTemp
            End If

            objConnection.Close()
            objDataSet.Dispose()
            objConnection.Dispose()
            objSqlDataAdapter.Dispose()

            objCommand = Nothing
            objDataSet = Nothing
            objConnection = Nothing
            objSqlDataAdapter = Nothing

            Return True

        Catch ex As Exception
            RaiseEvent OnError("KeListView", ex)
            Return False
        End Try

    End Function

End Class

PENGGUNAAN :

Public Class frmMain

    Private WithEvents mSQL As clsSQL

    Private Sub btnAbout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAbout.Click
        If IsNothing(mSQL) Then
            mSQL = New clsSQL(txtConnString.Text.ToString)
        End If
        MsgBox(mSQL.About, MsgBoxStyle.Information)
    End Sub

    Private Sub SetIDE(ByVal oObject As Object)
        txtGrid1.Visible = False
        lstView1.Visible = False
        dgView1.Visible = False
        oObject.Visible = True
    End Sub

    Private Sub mSQL_OnError(ByVal strProced As String, ByVal objEx As System.Exception) Handles mSQL.OnError
        MsgBox(objEx.Message, MsgBoxStyle.Exclamation, strProced)
    End Sub

    Private Sub btnTestConn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTestConn.Click
        If Not IsNothing(mSQL) Then
            mSQL = Nothing
        End If

        mSQL = New clsSQL(txtConnString.Text.ToString)

        If mSQL.TestConnection Then
            MsgBox("Test Koneksi OK", MsgBoxStyle.Information)
            grpOp.Enabled = True
        Else
            MsgBox("Test Koneksi Gagal", MsgBoxStyle.Exclamation)
            grpOp.Enabled = False
        End If
        'Set object visible ..
        SetIDE(Me)
    End Sub

    Private Sub btnToDataGrid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnToDataGrid.Click
        Try
            mSQL.ToDataGrid(dgView1, txtQuery.Text.ToString)
            'Set object visible ..
            SetIDE(dgView1)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub btnToListView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnToListView.Click
        Try
            mSQL.ToListView(lstView1, txtQuery.Text.ToString)
            'Set object visible ..
            SetIDE(lstView1)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub btnToTextBox_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnToTextBox.Click
        Try
            mSQL.ToTextBox(txtGrid1, txtQuery.Text.ToString)
            'Set object visible ..
            SetIDE(txtGrid1)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub btnExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecute.Click
        Try
            If mSQL.Execute(txtQuery.Text.ToString) Then
                MsgBox("Perintah SQL Error.", MsgBoxStyle.Information)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub btnToXML_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnToXML.Click
        Dim strTemp As String
        Try
            ofdSave.FileName = "MASSEMAR"
            ofdSave.Filter = "SEMAR XML (*.xml)|*.xml"
            If Me.ofdSave.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                strTemp = ofdSave.FileName
            Else
                Exit Sub
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try

        Try
            If mSQL.ToXML(txtQuery.Text.ToString, strTemp, clsSQL.XmlType.Normal) Then
                MsgBox("XML file exported with success.", MsgBoxStyle.Information)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub btnToDataReader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnToDataReader.Click
        Dim objDR As System.Data.SqlClient.SqlDataReader
        Try
            objDR = mSQL.ToDataReader(txtQuery.Text.ToString)

            Do While objDR.Read()
                txtGrid1.Text &= vbCrLf & objDR.Item(0)
            Loop

            objDR.Close()
            objDR = Nothing

            SetIDE(txtGrid1)

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub btnToDataSet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnToDataSet.Click
        Dim objDataSet As New DataSet
        Try
            objDataSet = mSQL.ToDataSet(txtQuery.Text.ToString)
            If Not IsNothing(objDataSet) Then

                txtGrid1.Text = "KeDataSet - " & objDataSet.Tables(0).Rows.Count & " row(s) affected"
                SetIDE(txtGrid1)

                objDataSet.Dispose()
                objDataSet = Nothing
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub btnToDataSetFromXML_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnToDataSetFromXML.Click
        Dim strTemp As String
        Try
            ofdOpen.FileName = "MASSEMAR"
            ofdOpen.Filter = "SEMAR XML (*.xml)|*.xml"
            If Me.ofdOpen.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                strTemp = ofdOpen.FileName
            Else
                Exit Sub
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try

        Dim objDataSet As New DataSet
        Try
            objDataSet = mSQL.ToDataSetFromXML(strTemp, clsSQL.XmlType.Normal)
            If Not IsNothing(objDataSet) Then

                txtGrid1.Text = "KeDataSetFromXML - " & objDataSet.Tables(0).Rows.Count & " row(s) affected"
                SetIDE(txtGrid1)

                objDataSet.Dispose()
                objDataSet = Nothing
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

End Class