In a project, I needed to run a task every day to process some data in an Azure SQL Database. I thought Azure Function App would be the perfect candidate for that because we can attach them on a schedule, and I will only get charges when they are running. In this post, I will create a function that will be executed every five minutes. It will read the information from an SQL table
Person and write the stats in another SQL table
Statistic.
Azure Setup
Let's start by creating the Azure Function App. From the Azure portal (portal.azure.com) click the "+" sign on the top left corner and in the Search textbox type
Function App. Fill-up the creation form like usual. Note that it will be a good idea to put your Function Appin the same location to what they will be interacting with; in this case an SQL Database. Once the Function App is created, it will be possible to create a new function. For that you can start with an empty one or use one of the multiple templates available. For this post, the TimeTrigger-CSharp was perfectly indicated.
On this page that you can configure the schedule. Here, I set it to
0 */5 * * * * because the task will be running every five minutes. It uses Cron expression, and to learn about it you can (should) refer to the
documentation. You can edit that value later by going to the
Integrate tab the function. Great, now that we have our function, we need to provide the SQL Database connection string. In this demo, I will use App Settings, but it could also be saved in the Azure Key Vault. Remember Function Apps are part of the Azure Apps ecosystem, thereby they have App Settings. To access it, it's really simple.
In the left panel menu click on the Function app setting options, then in the many choices look for Configure app settings. That will open the usual app setting blade that we use with other Web Apps. Scroll down until you've reached the Connection String section. Add your connection string to your server... And don't forget to save!
Database Setup
For this demo, I will use two simple tables.
CREATE TABLE [dbo].[Person] (
PersonID INT NOT NULL IDENTITY(1,1) PRIMARY key,
Firstname VARCHAR(50) NOT NULL,
Lastname VARCHAR(50) NOT NULL,
Age INT NOT NULL
)
I will insert manually records, adding people with a random age. The Function App on its side, will read the information from the table Person and calculate the average age and the number of people in the population. For finally inserted it into the table Statistic.
CREATE TABLE [dbo].[Statistic] (
StatisticID INT NOT NULL IDENTITY(1,1) PRIMARY key,
Population INT NOT NULL,
AverageAge INT NOT NULL,
DateTaken VARCHAR(50) NOT NULL
)
Coding the Function App
The code of this function is not styled or optimized. It was kept very simple so it was obvious was it was doing. Here is the code.
#r "System.Data"
using System;
using System.Configuration;
using System.Data.SqlClient;
public static async Task Run(TimerInfo myTimer, TraceWriter log)
{
log.Info($"C# Timer trigger function executed at: {DateTime.Now}");
var str = ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString;
using (SqlConnection conn = new SqlConnection(str))
{
var sum = 0;
var cnt = 0;
var avr = 0;
conn.Open();
var sqlStr = "SELECT Age FROM [dbo].[Person]";
using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
{
var dataReader = await cmd.ExecuteReaderAsync();
while(dataReader.Read()){
sum += dataReader.GetInt32(0);
cnt++;
}
avr = (sum / cnt);
log.Info($"The average actual population is {avr} .");
}
var sqlInsert = $"INSERT INTO [dbo].[Statistic](Population, AverageAge, DateTaken) VALUES ( {cnt} , {avr} , '{DateTime.Now}' )";
using(SqlCommand cmd = new SqlCommand(sqlInsert, conn)){
var rows = cmd.ExecuteNonQuery();
log.Info($"{rows} rows were inserted");
}
}
}
The first line is to add reference to
System.Data. Many libraries are already available to any Function App, and just require a reference. When you need an external library, you will need to create a file project.json and add it to the dependencies. Those will be loaded via Nuget. From there, the code talk by itself. Beginning by getting the connection string from the config, getting all the people and doing his magic, then finally inserting his result into the Statistic table.
Let's Run It
Everything is now in place. It's t to use your favorite SQL tool to insert some record into the Person table. Personally, I've created a little console application in .Net Core to do that. I share all about it at the end of this post. Once it's done, you will see the table Statistic growing every five minutes. Voila! Azure Function App a very useful and effective in this scenario. The code, of course, can be part of your repository, and now that Azure Function Tools have just been released you can even code them from Visual Studio!
Little Bonus
For a will now, every time I need to generate data, I tend to use a nice framework that my MVPs buddy the
ASP.Net Monsters has done called:
GenFu. Available on github, GenFu is a library that generates realistic test data. Here the code I used to generate the population in this demo.
using System;
using System.Data.SqlClient;
using GenFu;
namespace ConsoleApplication
{
public class Program
{
public static void Main(string[] args)
{
int counter = (args.Length > 0) ? Convert.ToInt32(args[0]) : 5;
var Users = A.ListOf<Person>(counter);
var connStr = "Server=YOURSERVERNAME.database.windows.net,1433;Initial Catalog=YOURDATABASENAME;Persist Security Info=False;User ID=YOURUSER;Password=YOURPASSWORD;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
foreach (var u in Users)
{
var sqlStr = $"INSERT INTO [dbo].[Person] (Firstname,Lastname,Age) VALUES ( '{u.Firstname}' , '{u.Lastname}' , {u.Age} )";
using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
{
var rows = cmd.ExecuteNonQuery();
Console.WriteLine($"{rows} rows were inserted");
}
}
conn.Close();
}
}
}
public class Person
{
public string Firstname { get; set; }
public string Lastname { get; set; }
public int Age { get; set; }
}
}
You will need to add Genfu reference to the project.json file.
{
"version": "1.0.0-*",
"buildOptions": {
"debugType": "portable",
"emitEntryPoint": true
},
"dependencies": {
"Genfu": "1.2.1",
"System.Data.SqlClient": "4.3.0"
},
"frameworks": {
"netcoreapp1.0": {
"dependencies": {
"Microsoft.NETCore.App": {
"type": "platform",
"version": "1.0.1"
}
},
"imports": "dnxcore50"
}
},
"runtimes": {
"win-x64":{}
}
}
Happy coding!
Suggestion of the week
