Azure SQL input binding for Azure Functions
This post is about Azure SQL input binding for Azure Functions - this feature is preview right now. The input binding helps us to read data from SQL Server in Azure Functions. To write to SQL Server we need to use the Output binding. Azure function now offers SQL trigger as well, we will be discussing this in a later post.
We will be first creating a function which reads from the SQL database using the Input binding. In the create function screen, we need to choose the SQL input binding, set the SQL connection string name and finally the table or view name.
In this example, we are reading from Notes table. Here is the schema of the table.
Since the Configure dependencies checkbox selected, Visual Studio will prompt to connect to the SQL Server, since I am working with my local development SQL Server, we can choose SQL Server Database (On-premise SQL Server Database)
And click on the next button to configure the local SQL server connection string. And for the connection string value in the local secrets file.
Once you click next after configuring the connection string, Visual Studio will display a summary of changes.
Click on the Finish button to apply the changes and create the azure function. Once we create the table and configured Azure Function, we will get some code like this.
Now we can run the application, and browse the URL in the console. Since we don’t have any records in the Notes table - it will show an empty JSON array. Once we add some records it will return the data as JSON. In this example we are selecting all the records, if we want to query a specific set of records we can customize the query and the input parameters we can get either from Query string or from the route parameters.
Here is an example where we are selecting a note by specifying the Id as query string.
In the above example the Id parameter is accessed from query string. We can use similar code for getting data from Route parameter as well. For example, the following code will display all the Notes created by the user.
This way we can use SQL Server Input binding in Azure Functions. In the next post we will explore the SQL Server Output binding. In the examples, we are using SQL Query text in the Azure Function, we can use Stored Procedure as well.