Data Access Layer Help

[bugtracker911]

I'm in the process of creating a large app in VB.net and have designed all forms and database (mysql), just need to code everything together. I understand a lot of old VB, but I am finding .net tricky. Anyway, I am in the process of creating a data access layer for mysql, and need some feedback, as I have been unable to find any simple examples so I have created this myself, so its probably !?%£&, it seems to work but it doesn't seem right. Feedback will be apreciated...

Imports MySql.Data.MySqlClient

Module modDatabase

    Dim conn As MySqlConnection
    Dim objData As DataTable
    Dim objDa As MySqlDataAdapter
    Dim objCb As MySqlCommandBuilder
    Public Sub DB_Connect()
        'check if connection exists
        If Not conn Is Nothing Then conn.Close()

        'connect to database
        Dim connStr As String
        connStr = String.Format("server=localhost;user id=root; password=; database=totalcontrol; pooling=false")

        Try
            conn = New MySqlConnection(connStr)
            conn.Open()
        Catch ex As MySqlException
            MessageBox.Show("Error connecting to the server: " + ex.Message)
        End Try
    End Sub
    Public Function DB_GetDataSet(ByVal sql)
        objData = New DataTable

        objDa = New MySqlDataAdapter(sql, conn)
        objCb = New MySqlCommandBuilder(objDa)

        objDa.Fill(objData)

        Return objData
    End Function
    Public Function DB_Close()
        objData.Dispose()
        objDa.Dispose()
        objCb.Dispose()

        conn.Close()
        conn.Dispose()
    End Function
End Module

 

[JohnH]

Ok, you have set up a connection and want to use it, best advice is to get into the 7-part MySql tutorial indexed here: http://www.vbmysql.com/articles/ If you know all about databases and setting up MySql I think you can start directly on part 3 (well, it's about the connection, so perhaps you can start with part 4... ).

Thread was moved to MySql forum.

 

[bugtracker911]

Thanks John for the link, I printed the tutorials and had a good read, it should help me a lot... Many Thanks

 

[TechGnome]

Ummmm.... MySQL or SQLServer? There is a BIG difference.... you seem to be using the SQLClient class, but that's geared towards SQLServer, and won't work against other DBMSs.... You'll either want to use OLEDB or find out if there is MySQL adaptor/class that you can use.

-tg

 

[JohnH]

?TG? All the code was MySqlClient

 

[TechGnome]

I desperately need to get my eyes checked.... I'd swear it was SQLClient last night when I looked.

-tg