Database para vb.net (VB)

Database.vb

Imports System.Data.OleDb
Imports System.Security.Cryptography
Imports System.Text

Public Class Database
    Private DBCon As New OleDbConnection(My.Settings.DataBaseConnectionString)
    Private DBCmd As New OleDbCommand
    Private DBDA As New OleDbDataAdapter
    Private DTTB As New DataTable
    Public Param As New List(Of OleDbParameter)

    Private _Table As String
    Private PrimaryCollum As String = "ID"
    Public UpdateCollumPrefix As String = "updtcollum__"
    Public Erro As String
    Public Count As Integer = 0
    Public Rows As DataRowCollection

    Public Sub New(ByVal Optional NewTable As String = Nothing, ByVal Optional _PrimaryCollum As String = Nothing)
        If Not NewTable Is Nothing Then
            Table = NewTable
        End If

        If Not _PrimaryCollum Is Nothing Then
            PrimaryCollum = _PrimaryCollum
        End If
    End Sub

    ' cript password
    Private myKey As String = "95fg18wer4jk1iuuio8744zx4df165fsd"
    Private des As New TripleDESCryptoServiceProvider()
    Private hashmd5 As New MD5CryptoServiceProvider()
    Public Function cript(_Senha As String)
        des.Key = hashmd5.ComputeHash(ASCIIEncoding.ASCII.GetBytes(myKey))
        des.Mode = CipherMode.ECB
        Dim desdencrypt As ICryptoTransform = des.CreateEncryptor()
        Dim MyASCIIEncoding = New ASCIIEncoding()
        Dim buff() As Byte = ASCIIEncoding.ASCII.GetBytes(_Senha)
        Return Convert.ToBase64String(desdencrypt.TransformFinalBlock(buff, 0, buff.Length))
    End Function

    'name of actual table
    Public Property Table
        Get
            Return _Table
        End Get
        Set(NewTable)
            _Table = NewTable
        End Set
    End Property

    'set table inline
    Public Function SetTable(NewTable As String)
        Table = NewTable
        Return Me
    End Function

    'find data in database when the date has already been set
    Public Function Find(ByVal Optional Colunas As String = "*")
        If Param.ToArray.Count.Equals(0) Then
            MessageBox.Show("Você precisa adicionar Parametros para Selecionar algum dado!",
                                    "Error! Adicione parâmetros", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If

        Dim StringForExecute = String.Format("SELECT {0} FROM {1} {2}", Colunas, Table, PrepareStringWhere)
        Execute(StringForExecute)

        Return Me
    End Function

    'find by with data in parameters
    Public Function FindBy(CollunsAndValues As List(Of Object), ByVal Optional Colluns As String = "*")
        Dim Where = PrepareStringWhere(CollunsAndValues)
        Dim StringForExecute = String.Format("SELECT {0} FROM {1} {2}", Colluns, Table, Where)
        Execute(StringForExecute)
        Return Me
    End Function

    'update data
    Public Function Update(CollunsAndValues As List(Of Object), WhereList As List(Of Object))
        Dim UpdateString = PrepareStringUpdate(CollunsAndValues)
        Dim Where = PrepareStringWhere(WhereList)

        Execute(String.Format("UPDATE {0} SET {1} {2}", Table, UpdateString, Where))

        Return Me
    End Function

    'this prepare without list
    Private Function PrepareStringWhere()
        Return PrepareStringWhere(New List(Of Object))
    End Function

    'prepare string where with list of collums
    Private Function PrepareStringWhere(WhereList As List(Of Object))
        Dim StringWhere = Nothing
        Dim Name As String
        Dim Value As String

        For Each ItemWhere In WhereList.ToArray
            Name = ItemWhere(0)
            Value = ItemWhere(1)
            AddParam(Name, Value)
        Next

        If Param.ToArray.Count Then
            StringWhere = " WHERE "

            For Each ItemParametro In Param
                Name = ItemParametro.ParameterName.ToString
                Value = ItemParametro.Value.ToString

                StringWhere &= String.Format("{0}=@{0}", Name)

                If Not Array.IndexOf(Param.ToArray, ItemParametro).Equals(Param.Count - 1) Then
                    StringWhere &= " AND "
                End If
            Next

            Return StringWhere
        End If

        Return Nothing
    End Function

    'prepare string for update data string
    Private Function PrepareStringUpdate(CollunsAndValues As List(Of Object)) As String
        Dim UpdateString As String = ""
        Dim Name As String
        Dim Value As String

        For Each ItemUpdate In CollunsAndValues.ToArray
            Name = String.Format("{0}{1]", UpdateCollumPrefix, ItemUpdate(0))
            Value = ItemUpdate(1)
            AddParam(Name, Value)

            UpdateString &= String.Format("{0}=@{0}", Name)

            If Not Array.IndexOf(CollunsAndValues.ToArray, ItemUpdate).Equals(Count - 1) Then
                UpdateString &= ", "
            End If
        Next

        Return UpdateString
    End Function

    'get the first item of the rows
    Public Function First()
        If Count.Equals(Nothing) Then
            MessageBox.Show("Nenhum dado a exibir em: Element.First()",
                                        "Ops! Um Erro encontrado!",
                                        MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
            Return False
        End If

        Return Rows.Item(0)
    End Function

    'get the last item of the rows
    Public Function Last()
        If Count.Equals(Nothing) Then
            MessageBox.Show("Nenhum dado a exibir em: Element.Last()",
                                        "Ops! Um Erro encontrado!",
                                        MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
            Return Nothing
        End If
        Return Rows.Item(Count - 1)
    End Function

    'get an specificy item of list
    Public Function Item(Position As Integer)
        If Rows.Count.Equals(Nothing) Or Rows.Count - 1 < Position Then
            MessageBox.Show(String.Format("Não existem elementos na posição {0}, em: Elemento.Item({0})", Position),
                                        "Ops! Um Erro encontrado!",
                                        MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
            Return Nothing
        End If
        Return Rows.Item(Position)
    End Function

    'execute query of the database
    Public Function Execute(Query As String)
        Erro = Nothing
        If Not _Table.Equals(Nothing) Then
            Query = Query.Replace("{Tabela}", _Table)
        End If

        Try
            ' Abre conexao
            DBCon.Open()
            ' Cria o comando
            DBCmd = New OleDbCommand(Query, DBCon)
            ' Adiciona os parametros
            Param.ForEach(Sub(p) DBCmd.Parameters.Add(p))
            ' Limpar parametros
            Param.Clear()
            ' Cria tabela
            DTTB = New DataTable
            ' Executa
            DBDA = New OleDbDataAdapter(DBCmd)
            ' Preenche o DataTable
            DBDA.Fill(DTTB)
            Rows = DTTB.Rows
            Count = Rows.Count

            Return Me

        Catch ex As Exception
            Erro = ex.Message
            Dim Data As String = String.Join(vbCrLf, ex.Data)
            MessageBox.Show(Erro & vbCrLf & Data & vbCrLf & ex.Source, "Ocorreu um erro!", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return Erro
        Finally
            If DBCon.State = ConnectionState.Open Then
                DBCon.Close()
            End If
        End Try
    End Function

    'add an param into list
    Sub AddParam(Nome As String, Valor As Object)
        Dim Novo As New OleDbParameter(Nome, Valor)
        Param.Add(Novo)
    End Sub
End Class

how-to-test.vb

Public Class TestDatabase
    
  Sub New()
      Dim Test As New Database("Alunos", "ID")
      Test.AddParam("ID", 1)
      Dim DataFind As Object = Test.Find().First

      If Not DataFind Is Nothing Then
          Dim Name = DataFind.Item("Name")
          ' this show an message: "Test Name"
          MessageBox.Show(Name)
      End If
  End Sub
  
End Class