Use Azure Functions to connect to an Azure SQL Database

This topic shows you how to use Azure Functions to create a scheduled job that cleans up rows in a table in an Azure SQL Database. The new C# function is created based on a pre-defined timer trigger template in the Azure portal. To support this scenario, you must also set a database connection string as a setting in the function app. This scenario uses a bulk operation against the database. To have your function process individual CRUD operations in a Mobile Apps table, you should instead use Mobile Apps bindings.

Prerequisites

Get connection information

You need to get the connection string for the database you created when you completed Create an Azure SQL database in the Azure portal.

  1. Log in to the Azure portal.

  2. Select SQL Databases from the left-hand menu, and select your database on the SQL databases page.

  3. Select Show database connection strings and copy the complete ADO.NET connection string.

    Copy the ADO.NET connection string.

Set the connection string

A function app hosts the execution of your functions in Azure. It is a best practice to store connection strings and other secrets in your function app settings. Using application settings prevents accidental disclosure of the connection string with your code.

  1. Navigate to your function app you created Create a function in Azure that is triggered by a timer.

  2. Select Platform features > Application settings.

    Application settings for the function app.

  3. Scroll down to Connection strings and add a connection string using the settings as specified in the table.

    Add a connection string to the function app settings.

    Setting       Suggested value Description             
    Name  sqldb_connection  Used to access the stored connection string in your function code.   
    Value Copied string Past the connection string you copied in the previous section.
    Type SQL Database Use the default SQL Database connection.
  4. Click Save.

Now, you can add the C# function code that connects to your SQL Database.

Update your function code

  1. In your function app, select the timer-triggered function.

  2. Add the following assembly references at the top of the existing function code:

    #r "System.Configuration"
    #r "System.Data"
    
  3. Add the following using statements to the function:

    using System.Configuration;
    using System.Data.SqlClient;
    using System.Threading.Tasks;
    
  4. Replace the existing Run function with the following code:

    public static async Task Run(TimerInfo myTimer, TraceWriter log)
    {
        var str = ConfigurationManager.ConnectionStrings["sqldb_connection"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(str))
        {
            conn.Open();
            var text = "UPDATE SalesLT.SalesOrderHeader " + 
                    "SET [Status] = 5  WHERE ShipDate < GetDate();";
    
            using (SqlCommand cmd = new SqlCommand(text, conn))
            {
                // Execute the command and log the # rows affected.
                var rows = await cmd.ExecuteNonQueryAsync();
                log.Info($"{rows} rows were updated");
            }
        }
    }
    

    This sample command updates the Status column based on the ship date. It should update 32 rows of data.

  5. Click Save, watch the Logs windows for the next function execution, then note the number of rows updated in the SalesOrderHeader table.

    View the function logs.

Next steps

Next, learn how to use Functions with Logic Apps to integrate with other services.

For more information about Functions, see the following topics: