Import and export data for development in Azure SQL Database

In this exercise, you will import data from an external REST endpoint (simulated using Azure Static Web App) and export data using an Azure Function. The lab will provide practical experience in working with Azure SQL Database for development purposes, focusing on integrating REST APIs and Azure Functions to handle data import/export operations.

Prerequisites

Before starting this lab, ensure you have the following:

An active Azure subscription with permission to create and manage resources.
Basic knowledge of Azure SQL Database, REST APIs, and Azure Functions.
Visual Studio Code installed with the following extensions: - Azure Functions extension.
GitHub installed for cloning the repository.
SQL Server Management Studio (SSMS) or Azure Data Studio for managing the database.

Set up the environment

Let’s begin by setting up the necessary resources for this lab, including an Azure SQL Database and the tools needed to import and export data.

Create an Azure SQL Database

This step requires you to create a database in Azure:

In the Azure portal, go to the SQL Databases page.
Select Create.

Fill out the required fields:

Setting	Value
Free serverless offer	Apply offer
Subscription	Your subscription
Resource group	Select or create a new resource group
Database name	MyDB
Server	Select or create a new server
Authentication method	SQL authentication
Server admin login	sqladmin
Password	Enter a secure password
Confirm password	Confirm the password
Service tier	Basic

Select Review + Create, then Create.
After the deployment finishes, navigate to the Networking section of your SQL Server (not SQL Database) and add your IP address to the firewall rules.
Save your changes.

Clone the GitHub repository

Open Visual Studio Code.
Clone the GitHub Repo and prepare your project:
1. In Visual Studio Code, open the Command Palette by pressing Ctrl+Shift+P (Windows) or Cmd+Shift+P (Mac).
2. Type Git: Clone and select Git: Clone.
3. In the prompt, enter the following URL to clone the repository:
```
   https://github.com/MicrosoftLearning/mslearn-sql-dev.git
```
1. Choose the destination folder where you’d like to clone the repository.

Set Up Azure Blob Storage for JSON data

We’ll now set up Azure Blob Storage to host the employees.json file. Follow these steps within the Azure portal and Visual Studio Code.

Let’s start by creating an Azure Storage Account.

In the Azure portal, go to the Storage Accounts page.
Select Create.

Fill out the required fields:

Setting	Value
Subscription	Your subscription
Resource group	Select or create a new resource group
Storage account name	Choose a globally unique name
Region	Choose the region closest to you
Primary service	Azure Blob Storage or Azure Data Lake Storage Gen2
Performance	Standard
Redundancy	Locally-redundant storage (LRS)

Select Review + Create, then Create.
Wait for the storage account to be created.

Now that we have an account, let’s upload employees.json to Blob Storage.

Go to the Storage Accounts page in the Azure portal.
Select your storage account.
Navigate to the Containers section.
Create a new container named jsonfiles.
Inside the container, click Upload and upload the employees.json file located under the /Allfiles/Labs/04/blob-storage in the cloned directory.

While we could allow anonymous access to the file, in our case, let’s generate a Shared Access Signature (SAS) for this file to ensure secure access.

On the jsonfiles container, select the employees.json file.
Select Generate SAS from the file’s context menu.
Review the settings and select Generate SAS and URL.
A Blob SAS token and Blob SAS URL will be generated. Copy the Blob SAS token and the Blob SAS URL for use in the next steps. You won’t be able to access the token value again once you close that window.

We should now have a secure URL to access the employees.json file, let’s go ahead and test it.

Open a new browser tab and paste the Blob SAS URL.

You should see the contents of the employees.json file displayed in the browser, which should look like this:

 {
     "employees": [
         {
             "EmployeeID": 1,
             "FirstName": "John",
             "LastName": "Doe",
             "Department": "HR"
         },
         {
             "EmployeeID": 2,
             "FirstName": "Jane",
             "LastName": "Smith",
             "Department": "Engineering"
         }
     ]
 }

Import data from the blob storage to Azure SQL Database

We are now ready to import the data from the employees.json file hosted on Azure Blob Storage to our Azure SQL Database.

We need to start by creating a Master Key and a Database Scoped Credential in the Azure SQL Database.

Connect to your Azure SQL Database using SQL Server Management Studio (SSMS) or Azure Data Studio.
Run the following SQL command to create a master key if you don’t already have one:
```
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword!';
```
Next, create a Database Scoped Credential to access the Azure Blob Storage by running the following SQL command:
```
 CREATE DATABASE SCOPED CREDENTIAL MyBlobCredential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE', 
 SECRET = '<your-sas-token>';
```
Replace ****** with the **Blob SAS token** generated earlier.

Finally you need a Data Source to access the Azure Blob Storage. Run the following SQL command to create a Data Source:

 CREATE EXTERNAL DATA SOURCE MyBlobDataSource
 WITH (
     TYPE = BLOB_STORAGE,
     LOCATION = 'https://<your-storage-account-name>.blob.core.windows.net',
     CREDENTIAL = MyBlobCredential
 );

Replace ****** with the name of your Azure Storage Account.

Everything is now set up to import the data from the employees.json file into the Azure SQL Database.

Use the following SQL command to import data from the employees.json file hosted on Azure Blob Storage:

SELECT EmployeeID
    , FirstName
    , LastName
    , Department
INTO dbo.employee_data
FROM OPENROWSET(
    BULK 'jsonfiles/employees.json',
    DATA_SOURCE = 'MyBlobDataSource',
    SINGLE_CLOB
) AS JSONData
CROSS APPLY OPENJSON(JSONData.BulkColumn, '$.employees')
WITH (
    EmployeeID INT '$.EmployeeID',
    FirstName NVARCHAR(50) '$.FirstName',
    LastName NVARCHAR(50) '$.LastName',
    Department NVARCHAR(50) '$.Department'
) AS EmployeeData;

This command reads the employees.json file from the jsonfiles container in the Azure Blob Storage and imports the data into the employee_data table in the Azure SQL Database.

You can now run the following SQL command to verify the data import:

SELECT * FROM dbo.employee_data;

You should see the data from the employees.json file imported into the employee_data table.

Export data using an Azure Function App

In this part of the lab, you’ll create an Azure Function App in C# to export data from your Azure SQL Database. This function will retrieve the data and return it as a JSON response.

Create an Azure Function App in Visual Studio Code

Let’s start by creating an Azure Function App in Visual Studio Code:

Open Visual Studio Code.
On the explorer pane, navigate to the /Allfiles/Labs/04/azure-functions folder.
Right-click the azure-functions folder and select Open in Integrated Terminal.
In the VS Code terminal, log in to Azure using the following command:
```
 az login
```
Set the active subscription if you have multiple subscriptions:
```
 az account set --subscription <your-subscription-id>
```

Run the following command to create an Azure Function App:

 $functionappname = "YourUniqueFunctionAppName"
 $resourcegroup = "YourResourceGroupName"
 $location = "YourLocation"
 $storageaccount = "YourStorageAccountName"

 az storage account create --name $storageaccount --location $location --resource-group $resourcegroup --sku Standard_LRS
    
 az functionapp create --resource-group $resourcegroup --consumption-plan-location $location --runtime dotnet --name  $functionappname --os-type Linux --storage-account $storageaccount --functions-version 4

Replace the placeholders with your own values.

Create a new function app in Visual Studio Code

Let’s create a new function in Visual Studio Code to export data from the Azure SQL Database:

You might need to add the Azure Functions extension to Visual Studio Code if you haven’t already. You can do this by searching for Azure Functions in the extensions pane and installing it.

In Visual Studio Code, press Ctrl+Shift+P (Windows) or Cmd+Shift+P (Mac) to open the command palette.
Type and select Azure Functions: Create New Project.
Choose your Function App directory. Pick the /Allfiles/Labs/04/azure-functions folder of the GitHub clone repo.
Choose C# as the language.
Choose .Net 8.0 LTS as the runtime.
Choose HTTP trigger as the template.
Call the function ExportDataFunction.
Make the namespace Contoso.ExportFunction.
Give the function the anonymous access level.

Write the C# code for exporting data

The Azure Function App might need a few packages to be installed first. You can install them by running the following commands:

 dotnet add package Microsoft.Data.SqlClient
 dotnet add package Newtonsoft.Json
 dotnet restore
    
 npm install -g azure-functions-core-tools@4 --unsafe-perm true

Replace the placeholder function code with the following C# code to query your Azure SQL Database and return the results as JSON:

 using System;
 using System.IO;
 using Microsoft.AspNetCore.Mvc;
 using Microsoft.Azure.WebJobs;
 using Microsoft.Azure.WebJobs.Extensions.Http;
 using Microsoft.AspNetCore.Http;
 using Microsoft.Extensions.Logging;
 using Microsoft.Data.SqlClient;
 using Newtonsoft.Json;
 using System.Collections.Generic;
 using System.Threading.Tasks;
    
 public static class ExportDataFunction
 {
     [FunctionName("ExportDataFunction")]
     public static async Task<IActionResult> Run(
         [HttpTrigger(AuthorizationLevel.Function, "get", Route = null)] HttpRequest req,
         ILogger log)
     {
         // Connection string to the database
         string connectionString = "Server=tcp:yourserver.database.windows.net;Database=DataLabDatabase;User ID=youruserid;Password=yourpassword;Encrypt=True;";
            
         // List to hold employee data
         List<Employee> employees = new List<Employee>();
            
         try
         {
             // Establishing connection to the database
             using (SqlConnection conn = new SqlConnection(connectionString))
             {
                 await conn.OpenAsync();
                 var query = "SELECT EmployeeID, FirstName, LastName, Department FROM employee_data";
                    
                 // Executing the query
                 using (SqlCommand cmd = new SqlCommand(query, conn))
                 {
                     // Adding parameters to the query (if needed)
                     // cmd.Parameters.AddWithValue("@ParameterName", parameterValue);

                     using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
                     {
                         // Reading data from the database
                         while (await reader.ReadAsync())
                         {
                             employees.Add(new Employee
                             {
                                 EmployeeID = (int)reader["EmployeeID"],
                                 FirstName = reader["FirstName"].ToString(),
                                 LastName = reader["LastName"].ToString(),
                                 Department = reader["Department"].ToString()
                             });
                         }
                     }
                 }
             }
         }
         catch (SqlException ex)
         {
             // Logging SQL errors
             log.LogError($"SQL Error: {ex.Message}");
             return new StatusCodeResult(500);
         }
         catch (System.Exception ex)
         {
             // Logging unexpected errors
             log.LogError($"Unexpected Error: {ex.Message}");
             return new StatusCodeResult(500);
         }
    
         // Returning the list of employees as a JSON response
         return new OkObjectResult(JsonConvert.SerializeObject(employees, Formatting.Indented));
     }
    
     // Employee class to hold employee data
     public class Employee
     {
         public int EmployeeID { get; set; }
         public string FirstName { get; set; }
         public string LastName { get; set; }
         public string Department { get; set; }
     }
 }

Remember to replace the connectionString with the connection string to your Azure SQL Database.

Note: In a production environment, restrict access to only the necessary IP addresses. Additionally, consider using Managed Identities for your Azure Function App to access the database instead of SQL authentication. For more information, see the Managed identities in Microsoft Entra for Azure SQL.

Save the function code and ensure that your .csproj file includes the Newtonsoft.Json package for serializing objects to JSON. If it’s not included, add it:
```
 <PackageReference Include="Newtonsoft.Json" Version="13.X.X" />
```

Time to deploy the Azure Function App to Azure.

Deploy the Azure Function App to Azure

In the Visual Studio Code integrated terminal, run the following command to deploy the Azure Function to Azure:
```
 func azure functionapp publish <your-function-app-name>
```
Replace ****** with the name of your Azure Function App.
Wait for the deployment to complete.

Test the Azure Function App

Once the deployment is complete, you can test the function by sending an HTTP request:
```
 curl https://<your-function-app-name>.azurewebsites.net/api/ExportDataFunction
```
The response should contain the exported data from your employee_data table in JSON format.

While this function is a simple example, you can extend it to include more complex logic and data processing, such as filtering, sorting, and aggregating data, and more. Your code could also be extended to include error handling, logging, and security features.

Clean up the resources

After completing the lab, you can delete the resources created in this exercise to avoid incurring additional costs:

Delete the Azure SQL Database.
Delete the Azure Storage Account.
Delete the Azure Function App.
Delete the resource group containing the resources.