Integrate Relational Data into a Web App

The SQL Server is a relational database management system. It is created and developed to maintain and collect data. The system maintains multiple business intelligence operations, analytics operations, and transaction processing.

In this hands-on lab, you will learn how to integrate relational data into a web app. You will write code to create a SQL Server table and populate the table with sample data. Next, you will write code to retrieve data from the table and then write code to create and populate a MySQL table. Finally, you will write code to retrieve the data from the MySQL table and verify each step as you proceed through the challenge.

Understand the Scenario

In this lab, you are a developer for a company that is migrating its primary web app from its datacenter to Azure. You need to write code to access an Azure SQL database and an Azure Database for MySQL database used by the web app. For this challenge, you are provided with Visual Studio 2017 installed on a virtual machine. The ASP.NET MVC project for this challenge is in the D:\challenge folder on the virtual machine. You will work with the Models\SQLServerContext.cs and Models\MySQLContext.cs files for this challenge. You are using an Azure resource group that contains a SQL Server server and database, an Azure Database for MySQL database, an app service plan, and a web app. The configuration of the resources may not be complete when you begin this challenge. You will receive a notification when the automated configuration is complete. Take a moment to familiarize yourself with what you have in the environment.

Configure your environment

In this section of the lab, learners will learn how to configure the environment. First, they will sign in to the Azure portal and record the SQL Server connection string name and the MySQL connection string name. Learners will need to replace the tokens in both connection strings. The SQL Server database is named customers, and the Azure Database for MySQL database is named vendors. Next, learners will open a Remote Desktop client connection to the virtual machine and open the challenge project at D:\challenge\AzureStorageDevelopment.sln. Next, they will sign in to Visual Studio and open web.config, and then set the TestType appSetting to "12". Finally, in web.config, they will set the SQLConnection appSetting and then set the MySQLConnection appSetting.

Upload records to SQL Server

After configuring the environment, in this section of the lab, learners will learn how to upload records to SQL Server. First, in the CreateTable method of the SQLServerContext class, they will write code to test for the existence of the table that is identified by the tableName parameter and create the table if it does not exist. The table must support the CustomerData data structure. Next, they will clear any records from the table. In the LoadData method of the SQLServerContext class, they will write code to upload the data in the customer's parameter to the table identified by the tableName parameter and then test the application locally. The page should show that the SQL Server upload was successful. All other tests will fail at this point. Finally, they will deploy the project to the Azure web app and verify that data is uploaded to SQL Server.

Download records from SQL Server

In this section of the lab, learners will learn how to download records from SQL Server. First, in the GetData method of the SQLServerContext class, they will write code to retrieve all records from the table identified by the tableName parameter and populate and return a list of CustomerData objects. Next, they will test the application locally. The page should show that the SQL Server upload and download methods were successful. All other tests will fail at this point. Finally, they will deploy the project to the Azure web app and verify that data is downloaded from an Azure SQL Database.

Upload records to an Azure Database for MySQL table

In this section of the lab, learners will learn how to upload records to an Azure Database for MySQL table. First, in the CreateTable method of the MySQLContext class, they will write code to test for the existence of the table identified by the tableName parameter. Next, they will create the table if it does not exist and clear any records from the table. After this, in the LoadData method of the MySQLContext class, they will write code to upload the data in the vendor's parameter to the table identified by the tableName parameter and test the application locally. The page should show that the MySQL upload was successful. The MySQL download will fail at this point. Finally, they will deploy the project to the Azure web app and verify that a table was created in the Azure Database for MySQL vendors database, and then data was uploaded to the table.

Download records from the Azure Database for MySQL table

After uploading records to an Azure Database for MySQL table, in this section of the lab, learners will learn how to download records from the Azure Database for MySQL table. To accomplish this, first, in the GetData method of the MySQLContext class, they will write code to retrieve all records from the table identified by the tableName parameter and populate and return a list of VendorData objects. Next, they will test the application locally. The page should show that the MySQL upload and download methods were successful. All tests should pass at this point. Finally, they will deploy the project to the Azure web app and verify that data was downloaded from an Azure Database for MySQL table.

Lab Summary Conclusion

After completing the "Integrate Relational Data into a Web App" virtual lab, you will have accomplished the following:

• Created a table in an Azure SQL database.
• Uploaded data to an Azure SQL database.
• Downloaded data from an Azure SQL database.
• Created a table in an Azure Database for MySQL database.
• Uploaded data to an Azure Database for MySQL database.
• Downloaded data from an Azure Database for MySQL database.