[VB.NET] Classe de MSSQL para operação básica

Option Strict Off

'                                                             *                                       
'                                                              W@                                   
'                                                                WW                                
'                                                                 WW*                             
'                                                                   #WW                           
'                                                                     WWW                         
'                                                                       WW#                       
'                                                                        #WW                      
'                                                                          WW#                    
'                                                                           WWW                   
'  WWWWWWW       WWWWWWW*     WWW          WWWWW,   #WW     WWWW   WWWWWWW   WWW                  
' WWWWWWWWW     WWWWWWWWWW    WWW         WWWWWWW,  #WW    WWWW    WWWWWWW    WWW                 
' WWW   @WWW   WWWW    WWW@   WWW        +WW+  WWW  #WW   WWWW     WWWWW@#     WWW                
' WW     WWW   WWW      WWW   WWW        WWW   WWW  #WW  *WWW      WW           WWW               
' WWW         +WW,      +WW,  WWW              WWW  #WW +WWW      :WW @#        WWWW              
' WWWWWW@     WWW        WW+  WWW              WWW  #WW,WWW@      WWWWWWW#       WWW              
'  WWWWWWWW   WWW        WW@  WWW             WWW   #WWWWWWW      WWWWWWWW,      WWWW             
'     WWWWWW  WWW        WW#  WWW            WWW    #WWWW WWW     @W+   WWW      ,WWW             
'        WWW  @WW       ,WW.  WWW           WWW+    #WWW  WWW           WWW       WWW             
'WWW     #WW  ,WW#   W  WWW   WWW          WWW.     #WW    WWW          WWW       WWW             
'WWW     WWW   WWW  WWWWWWW   WWW         WWW       #WW    WWWW   WWW   WWW      @WWW             
' WWWW  WWWW   :WWWW  WWWW    WWWWWWW@#* .WWWWWWWW  #WW     WWW   WWWW WWW.      WWWW             
' +WWWWWWWW,    +WWWWWWWWW#   WWWWWWWWWW WWWWWWWWW  #WW     +WWW   WWWWWWW      WWWW+             
'   WWWWW@        WWWWW@WWWW  WWWWWWWWWW WWWWWWWWW  #WW      WWW+   WWWW+      WWWW#              
'                        W.                                                 WWWWW                
'                                                                         :WWWWW@
'SQL simplified. By Sérgio Ribeiro
'[email protected]

'ChangeLog
' - Removed Singleton pattern implementation

Public Class SQL2k5

#Region "Declarations"

    Const myVer As String = "1.1.STABLE"
    Private DSource As String
    Private Dbase As String
    Private DBUser As String
    Private DBUPassword As String
    Private TypeOfCommand As Integer

    Private Log As New ArrayList
    Private ErrorLog As New ArrayList

    Private HaveIFailed As Boolean
    Private ShowError As Boolean

    Private ErrorTitle As String
    Private ErrorMsg As String

    Private SQLP() As String
    Private SQLParams() As SqlClient.SqlParameter

    Public Enum CommandType
        StoredProcedure = 0
        Text = 1
    End Enum

#End Region

#Region "Properties"

    ''' <summary>
    ''' Determine if an error dialog should appear when errors occur
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>This error can be customized</remarks>
    Public Property DisplayErrorMessages() As Boolean
        Get
            LogEntry("Display error messages value was delivered")
            Return ShowError
        End Get
        Set(ByVal value As Boolean)
            ShowError = value
            LogEntry("Display error messages value was updated. Value: " & value.ToString)
        End Set
    End Property

    ''' <summary>
    ''' Get the current class version
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property ClassVersion() As String
        Get
            Return myVer
            LogEntry("My version value was delivered")
        End Get
    End Property

    ''' <summary>
    ''' Checks if the last query was failed
    ''' </summary>
    Public ReadOnly Property Failed() As Boolean
        Get
            LogEntry("Last query success status was delivered")
            Return HaveIFailed
        End Get
    End Property

    ''' <summary>
    ''' Set/Get the type of command this instance handles
    ''' </summary>
    Public Property Type() As CommandType
        Get
            LogEntry("Command type value was delivered")
            Return CType(TypeOfCommand, CommandType)
        End Get
        Set(ByVal value As CommandType)
            TypeOfCommand = value
            LogEntry("Type of command value was updated. Value: " & value.ToString)
        End Set
    End Property

    ''' <summary>
    ''' Sets the SQL parameters to send along with the query
    ''' </summary>
    Public WriteOnly Property QueryParams() As String
        Set(ByVal value As String)
            Try
                SQLP = Split(value, ";")

                Dim x As Integer

                ReDim SQLParams(SQLP.Length - 1)

                For x = 0 To SQLP.Length - 1
                    Dim ParamParse() As String = Split(SQLP(x), ",")

                    If ParamParse.Length <> 0 Then SQLParams(x) = New SqlClient.SqlParameter(ParamParse(0), ParamParse(1))

                Next

                LogEntry("Query parameters value was updated. Value: " & value)
            Catch ex As Exception
                ProcessError(ex.Message)
            End Try

        End Set
    End Property

    ''' <summary>
    ''' Set/Get the data source for this instance
    ''' </summary>
    Public Property DataSource() As String
        Get
            LogEntry("Data source value was delivered")
            Return DSource
        End Get
        Set(ByVal value As String)
            LogEntry("Data source value was updated. Value: " & value)
            DSource = value
        End Set
    End Property

    ''' <summary>
    ''' Set/Get the database name for this instance
    ''' </summary>
    Public Property DataBase() As String
        Get
            LogEntry("Data base value was delivered")
            Return Dbase
        End Get
        Set(ByVal value As String)
            LogEntry("Data base value was updated. Value: " & value)
            Dbase = value
        End Set
    End Property

    ''' <summary>
    ''' Set/Get the database username for this instance
    ''' </summary>
    Public Property Username() As String
        Get
            LogEntry("Username value was delivered")
            Return DBUser
        End Get
        Set(ByVal value As String)
            LogEntry("User value was updated. Value: " & value)
            DBUser = value
        End Set
    End Property

    ''' <summary>
    ''' Set/Get the database username password for this instance
    ''' </summary>
    Public Property Password() As String
        Get
            LogEntry("Password value was delivered")
            Return DBUPassword
        End Get
        Set(ByVal value As String)
            LogEntry("Password value was updated. Value: " & value)
            DBUPassword = value
        End Set
    End Property

#End Region

#Region "Exposed Methods"

    ''' <summary>
    ''' Customize the information to be shown when an error occurs
    ''' </summary>
    ''' <param name="Title">Error message title to be displayed when an error occurs</param>
    ''' <param name="Message">Message to be displayed when an error occurs (check remarks)</param>
    ''' <remarks>%%errormsg%% will be replaced by the error message. If not found, will display it at the end of the message.</remarks>
    Public Sub ErrorMessageInformation(ByVal Title As String, ByVal Message As String)
        ErrorTitle = Title
        ErrorMsg = Message
        LogEntry("Error message information was updated")
    End Sub

    ''' <summary>
    ''' Configures this instance of the class
    ''' </summary>
    ''' <param name="DataSource">Data source for this instance</param>
    ''' <param name="Database">Database name for this instance</param>
    ''' <param name="User">Database username for this instance</param>
    ''' <param name="Password">Database user password for this instance</param>
    ''' <remarks></remarks>
    Public Sub ConfigureMe(ByVal DataSource As String, ByVal Database As String, ByVal User As String, ByVal Password As String, ByVal Type As CommandType)
        DSource = DataSource
        Dbase = Database
        DBUser = User
        DBUPassword = Password
        TypeOfCommand = Type

        LogEntry("Configuration successful. Values: " & DataSource & " " & Database & " " & User & " " & Password)
    End Sub

    '''<summary>Database values for connection initialization</summary>
    ''' <param name="DataSource">Data source for this instance</param>
    ''' <param name="Database">Database name for this instance</param>
    ''' <param name="User">Database username for this instance</param>
    ''' <param name="Password">Database user password for this instance</param>
    ''' <param name="Type">Type of queries this instance will handle</param>
    Sub New(Optional ByVal DataSource As String = "null", Optional ByVal Database As String = "null", _
            Optional ByVal User As String = "null", Optional ByVal Password As String = "null", _
            Optional ByVal Type As CommandType = 1)

        DSource = DataSource
        Dbase = Database
        DBUser = User
        DBUPassword = Password
        TypeOfCommand = Type

        LogEntry("Class instance created!")
        LogEntry("Configuration successful. Values: " & DataSource & " " & Database & " " & User & " " & Password)

    End Sub

    ''' <summary>
    ''' Executes a given query in the specified database
    ''' </summary>
    ''' <param name="Query">The command that you want to execute</param>
    ''' <param name="Timeout">Timeout for the query, in seconds</param>
    ''' <remarks></remarks>
    ''' <returns>Filled datatable, or nothing</returns>
    Public Function ExecuteQuery(ByVal Query As String, Optional ByVal Timeout As Integer = 15) As System.Data.DataTable

        HaveIFailed = False
        Dim ConnString As String = "Data Source=" & DSource & ";Initial Catalog=" & Dbase & ";Persist Security Info=True;User ID=" & DBUser & ";Password=" & DBUPassword
        LogEntry("Connection string built: " & ConnString)
        Dim SQLConn As SqlClient.SqlConnection = New SqlClient.SqlConnection(ConnString)
        LogEntry("Connection created.")
        Dim SQLCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand(Query, SQLConn)

        Select Case TypeOfCommand
            Case 0
                SQLCommand.CommandType = Data.CommandType.StoredProcedure
            Case 1
                SQLCommand.CommandType = Data.CommandType.Text
        End Select

        SQLCommand.CommandTimeout = Timeout

        Try
            If IsNothing(SQLParams) = False Then
                If SQLParams.Length > 0 Then
                    SQLCommand.Parameters.AddRange(SQLParams)
                    LogEntry("Parameters successfully added")
                End If
            End If
        Catch ex As Exception
            ProcessError(ex.Message)
        End Try

        Try
            SQLConn.Open()
            LogEntry("Connection opened.")

            Dim DA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(SQLCommand)

            Dim DT As New DataTable
            DT.Locale = System.Globalization.CultureInfo.InvariantCulture

            DA.Fill(DT)
            LogEntry("Query executed successfully.")

            SQLConn.Close()
            LogEntry("Connection closed.")
            SQLParams = Nothing
            Return DT

        Catch ex As Exception
            ProcessError(ex.Message)
            HaveIFailed = True
            SQLParams = Nothing
            If SQLConn.State <> ConnectionState.Closed Then SQLConn.Close()
            Return Nothing
        End Try

        Return Nothing

    End Function

    ''' <summary>
    ''' Test the SQL Connection within this instance
    ''' </summary>
    ''' <remarks></remarks>
    ''' <returns>True if the connection is active, false if it's not</returns>
    Public Function TestConnection() As Boolean

        Dim ConnString As String = "Data Source=" & DSource & ";Initial Catalog=" & Dbase & ";Persist Security Info=True;User ID=" & DBUser & ";Password=" & DBUPassword
        LogEntry("Connection string built: " & ConnString)
        Dim SQLConn As SqlClient.SqlConnection = New SqlClient.SqlConnection(ConnString)
        LogEntry("Connection created.")
        Dim SQLCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("select 1 as [test]", SQLConn)

        Try
            SQLConn.Open()
            LogEntry("Connection opened.")
            SQLCommand.ExecuteNonQuery()
            LogEntry("Database successfully tested.")
            SQLConn.Close()
            LogEntry("Connection closed.")
            Return True
        Catch ex As Exception
            ProcessError(ex.Message)
            If SQLConn.State <> ConnectionState.Closed Then SQLConn.Close()
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Get this session Log list
    ''' </summary>
    ''' <returns>An ArrayList containing the log entries</returns>
    ''' <remarks></remarks>
    Public Function GetLog() As ArrayList
        LogEntry("Log was delivered")
        Return Log
    End Function

    ''' <summary>
    ''' Get this session Error Log list
    ''' </summary>
    ''' <returns>An ArrayList containing the log entries</returns>
    ''' <remarks></remarks>
    Public Function GetErrorLog() As ArrayList
        LogEntry("Error Log was delivered")
        Return ErrorLog
    End Function

    ''' <summary>
    ''' Clear all Log entries for this session
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub ClearSessionLog()
        LogEntry("Log was cleared")
        Log.Clear()
    End Sub

    ''' <summary>
    ''' Clear all error Log entries for this session
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub ClearSessionErrorLog()
        LogEntry("Error Log was cleared")
        ErrorLog.Clear()
    End Sub

#End Region

#Region "Internal Methods"

    Private Function GetNowTime() As String
        Dim NTime As String = My.Computer.Clock.LocalTime.ToShortTimeString
        Dim NDate As String = My.Computer.Clock.LocalTime.ToShortDateString

        Return NDate & " - [" & NTime & "]"
    End Function

    Private Sub LogErrorEntry(ByVal ErrorMessage As String)
        ErrorLog.Add(GetNowTime() & " - " & ErrorMessage)
    End Sub

    Private Sub LogEntry(ByVal Message As String)
        Log.Add(GetNowTime() & " - " & Message)
    End Sub


    Private Sub ProcessError(ByVal ErrorMessage As String)
        LogErrorEntry("An error was detected: " & ErrorMessage)

        If ShowError = True Then
            If String.IsNullOrEmpty(ErrorMsg) = False Then
                If ErrorMsg.Contains("%%errormsg%%") Then
                    MsgBox(Replace(ErrorMsg, "%%errormsg%%", ErrorMessage), MsgBoxStyle.Critical, ErrorTitle)
                Else
                    MsgBox(ErrorMsg & vbCrLf & ErrorMessage, MsgBoxStyle.Critical, ErrorTitle)
                End If
            Else
                MsgBox(ErrorMessage)
            End If
        End If

    End Sub
#End Region

End Class

        Dim S As New SQL2k5("datasource", "database", "username", "password", SQL2k5.CommandType.Text)

        S.DisplayErrorMessages = True
        S.ErrorMessageInformation("Erro no SQL", "Ocorreu o seguinte erro: " & vbCrLf & vbCrLf & "%%errormsg%%" & vbCrLf & vbCrLf & "A operação foi forçada a abortar.")