Integrate a SQL Server Database

The SQL Server is a relational database management system. It is designed and developed to maintain and collect data. The system carries many business intelligence services, analytics services, and transaction processing. The data stored on the server is saved in the relational database. The SQL stands for Structured Query Language that controls and manages the server.

In this hands-on lab, learners will learn how to integrate a SQL Server database. First, they will write code to create a SQL Server table and populate the table with sample data. Next, they will write code to retrieve data from the table. Finally, they will test the configuration using a test page provided with the web app.

Understand the Scenario

In this virtual lab, you are a developer for a company that is migrating its primary web app from its datacenter to Azure. Your job is to write code to access an Azure SQL database used by the web app. For this challenge, you will use 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. All of the code that you will write will be in a single code file. You will find a list of the files and objects used for this challenge in D:\challenge\CSST04.txt. Learners will use an Azure resource group that contains an Azure storage account. The configuration of the storage account may not be complete when you begin this challenge. You will receive a notification when the automated configuration is complete.

Configure access to a SQL Server

In this section of the lab, you will learn how to configure access to a SQL Server. First, you will sign in to the Azure portal and open a Remote Desktop client connection to the virtual machine. In the virtual machine, you will sign in to the Azure portal and display the Firewalls and virtual networks page of the SQL server. Next, you will add your client IP address to the firewall list, and then save the changes and display the Connection strings page for the customers Azure SQL database. Next, you will copy the ADO.NET connection string, open Notepad, and then paste the connection string into Notepad (or any text editor and replace the {your_username} and {your_password} values. Finally, they will check and confirm that a firewall rule exists for the IP address, and they pasted the ADO.NET connection string into a text editor and then updated the Azure SQL User ID and Password.

Open the challenge project

In this section of the virtual lab, you will open the D:\challenge\AzureStorageDevelopment.sln solution file and open Web.config and then set the testType appSetting to "4" and leave the project open for the rest of the challenge. Finally, you will check and confirm that the StorageChallenge project is open and that the testType appSetting in the Web.config file is set to "4".

Write the code for the CreateTable method

In this section of the virtual lab, you will learn how to write the code for the create table method. First, in Visual Studio, you will open Models/SQLServerContext.cs. and add a using statement for System.Data.SqlClient at the top of the file. Next, in the CreateTable method definition, you will create a string variable containing SQL code that creates a table identified by the tableName parameter. The table must support the CustomerData class. The schema of the CustomerData class is documented in the comment block at the top of the SQLServerContext.cs file. Next, using the ConnectionString field, you will open a connection to the Azure SQL database and test for the existence of the table and create the table if it does not exist. Finally, you will define a SQL statement to clear all rows from the table identified by the tableName parameter, and then execute the statement.

Write the code for the LoadData method

In this section of the virtual lab, you will write the code for the GetData method in SQL Server. First, in Models/SQLServerContext.cs, in the LoadData method, you will instantiate a SqlConnection variable using the connectionString field and instantiate a SqlCommand variable to insert a record into the table identified by the tableName parameter. Next, you will iterate through the CustomerData items in the customer's parameter and insert a row into the table for each item. The schema of the CustomerData class is documented in the comment block at the top of the SQLServerContext.cs file. Finally, you will check your work and confirm that a SqlConnection object is instantiated for the customers database and confirm that the code inserts a row into the table identified by the tableName parameter for each CustomerData object in the customer's parameters.

Write the code for the GetData method and Test the application

After writing the code for the LoadData method, in this section of the lab, learners will learn how to write the code for the GetData method and test the application. First, in Models/SQLServerContext.cs, in the GetData method, learners will instantiate a SqlConnection variable using the connectionString field and instantiate a SqlCommand variable to retrieve all rows from the table identified by the tableName parameter. Next, they will iterate through the results and then add a CustomerData instance to the list for each row and return the CustomerData objects list. Next, they will check and confirm that the code returns a list of CustomerData objects from the table identified by the tableName parameter. Finally, they will test the application by pressing F5 and using the SQL Server connection string and confirm that the web page displays the message that the SQL Server database is successfully configured and that the web page displays the customer rows.

Lab Summary Conclusion

After completing the "Integrate a SQL Server Database" virtual lab, you will have accomplished the following:

• Accessed an Azure SQL database programmatically.
• Created a SQL Server table.
• Loaded data to an Azure SQL database.
• Retrieved data from an Azure SQL database.