page_type	languages	products	description	urlFragment
sample	tsql sql csharp	azure azure-sql-database dotnet azure-functions	Geocoding sample for Azure SQL DB with Azure Functions	azure-sql-functions-geocode

Azure SQL geocoding with Azure Functions

The geocoding sample for Azure SQL leverages the sp_invoke_external_rest_endpoint stored procedure in Azure SQL Database and Azure SQL bindings for Azure Functions to geocode addresses after they are inserted in a table. In scenarios where the application inserting data to Azure SQL Database cannot be modified, it may be necessary to trigger a process after the data is inserted.

Contents

File/folder	Description
.github/workflows	GitHub Actions workflows for the sample to deploy the Functions and the SQL project.
architecture.png	Architecture diagram for the sample.
data-api-builder	Data API Builder component for the sample.
functions	C# Azure Functions for the sample.
testing.http	Sample HTTP requests for testing the sample.
workorder-database	SQL Database project for the sample.

Getting Started

Development Environment

• .NET 6 SDK
• Azure Functions Core Tools
• VS Code and the Azure Functions extension
• mssql extension for VS Code

Overall Architecture

• the Workorder Creation Form is a hypothetical application that creates work orders in Azure SQL Database, writing only the summary and address information to the database
• the Location Geocoding durable Azure Functions are triggered by the database with sp_invoke_external_rest_endpoint to the Workorder table in Azure SQL Database if the workorder is missing the geocoding information
• the Additional APIs are Azure Functions with SQL bindings for reading and writing to the Workorder table in Azure SQL Database
• the Data API Builder component has more information available in the Data API Builder README

Local Quickstart, Geocoding

1. From the Database Projects pane, right-click the workorder-database project and select Publish. Follow the dialogs to Publish to new Azure SQL Database emulator, which will deploy the project's schema to a container. The emulator will be available at localhost,1433 with the username sa and password you specified.
2. Copy the file functions/default.local.settings.json to functions/local.settings.json and update the SqlConnectionString value to point to the Azure SQL Database emulator.
3. Update the local Functions runtime settings with the Azure Functions: Set AzureWebJobsStorage command, which will link an Azure Storage account to the local Functions runtime for storing the state and queues of the durable function.
4. Create an Azure Maps account and locate the primary key. Add the primary key to functions/local.settings.json as the setting AzureMapKey.

sp_invoke_external_rest_endpoint is not yet publicly available, to test the geocoding function you will need to send a POST request to the function's endpoint. A sample request is available in testing.http.

Local Quickstart, APIs with SQL bindings

1. If you have not already done so, follow the steps 1 and 2 in the Local Quickstart, Geocoding section to deploy the database project and configure the local Functions runtime.
2. In testing.http, two sample GET requests are provided to try the SQL bindings APIs. The request with the route /api/ListWorkorders returns all workorders, the request with the route /api/Workorder/{id} returns a single workorder by ID.
3. In testing.http, a sample POST request is provided to try using the SQL bindings APIs to add a workorder.

Resources

• Azure SQL Database emulator
• SQL bindings for Azure Functions