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

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

Discussão em 'Programação' iniciada por ribeiro55, 11 de Março de 2009. (Respostas: 0; Visualizações: 2360)

  1. ribeiro55

    ribeiro55 Power Member

    Classe de MSSQL para operação básica
    Contributo para repositório de código


    Existem 3 métodos para configurar uma instância:

    - Argumentos do New
    - Método "ConfigureMe"
    - Propriedade a propriedade

    Depois de instanciada e configurada, podem configurar as mensagens de erro do errorcatcher builtin.
    Tem um log de acções temporário, acessível através de métodos.
    Utilizem o método "ExecuteQuery" para executar uma query ou StoredProcedure. Este método vai devolver-vos uma DataTable com o resultado.

    Código:
    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@        [email protected]  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
    Exemplo prático de configuração:

    Código:
            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.")
     

Partilhar esta Página