ASP.Net: CRUD operaion with MySQL Database using Business class in VB.Net

ilanocf

SOLVED CLOSED ACTIVE ANSWERED
User: ilanocf
Posted: on Nov 19, 2023 10:14 PM
Forum: ASP.Net Web Forms
Answer: 1
Views: 1342

Hey guys,

I have always worked with SQL Server and now I need to work with MySQL. I can do CRUD in MySQL but I would like to adapt the functions Public Function Query(ByVal pParametro As String, ByVal pOrdem As String) As Data.DataSet and Private Function Busca(ByVal pParametro As String) As Boolean for MySQL, how can I make this adaptation?

Below is the entire structure of the CRUD object but I would like to adapt the two functions mentioned above.

Imports System.Data.SqlClient

Namespace AUXILIO

    Public Class objCategoria

        Private Db As classDatabase.DbSql
        Private Cm As SqlCommand
        Private Fn As New Funcoes.classFuncao
        ' Declaracao das variaveis

        Private vlID As Integer
        Private vlNOME As String

        ' Declaracao das propriedades

        Property ID()
            Get
                Return vlID
            End Get
            Set(ByVal Value)
                vlID = Value
            End Set
        End Property

        Property NOME()
            Get
                Return vlNOME
            End Get
            Set(ByVal Value)
                vlNOME = Value
            End Set
        End Property

        ' Declaracao dos procedimentos
        Public Function Inserir()
            Try
                Cm = New SqlCommand

                ' Gera o código
                Db = New classDatabase.DbSql
                Db.NomeTabela = "CATEGORIA"
                vlID = Db.Chave("ID")
                Db = Nothing

                With Cm.Parameters
                    .AddWithValue("@ID", vlID)
                    .AddWithValue("@NOME", Trim(vlNOME))
                End With

                If Busca(" NOME = '" & Trim(vlNOME) & "' ") Then
                    Return "REGISTRO JÁ CADASTRADO!"
                Else
                    ' Cria uma instância do objeto de conexao com o banco de dados
                    ' e preenche os dados necessarios para realizar as operacoes
                    Db = New classDatabase.DbSql
                    Db.NomeTabela = "CATEGORIA"
                    Return Db.Salvar(Cm)

                End If

            Catch ex As Exception
                Return "OCORREU UM ERRO AO TENTAR INCLUIR UM NOVO REGISTRO:" & ex.Message

            Finally
                Cm = Nothing
                Db = Nothing

            End Try

        End Function

        Public Function Alterar() As String
            Try
                Cm = New SqlCommand

                With Cm.Parameters
                    .AddWithValue("@NOME", Trim(vlNOME))
                End With

                If Busca(" ID <> " & vlID & " AND NOME = '" & Trim(vlNOME) & "' ") Then
                    Return "REGISTRO JÁ CADASTRADO!"
                Else
                    ' Cria uma instância do objeto de conexao com o banco de dados
                    ' e preenche os dados necessarios para realizar as operacoes
                    Db = New classDatabase.DbSql
                    Db.NomeTabela = "CATEGORIA"
                    Return Db.Salvar(Cm, "ID", vlID)

                End If

            Catch ex As Exception
                Return "Erro:" & ex.Message

            Finally
                Cm = Nothing
                Db = Nothing

            End Try

        End Function

        Public Function Excluir() As String
            Try
                ' Cria uma instância do objeto de conexao com o banco de dados
                ' e preenche os dados necessarios para realizar as operacoes
                Db = New classDatabase.DbSql
                Db.NomeTabela = "CATEGORIA"
                Return Db.Excluir("ID", vlID)

            Catch ex As Exception
                Return "OCORREU UM ERRO AO TENTAR EXCLUIR ESTE REGISTRO: " & ex.Message

            Finally
                Db = Nothing

            End Try

        End Function

        Public Function Consultar(ByVal pParametro As String, ByVal pOrdem As String) As Data.DataSet
            Dim SQL As New System.Text.StringBuilder

            SQL.Append(" SELECT ID, NOME ")
            SQL.Append(" FROM CATEGORIA ")

            If Trim(pParametro) <> "" Then SQL.Append(" WHERE " & pParametro & "")
            If Trim(pOrdem) <> "" Then SQL.Append(" ORDER BY " & Trim(pOrdem)) Else SQL.Append(" ORDER BY NOME ")

            Try
                ' Cria uma instância do objeto de conexao com o banco de dados
                ' e preenche os dados necessarios para realizar as operacoes
                Db = New classDatabase.DbSql
                Return Db.Consultar(SQL.ToString)
            Catch ex As Exception
                Return Nothing
            Finally
                Db = Nothing
            End Try

        End Function

        Public Function ConsultaGenerica(ByVal pSQL As String) As Data.DataSet
            Dim SQL As New System.Text.StringBuilder

            SQL.Append(pSQL)

            Try
                ' Cria uma instância do objeto de conexao com o banco de dados
                ' e preenche os dados necessarios para realizar as operacoes
                Db = New classDatabase.DbSql
                Return Db.Consultar(SQL.ToString)
            Catch ex As Exception
                Return Nothing
            Finally
                Db = Nothing
            End Try

        End Function

        Public Function ExecutaSQLGenerica(ByVal pSQL As String) As String
            Dim Sql As New System.Text.StringBuilder

            Sql.Append(pSQL)

            Try
                'Cria uma instância do objeto de conexao com o banco de dados
                ' e preenche os dados necessarios para realizar as operacoes
                Db = New classDatabase.DbSql
                Return Db.ExecutaSql(pSQL)

            Catch ex As Exception
                Return Nothing
            Finally
                Db = Nothing
            End Try

        End Function

        'Função para retornar verdadeiro quando estação encontrada
        Private Function Busca(ByVal pParametro As String) As Boolean
            Dim Ds As Data.DataSet
            Ds = Consultar(pParametro, "")
            If Ds.Tables(0).Rows.Count <> 0 Then
                Return True
            Else
                Return False
            End If
        End Function

    End Class

End Namespace

Grateful,

Ilano.

ilanocf

0 4

Reply
ANSWER
User: ilanocf
Replied: on Nov 21, 2023 08:23 AM
Report

I managed to solve it, it worked. The code looked like this:

Imports System.Data.SqlClient
Imports MySql.Data
Imports MySql.Data.MySqlClient

Namespace ESCOLA

    Public Class objPerfis

        Dim vlID As Integer
        Dim vlNOME As String

        Property ID()
            Get
                Return vlID
            End Get
            Set(ByVal value)
                vlID = value
            End Set
        End Property
        Property NOME()
            Get
                Return vlNOME
            End Get
            Set(ByVal value)
                vlNOME = value
            End Set
        End Property

        Public Function Incluir() As String
            Dim pRetorno As String = "Inclusão realizada com sucesso!!!"

            Try
                Dim conexaoMySQL As MySqlConnection
                Dim Cm As New MySqlCommand
                Dim SQL As New System.Text.StringBuilder
                Dim Conexao As String
                Conexao = ConfigurationManager.AppSettings("cStrConexao")
                conexaoMySQL = New MySqlConnection(Conexao)

                SQL.Append("INSERT INTO perfis (nome) VALUES (?Nome) ")
                With Cm.Parameters
                    .Add(New MySqlParameter("?Nome", UCase(Trim(vlNOME))))
                End With

                Cm.Connection = conexaoMySQL
                Cm.CommandText = SQL.ToString
                conexaoMySQL.Open()
                Cm.ExecuteNonQuery()
                conexaoMySQL.Close()

            Catch ex As Exception
                pRetorno = "Ocorreu um erro ao tentar incluir o registro: " & ex.Message

            End Try

            Return pRetorno

        End Function

        Public Function Alterar() As String
            Dim pRetorno As String = "Alteração efetuada com sucesso!!!"

            Try
                Dim conexaoMySQL As MySqlConnection
                Dim Cm As New MySqlCommand
                Dim SQL As New System.Text.StringBuilder
                Dim Conexao As String
                Conexao = ConfigurationManager.AppSettings("cStrConexao")
                conexaoMySQL = New MySqlConnection(Conexao)
                SQL.Append(" UPDATE perfis SET ")
                SQL.Append(" nome = ?nome ")
                SQL.Append(" WHERE id = ?id ")

                With Cm.Parameters
                    .Add(New MySqlParameter("?nome", Trim(vlNOME)))
                    .Add(New MySqlParameter("?id", vlID))
                End With

                Cm.Connection = conexaoMySQL
                Cm.CommandText = SQL.ToString
                conexaoMySQL.Open()
                Cm.ExecuteNonQuery()
                conexaoMySQL.Close()

            Catch ex As Exception
                pRetorno = "Ocorreu um erro ao tentar alterar o registro: " & ex.Message

            End Try

            Return pRetorno

        End Function

        Public Function Excluir() As String
            Dim pRetorno As String = "Exclusão efetuada com sucesso!!!"

            Try
                Dim conexaoMySQL As MySqlConnection
                Dim Cm As New MySqlCommand
                Dim SQL As New System.Text.StringBuilder
                Dim Conexao As String
                Conexao = ConfigurationManager.AppSettings("cStrConexao")
                conexaoMySQL = New MySqlConnection(Conexao)

                SQL.Append(" DELETE FROM perfis ")
                SQL.Append(" WHERE id = " & vlID)

                Cm.Connection = conexaoMySQL
                Cm.CommandText = SQL.ToString
                conexaoMySQL.Open()
                Cm.ExecuteNonQuery()
                conexaoMySQL.Close()

            Catch ex As Exception
                pRetorno = "Ocorreu um erro ao tentar excluir o registro: " & ex.Message

            End Try

            Return pRetorno

        End Function

        Public Function Consultar(ByVal pParametro As String, ByVal pOrdem As String) As Data.DataSet
            Try
                Dim conexaoMySQL As MySqlConnection
                Dim Da As MySqlDataAdapter
                Dim Ds As Data.DataSet
                Dim SQL As New System.Text.StringBuilder
                Dim Conexao As String
                Conexao = ConfigurationManager.AppSettings("cStrConexao")
                conexaoMySQL = New MySqlConnection(Conexao)

                SQL.Append(" SELECT id, nome ")
                SQL.Append(" FROM perfis ")

                If Trim(pParametro) <> "" Then SQL.Append(" WHERE " & pParametro & "")

                If Trim(pOrdem) <> "" Then SQL.Append(" ORDER BY " & Trim(pOrdem)) Else SQL.Append(" ORDER BY nome ")

                Da = New MySqlDataAdapter(SQL.ToString, conexaoMySQL)
                conexaoMySQL.Open()
                Ds = New Data.DataSet
                Da.Fill(Ds)
                Return Ds

            Catch ex As Exception
                Return Nothing

            End Try

        End Function

        Public Function ConsultaGenerica(ByVal pSQL As String) As Data.DataSet
            Try
                Dim conexaoMySQL As MySqlConnection
                Dim Da As MySqlDataAdapter
                Dim Ds As Data.DataSet
                Dim SQL As New System.Text.StringBuilder
                Dim Conexao As String
                Conexao = ConfigurationManager.AppSettings("cStrConexao")
                conexaoMySQL = New MySqlConnection(Conexao)

                SQL.Append(pSQL)

                Da = New MySqlDataAdapter(SQL.ToString, conexaoMySQL)
                conexaoMySQL.Open()
                Ds = New Data.DataSet
                Da.Fill(Ds)
                Return Ds

            Catch ex As Exception
                Return Nothing

            End Try

        End Function

        Public Function ExecutaSQLGenerica(ByVal pSQL As String) As String
            Dim pRetorno As String = "Script realizado com sucesso!!!"

            Try
                Dim conexaoMySQL As MySqlConnection
                Dim Cm As New MySqlCommand
                Dim SQL As New System.Text.StringBuilder
                Dim Conexao As String
                Conexao = ConfigurationManager.AppSettings("cStrConexao")
                conexaoMySQL = New MySqlConnection(Conexao)

                SQL.Append(pSQL)

                Cm.Connection = conexaoMySQL
                Cm.CommandText = SQL.ToString
                conexaoMySQL.Open()
                Cm.ExecuteNonQuery()
                conexaoMySQL.Close()

            Catch ex As Exception
                pRetorno = "Ocorreu um erro ao executar seu script: " & ex.Message

            End Try

            Return pRetorno

        End Function

        Public Function Busca(ByVal pParametro As String) As Boolean
            Dim pRetorno As Boolean = False
            Dim Ds As Data.DataSet
            Ds = Consultar(pParametro, "")

            If Not Ds Is Nothing Then
                If Ds.Tables(0).Rows.Count > 0 Then pRetorno = True
            End If

            Return pRetorno

        End Function

    End Class

End Namespace

ASP.Net: CRUD operaion with MySQL Database using Business class in VB.Net

Company

Explore

Follow us

Contact Us