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.
Exemplo prático de configuração:
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@ 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
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.")