Loading a JSON File From an azure blog to a SQL server using C#

INTRODUCTION:

This blog demonstrates how to move an array of JSON files from an Azure blob to a SQL database and loading values from a nested array of JSON into a SQL table.

STEP-1: CREATE A STORAGE ACCOUNT

• Every type of data item, such as a blob, file shares, queue, table or disc is stored in an Azure storage account. The Storage account creates a distinctive namespace for Azure Storage data and may be accessed from anywhere in the world using HTTP or HTTPS.
• Create a storage account in azure and add a subscription to use blob storage.

STORAGE ACCOUNT

STEP-2: STORE FILE IN BLOB STORAGE

Blob storage allows for the storing of several file kinds, including JSON, text, and other formats. Selecting the upload option allows sending files from the local drive to blob storage.

BLOB STORAGE

• JSON file stored in azure blob contains an array of objects mentioned below.

JSON VALUES

STEP-3: CREATE A FUNCTION APP

• Create a function app in visual studio to get the file from azure blob storage.
• click create a project to create a function app.
• Search for the Azure function and Click the Azure function app that is presented below.
• Click next to add the project title.
• Click create to complete the function app creation.
• User’s can manually activate/trigger our function app, it retrieves files from Azure Blob Storage, and also user’s can set an event trigger for the function app.

FUNCTION APP

STEP-4: CREATE VISUAL STUDIO CODE

• The Below code describes how to get the file from azure, parse JSON and load it into an SQL table.
• Install the packages Microsoft.WindowsAzure.Storage, using Microsoft.WindowsAzure.Storage.Blob, System.Data.SqlClient,System.Threading.Tasks,Newtonsoft.Json.Linq,System.Data from NuGet Package Manager .
• Use Azure storage account credentials and SQL credentials to connect with the function app.
• To read JSON values and insert them into SQL tables, the stored procedure is utilized in the function app.

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
using System;
using System.Data.SqlClient;
using System.IO;
using System.Threading.Tasks;
using Newtonsoft.Json.Linq;
using System.Data;
namespace BlobQuickstartV12
{
class storageaccount
{
private static void Main()
{
try
   {
string storageConnectionString = “your connection string”;
CloudStorageAccount storageAccount =     CloudStorageAccount.Parse(storageConnectionString);
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
CloudBlobContainer container = blobClient.GetContainerReference(“container name”);
CloudBlockBlob blob = container.GetBlockBlobReference(“file name”);
string jsonFile = blob.DownloadTextAsync().Result;
//Console.WriteLine(jsonFile);
string connectionString;
SqlConnection cnn;
connectionString = @”SQL connection string”;
SqlConnection sqlConnection = new SqlConnection(connectionString);
cnn = sqlConnection;
cnn.Open();
Console.WriteLine(“success opened”);
using (SqlCommand cmd = new SqlCommand(“procedure name”, cn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“@json1”, SqlDbType.VarChar).Value = jsonFile;
cmd.ExecuteNonQuery();
Console.WriteLine(“Successfully inserted”);
cnn.Close();
}
}
catch (Exception e)
{
Console.WriteLine(“Error: “ + e.Message);
}
}
}
}

STEP-5: CREATE PROCEDURE IN SQL SERVER

A Stored procedure is constructed to insert entries into a SQL table. A stored procedure is a portion of SQL Query that can be saved and utilized again. In this stored method, the data is retrieved from a JSON file without the need for an index value and is parsed in a lateral format. In this procedure cross apply is utilized to extract the id value for both arrays in the JSON file.

CREATE PROCEDURE jsonproc_1 @json1 NVARCHAR(400)
AS
BEGIN
INSERT INTO JSON_TABLE
SELECT id, fills, active, quantity, price, clientOrderId, actualExecutionBroker
FROM OPENJSON(@json1)
WITH (
id INT’$.id’,
fills nvarchar(MAX)’$.fills’ AS JSON)
CROSS APPLY OPENJSON(fills) WITH(
active BIT’$.active’,
quantity FLOAT’$.quantity’,
price FLOAT’$.price’,
clientOrderId VARCHAR(256)’$.clientOrderId’,
actualExecutionBroker VARCHAR(256)’$.actualExecutionBroker’
)
END

STEP-6: CREATE A TABLE IN SQL SERVER

• Create a SQL table to load JSON values.
• While creating an SQL table create a distinct column to load an array of JSON and parse the JSON to load them into the individual column.

CREATE TABLE JSONTABLE(
id INT,
fills nvarchar(MAX),
active BIT,
quantity FLOAT,
price FLOAT,
clientOrderId VARCHAR(256),
actualExecutionBroker VARCHAR(256),
);

• Obtain the following results after executing above code.

• By using the table above as a reference, the id value and values of the JSON parsed process are correctly entered into the JSON array.

CONCLUSION:

Finally, this blog has shown how to parse JSON, get files from Azure, and load JSON data into SQL tables using function apps.

About us:

Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe and we’re quickly building a brand that is well known for superior delivery.

Website: https://bi3technologies.com/

Follow us on,
LinkedIn : https://www.linkedin.com/company/bi3technologies
Instagram : https://www.instagram.com/bi3technologies/
Twitter : https://twitter.com/Bi3Technologies