Azure Functions SQL trigger and bindings design

bindings.md

Azure Functions SQL Server Trigger and Bindings Design

Trigger

C#

[FunctionName("ProductUpdates")]
public async static Task Run(
    [SqlTrigger("dbo.Products", Type = SqlTriggerType.ChangeTracking,
        ConnectionStringSetting = "SqlConnectionString",
        Columns = new string[] { "ProductId", "Name", "ShortDescription", "Price" })]
        IEnumerable<SqlChangeTrackingEntry<Product>> changes,
    ILogger logger)
{
    foreach (var change in changes)
    {
        SqlChangeTrackingChangeType changeType = change.ChangeType;
        Product product = change.Data;
    }
}

• Type - Only one type for now ChangeTracking
• ConnectionStringSetting - defaults to SqlConnectionString
• Columns - defaults to *

Parameter can be:

• IEnumerable<SqlChangeTrackingEntry<T>> - Retrieve changes in batches (how big?)
• SqlChangeTrackingEntry<T> - Trigger function for every change table entry
• IEnumerable<T> or T - Same as above, but without metadata
• IEnumerable<SqlChangeTrackingEntry> or SqlChangeTrackingEntry - data is dynamic

[FunctionName("ProductUpdates")]
public async static Task Run(
    [SqlTrigger("dbo.Products", Type = SqlTriggerType.ChangeTracking)]
        SqlChangeTrackingEntry<Product> change,
    ILogger logger)
{
    SqlChangeTrackingChangeType changeType = change.ChangeType;
    Product product = change.Data;
}

JavaScript

function.json

// binding
{
    "name": "changes",
    "direction": "in",
    "type": "sqlTrigger",
    "table": "dbo.Products",
    "connectionStringSetting": "SqlConnectionString",
    "columns": [
        "ProductId",
        "Name",
        "ShortDescription",
        "Price"
    ],
    "cardinality": "many" // or "one"
}

index.js

module.exports = async function (context, changes) {
    for (change of changes) {
        var changeType = change.changeType; // Insert, Update, Delete
        var product = change.data
    }
};

Input binding

C#

[FunctionName("GetProducts")]
public static IEnumerable<Product> Run(
    [HttpTrigger("get", Route = "products")]
        HttpRequest req,
    [Sql("select * from dbo.Products",
        ConnectionStringSetting = "SqlConnectionString")]
        IEnumerable<Product> products,
    ILogger logger)
{
    return products;
}

[FunctionName("GetProduct")]
public static Product Run(
    [HttpTrigger("get", Route = "products/{id}")]
        HttpRequest req,
    [Sql("select * from dbo.Products where ProductId = {id}",
        ConnectionStringSetting = "SqlConnectionString")]
        IEnumerable<Product> products,
    ILogger logger)
{
    return products.SingleOrDefault();
}

Output binding

C#

[FunctionName("CreateProduct")]
public async static Task<IActionResult> Run(
    [HttpTrigger("post", Route = "products")]
        HttpRequest req,
    [Sql("dbo.Products",
        ConnectionStringSetting = "SqlConnectionString")]
        IAsyncEnumerable<Product> products,
    ILogger logger)
{
    var requestBody = await new StreamReader(req.Body).ReadToEndAsync();
    var product = JsonConvert.DeserializeObject<Product>(requestBody);
    await products.AddAsync(product);
    // how do we get the new id?
    return new CreatedResult($"/api/products/{newId}", product);
}

[FunctionName("CreateProduct")]
public async static Task<IActionResult> Run(
    [HttpTrigger("post", Route = "products")]
        HttpRequest req,
    [Sql(ConnectionStringSetting = "SqlConnectionString")]
        SqlConnection connection,
    ILogger logger)
{
    var requestBody = await new StreamReader(req.Body).ReadToEndAsync();
    var product = JsonConvert.DeserializeObject<Product>(requestBody);

    SqlCommand command = new SqlCommand("insert into...", connection);
    command.Parameters.Add(...);
    await command.ExecuteNonQueryAsync();
    // get the new id

    return new CreatedResult($"/api/products/{newId}", product);
}