Azure – Access Azure SQL Database From Azure Function

Hello Everybody,
In our last blog, we have discussed about, how to create basic Azure Function.In this blog, we will go little ahed and discuss, how to communicate the Azure Function with Azure SQL Database.In one of our blog we have discussed, how to create and consume Azure Sql Database.

To proceed with our discussion, we will create another Azure Function, which will communicate with one of the existing Azure SQL Database.Following are the steps to achieve the goal.

  1. Here we are not going in details about, how to create a Azure Function App as there is another blog on this.Here we are adding a new function to the existing Function App “ManasSampleFunction”.So go to your existing Function App and expand the Function App.Click the Add sign, to add a new function as shown in following figure.

  2. Azure - Add New Azure Function to existing Azure Function App
    Figure 1: Azure – Add New Azure Function to existing Azure Function App

  3. Once the function is created, Go to that function and Click on “View Files” on the Right side Blade. Click on Add image to add a file Give a file name as “Project.json” and Hit Enter to create that file.

  4. Azure - ViewFilesOfAzureFunction_1
    Figure 2: Azure – ViewFiles Of Azure Function_1

    Azure - ViewFilesOfAzureFunction_2
    Figure 3: Azure – ViewFiles Of Azure Function_2

  5. Click on newly created Project.json file and add the following code as in following figure and save the file.
    Figure 4: Azure – Content of Project. json file

    	 "frameworks": {
    			"dependencies": {
    			  "Dapper": "1.42.0",
  7. Click on newly created run.csx file and replace the file with following code and save the file.
  8.     using Microsoft.Azure.WebJobs.Host;
        using System.Configuration;
        using System.Data.SqlClient;
        using System.Net;
        using System;
        using System.Net.Http;
        using System.Threading.Tasks;
        using Dapper;
    	using DapperExtensions;
                public static async Task Run(HttpRequestMessage req, TraceWriter log)
                    log.Info($"C# HTTP trigger function processed a request. RequestUri={req.RequestUri}");
                        dynamic data = await req.Content.ReadAsAsync<object>();
                        // parse query parameter for Task Name
                        string taskName = req.GetQueryNameValuePairs()
                        .FirstOrDefault(q => string.Compare(q.Key, "name", true) == 0)
                        // parse query parameter for Task Description
                        string taskDescription = req.GetQueryNameValuePairs()
                        .FirstOrDefault(q => string.Compare(q.Key, "desc", true) == 0)
                       // parse query parameter for Task Priority
                        int taskPriority = Convert.ToInt32(req.GetQueryNameValuePairs()
                        .FirstOrDefault(q => string.Compare(q.Key, "prio", true) == 0)
                        //int taskPriority=Convert.ToInt32(priority);
                        var cnnString = ConfigurationManager.ConnectionStrings["ManasTaskSqlConnection"].ConnectionString;
                        using (SqlConnection connection = new SqlConnection(cnnString))
                            // insert a log to the database
                            string query = "INSERT INTO [dbo].[FarmingTasks] (TaskName,TaskDescription,TaskPriority) VALUES (@TaskName,@TaskDescription,@TaskPriority)";
                            using (SqlCommand cmd = new SqlCommand(query, connection))
                               cmd.Parameters.AddWithValue("@TaskName", taskName);
                                 log.Info("Data saved successfully!");
                         log.Info($"Unable to process your request!");
  9. We are almost ready with our Azure function and now need to configure our Azure Sql Database.For that we need to first get the connection string of our database as shown in following figure.

  10. Azure - ViewFilesOfAzureFunction_2
    Figure 5: Azure – Get Azure SQL Database Connection string

  11. Once we got our database connection string, need to configure our Azure Function.To configure Application connection string,
    Select your Azure Function App and go to Application Setting. Scroll down up to “Connection String”.Set connection name as “ManasTaskSqlConnection” and paste your copied connection string in the value field. Select “SQL Azure” form the drop down and click save to save the configuration as shown in following figure.

  12. Azure - Configure Connection string for the function
    Figure 5: Azure – Configure Connection string for the function

  13. Now we are fully ready with our Newly created Azure function.In this function we are adding a new record to our Farming task table.In the above function it is taking 3 parameters for 3 columns of the table .If you don’t pass any value to the function then, it will put “NA” as default value for that column as shown in the following figure of my table with a value of NA to each column.

    Azure - Data table before run the table with correct value
    Figure 6: Azure – Data table before run the table with correct value

    following are the parameters need to be set to test the function correctly.

    “name” for “TaskName” column,
    “desc” for “Task Description” and
    “prio” for Priority column.

    Now you can save and click run the function to test by providing the following test value as parameter.

    &name=Test Soil,&desc=Test the soil before start Teak wood farming,&prio=1

    and once it successfully execute check the database to confirm if it executed successfully and it will be look like following figure.

  14. Azure - Data table after run the table with correct value
    Figure 6: Azure – Data table after run the table with correct value

I hope this blog gives you a basic idea about how to communicate with database using Azure function.

Thanks for reading 

Keep reading, share your thoughts, experiences. Feel free to contact us to discuss more. If you have any suggestion / feedback / doubt, you are most welcome.

Stay tuned on Knowledge-Junction, will come up with more such articles

3 thoughts on “Azure – Access Azure SQL Database From Azure Function

This site uses Akismet to reduce spam. Learn how your comment data is processed.