Click here to Skip to main content
13,192,855 members (54,877 online)
Click here to Skip to main content
Add your own
alternative version

Stats

18.2K views
5 bookmarked
Posted 5 Jul 2016

Azure Functions Tutorial – SQL Database

, 8 Jul 2016
Rate this:
Please Sign up or sign in to vote.
Often a service is required to connect to a database to perform some operations. In this post, we are going to look at having an Azure Function connect and insert a record in database table. We will showcase the usage on nuget packages and usage in Azure Function to automate package management.

In my previous post, I touched on how to create a simple Azure Function and invoke it through a web request.

In this post, I want to do something more interesting that has not yet been covered in any of the templates provided.

My objective is to insert a record into SQL Azure Database every time I receive a request.

For this post, we are going to assume you already have Azure SQL database up and running and already have a function app created.

Prerequisites

  1. Create an Azure SQL Database from the portal.
  2. Create a table called LogRequest with two columns (Id [PK, int, identity] , Log [nvarchar(max)]

    I used Visual Studio 2015 community to connect to SQL database can create the new table.

    sql database with table

Function Code and Database Configuration

  1. Click on “New Function“, then select “HttpTrigger – C#“, Name your function “HttpTriggerSqlDatabase” to make it easy to locate.
  2. Once you get the default code view, find the small link down the bottom of the code text box called “View files“.
  3. Click on the “+” sign to add new file, name the file “project.json”. We are going to use this file to add all required nuget packages.
    Copy and paste the json content below and hit save.
    {
      "frameworks": {
        "net46":{
          "dependencies": {
            "Dapper": "1.42.0",
            "System.Data.SqlClient":"4.1.0",
            "Microsoft.WindowsAzure.ConfigurationManager":"3.2.1"
          }
        }
       }
    }

    You should start seeing the logs restoring all missing nuget packages, then compile the code.

  4. Associate your database connection string to the Function App. To do that, click on the top link “Function app settings” then click the button “Go to App Service Settings

    azure function manage app service settings

    It will open your Function App settings page, then click on Data Contentions, Add.

    add new data connection

    Once you successfully added a connection called “SqlConnection“, close the views and navigate back to your function using the breadcrumbs.

  5. Click on your function named “HttpTriggerSqlDatabase”, copy the snippet below.
    using System.Net;
    using Dapper;
    using System.Data.SqlClient;
    using System.Configuration;
    
    public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
    {
        log.Info($"C# HTTP trigger function processed a request. RequestUri={req.RequestUri}");
    
        var successful =true;
        try
        {
            var cnnString  = ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString;
            
            using(var connection = new SqlConnection(cnnString))
            {
                connection.Open();
                
                var rLog = await req.Content.ReadAsAsync<LogRequest>();
                
                // insert a log to the database
                connection.Execute("INSERT INTO [dbo].[LogRequest] ([Log]) VALUES (@Log)", rLog);
                log.Info("Log added to database successfully!");
            }
        }
        catch
        {
            successful=false;
        }
        
        return !successful
            ? req.CreateResponse(HttpStatusCode.BadRequest, "Unable to process your request!")
            : req.CreateResponse(HttpStatusCode.OK, "Data saved successfully!");
    }
    public class LogRequest
    {
        public int Id{get;set;}
        public string Log{get;set;}
    }

Now save the code and ensure the logs show the function compiled successfully.

Testing the Function

Scrolling down the page to the Run section, you can invoke your API.

testing the api

I used LinqPad to retrieve the record from the database to verify that my data was actually saved successfully.

linqpad test

Conclusion

It was relatively easy to have my Azure Function connect to a database and insert a record triggered by a web request.

Using nuget to download packages and reference them in the function is extremely useful, given that most of the .NET framework and 3rd party are available as packages.

This scenario gives us the opportunity to design solutions that store request’s data into a relational database which is a step closer to real business application’s requirement.

In future posts, we will take this a step further and emit events to trigger other Functions to perform other actions meaningful to the data received.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sarmaad Amin
Architect MasterCard
Australia Australia
Coder by day, ninja by night..

I have been writing software for over 15 years spending a lot of time in domains like Real Estate, Warehousing & Logistics, IT & Telecommunication and Finance.

Since the rise of cloud computing, I grew passionate about solving problems with scale.

Today, I enjoy life travelling with my family and learning new "human" languages while meeting new people.

You may also be interested in...

Comments and Discussions

 
Questionconnection.Execute where did you get the documentation for the Execute methode? Pin
lapsdba2-Jan-17 14:50
memberlapsdba2-Jan-17 14:50 
AnswerRe: connection.Execute where did you get the documentation for the Execute methode? Pin
Member 110930545-Mar-17 22:43
memberMember 110930545-Mar-17 22:43 
QuestionI was looking for a way to use Azure function to replicate REST API I can down on your post Pin
lapsdba31-Dec-16 12:29
memberlapsdba31-Dec-16 12:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171017.2 | Last Updated 8 Jul 2016
Article Copyright 2016 by Sarmaad Amin
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid