TIP

💡 Learn more : Azure Functions Documentation (opens new window).

Run TSQL on an Azure SQL database with Azure Functions

I've recently been adding Azure SQL tips such as Easily reset the Administrator password for an Azure SQL database (opens new window) and Rename an Azure SQL database (opens new window). and you all seem to like them. So I'm back with another SQL post that addresses another common scenario that folks ask "How do I run TSQL on an Azure SQL database with Azure Functions"?

SQL Database

Before we begin you'll need to grab the connection string from the database you created earlier. Simply select SQL Databases and select your database on the SQL databases page.

Click Show database connection strings and copy the string to your clipboard.

Go ahead and replace {your_username} and {your_password} placeholders with real values and save it somewhere easily accessible.

Azure Functions

Create a new Azure Function and select Timer Trigger. You typically want to store this secret in Platform features > Application settings in the Connection strings placeholder. So go ahead and do that as shown below:

Now use the following code

#r "System.Configuration"
#r "System.Data"

using System.Configuration;
using System.Data.SqlClient;
using System.Threading.Tasks;

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 MichaelTestDB.User " +
                "SET [Item] = 5  WHERE DateAdded < GetDate();";

        using (SqlCommand cmd = new SqlCommand(text, conn))
        {
            var rows = await cmd.ExecuteNonQueryAsync();
        }
    }
}

The previouse code may not work on Azure Functions Runtime Version 2 or above.ConfigurationManager is not used anymore. Check out this post https://www.koskila.net/how-to-access-azure-function-apps-settings-from-c/

Also, according to this Microsoft doc, ConnctionString is not recommended. "A collection. Don't use this collection for the connection strings used by your function bindings. This collection is used only by frameworks that typically get connection strings from the ConnectionStrings section of a configuration file, like Entity Framework. Connection strings in this object are added to the environment with the provider type of System.Data.SqlClient. Items in this collection aren't published to Azure with other app settings. You must explicitly add these values to the Connection strings collection of your function app settings. If you're creating a SqlConnection in your function code, you should store the connection string value with your other connections in Application Settings in the portal." https://docs.microsoft.com/en-us/azure/azure-functions/functions-run-local#local-settings-file

In addtion, Microsoft also recommended to use Microsoft.Data.SqlClient instead of System.Data.SqlClient. https://devblogs.microsoft.com/dotnet/introducing-the-new-microsoftdatasqlclient/

Proposed to use the following code:

#r "System.Configuration"
#r "System.Data"

#r more namespace will be generted by using visual studio
using System.Configuration;
//using System.Data.SqlClient; //replace it with Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient;
using System.Threading.Tasks;

using Microsoft.Extensions.Configuration; //need this for ConfigurationBuilder

public static async Task Run(TimerInfo myTimer, TraceWriter log, ExecutionContext context))
{
    var config = new ConfigurationBuilder().SetBasePath(context.FunctionAppDirectory).
                    AddJsonFile("local.settings.json", optional: true, reloadOnChange: true).AddEnvironmentVariables().Build();

    //var str = ConfigurationManager.ConnectionStrings["sqldb_connection"].ConnectionString;
    var str = config["sqldb_connection"];

    using (SqlConnection conn = new SqlConnection(str))
    {
        conn.Open();
        var text = "UPDATE MichaelTestDB.User " +
                "SET [Item] = 5  WHERE DateAdded < GetDate();";

        using (SqlCommand cmd = new SqlCommand(text, conn))
        {
            var rows = await cmd.ExecuteNonQueryAsync();
        }
    }
}