1. Este site usa cookies. Ao continuar a usar este site está a concordar com o nosso uso de cookies. Saber Mais.

SQL Class (VB.NET) [Repositorio Codigo]

Discussão em 'Programação' iniciada por fLaSh_CF, 20 de Outubro de 2008. (Respostas: 0; Visualizações: 3770)

  1. fLaSh_CF

    fLaSh_CF Banido

    Boas;

    Coloco aqui uma class para fácil acesso ao servidor MS SQL, crei-a á alguns meses atrás para um projecto que realizei.

    A classe "tem tudo em um", desde correr simples comandos, como fazer teste de ligação, inserir imagens na base de dados, correr comandos para objectos, etc..

    clsSQL:
    Código:
    '*********************************************
    '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
    
    Pequeno exemplo de como utilizar:
    Código:
            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
    Compr.
     

Partilhar esta Página