How to write SQL queries for azure functions

As we all know, azure functions only support C# and javascript languages for writing codes. So if we want to write SQL queries in our function app, how will we do that? Well here is the answer: Use SQL cmd Utility! Yes, this utility can be used inside any of your functions which you would like to execute a SQL query.

Let’s see how to use this utility first…

You would need to download SQL cmd Utility provided by Microsoft which is of two types one for windows machines and the other is Linux. This utility works with any type of machine but it can be used only inside a function app written in C# language. Here are the steps:[Note: I am using windows 10 machine and Visual studio 2017 for this example.]

Download SQL cmd Utility from here  For windows machines, you would need to download the version according to your machine whether it is 32 bit or 64 bit. In my case, I downloaded “SqlLocalDB.110.dll” for a machine that was compiled in 64bit.

Next, create a new function app and add reference of SqlLocalDB.110.dll at your solution level as shown in the below screenshot:

Now we would need to configure these dll using function app settings so that we can use it inside our functions. For this, right-click on your “Function app settings” and click on the view setting. This will open up the config file for your function app titled azurefunction.json (if you are not sure about this file location, go to the top left corner of Visual Studio where you see two files with green color sql local and another one is an azure function which is your application’s config file).

Now, we can write a simple function that uses SQL cmd utility as shown in the below code:

public static async Task < HttpResponseMessage > Run ( HttpRequestMessage req , TraceWriter log ) { using (
var conn = new SqlConnection ( 
       "Server=tcp:DBServer.database.windows.net;
        Database=YourDB;
        User ID=your_user_ID; 
        Password=your_password" )) 
         } 
// conn is your connection string which you can get from function app setting by clicking on view setting for // this application string 
cmd = conn . Connection String ; 
// we are creating sql statement here to send to SQL cmd Utility and 
// we will pass it as parameter whenever we make a call to this function cmd 
+= @"select * from dbo.tablename where col1='value'" ; 
using ( 
var command = new SQL Command ( cmd , conn )) { 
           await command . Execute Non Query Async (); 
           } 
string result = await command . 
Execute Scalar Async (); 
return new Ok Object Result (“Value was found at column '" + result + "'" ); 
} }

You can see that the above code is very simple where we are creating a connection to our SQL server using Conn variable. We pass this connection string to SQL Command class so that it uses this during the execution of any SQL queries. You would need to pass all your parameters as query string values so that you can call this function from your application which consumes data from azure functions by passing the parameter in its query string e.g.: GET /api/myfunc?param1=value¶m2=13

As I mentioned earlier, in order to execute this query, you need to pass the parameter values in the query string. If you are wondering, what is that Ok Object Result doing there? This is because whenever we execute a command using SQL Command class, it returns us an object. So in our case, it will be an SQL data reader object which holds all the information about rows returned by our query or statement. Of course, if you are expecting only one row then this might not be useful for you so I am returning ok Object Result which can take multiple types.

So basically whenever you call your function with correct parameters, it would return your “value” which was found at column ‘col1’ row from the table name. Well don’t get worried, I will be coming up with a couple more examples soon.

FAQs:

1. Why did I use string variable Conn in my function?

Good question! This is because when it comes to Azure functions, all the variables are treated as strings so you need to cast them before using them. It’s a restriction of Azure functions but you can overcome this issue by using the “using static” directive where your class will have only static methods so that you need not worry about casting. But since we are just getting started with Azure Functions, I would suggest you do not over-engineer things right now and focus on the basic functionality first. Use it as a learning experience for yourself rather than taking stress because of how our code looks.

2. What is the difference between adding parameters in query string vs passing parameters directly to SQL Command class?

Well, the main difference is that I have used string concatenation to create my SQL command statement which includes my query as well as parameter values. This will work fine when you are executing locally but this won’t work when you are calling Azure function remotely because AFAIK, there is no way to pass string value through parameter binding in Azure Web Jobs at the moment.

Conclusion:

I hope this post will help you in getting started with Azure Functions. There are still a few things to look out for like method signature and the way we need to pass values through parameters when calling remote functions from client applications but it’s a great addition in Microsoft Azure cloud and that too without any coding effort!