ADO.NET: manage local MySQL database to create a Master-Details application
Overview
From Wikipedia, the free encyclopedia
"MySQL (officially pronounced "My S-Q-L") is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language."
This example shows how to create, delete, and use a local MySQL database with ADO.Net to create a Master-Details application.
Where possible, in the SQL SELECT, and INSERT commands, Command Parameters have been used. Using Parameters as opposed to string concatenation protects our database from SQL Injection Attacks, which are possible with the string concatenation method as more than one SQL command can be executed in one statement. Command Parameters guard against that. However, there are several situations where Parameters can't be used.
Each Function and Method shown has XML Documentation Comments, and in the TechNet Wiki (this) version, is explained in more detail before each individual code block. Most of the code blocks are fairly short and require little explanation, and the longer code blocks are usually repetitive in the code they use.
This example shows how to create a local MySQL Master-Details application from the ground up...
The code - explained
Connecting to MySQL and your database
To connect to MySQL without specifying a database, we can call this function with an empty database name. This is the connection we'd use for creating or deleting a database in MySQL. Connecting with a valid local MySQL database name allows us to interact with our database.
''' <summary>
''' Connects to specified MYSQL database and returns that open MySqlConnection
''' </summary>
''' <param name="dbName">The name of the database, if any</param>
''' <returns></returns>
Public Shared Function getConnection(dbName As String) As MySqlConnection
Dim conn As New MySqlConnection("DATABASE=" & dbName & ";SERVER=localhost;user id=root;password=;port=3306;charset=utf8")
conn.Open()
Debug.Print(conn.State)
Return conn
End Function
Creating a database
To create a local MySQL database with a specified name, we'd use this function. Notice the syntax IF NOT EXISTS? This is because the function is called every time the application is run. If there is no database, it is created, else, the existing database already exists.
This is one of the five scenarios where Parameters can't be used.
''' <summary>
''' Used to create the database
''' </summary>
''' <param name="dbName">The name of the database to create</param>
Public Shared Sub createDataBase(dbName As String)
Dim conn As MySqlConnection = getConnection("")
Dim command As New MySqlCommand("CREATE DATABASE IF NOT EXISTS " & dbName, conn)
Debug.Print(command.ExecuteScalar)
Debug.Print("DataBase created or Exists " & dbName)
conn.Close()
End Sub
Deleting a database
To delete a database we'd call this function, passing a valid local MySQL database name as an argument to the method.
This is the second of the five scenarios where Parameters can't be used.
''' <summary>
''' Used to delete the specified database
''' </summary>
''' <param name="dbName">The name of the database</param>
Public Shared Sub dropDataBase(dbName As String)
Dim conn As MySqlConnection = getConnection("")
Dim command As New MySqlCommand("DROP DATABASE " & dbName, conn)
Debug.Print(command.ExecuteScalar)
Debug.Print("Database Dropped - " & dbName)
conn.Close()
End Sub
Adding tables to your database
To add a table to a specified database, we'd use an SQL statement as demonstrated in this method. Here, two example tables are created, with the database being a Books database, and the two tables being Authors and Titles. The authorID field in the Authors table is the PRIMARYKEY, and the authorID field in the Titles table is the FOREIGNKEY. This constraint ensures the titles records retain a link to the author.
This is the third of the five scenarios where Parameters can't be used.
''' <summary>
''' Adds two tables with specified fields to database
''' authorID is PRIMARY KEY in Authors and
''' FOREIGNKEY in Titles. This ensures the tables
''' stay linked as the autonumbers provided by the
''' DataTables are a little unpredictable.
''' </summary>
''' <param name="dbName">The name of the database</param>
Public Shared Sub createTables(dbName As String)
Dim conn As MySqlConnection = getConnection(dbName)
Dim myTable1 = "CREATE TABLE IF NOT EXISTS Authors (" &
"authorID INT PRIMARY KEY NOT NULL UNIQUE," &
"firstName VARCHAR(32)," &
"lastName VARCHAR(32))"
Dim command As New MySqlCommand(myTable1, conn)
Debug.Print(command.ExecuteScalar)
Debug.Print( "Table Created or Exists - Authors" )
Dim myTable2 = "CREATE TABLE IF NOT EXISTS Titles (" &
"titleID INT PRIMARY KEY NOT NULL UNIQUE," &
"authorID INT NOT NULL REFERENCES Authors(authorID)," &
"title VARCHAR(128))"
command = New MySqlCommand(myTable2, conn)
Debug.Print(command.ExecuteScalar)
Debug.Print( "Table Created or Exists - Titles" )
conn.Close()
End Sub
Deleting a table
To delete a table, again we'd use an SQL statement that can't use Command Parameters. To delete a table, we need a connection to the database, and a valid table name (both passed as arguments).
''' <summary>
''' Used to delete the specified table from the specified database
''' </summary>
''' <param name="dbName">The name of the database</param>
'''<param name="tableName">The name of the table</param>
Public Shared Sub dropTable(dbName As String, tableName As String)
Dim conn As MySqlConnection = getConnection(dbName)
Dim command As New MySqlCommand("DROP TABLE " & tableName, conn)
Debug.Print(command.ExecuteScalar)
Debug.Print("Table Dropped - " & tableName)
conn.Close()
End Sub
Counting records in a table
To ensure a table contains records, we can use a SELECT COUNT statement, which can't use Parameters for all of the variables but doesn't directly use user input for either the dbName, fieldName or tableName arguments...
''' <summary>
''' Used to check if the database has been populated
''' </summary>
''' <param name="dbName">The name of the database</param>
''' <param name="tableName">The name of the table</param>
''' <param name="fieldName">A fieldName to count</param>
''' <returns></returns>
Public Shared Function hasRecords(dbName As String, tableName As String, fieldName As String) As Boolean
Dim conn As MySqlConnection = getConnection(dbName)
Dim command As New MySqlCommand("SELECT COUNT(" & fieldName & ") FROM " & tableName, conn)
Dim count As Integer = CInt(command.ExecuteScalar)
Debug.Print(count)
conn.Close()
Return count > 0
End Function
Adding some example data to your tables
This just adds some example data to the Authors table and to the Titles table. Later a relation will be created, linking the two tables through the mutual authorID field.
Both of the tables have an integer primary key field, which is authorID in Authors and titleID in Titles. The authorID from the newly inserted Authors record is used when adding data to the Titles table.
Here Command Parameters are partly used as an example, though the data input is hardcoded rather than through user input. Data input through user input poses an SQL Injection threat...
''' <summary>
''' Adds some example data to the database
''' </summary>
Public Shared Sub populateTables()
Dim conn As MySqlConnection = getConnection("books2")
Dim command As New MySqlCommand("INSERT INTO Authors (authorID, firstName, lastName) VALUES (1, @firstName, @lastName)", conn)
command.Parameters.AddWithValue( "@firstName" , "James" )
command.Parameters.AddWithValue( "@lastName" , "Joyce" )
command.ExecuteScalar()
command = New MySqlCommand("INSERT INTO Titles (titleID, authorID, title) VALUES (1, 1, @title)", conn)
command.Parameters.AddWithValue( "@title" , "Ulysses" )
command.ExecuteScalar()
Debug.Print( "James Joyce - added" )
command = New MySqlCommand("INSERT INTO Authors (authorID, firstName, lastName) VALUES (2, @firstName, @lastName)", conn)
command.Parameters.AddWithValue( "@firstName" , "Miguel" )
command.Parameters.AddWithValue( "@lastName" , "de Cervantes" )
command.ExecuteScalar()
command = New MySqlCommand("INSERT INTO Titles (titleID, authorID, title) VALUES (2, 2, @title)", conn)
command.Parameters.AddWithValue( "@title" , "Don Quixote" )
command.ExecuteScalar()
Debug.Print( "Miguel de Cervantes - added" )
command = New MySqlCommand("INSERT INTO Authors (authorID, firstName, lastName) VALUES (3, @firstName, @lastName)", conn)
command.Parameters.AddWithValue( "@firstName" , "Herman" )
command.Parameters.AddWithValue( "@lastName" , "Melville" )
command.ExecuteScalar()
command = New MySqlCommand("INSERT INTO Titles (titleID, authorID, title) VALUES (3, 3, @title)", conn)
command.Parameters.AddWithValue( "@title" , "Moby Dick" )
command.ExecuteScalar()
command = New MySqlCommand("INSERT INTO Titles (titleID, authorID, title) VALUES (4, 3, @title)", conn)
command.Parameters.AddWithValue( "@title" , "His First Voyage" )
command.ExecuteScalar()
Debug.Print( "Herman Melville - added" )
conn.Close()
End Sub
Creating bindable datasources
To create Master-Details BindingSources, two DataTables are filled with all of the data from the Authors and Titles tables respectively. These DataTables are then added to a DataSet, which allows a DataRelation to be added, linking the authorID field in the Authors table with the authorID field in the Titles table. This means that when bound to Controls (a ComboBox is Master, and two TextBoxes and a DataGridView are Details), when the Master is changed, only relevant information for the selected Author is automatically displayed as Details.
Also, binding this way, minimizes the coding needed significantly, as the bindings take care of any insert, edit, or delete actions and as we'll see in the next code block, saving our changes is simple.
''' <summary>
''' Reads the data from both database tables, populates two datatables,
''' adds tables to a dataset to create a datarelation, so changing the Author
''' selected in the combobox displays filtered Titles by that Author in the DGV
''' </summary>
''' <param name="table1Name">The first table name</param>
''' <param name="table2Name">The second table name</param>
''' <returns></returns>
Public Shared Function getDataSources(table1Name As String, table2Name As String) As Object()
conn2 = getConnection( "books2" )
daAuthors = New MySqlDataAdapter("SELECT authorID, CONCAT(firstName, ' ', lastName) AS Author, firstName, lastName FROM " & table1Name, conn2)
Dim ds As New DataSet
dtAuthors = New DataTable(table1Name)
daAuthors.Fill(dtAuthors)
ds.Tables.Add(dtAuthors)
Dim cb As New MySqlCommandBuilder(daAuthors)
daTitles = New MySqlDataAdapter("SELECT * FROM " & table2Name & " WHERE title IS NOT NULL", conn2)
dtTitles = New DataTable(table2Name)
daTitles.Fill(dtTitles)
ds.Tables.Add(dtTitles)
cb = New MySqlCommandBuilder(daTitles)
dtAuthors.Columns( "authorID" ).AutoIncrement = True
dtTitles.Columns( "titleID" ).AutoIncrement = True
dtAuthors.Columns(0).AutoIncrementSeed = dtAuthors.Rows.Cast(Of DataRow).Max( Function (dr) dr.Item( "authorID" )) + 1
dtAuthors.Columns(0).AutoIncrementStep = 1
dtTitles.Columns(0).AutoIncrementSeed = dtTitles.Rows.Cast(Of DataRow).Max( Function (dr) dr.Item( "titleID" )) + 1
dtTitles.Columns(0).AutoIncrementStep = 1
dtAuthors.Columns( "Author" ).Expression = "firstName + ' ' + lastName"
ds.Relations.Add( New DataRelation("relation", ds.Tables(table1Name).Columns("authorID"), ds.Tables(table2Name).Columns("authorID")))
bs1 = New BindingSource(ds, "Authors")
bs2 = New BindingSource(bs1, "relation")
Return New BindingSource() {bs1, bs2}
End Function
Saving changes to your database
First, when binding, Class level variables are used, so they can be called later to update our database...
''' <summary>
''' DataSource variables, used for binding tables
''' </summary>
Private Shared dtAuthors As DataTable
Private Shared dtTitles As DataTable
Private Shared daAuthors As MySqlDataAdapter
Private Shared daTitles As MySqlDataAdapter
Private Shared bs1 As BindingSource
Private Shared bs2 As BindingSource
Private Shared conn2 As MySqlConnection
'''''''''''''''''''''''''''''''''''''''''''''''''''
'**************************************************
'''''''''''''''''''''''''''''''''''''''''''''''''''
With data bindings created this way, it really is as simple as calling a few lines of code to update both database tables.
''' <summary>
''' Save any changes made to the datasources
''' </summary>
Public Shared Sub saveChanges()
If dtAuthors.GetChanges?.Rows.Count > 0 Then
daAuthors.Update(dtAuthors)
dtAuthors.AcceptChanges()
End If
If dtTitles.GetChanges?.Rows.Count > 0 Then
daTitles.Update(dtTitles)
dtTitles.AcceptChanges()
End If
End Sub
''' <summary>
''' Close the MySQLConnection
''' </summary>
Public Shared Sub closeConnection()
conn2.Close()
End Sub
Conclusion
Using powerful ADO.Net features for data binding with a versatile and free to use MySQL database, it's easy to create very usable data management applications in VB.Net.
Download
Download here... (VB.Net and C# versions)