How to create Azure Function app with Sql Input Binding?

 Description

Here we are going to see how to make an Azure Function app with SQL Input Binding by practical

Prerequisite:

ü Azure Subscription

ü Visual Studio 2022

ü Knowledge of CRUD using C# with API is enough

ü Knowledge of SQL DB

What is Azure Function of Sql Input Binding?

Azure Functions are a serverless solution that allows you to write less code, maintain less infrastructure, and save on costs. Azure SQL bindings for Azure Functions make integrating your SQL data with serverless applications easier than ever. More information’s we can get it from here Hope this is enough, let’s move into the practical session

How to create Azure Functions from VS 2022?

1.  Open Visual Studio, from the initial screen Choose Create a new project

Azure Functions

 

2.  From the next window type function from the search option then Choose Azure Functions

3.  Give the project name as FunctionAppJ you could use any name and click Next

4.  Choose .Net 6 LTS, function as SQL input binding , set Connection string as J_SqlDbConnection (feel free to set any name as you wish) then click Create; now the project is created. Lets see our DB which is Azure SQL (here is the link to create new SQL DB, using this you can create new SQL server in Azure)

 

5.  Below is our DB and we are going to read data from Address table from the SalesLT schema

If you dont know about how to create and accecss Azure Sql Datbase then please use my blog by clicking here

6.  Lets go back to the VS 2022 so, after created the project we could see the Dependency window, there choose Azure Sql Database and click Next

7.  Next, VS force you to authenticate you by your Azure Subscription account, this will take more time sometimes you have to close the window and open again after auth we could see our Azure SQL select it and click Next

8.  Next window by default we could see our Database connection string name, type azure sql user id and password then Save Connection string value in Local user secrets file click Next

9.  Next window will show the necessary files, packages, class going to create there just click Finish

10.              Next window will start prepare as per above selection

11.              At the end automatically a class file called Function1 is opened with pre written code like below, it has Http Verbose, route, SQL query, connection string, sql command type and return sign.

12.              Now, just change [FunctionName("functionSqlInp")] and Sql statement into Sql("SELECT * FROM [SalesLT].[Address]" that’s it just run this project by pressing F5

13.              From the next screen copy the GET url and paste into the browser, now we could see the result in json format

 

14.              So, its working in localhost lets publish the same project into azure functions and lets see. Here I have written that how to create Azure Functions with screenshot’s if you haven’t created then please do it before next step.

15.              Now, right click from the project and click Publish, from the next window click Add a publish profile

16.              Then choose Azure

17.              Then choose Azure Function App (Windows)

18.              We have to Auth one more time then choose the Azure function which we created and click Finish

19.              Once, the above window dis-appears then click Publish  button and wait for few seconds since, it will move our files with all configurations until see Function app is ready in the output window

 

20.              Next , lets go back to browser, login azure.com and open our Function app called FuncAppJ from the left pane click Functions

 

21.              We could see the Azure function which we deployed from the VS2022, click on it

22.              From the new sub page, click get Function Url then it will show a small popup there we copy the link of our function to call

23.              Paste this link into a new tab in the browser now, we could see a error message so, we don’t know the actual error message but, there is a way to see the actual error message

24.              From the azure portal , under Functions from the left pane click Code + Test its like swagger to feed input and see the response with logs

25.              Click Test/Run from the top menu like below

26.              A new window opened at right side, there choose Http method as GET and click Run button then left side black color console window shows the logs, from there we could see that the connection string is not initialized so, Close this window and go back to FunctionAppJ from left side top

27.              Type connection  from the search box from the result select Configuration then new page will appear there click New Connection String

28.              There give the same connection string name which we used in VS2022 as J_SqlDbConnection value field is for connection string like in below format then choose Type as SqlAure then click Close

Server=tcp:xxxxx.xxxx.net,1433;Initial Catalog=JTestDB;Persist Security Info=False;User ID=YOURSQLID;Password=YOURSQLPASSWORD;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=300;

29.              that’s all the connection string is created. Click Save ; this save will restart our service so, wait for few seconds then after go back to the same url which we have tried and throws error refresh the page now, we could see the result like the last screenshot in json format

 Conclussion

Here the beauty is, I have just written table name and route, but, I have the end point to GET  data from my DB. For this I no need of server no need of Infra no need of OS License no monthly charge, the charges are based on our consumption.

Hope this is helpful, soon we’ll meet in new topic.