classes/mysqlfunctions.vb - main - ivan/ModMas

classes/mysqlfunctions.vb
290 строк9 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports Microsoft.VisualBasic
Imports MySql.Data.MySqlClient


Namespace dbFunctions
    Public Class mySqlFunctions
        Implements IDisposable

        'Simple class for basic DB operations

    Public Shared ConString As String = "server=o3.serk.lan;User Id=#id;Persist Security Info=True;database=inventory"
        Public lastException As String = ""
        Public Event ErrorHappaned(source As String, ex As Exception)
        Private _con 'As MySql.Data.MySqlClient.MySqlConnection
        Public Sub New(Optional ByVal sConString As String = "")
            If sConString <> "" Then
                ConString = sConString
            End If

        End Sub

        Private Sub Open()
            Try
                _con = New MySqlConnection(ConString)
                _con.Open()
            Catch ex As Exception
            End Try
        End Sub

        Private Sub Close()
            Try
                _con.Close()
                _con = Nothing
            Catch ex As Exception
            End Try
        End Sub

        Public Function GetScalar(ByVal sqlScript As String) As String

            Dim result As String = ""
            Try
                Open()
                Dim com As New MySqlCommand(sqlScript, _con)
                result = com.ExecuteScalar
                If IsNothing(result) Then
                    result = ""
                End If
                com = Nothing
            Catch ex As Exception
                lastException = ex.Message
            Finally
                Close()
            End Try
            Return result
        End Function

        'Public Function GetScalar(ByVal com As MySqlCommand) As String

        '    Dim result As String = ""
        '    'Try
        '    '    Open()
        '    '    com.Connection = _con
        '    '    com.CommandText = com.CommandText
        '    '    result = com.ExecuteScalar
        '    '    If IsNothing(result) Then
        '    '        result = ""
        '    '    End If
        '    'Catch ex As Exception
        '    '    lastException = ex.Message
        '    'Finally
        '    '    Close()
        '    'End Try
        '    Return result
        'End Function

        Public Function NonQuery(ByVal Query As String, Optional ByRef ProcessedRecords As Integer = 0, Optional ByRef ExMessage As String = "") As Integer
            ExMessage = ""
            Try
                Open()
                Dim cmd As New MySqlCommand(Query, _con)
                cmd.CommandTimeout = 180
                ProcessedRecords = cmd.ExecuteNonQuery()
            Catch ex As Exception
                ExMessage = ex.Message
                lastException = ex.Message
            Finally
                Close()
            End Try
            If ExMessage <> "" Then
                Return -1
            Else
                Return ProcessedRecords
            End If
        End Function

        Public Function Insert(ByVal Query As String) As Integer
            Dim identity As Integer = -1
            Try
                Open()
                Dim cmd As New MySqlCommand(Query, _con)
                cmd.CommandTimeout = 180
                cmd.ExecuteNonQuery()
                cmd.CommandText = "SELECT LAST_INSERT_ID()"
                identity = cmd.ExecuteScalar
            Catch ex As Exception
                lastException = ex.Message
            Finally
                Close()
            End Try
            Return identity
        End Function

        'Public Function Insert(ByVal cmd As MySqlCommand) As Integer
        '    Dim identity As Integer = -1
        '    'Try
        '    '    Open()
        '    '    cmd.Connection = _con
        '    '    cmd.CommandTimeout = 180
        '    '    cmd.ExecuteNonQuery()
        '    '    cmd.CommandText = "SELECT LAST_INSERT_ID()"
        '    '    identity = cmd.ExecuteScalar
        '    'Catch ex As Exception
        '    '    lastException = ex.Message
        '    'Finally
        '    '    Close()
        '    'End Try
        '    Return identity
        'End Function


        'Public Function NonQuery(ByVal com As MySqlCommand, Optional ByRef ProcessedRecords As Integer = 0, Optional ByRef ExMessage As String = "") As Integer

        '    ExMessage = ""
        '    'Try
        '    '    Open()
        '    '    com.Connection = _con
        '    '    com.CommandText = com.CommandText
        '    '    ProcessedRecords = com.ExecuteNonQuery()
        '    'Catch ex As Exception
        '    '    lastException = ex.Message
        '    '    ExMessage = ex.Message
        '    'Finally
        '    '    Close()
        '    'End Try
        '    If ExMessage <> "" Then
        '        Return -1
        '    Else
        '        Return ProcessedRecords
        '    End If
        'End Function

        'Public Function NonQueryWithTransaction(ByVal Query As String, Optional ByRef ProcessedRecords As Integer = 0, Optional ByRef ExMessage As String = "") As Boolean

        '    Dim transaction As MySqlTransaction

        '    'Dim cmd As New mySqlCommand(Query, con)

        '    ExMessage = ""
        '    'Try
        '    '    Open()
        '    '    Dim cmd As MySqlCommand = _con.CreateCommand
        '    '    transaction = _con.BeginTransaction("NewTransaction")
        '    '    cmd.Connection = _con
        '    '    cmd.Transaction = transaction
        '    '    cmd.CommandText = Query
        '    '    ProcessedRecords = cmd.ExecuteNonQuery()
        '    '    transaction.Commit()

        '    '    Return True
        '    'Catch ex As Exception
        '    '    ExMessage = ex.Message
        '    '    lastException = ex.Message

        '    '    Try
        '    '        transaction.Rollback()
        '    '    Catch exx As Exception

        '    '    End Try
        '    'Finally
        '    '    Close()

        '    'End Try

        '    If ExMessage <> "" Then
        '        Return False
        '    Else
        '        Return True
        '    End If
        'End Function

        Public Function GetRow(ByVal Query As String) As DataRow
            Dim ds As New DataSet
            Try
                Open()
                Dim cmd As New MySqlCommand(Query, _con)
                Dim adapter As New MySqlDataAdapter(cmd)
                adapter.Fill(ds)

            Catch ex As Exception
                lastException = ex.Message
            Finally
                Close()
            End Try
            If Not IsNothing(ds) Then
                If ds.Tables.Count > 0 Then
                    If ds.Tables(0).Rows.Count > 0 Then
                        Return ds.Tables(0).Rows(0)
                    End If
                End If
            End If
            Return Nothing
        End Function

        Public Function GetData(ByVal Query As String) As DataTable
            Dim ds As New DataSet
            Try
                Open()
                Dim cmd As New MySqlCommand(Query, _con)
                Dim adapter As New MySqlDataAdapter(cmd)
                adapter.Fill(ds)
            Catch ex As Exception
                lastException = ex.Message
            Finally
                Close()
            End Try
            If Not IsNothing(ds) Then
                If ds.Tables.Count > 0 Then
                    Return ds.Tables(0)
                End If
            End If
            Return Nothing
        End Function

        'Public Function GetData(ByVal com As MySqlCommand) As DataTable
        '    Dim ds As New DataSet
        '    'Try
        '    '    Open()
        '    '    com.Connection = _con
        '    '    com.CommandText = com.CommandText
        '    '    Dim adapter As New MySqlDataAdapter(com)
        '    '    adapter.Fill(ds)
        '    'Catch ex As Exception
        '    '    lastException = ex.Message
        '    'Finally
        '    '    Close()
        '    'End Try
        '    If Not IsNothing(ds) Then
        '        If ds.Tables.Count > 0 Then
        '            Return ds.Tables(0)
        '        End If
        '    End If
        '    Return Nothing
        'End Function




#Region "IDisposable Support"
        Private disposedValue As Boolean ' To detect redundant calls

        ' IDisposable
        Protected Overridable Sub Dispose(disposing As Boolean)
            If Not Me.disposedValue Then
                If disposing Then
                    ' TODO: dispose managed state (managed objects).
                End If

                ' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
                ' TODO: set large fields to null.
            End If
            Me.disposedValue = True
        End Sub

        ' TODO: override Finalize() only if Dispose(ByVal disposing As Boolean) above has code to free unmanaged resources.
        'Protected Overrides Sub Finalize()
        '    ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        '    Dispose(False)
        '    MyBase.Finalize()
        'End Sub

        ' This code added by Visual Basic to correctly implement the disposable pattern.
        Public Sub Dispose() Implements IDisposable.Dispose
            ' Do not change this code.  Put cleanup code in Dispose(disposing As Boolean) above.
            Dispose(True)
            GC.SuppressFinalize(Me)
        End Sub
#End Region

    End Class
End Namespace