MySQL master / detail example or tutorial

[cary1234]

Hello all!

I am looking for a MySQL master and detail tutorial or example, I am an intermediate .NET developer. I really don't know how to start on studying the master and detail topic using MySQL. There are many tutorials but all of them are using MS SQL and other database.

[jmcilhinney]

The database is basically irrelevant. If you're talking about how to display and edit master/detail data in the UI then you're using a DataSet as the data source so where the data comes from to populate the DataSet is irrelevant. If you're using SQL Server then you would use a SqlDataAdapter and if you're using MySQL then you'd use a MySqlDataAdapter but it's still ADO.NET so the pattern of the code to retrieve and save data is the same and then what you do with the populated DataSet is exactly the same.

[cary1234]

Oh, thank you so much. Assuming that I have basic knowledge of database, what I need to learn is to use ADO.NET and using datasets in MySQL right?

[jmcilhinney]

... what I need to learn is to use ADO.NET and using datasets in MySQL right?

Using DataSets is using ADO.NET and using DataSets is the same regardless of the database. Basically, you just need to learn how to use ADO.NET and then use the MySQL-specific ADO.NET provider in your app.

For instance, as you say, most ADO.NET examples will use SQL Server but you can do exactly the same with MySQL by simply swapping out the provider, e.g. use a MySql.Data.MySqlClient.MySqlDataAdapter instead of a System.Data.SqlClient.SqlDataAdapter. You still use it in exactly the same way though.

You can follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data for some code examples of common ADO.NET scenarios and my Master/Detail Data-Binding thread for the data-binding part. In that second thread, I don't use any database and simply populate a DataSet manually. As I said, it doesn't matter where the data comes from once it's in a DataSet. You use the DataSet the same way regardless.

[cary1234]

Thank you so much Sir!

By the way, for the past months I am able to access the database by using codes like this

#Region "Check database connection"
    Public Sub Check_DB_Con()
        'Declare database credentials so we can refresh it in recheck timer
        server = My.Settings.DB_Server
        username = My.Settings.DB_Username
        password = My.Settings.DB_Password
        database = My.Settings.DB_Database

        'Connection String
        MysqlConn.ConnectionString = "server=" & server & ";" _
        & "user id=" & username & ";" _
        & "password=" & password & ";" _
        & "database=" & database & ";Allow User Variables=True"
    End Sub
#End Region

#Region "Update database connection"
    Public Sub Update_DB_Con()
        Check_DB_Con()
        Try
            MysqlConn.Open()
            frmMain.Database_Status = "Connected"
            MysqlConn.Close()
        Catch myerror As MySqlException
            frmMain.Database_Status = "Disconnected"
        Finally
            MysqlConn.Dispose()
        End Try
    End Sub
#End Region

#Region "Command for Increment"
    Sub Command_Increment(hardware_Add As String)
        Check_DB_Con() 'Check if the connection is okay
        MySQL_Query = "UPDATE service_info " _
            & "JOIN teller_info " _
            & "ON service_info.service_id = teller_info.service_id " _
            & "SET service_info.current_serving = (service_info.current_serving +1) " _
            & "WHERE teller_info.hardware_add = " & hardware_Add
        Dim MySQL_CMD As New MySqlCommand(MySQL_Query, MysqlConn)

        Try
            MySQL_CMD.Connection.Open()
            MySQL_CMD.CommandText = MySQL_Query
            MySQL_CMD.ExecuteNonQuery()
        Catch myerror As MySqlException
            Console.WriteLine(myerror)
        Finally
            MysqlConn.Close()
            MysqlConn.Dispose()
            populate_Service()
            Get_Teller_Num(hardware_Add)
            Update_Remaining_Queue(hardware_Add)
        End Try
    End Sub
#End Region

The way I'm calling it, isn't that ADO.NET because I am not using Datasets. I remember also that I am able to connect to database using the database connection wizard and that's where I remember the word provider and datasets. Is that what you called ADO.NET?

[jmcilhinney]

If you're using System.Data or types derived from types in System.Data then you're using ADO.NET. You're using a MySqlConnection so you're using ADO.NET. The wizard creates a typed DataSet and that is also ADO.NET. It's just custom code generated for your data specifically but it's still using types derived from those in System.Data.

[cary1234]

Thank you so much Sir, now I'm a little bit clear.