THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

Azure Functions, Dapper and SQL Server JSON to store form data

A common problem

Recently we had to setup a couple of contact form pages to allow users to register to the App 2.0 Beta program (now RTM) or to tell us which of the products we launched at CES 2017 they are interested into.

Such kind of request are quite frequent and usually, from a developer perspective, they are the worst thing someone can ask. They are boring to architect, boring to write, boring to test. Still, business and marketing needs and love them.

So how to satisfy this requirement in the fastest and easiest way, but still delivering high-quality and easy to maintain code, while also keeping an eye on integrating the data with the existing environment?

Evaluating Azure DocumentDB

Given that we are on Azure, one option was Azure DocumentDB. No need to define a schema upfront and it is usable just using REST so the UX developer could do anything on its own, just using JQuery. Azure DocumentDB can also be queried using a SQL language, so extracting data from it wouldn’t have been a problem.

But at the end, I decided to go another way. All our data, after swimming in a Data Lake are saved in a Azure SQL database where we heavily rely on its column-store features. So having an additional database to integrate was something that would have made the solution a bit more more complex than the simplest possible one. The famous quote

everything should be made as simple as possible, but no simpler

is what drives all my architectural decisions, so I wasn’t really happy about going with Azure DocumentDB.

With the additional fact that there are no really good tooling around Azure DocumentDB yet, I started to look for alternatives. The obvious alternative, aside from saving data into a blob, which was not on option since that would have been too simple, because it doesn’t offer any querying capability, was to use Azure SQL.

Moving to Azure SQL?

With Azure SQL you have great tooling (SQL Server Management Studio and now also the online query editor), we already have all knowledge in house, but surely the fact that it doesn’t allow to use just REST to read and write data was, again, something that wasn’t making me really happy.

Beside that, Azure SQL seemed to be the perfect option. JSON is now natively supported, so there is no problem to store data without a strictly enforced schema.

Since we’re already using SQL Azure, we wound’t even have to spend any additional cent for it. The only problem to solve was that you can’t use Azure SQL directly via JQuery.

Serverless is the key

The missing link — the REST interface — can easily be created using Azure Functions and a microORM like Dapper. Thanks to the serverless nature of Azure Functions all it’s need are the few lines of code to get the HTTP Request Body that contains the contact form “jsonifyied” data and store into the SQL Azure database.

The created Azure Function gets called each time an HTTP Request is done, using an HTTP Trigger. Here the function.json file that defines the function bindings:

{
"bindings": [
{
"authLevel": "function",
"name": "req",
"type": "httpTrigger",
"direction": "in"
},
{
"name": "res",
"type": "http",
"direction": "out"
}
],
"disabled": false
}

and the function full code is here:

https://gist.github.com/yorek/89fb1afecc34db3f9419ceb3ae9664d9#file-azure-functions-save-form-data-to-sql-azure-csx

Such additional layer is also a welcome addition since it allows to inject some additional validation checks and business if needed.

I’ve used a Stored Procedure here for better decoupling, and it does just a simple insert, with some additional checks.

https://gist.github.com/yorek/2877b555452c5d5a155a8f50dbfd9bf7#file-azure-functions-save-form-data-to-sql-azure-sql

It also turns some JSON data into columns, to make querying even easier.

Dapper helps to make interacting with SQL Azure a breeze (two, yes two, lines of code to open the connection and write to SQL azure), so all simplicity requirements are finally met. No more impedance mismatch. No more.

Given the existing external factors (resources, knowledge, integration) the simplest solution but not simpler has been achieved.

Without any existing factors I would probably have chosen Azure DocumentDB. But as an afterthought, I still have the gut feeling that SQL Azure would have been better in the long term (for example I can connect Marketing Manager’s Excel directly to the SQL Azure, something that I cannot do yet with DocumentDB)…so maybe SQL Azure would be my choice onve again. After all software is made to serve people, and this should drive at least 50% (if not more) of the architectural choices, IMHO.

In conclusion

Since I joined Sensoria I’ve moved to work on the cloud 100% of my time. And the more I work on the cloud, the more it is clear to me that serverless and simplicity (which means, implementation-wise: microservices) is the way to go. Efficiency is increased so much in this way that it’s really worth the effort, and embracing PaaS clearly becomes an obvious choice.

Published Sunday, February 05, 2017 7:01 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Reza said:

Hi Davide,

Thanks for the article. I wonder if I can use a SQL Server provisioned in Azure and connect to it from the App Functions?

I already have my function app in the same VNET as my SQL Server VM in Azure. However the Integration to SQL Table as the App Function Integration points out, does not work; does it need special coding or can be done similarly you stated above?

Any thoughts?

Thanks in advance,

Reza

February 24, 2017 10:56 AM
 

Pranay said:

Hi Davide,

Thanks for the work above. Really helpful, and many things to learn from one complete example.

Quick note: I had to add a project.json file on azure functions to reference dapper and sqlclient. Here is what I added.

{

 "frameworks": {

   "net46":{

     "dependencies": {

       "Dapper": "1.50.2",

       "System.Data.SqlClient":"4.1.0",

       "Microsoft.WindowsAzure.ConfigurationManager":"3.2.1"

     }

   }

  }

}

April 5, 2017 3:12 AM
 

John Smith said:

Re: Website Google Ranking

Hi,

We can fairly quickly promote your website to the top of the search rankings with no long term contracts!

We can place your website on top of the Natural Listings on Google, Yahoo and Bing. Our Search Engine Optimization team delivers more top rankings then anyone else and we can prove it. We do not use "link farms" or "black hat" methods that Google and the other search engines frown upon and can use to de-list or ban your site. The techniques are proprietary, involving some valuable closely held trade secrets. Our prices are less then half of what other companies charge.

We would be happy to send you a proposal using the top search phrases for your area of expertise. Please contact me at your convenience so we can start saving you some money.

In order for us to respond to your request for information, please include your Name, company’s website address (mandatory) and /or phone number.

Sincerely,

John Smith

johnseomarketing9@gmail.com

COMPLETE INTERNET MARKETING SOLUTION

SEO - PPC - SMO - Link Building - Copyright - Web Designing - PHP

May 13, 2017 12:06 AM

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog

Syndication

Privacy Statement