SQL Class (VB.NET) [Repositorio Codigo]

'*********************************************
'Copyright(C) fLaSh - Carlos.DF 2008
'Email: [email protected]
'*********************************************
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.IO
Public Class clsSQL
    Implements System.IDisposable

#Const DEBUG_MODE = True

#Region "== Variables =="

    ''' <summary>
    ''' Enum para o encoding do XML
    ''' </summary>
    ''' <remarks></remarks>
    Public Enum XmlType As Short
        Normal = 0
        Schema = 1
    End Enum

    ''' <summary>
    ''' Guarda a coneção á bd defenida ao iniciar a class
    ''' </summary>
    ''' <remarks></remarks>
    Private m_sConnectionString As String = String.Empty

    ''' <summary>
    ''' Esta variavel de de certa forma como contentor de código
    ''' é similar á propriedade dos objectos TAG
    ''' </summary>
    ''' <remarks></remarks>
    Private m_Tag As String = String.Empty

    Private m_blnDisposedValue As Boolean

    Friend WithEvents m_objConnection As SqlConnection
    Friend WithEvents m_objCommand As SqlCommand

#End Region

#Region "== Class Events =="

    ''' <summary>
    ''' Quando a class é inicializada
    ''' </summary>
    ''' <param name="sConnectionString">Defeni a conexão do servidor</param>
    ''' <remarks></remarks>
    Public Sub New(ByVal sConnectionString As String)
        ConnectionString = sConnectionString
    End Sub

    ''' <summary>
    ''' Quando a class termina
    ''' </summary>
    ''' <remarks></remarks>
    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub

    ''' <summary>
    ''' Evento para correr todos os erros, é uma interface da class
    ''' </summary>
    ''' <param name="strProced"></param>
    ''' <param name="objEx"></param>
    ''' <remarks></remarks>
    Public Event OnError(ByVal strProced As String, ByVal objEx As Exception)

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal b As Boolean)
        If Not m_blnDisposedValue Then
            If b Then
                If m_objConnection IsNot Nothing Then
                    m_objConnection = Nothing
                End If
            End If
        End If
        m_blnDisposedValue = True
    End Sub
 
    Public Sub Dispose() Implements IDisposable.Dispose
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub

#End Region

#Region "== Privates =="

    ''' <summary>
    ''' Converte uma imaguem de um picture box para array byte
    ''' </summary>
    ''' <param name="oPictureBox">objecto</param>
    ''' <returns>imagem em array byte</returns>
    ''' <remarks></remarks>
    Private Function ConvPictureBoxToArray(ByVal oPictureBox As PictureBox) As Byte()
        Dim oStream As New System.IO.MemoryStream
        Dim bmp As New Bitmap(oPictureBox.Image)
        Try
            bmp.Save(oStream, Imaging.ImageFormat.Bmp)
            ConvPictureBoxToArray = oStream.ToArray
            bmp.Dispose()
            oStream.Close()
        Catch ex As Exception
            RaiseEvent OnError("ConvPictureBoxToArray", ex)
            Return Nothing
        End Try
    End Function

#End Region

#Region "== Propertys =="

    ''' <summary>
    ''' Informação do criador da class class :)
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property About() As String
        Get
            Return "Copyright(C)fLaSh - Carlos.DF" & vbNewLine & _
                   "E-Mail: [email protected]" & vbNewLine & _
                   "Phone: 966 506 396" & vbNewLine & _
                   "20 - February - 2008" & vbNewLine & _
                   "Braga - S. Victor - Portugal"
        End Get
    End Property

    ''' <summary>
    ''' Propriedade da conexão string
    ''' </summary>
    ''' <value>conexão string</value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property ConnectionString() As String
        Get
            Return m_sConnectionString
        End Get
        Set(ByVal sValue As String)
            m_sConnectionString = sValue
        End Set
    End Property

    ''' <summary>
    ''' Retorna o estado da conexão
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property ConnectionState() As Short
        Get
            Return m_objConnection.State
        End Get
    End Property

    ''' <summary>
    ''' Proiedade para armazenar código
    ''' </summary>
    ''' <svalue></svalue>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property Tag() As String
        Get
            Return m_Tag
        End Get
        Set(ByVal sValue As String)
            m_Tag = sValue
        End Set
    End Property

#End Region

#Region "== Publics =="

    ''' <summary>
    ''' Abre a conexão á base de dados
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function OpenConnection() As Boolean
        Try
            m_objConnection = New SqlConnection(m_sConnectionString)
            m_objConnection.Open()
            Select Case m_objConnection.State
                Case Data.ConnectionState.Open : Return True
                Case Else : Return False
            End Select
        Catch : End Try
    End Function

    ''' <summary>
    ''' Fecha a conexão da base de dados
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub CloseConnection()
        On Error Resume Next
        m_objConnection.Close()
    End Sub

    ''' <summary>
    ''' Faz um teste de conexão ao servidor
    ''' </summary>
    ''' <returns>True/False</returns>
    ''' <remarks></remarks>
    Public Function TestConnection(Optional ByVal bShowMsgBox As Boolean = False) As Boolean
        Try
            Select Case m_objConnection.State
                Case Data.ConnectionState.Broken, Data.ConnectionState.Closed : TestConnection = False
                Case Else : Return True
            End Select
        Catch ex As Exception
            If bShowMsgBox Then
                MsgBox(ex.Message)
            End If
        End Try
    End Function

    ''' <summary>
    ''' Executa um query para a bd
    ''' </summary>
    ''' <param name="sQuery"></param>
    ''' <returns>True/False</returns>
    ''' <remarks></remarks>
    Public Function Execute(ByVal sQuery As String) As Boolean
        Try
#If DEBUG_MODE Then
            Debug.WriteLine(sQuery)
#End If
            m_objCommand = New SqlCommand(sQuery, m_objConnection)

            m_objCommand.ExecuteNonQuery()

            m_objCommand = Nothing

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

    ''' <summary>
    ''' Executa um query para o objecto
    ''' </summary>
    ''' <param name="sQuery">Query string</param>
    ''' <returns>SqlDataReader</returns>
    ''' <remarks></remarks>
    Public Function ToDataReader(ByVal sQuery As String) As SqlDataReader
        Try
#If DEBUG_MODE Then
            Debug.WriteLine(sQuery)
#End If
            Dim objDR As System.Data.SqlClient.SqlDataReader

            Dim objCommand = New SqlCommand(sQuery, m_objConnection)

            'Executa o reader
            objDR = objCommand.ExecuteReader
            '
            Return objDR

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

    ''' <summary>
    ''' Executa um query para o objecto
    ''' </summary>
    ''' <param name="sQuery">Query string</param>
    ''' <param name="sTable"></param>
    ''' <returns>DataSet</returns>
    ''' <remarks></remarks>
    Public Function ToDataSet(ByVal sQuery As String, _
                     Optional ByVal sTable As String = "") As DataSet
        Try
#If DEBUG_MODE Then
            Debug.WriteLine(sQuery)
#End If
            m_objCommand = New SqlCommand(sQuery, m_objConnection)

            Dim objDataSet As New DataSet
            Dim objSqlDataAdapter As SqlDataAdapter = New SqlDataAdapter(m_objCommand)

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

            objSqlDataAdapter.Dispose()
            objSqlDataAdapter = Nothing

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

    ''' <summary>
    ''' Carrega dataset de um fiheiro xml
    ''' </summary>
    ''' <param name="sPath">directoria do xml</param>
    ''' <param name="iXmlType">enconding do xml (xml ou schema)</param>
    ''' <returns>DataSet</returns>
    ''' <remarks></remarks>
    Public Function ToDataSetFromXML(ByVal sPath As String, _
                                     ByVal iXmlType As XmlType) As DataSet
        Try
#If DEBUG_MODE Then
            Debug.WriteLine(sPath)
#End If
            'Cria o objecto
            Dim objDataSet As New DataSet

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

            Return objDataSet

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

    ''' <summary>
    ''' Exporta informação a partir de um query para xml
    ''' </summary>
    ''' <param name="sQuery">query string</param>
    ''' <param name="sOutPut">directuria do destino do ficheiro</param>
    ''' <param name="iXmlType">enconding do xml</param>
    ''' <param name="sTable">opcional nome da tabela</param>
    ''' <param name="sNamespace">espaço do nome</param>
    ''' <param name="sDataSetName">nome do dataset para o xml</param>
    ''' <returns>True/False</returns>
    ''' <remarks></remarks>
    Public Function ToXML(ByVal sQuery As String, _
                          ByVal sOutPut As String, _
                          ByVal iXmlType As XmlType, _
                 Optional ByVal sTable As String = "", _
                 Optional ByVal sNamespace As String = "", _
                 Optional ByVal sDataSetName As String = "") As Boolean
        Try
#If DEBUG_MODE Then
            Debug.WriteLine(sQuery)
#End If
            Dim objDataSet As New DataSet
            'Preenche o dataset
            objDataSet = ToDataSet(sQuery, sTable)
            'Verifica o enconding do XML
            If iXmlType = XmlType.Normal Then
                objDataSet.WriteXml(sOutPut)
            ElseIf iXmlType = XmlType.Normal Then
                objDataSet.WriteXmlSchema(sOutPut)
            End If

            objDataSet.Dispose()
            objDataSet = Nothing

            Return True

        Catch ex As Exception
            RaiseEvent OnError("ToXML", ex)
        End Try
    End Function

    ''' <summary>
    ''' Preenche um datagrid com um query
    ''' </summary>
    ''' <param name="oDataGrid">objecto</param>
    ''' <param name="sQuery">query string</param>
    ''' <param name="sTable">opcional nome da tabela</param>
    ''' <returns>True/False</returns>
    ''' <remarks></remarks>
    Public Function ToDataGrid(ByVal oDataGrid As DataGridView, _
                               ByVal sQuery As String, _
                      Optional ByVal sTable As String = "") As Boolean
        Try
#If DEBUG_MODE Then
            Debug.WriteLine(sQuery)
#End If
            Dim objDataSet As New DataSet
            'Preenche o dataset
            objDataSet = ToDataSet(sQuery, sTable)

            oDataGrid.DataSource = objDataSet.Tables(0)

            objDataSet.Dispose()
            objDataSet = Nothing

            Return True
        Catch ex As Exception
            RaiseEvent OnError("ToDataGrid", ex)
        End Try
    End Function

    ''' <summary>
    ''' Preenche um listview com um query 
    ''' </summary>
    ''' <param name="oListView">objecto</param>
    ''' <param name="sQuery">query string</param>
    ''' <param name="sTable">opcional nome da tabela</param>
    ''' <param name="iDefautColumSize">opcional tamnaho das colunas</param>
    ''' <returns>True/False</returns>
    ''' <remarks></remarks>
    Public Function ToListView(ByVal oListView As ListView, _
                               ByVal sQuery As String, _
                      Optional ByVal sTable As String = "", _
                      Optional ByVal iDefautColumSize As Integer = 100) As Boolean
        Try
#If DEBUG_MODE Then
            Debug.WriteLine(sQuery)
#End If
            Dim objDataSet As New DataSet
            'Preenche o dataset
            objDataSet = ToDataSet(sQuery, sTable)

            If objDataSet.Tables(0).Rows.Count > 0 Then
                oListView.BeginUpdate()
                oListView.Items.Clear()
                oListView.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
                    oListView.Columns.Add(objDataSet.Tables(0).Columns(i).ToString, iDefautColumSize)
                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 = oListView.Items.Add(objDataSet.Tables(0).Rows.Item(i).Item(0).ToString) _
                    Else objLVWItem = oListView.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
                oListView.EndUpdate()
            End If

            objDataSet.Dispose()
            objDataSet = Nothing

            Return True

        Catch ex As Exception
            RaiseEvent OnError("ToListView", ex)
        End Try
    End Function

    ''' <summary>
    ''' Preenche um textbox com um query 
    ''' </summary>
    ''' <param name="oTextBox">objecto</param>
    ''' <param name="sQuery">query string</param>
    ''' <param name="sTable">opcional nome da tabela</param>
    ''' <param name="iSepTabs">numero de Tabs a separar os campos</param>
    ''' <returns>True/False</returns>
    ''' <remarks></remarks>
    Public Function ToTextBox(ByVal oTextBox As TextBox, _
                              ByVal sQuery As String, _
                     Optional ByVal sTable As String = "", _
                     Optional ByVal iSepTabs As Integer = 1) As Boolean
        Try
#If DEBUG_MODE Then
            Debug.WriteLine(sQuery)
#End If
            Dim objDataSet As New DataSet
            'Preenche o dataset
            objDataSet = ToDataSet(sQuery, sTable)

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

            For x = 1 To iSepTabs
                strTabs &= vbTab
            Next

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

                oTextBox.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
                oTextBox.Text = strTemp
            End If

            objDataSet.Dispose()
            objDataSet = Nothing

            Return True

        Catch ex As Exception
            RaiseEvent OnError("ToListView", ex)
        End Try
    End Function

    ''' <summary>
    ''' Preenche um combox com um query 
    ''' </summary>
    ''' <param name="oComboBox">objecto</param>
    ''' <param name="sQuery">query string</param>
    ''' <param name="sField">name do campo</param>
    ''' <param name="sTable">opcional nome da tabela</param>
    ''' <returns>True/False</returns>
    ''' <remarks></remarks>
    Public Function ToComboBox(ByVal oComboBox As ComboBox, _
                               ByVal sQuery As String, _
                               ByVal sField As String, _
                      Optional ByVal sTable As String = "", _
                      Optional ByVal bSelectRow As Boolean = True) As Boolean
        Try
            Dim objDataSet As New DataSet
            'Preenche o dataset
            objDataSet = ToDataSet(sQuery, sTable)

            If objDataSet.Tables(0).Rows.Count > 0 Then
                oComboBox.Items.Clear()
                Dim i As Integer
                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 _
                         oComboBox.Items.Add(objDataSet.Tables(0).Rows.Item(i).Item(sField).ToString)
                Next
                ToComboBox = True
                If bSelectRow Then
                    oComboBox.Text = oComboBox.Items(0)
                End If
            Else
                ToComboBox = False
            End If

            objDataSet.Dispose()
            objDataSet = Nothing

            Return True

        Catch ex As Exception
            RaiseEvent OnError("ToListView", ex)
        End Try
    End Function

    ''' <summary>
    ''' Carrega uma imagem da bd para um PictureBox
    ''' </summary>
    ''' <param name="sQuery">query string</param>
    ''' <param name="sField">>opcional nome da tabela</param>
    ''' <param name="oPicBox">objecto</param>
    ''' <returns></returns>
    ''' <remarks>True/False</remarks>
    Public Function ImageToPictureBox(ByVal sQuery As String, _
                                      ByVal sField As String, _
                                      ByVal oPicBox As PictureBox) As Boolean
        Try
#If DEBUG_MODE Then
            Debug.WriteLine(sQuery)
#End If
            Dim objMS As MemoryStream
            Dim bitPic() As Byte = Nothing
            Dim bitBuffer(0) As Byte
            Dim bmpPic As Bitmap

            Dim objDR As SqlDataReader
            'Preeche o DataReader
            objDR = ToDataReader(sQuery)

            If objDR.Read() Then
                bitPic = objDR.Item(sField)
            Else
                Return False
            End If

            objDR.Close()
            objDR = Nothing

            If Not IsNothing(bitPic) Then
                'Converte imagem em binary para BMP
                bitBuffer = CType(bitPic, Byte())
                If bitBuffer.Length > 0 Then
                    objMS = New MemoryStream
                    objMS.Write(bitBuffer, 0, bitBuffer.Length)
                    bmpPic = New Bitmap(Image.FromStream(objMS))
                    oPicBox.Image = bmpPic
                End If
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            RaiseEvent OnError("ImageToPictureBox", ex)
        End Try
    End Function

    ''' <summary>
    ''' Guarda uma imagem na bd a partir de uma picture box
    ''' </summary>
    ''' <param name="sQuery">query string</param>
    ''' <param name="sField">opcional nome da tabela</param>
    ''' <param name="oPicBox">objecto</param>
    ''' <returns>True/False</returns>
    ''' <remarks></remarks>
    Public Function PictureBoxToDb(ByVal sQuery As String, _
                                   ByVal sField As String, _
                                   ByVal oPicBox As PictureBox) As Boolean
        Try
#If DEBUG_MODE Then
            Debug.WriteLine(sQuery)
#End If
            Dim bytPic() As Byte = ConvPictureBoxToArray(oPicBox)

            m_objCommand = New SqlCommand(sQuery, m_objConnection)

            m_objCommand.Parameters.AddWithValue("@" & sField, OleDbType.Binary).Value = bytPic
            m_objCommand.ExecuteNonQuery()

            m_objCommand = Nothing

            Return True

        Catch ex As Exception
            RaiseEvent OnError("PictureBoxToDb", ex)
        End Try
    End Function

    ''' <summary>
    ''' Faz um backup da base de dados
    ''' </summary>
    ''' <param name="sDir">destino da base de dados</param>
    ''' <param name="sFileName">nome da base de dados</param>
    ''' <param name="sPassword">opcional palavra passe</param>
    ''' <returns>true se ok</returns>
    ''' <remarks>false em caso de erro</remarks>
    Public Function BackUpDataBase(ByVal sDir As String, _
                                   ByVal sFileName As String, _
                          Optional ByVal sPassword As String = "") As Boolean
        Try
            Dim sQuery As String
            Dim sPams() As String = Split(m_sConnectionString.Trim, ";")
            Dim sBD As String = String.Empty

            'Extrai o nome da bd a partir da connection string..
            For Each sName As String In sPams
                If InStr(sName, "DATABASE") Then
                    sBD = sName.Substring(InStr(sName, "SERVER="))
                    If sBD.EndsWith(";") Then
                        sBD.Remove(sBD.LastIndexOf(";"))
                    End If
                End If
            Next

            sQuery = "BACKUP DATABASE '" & sBD & "' " & _
                         "TO DISK = '" & sDir & "' " & _
                         "WITH NAME = '" & sFileName & "', " & _
                         If(String.IsNullOrEmpty(sPassword), "", "PASSWORD = '" & sPassword & "', ") & _
                         "DIFFERENTIAL()"
            'Corre o comando
            Return Me.Execute(sQuery)
        Catch ex As Exception
            RaiseEvent OnError("BackUpDataBase", ex)
        End Try
    End Function

#End Region

#Region "== Extra =="

    ''' <summary>
    ''' Formatar a data para os comandos SQL
    ''' </summary>
    ''' <param name="dtData">data</param>
    ''' <returns>data string</returns>
    ''' <remarks>Formato: MM-DD-AAAA</remarks>
    Public Function EncodingDataSQL(ByVal dtData As Date) As String
        Try
            Return dtData.Month.ToString() & "-" & _
                   dtData.Day.ToString() & "-" & _
                   dtData.Year.ToString()
        Catch ex As Exception
            RaiseEvent OnError("EncodingDataSQL", ex)
            Return String.Empty
        End Try
    End Function

    ''' <summary>
    ''' Formata a data de SQL para o VB
    ''' </summary>
    ''' <param name="strData">data</param>
    ''' <returns>data string</returns>
    ''' <remarks>Formato: DD-MM-AAAA</remarks>
    Public Function DecodingDataSQL(ByVal strData As String) As Date
        Try
            Return CDate(Mid(strData, 1, 2) & "-" & _
                         Mid(strData, 4, 2) & _
                         Mid(strData, 6, 5))
        Catch ex As Exception
            RaiseEvent OnError("DecodingDataSQL", ex)
            Return Nothing
        End Try
    End Function

    ''' <summary>
    ''' Formata a data e hora de SQL para o VB
    ''' </summary>
    ''' <param name="strData">data e hora</param>
    ''' <returns>data/hora string</returns>
    ''' <remarks>Formato: DD-MM-AAAA HH:MM:SS</remarks>
    Public Function DecodingDataTimeSQL(ByVal strData As String) As Date
        Try
            Return CDate(Mid(strData, 4, 2) & "-" & _
                         Mid(strData, 1, 2) & "-" & _
                         Mid(strData, 7, 4) & " " & _
                         Mid(strData, 12, 2) & _
                         Mid(strData, 15, 2) & ":" & _
                         Mid(strData, 18, 2))
        Catch ex As Exception
            RaiseEvent OnError("DecodingDataTimeSQL", ex)
            Return Nothing
        End Try
    End Function

    ''' <summary>
    ''' formata a data e hora para os comandos SQL
    ''' </summary>
    ''' <param name="dtData">data e hora</param>
    ''' <returns>data/hora string</returns>
    ''' <remarks>Formato: MM-DD-AAAA HH:MM:SS</remarks>
    Public Function EncodingDataTimeSQL(ByVal dtData As Date) As String
        Try
            Return dtData.Month.ToString() & "-" & _
                   dtData.Day.ToString() & "-" & _
                   dtData.Year.ToString() & " " & _
                   dtData.Hour & ":" & _
                   dtData.Minute & ":" & _
                   dtData.Second
        Catch ex As Exception
            RaiseEvent OnError("EncodingDataTimeSQL", ex)
            Return String.Empty
        End Try
    End Function

    ''' <summary>
    ''' Verifica se uma campo é NULL
    ''' </summary>
    ''' <param name="sText">string</param>
    ''' <param name="bWithAsp">se com aspas</param>
    ''' <returns>NULL ou o mesmo valor do imput</returns>
    ''' <remarks></remarks>
    Public Function IsNULL(ByVal sText As String, _
                  Optional ByVal bWithAsp As Boolean = True) As String
        Try
            If IsNothing(sText) Or String.IsNullOrEmpty(sText) Then _
                 Return "NULL" _
            Else Return IIf(bWithAsp = True, "'" & sText & "'", sText)
        Catch ex As Exception
            RaiseEvent OnError("IsNULL", ex)
            Return String.Empty
        End Try
    End Function

    ''' <summary>
    ''' Função para evitar erro de syntax nos comandos SQL..
    ''' </summary>
    ''' <param name="sText">string</param>
    ''' <returns>string corregida</returns>
    ''' <remarks></remarks>
    Public Function ReplacePelicas(ByVal sText As String) As String
        Try
            If Not InStr(sText, "'") = 0 Then _
                 Return Replace(sText, "'", "''") _
            Else Return ""
        Catch ex As Exception
            RaiseEvent OnError("ReplacePelicas", ex)
            Return String.Empty
        End Try
    End Function

    'Simples função para criar a connection string..
    Public Function BuildConnString(ByVal sServerName As String, ByVal sUserID As String, ByVal sPassword As String, ByVal sDataBase As String) As String
        Dim strConn As String
        strConn = "SERVER=" & sServerName & _
                  ";USER ID=" & sUserID & _
                  ";PASSWORD=" & sPassword & _
                  ";DATABASE=" & sDataBase
        Return strConn
    End Function

#End Region
 
End Class

        Using oSQL As New clsSQL("SERVER=" & sServerName & _
                                ";USER ID=" & sUserID & _
                                ";PASSWORD=" & sPassword & _
                                ";DATABASE=" & sDataBase)
            If oSQL.OpenConnection Then
                If oSQL.Execute("DELETE FROM users = WHERE name = 'fLaSh'") Then
                    MessageBox.Show("OK")
                Else
                    MessageBox.Show("Erro")
                End If
            Else
                MessageBox.Show("Erro")
            End If
        End Using