Getting an Azure function to do something like write to a database is kind of handy and it's really straight forward.
In this post I'll be focusing on the SQL so I've assumed your up to speed on Azure functions. Check out my series of posts Click here for more on getting started with Azure functions.
We'll build on the other functions we have been building so check out my other posts if your unsure. We'll use an Azure HTTP trigger function to take our person payload and load it into the a SQL database. See Azure HTTP Trigger for more information on creating an Azure HTTP function
For example we have a webhook and when someone signs up on our website we want to load them into the database.
Our current function looks something like this
#r "System.Data"
using System.Net;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
log.Info("C# HTTP trigger function processed a request.");
string name = "";
dynamic body = await req.Content.ReadAsStringAsync();
var e = JsonConvert.DeserializeObject<Person>(body as string);
name = e.firstname + " " + e.lastname;
return name == " "
? req.CreateResponse(HttpStatusCode.BadRequest, "Please pass a name on the query string or in the request body")
: req.CreateResponse(HttpStatusCode.OK, "Hello " + name);
}
public class Person{
public string firstname {get;set;}
public string lastname {get;set;}
}
Last time around we added a custom route to the binding Azure Functions HTTP Routes
So instead of returning the "hello .." we want to return a status of "Ok". You can turn it off and have no response if you want but for us we're keeping the response.
So we change the return line of our code from
return name == " "
? req.CreateResponse(HttpStatusCode.BadRequest, "Please pass a name on the query string or in the request body")
: req.CreateResponse(HttpStatusCode.OK, "Hello " + name);
to return "ok"
return req.CreateResponse(HttpStatusCode.OK, "Ok");
Side note: you could return different HttpStatusCodes if you wanted for say a failre to update or if something wasn't right in the data. But for our purpose and in order to keep it clean we're going with a HttpStatusCode.Ok and a single message everytime.
What our code does now is to take a json payload
{
"firstname": "Azure",
"lastname": "Functions"
}
It then converts that payload into a person object
dynamic body = await req.Content.ReadAsStringAsync();
var e = JsonConvert.DeserializeObject<Person>(body as string);
And we now have an object e that has our data in.
So we want to write that to a database.
Azure Function SQL Insert
The simple way to insert data into a SQL database is as follows. This is the most common way according to the docs on MSDN as well.
var cnnString = "XXX";
using (SqlConnection conn = new SqlConnection(cnnString))
{
conn.Open();
// Insert Signup
var signupInsert = "INSERT INTO [dbo].[SubscriberDemo] ([FirstName],[LastName],[SignedUp])" +
"VALUES ('" + e.firstname + "','" + e.lastname + "',GETDATE())";
// Execute and load data into database.
using (SqlCommand cmd = new SqlCommand(signupInsert, conn))
{
var rows = cmd.ExecuteNonQuery();
}
}
Let's break down what we're doing before we talk about it because there is a bit of a problem doing it this way.
var cnnString = "XXX";
using (SqlConnection conn = new SqlConnection(cnnString))
{
conn.Open();
First step we get our connection string all setup, then open the connection to our database.
// Insert Signup
var signupInsert = "INSERT INTO [dbo].[Signup] ([FirstName],[LastName],[SignedUp])" +
"VALUES ('" + e.firstname + "','" + e.lastname + "',GETDATE())";
Next we setup our SQL string. We're inserting the information into the signup table, and we're looking for the firstname and lastname from the person object and we have a GetDate() to create todays date.
using (SqlCommand cmd = new SqlCommand(signupInsert, conn))
{
var rows = cmd.ExecuteNonQuery();
}
We now go ahead and execute our SQL script against out database. We use the cmdExecuteNonQuery() method because we are not getting anything back.
This will insert our data into our database for us.
But heres the problem, this code doing it this way could leave you open to a SQL injection attack.
The code I've used is ok as I have parameterised the values. But your just 1 simple mistake away from a real problem.
You can protect yourself of course, do the code like I have with only strings. Validate the inputs, etc. But you don't have to there are other options out there.
So lets look at another way to load data into SQL Server.
Azure Function Stored Procedures
One way to load data into SQL is through Store Procedures.
I have a Stored Procedure called "Subscriber_Insert" in the database.
It does the same as the above insert script. But we can do more checks on the data we are sending in.
#r "System.Data"
using System.Net;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
log.Info("C# HTTP trigger function processed a request.");
string name = "";
dynamic body = await req.Content.ReadAsStringAsync();
var e = JsonConvert.DeserializeObject<Person>(body as string);
name = e.firstname + " " + e.lastname;
//Connect to SQL
var cnnString = "XXXXX";
using (SqlConnection conn = new SqlConnection(cnnString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("Subscriber_Insert", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@FirstName", e.firstname));
cmd.Parameters.Add(new SqlParameter("@LastName", e.lastname));
var reader = cmd.ExecuteReader();
}
return req.CreateResponse(HttpStatusCode.OK, "Ok");
}
public class Person{
public string firstname {get;set;}
public string lastname {get;set;}
}
For the most part it's the same code. The big difference you can see is with our SqlCommand
SqlCommand cmd = new SqlCommand("Subscriber_Insert", conn);
cmd.CommandType = CommandType.StoredProcedure;
Our SqlCommand we set it to the name of the Stored Procedure.
CommandType we set to CommandType.StoredProcedure.
cmd.Parameters.Add(new SqlParameter("@FirstName", e.firstname));
cmd.Parameters.Add(new SqlParameter("@LastName", e.lastname));
var reader = cmd.ExecuteReader();
We set our parameters and then with cmd.ExecuteReader we call our Stored procedure and execute. Any response from the Stored procedure is in our reader.
That's it this is two different ways to get data into SQL from an Azure function.
We can build our script in the function or we can use a Stored procedure.
Comments