Connect Azure Functions to Azure SQL Database using Visual Studio Code

Before you begin, you must complete the quickstart: Create a C# function in Azure using Visual Studio Code. If you already cleaned up resources at the end of that article, go through the steps again to recreate the function app and related resources in Azure.

Before you begin, you must complete the quickstart: Create a JavaScript function in Azure using Visual Studio Code. If you already cleaned up resources at the end of that article, go through the steps again to recreate the function app and related resources in Azure.

Before you begin, you must complete the quickstart: Create a Python function in Azure using Visual Studio Code. If you already cleaned up resources at the end of that article, go through the steps again to recreate the function app and related resources in Azure.

With the exception of HTTP and timer triggers, bindings are implemented as extension packages. Run the following dotnet add package command in the Terminal window to add the Azure SQL extension package to your project.

dotnet add package Microsoft.Azure.Functions.Worker.Extensions.Sql

Your project has been configured to use extension bundles, which automatically installs a predefined set of extension packages.

Extension bundles usage is enabled in the host.json file at the root of the project, which appears as follows:

{
  "version": "2.0",
  "logging": {
    "applicationInsights": {
      "samplingSettings": {
        "isEnabled": true,
        "excludedTypes": "Request"
      }
    }
  },
  "extensionBundle": {
    "id": "Microsoft.Azure.Functions.ExtensionBundle",
    "version": "[4.*, 5.0.0)"
  },
  "concurrency": {
    "dynamicConcurrencyEnabled": true,
    "snapshotPersistenceEnabled": true
  }
}

:::

Open the HttpExample.cs project file and add the following ToDoItem class, which defines the object that is written to the database:

namespace AzureSQL.ToDo
{
    public class ToDoItem
    {
        public Guid Id { get; set; }
        public int? order { get; set; }
        public string title { get; set; }
        public string url { get; set; }
        public bool? completed { get; set; }
    }
}

In a C# class library project, the bindings are defined as binding attributes on the function method. The function.json file required by Functions is then auto-generated based on these attributes.

Open the HttpExample.cs project file and add the following output type class, which defines the combined objects that will be output from our function for both the HTTP response and the SQL output:

public static class OutputType
{
    [SqlOutput("dbo.ToDo", connectionStringSetting: "SqlConnectionString")]
    public ToDoItem ToDoItem { get; set; }
    public HttpResponseData HttpResponse { get; set; }
}

Add a using statement to the Microsoft.Azure.Functions.Worker.Extensions.Sql library to the top of the file:

using Microsoft.Azure.Functions.Worker.Extensions.Sql;

Binding attributes are defined directly in your code. The Azure SQL output configuration describes the fields required for an Azure SQL output binding.

For this MultiResponse scenario, you need to add an extraOutputs output binding to the function.

app.http('HttpExample', {
  methods: ['GET', 'POST'],
  extraOutputs: [sendToSql],
  handler: async (request, context) => {

Add the following properties to the binding configuration:

const sendToSql = output.sql({
  commandText: 'dbo.ToDo',
  connectionStringSetting: 'SqlConnectionString',
});

Binding attributes are defined directly in the function_app.py file. You use the generic_output_binding decorator to add an Azure SQL output binding:

@app.generic_output_binding(arg_name="toDoItems", type="sql", CommandText="dbo.ToDo", ConnectionStringSetting="SqlConnectionString"
    data_type=DataType.STRING)

In this code, arg_name identifies the binding parameter referenced in your code, type denotes the output binding is a SQL output binding, CommandText is the table that the binding writes to, and ConnectionStringSetting is the name of an application setting that contains the Azure SQL connection string. The connection string is in the SqlConnectionString setting in the local.settings.json file.

Replace the existing Run method with the following code:

[Function("HttpExample")]
public static OutputType Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post")] HttpRequestData req,
    FunctionContext executionContext)
{
    var logger = executionContext.GetLogger("HttpExample");
    logger.LogInformation("C# HTTP trigger function processed a request.");

    var message = "Welcome to Azure Functions!";

    var response = req.CreateResponse(HttpStatusCode.OK);
    response.Headers.Add("Content-Type", "text/plain; charset=utf-8");
    response.WriteString(message);

    // Return a response to both HTTP trigger and Azure SQL output binding.
    return new OutputType()
    {
         ToDoItem = new ToDoItem
        {
            id = System.Guid.NewGuid().ToString(),
            title = message,
            completed = false,
            url = ""
        },
        HttpResponse = response
    };
}

Add code that uses the extraInputs output binding object on context to send a JSON document to the named output binding function, sendToSql. Add this code before the return statement.

      const data = JSON.stringify([
        {
          // create a random ID
          Id: crypto.randomUUID(),
          title: name,
          completed: false,
          url: '',
        },
      ]);

      // Output to Database
      context.extraOutputs.set(sendToSql, data);

To utilize the crypto module, add the following line to the top of the file:

const crypto = require("crypto");

At this point, your function should look as follows:

const { app, output } = require('@azure/functions');
const crypto = require('crypto');

const sendToSql = output.sql({
  commandText: 'dbo.ToDo',
  connectionStringSetting: 'SqlConnectionString',
});

app.http('HttpExample', {
  methods: ['GET', 'POST'],
  extraOutputs: [sendToSql],
  handler: async (request, context) => {
    try {
      context.log(`Http function processed request for url "${request.url}"`);

      const name = request.query.get('name') || (await request.text());

      if (!name) {
        return { status: 404, body: 'Missing required data' };
      }

      // Stringified array of objects to be inserted into the database
      const data = JSON.stringify([
        {
          // create a random ID
          Id: crypto.randomUUID(),
          title: name,
          completed: false,
          url: '',
        },
      ]);

      // Output to Database
      context.extraOutputs.set(sendToSql, data);

      const responseMessage = name
        ? 'Hello, ' +
          name +
          '. This HTTP triggered function executed successfully.'
        : 'This HTTP triggered function executed successfully. Pass a name in the query string or in the request body for a personalized response.';

      // Return to HTTP client
      return { body: responseMessage };
    } catch (error) {
      context.log(`Error: ${error}`);
      return { status: 500, body: 'Internal Server Error' };
    }
  },
});

Update HttpExample\function_app.py to match the following code. Add the toDoItems parameter to the function definition and toDoItems.set() under the if name: statement:

import azure.functions as func
import logging
from azure.functions.decorators.core import DataType
import uuid

app = func.FunctionApp()

@app.function_name(name="HttpTrigger1")
@app.route(route="hello", auth_level=func.AuthLevel.ANONYMOUS)
@app.generic_output_binding(arg_name="toDoItems", type="sql", CommandText="dbo.ToDo", ConnectionStringSetting="SqlConnectionString",data_type=DataType.STRING)
def test_function(req: func.HttpRequest, toDoItems: func.Out[func.SqlRow]) -> func.HttpResponse:
     logging.info('Python HTTP trigger function processed a request.')
     name = req.get_json().get('name')
     if not name:
        try:
            req_body = req.get_json()
        except ValueError:
            pass
        else:
            name = req_body.get('name')

     if name:
        toDoItems.set(func.SqlRow({"Id": str(uuid.uuid4()), "title": name, "completed": False, "url": ""}))
        return func.HttpResponse(f"Hello {name}!")
     else:
        return func.HttpResponse(
                    "Please pass a name on the query string or in the request body",
                    status_code=400
                )

Run the function locally

Visual Studio Code integrates with Azure Functions Core tools to let you run this project on your local development computer before you publish to Azure.

1. To call your function, press F5 to start the function app project. The Terminal panel displays the output from Core Tools. Your app starts in the Terminal panel. You can see the URL endpoint of your HTTP-triggered function running locally.

   

   If you have trouble running on Windows, make sure that the default terminal for Visual Studio Code isn't set to WSL Bash.

2. With the Core Tools running, go to the Azure: Functions area. Under Functions, expand Local Project > Functions. Right-click (Windows) or Ctrl - click (macOS) the HttpExample function and choose Execute Function Now....

   

3. In the Enter request body, press Enter to send a request message to your function.

4. When the function executes locally and returns a response, a notification is raised in Visual Studio Code. Information about the function execution is shown in the Terminal panel.

5. Press Ctrl + C to stop Core Tools and disconnect the debugger.

Run the function locally

1. As in the previous article, press F5 to start the function app project and Core Tools.

2. With Core Tools running, go to the Azure: Functions area. Under Functions, expand Local Project > Functions. Right-click (Ctrl-click on Mac) the HttpExample function and choose Execute Function Now....

   

3. In Enter request body you see the request message body value of { "name": "Azure" }. Press Enter to send this request message to your function.

4. After a response is returned, press Ctrl + C to stop Core Tools.

• Examples of complete Function projects in C#.

• Azure Functions C# developer reference

• Examples of complete Function projects in JavaScript.

• Azure Functions JavaScript developer guide

• Examples of complete Function projects in Python.

• Azure Functions Python developer guide