Integrate a MySQL Database

In this IT Pro Challenge, learners will understand how to use Microsoft Visual Studio to modify an ADO.NET file to write code that will create a MySQL table, access an Azure Database for MySQL server, adds data to the table, and then retrieves data from the MySQL table. Learners will gain experience opening/closing connections, using for each statement, and try/catch blocks. The skills acquired in this hands-on lab are useful for a career as a software developer, system developer, or database administrator or developer.

Overview

The scenario for this hands-on lab is that you are a developer, and your company is migrating its primary web application from an on-premise datacenter to Azure. Your job is to write code to access an Azure database for the MySQL server used by the web application. You need to write code to create the MySQL table, populate the table with data, and retrieve data from the table. As a proof of concept, you will test your code and configuration using a test page that is part of the web application.

Configure the MySQL connection

To begin the lab, you need to sign in to the Azure portal, open a Remote Desktop client connection to the virtual machine, and then sign in to the Azure portal from that virtual machine. Then you need to navigate to the Connection security page of the provided Azure Database for MySQL server and add the client IP address to the firewall rules. Lastly, you need to copy the ADO.NET connection string and replace the values for {host_name}, {your_database}, {your_username}, and {our_password} with ones provided in the lab. Make a note of the new connection string.

Open the challenge project

Next, you will open the provided solution file, which will open Microsoft Visual Studio and add the MySQLData NuGet package to the project. Open the web.config file and set the testType appSetting.

Write the code for the CreateTable method

Now, in Microsoft Visual Studio, you will open the provided *.cs file and make the following changes:

• Create a string variable in the CreateTable method definition containing SQL code that creates a table identified by the tableName parameter that supports the VendorData class
• Use the ConnectionString field to open a connection to the Azure Database for MySQL server
• Test to see if the table exists; if it doesn’t, create it
• Create a SQL statement that clears all the table rows and executes the SQL statement

Example: string tableSQL = $"CREATE TABLE {tableName}(ID INT, Name VARCHAR (100), Industry VARCHAR(100));"; using (var conn = new MySqlConnection(this.ConnectionString)) { conn.Open(); using (var cmd = new MySqlCommand(tableSQL, conn)) { try { cmd.ExecuteNonQuery(); } catch { } } string clearSQL = $"DELETE FROM {tableName};"; using (var cmdClear = new MySQLCommand(clearSQL, conn)) { cmdClear.ExecuteNonQuery(); } conn.Close(); }

Write the code for the LoadData method

In the same *.cs file that you just edited, you will write code that instantiates a MySqlCommand variable, which inserts a row into the table identified in the tableName parameter. You will then write code that iterates through the VendorData items that are in the vendors parameter; insert a row into the table for each VendorData object.

Example: var sql = $"INSERT INTO {tableName}(ID, Name, Industry) VALUES(@ID, @Name, @Industry);"; using (var conn = new MySqlConnection(this.ConnectionString)) { using (var cmd = new MySqlCommand(sql, conn)) { cmd.Parameters.Add("@ID", MySqlDbType.Int32); cmd.Parameters.Add("@Name", MySqlDbType.String); cmd.Parameters.Add("@Industry", MySqlDbType.String); conn.Open(); foreach (var vendor in vendors) { cmd.Parameters["@ID"].Value = vendor.ID; cmd.Parameters["@Name"].Value = vendor.Name; cmd.Parameters["@Industry"].Value = vendor.Industry; cmd.ExecuteNonQuery(); } conn.Close(); } }

Write the code for the GetData method

Finally, in the *.cs file you’ve been using throughout the lab, you need to write code to instantiate a List of VendorData objects in the GetData method, instantiates a MySqlConnection variable using the connectionString field and a MySqlCommand variable to retrieve all rows defined in the tableName parameter. Then, you need to iterate through the results and add a VendorData instance to the list and return that list of VendorData objects.

Example: var result = new List(); var SQL = $"SELECT * FROM {tableName};"; using (var conn = new MySqlConnection(this.ConnectionString)) { using (var cmd = new new MySqlCommand(sql, conn)) { conn.Open(); var rdr = cmd.ExecuteReader(); while (rdr.Read()) { result.Add(new VendorData { ID = (int)rdr["ID"], Industry = rdr["Industry"].ToString(), Name = rdr["Name"].ToString() }); } conn.Close(); } }

Test the application

Finally, you will test all your work by running the application, pasting the connection string into the appropriate field, and clicking Test. If your code was correct, you will see a web page that tells you that you successfully uploaded vendor data to MySQL and successfully downloaded vendor data.

Summary Conclusion

By taking this virtual lab, you will learn how to programmatically access an Azure Database for MySQL, creating code for three methods. You will learn how to configure the MySQL connection and how to test your code changes.