Use Azure Functions to Execute a SQL Azure Stored Procedure on a Timer

It was hard to find this information all in one place, so I thought I'd throw a post together quick.

If you have the need to execute a stored procedure on a timer, that can be fairly easily accomplished with Azure Functions.

Create a new Timer Trigger Azure Function, set the cron timer as desired, and add code similar to the code below:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Dapper;

public static void Run(TimerInfo myTimer, TraceWriter log)
{
    var conString = ConfigurationManager.ConnectionStrings["ConString"]?.ConnectionString;
    
    using(var con = new SqlConnection(conString))
    {
        con.Execute("dbo.MyProc", commandType: CommandType.StoredProcedure);
    }
}

This code will not compile at this time. Note the name of the connection string, we'll need that in the app settings page.

Go to the Connection Strings section of the Application Settings for the function app (this will look very similar to the Connection Strings section of a Web App). Find the Application Settings by clicking on the name of the Function App and looking in the "Configured features" to the right.

Add a connection string with the same name that was used in your function app, "ConString" in the code above.

Now, click on the name of your function in the left pane (to see the .csx code), and on the far right side, next to "Test" click on the "View Files" tab. Add a new file called project.json, and place the following dependencies in the file. more info.

{
    "frameworks": {
        "net46": {
            "dependencies": {
                "Dapper": "1.50.2",
                "System.Data.SqlClient": "4.3.0",
                "Microsoft.WindowsAzure.ConfigurationManager": "3.2.3"
            }
        }
    }
}

You can add additional nuget package dependencies here as well.


And that should be it. Test your function by clicking the "Run" button and make sure everything was wired up correctly.


Hope this helps!
Aaron