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.



Figure 1: Azure – Add New Azure Function to existing Azure Function App

2. 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.



Figure 2: Azure – ViewFiles Of Azure Function_1



Figure 3: Azure – ViewFiles Of Azure Function_2

3. 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": {
		  "net46":{
			"dependencies": {
			  "Dapper": "1.42.0",
			  "System.Data.SqlClient":"4.1.0",
			  "Microsoft.WindowsAzure.ConfigurationManager":"3.2.1"
			}
		  }
		 }
	  }
	

4. Click on newly created run.csx file and replace the file with following code and save the file.

    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}");
    
                try
                {
                    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)
                    .Value;

                    if(string.IsNullOrEmpty(taskName))
                    {
                       taskName="NA"; 
                    }

                    // parse query parameter for Task Description
                    string taskDescription = req.GetQueryNameValuePairs()
                    .FirstOrDefault(q => string.Compare(q.Key, "desc", true) == 0)
                    .Value;

                    if(string.IsNullOrEmpty(taskDescription))
                    {
                       taskDescription="NA"; 
                    }

                   // parse query parameter for Task Priority
                    int taskPriority = Convert.ToInt32(req.GetQueryNameValuePairs()
                    .FirstOrDefault(q => string.Compare(q.Key, "prio", true) == 0)
                    .Value);

                    if(taskPriority==null)
                    {
                       taskPriority=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);
                           cmd.Parameters.AddWithValue("@TaskDescription",taskDescription);
                           cmd.Parameters.AddWithValue("@TaskPriority",taskPriority);

                            connection.Open();
                            cmd.ExecuteNonQuery();
                            connection.Close();
                             log.Info("Data saved successfully!");
                        }   
                    }
                }
                catch
                {
                    
                     log.Info($"Unable to process your request!");
                }   
                                 
            }

5. 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.



Figure 5: Azure – Get Azure SQL Database Connection string

6. 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.



Figure 5: Azure – Configure Connection string for the function

7. 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.

   
   
   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.



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