TIP

💡 Learn more : Azure Functions Documentation.

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 and Rename an Azure SQL database. 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();
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23