c# - SQL Input Binding w Azure Functions (isolated worker model) - Stack Overflow

admin管理员组

文章数量:1391955

I am working with an HTTP triggered Azure function utilizing the isolated worker model. Other functions are working fine, but when utilizing the SQL input binding the function is not being recognized at runtime. My SQL database is on-prem accessible through a key vault that is stored in a connected service attached to the solution.

For reference I utilized this sample code from Microsoft: Microsoft Doc

My code is as follows:

using System.Collections.Generic;
using System.Linq;
using AffiniusPipeline_General;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Azure.Functions.Worker.Extensions.Sql;
using Microsoft.Azure.Functions.Worker.Http;
using Microsoft.Azure.WebJobs;
using Microsoft.Extensions.Logging;

namespace AffiniusPipeline_General
{
    public class GetCountries
    {
        [FunctionName("GetCountries")]
        public static IActionResult Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route ="getCountries")]
            HttpRequest req,
            [SqlInput(commandText: "SELECT [hmy], [sName], [sCode] FROM Countries",
                commandType: System.Data.CommandType.Text,
                parameters: null,
                connectionStringSetting: "SqlConnectionString")]
            IEnumerable<CountryTest> countries)
        {
            return new OkObjectResult(countries.FirstOrDefault());
        }
    }

    public class CountryTest
    {
        public int hmy { get; set; }
        public string sName { get; set; }
        public string sCode { get; set; }
    }
}

One issue I know I am running into is changing Function to FunctionName. FunctionName is utilized by using Microsoft.Azure.Webjobs; which is a nuget package specific to the in-process model for Azure functions.

Is this type of HTTP trigger better to use with the in-process model even though it will be deprecated next year? How have you gotten around this seemingly incorrect Microsoft documentation to get it work with your code?

Other HTTP functions showing up when running locally for testing not using SQL input binding

I am working with an HTTP triggered Azure function utilizing the isolated worker model. Other functions are working fine, but when utilizing the SQL input binding the function is not being recognized at runtime. My SQL database is on-prem accessible through a key vault that is stored in a connected service attached to the solution.

For reference I utilized this sample code from Microsoft: Microsoft Doc

My code is as follows:

using System.Collections.Generic;
using System.Linq;
using AffiniusPipeline_General;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Azure.Functions.Worker.Extensions.Sql;
using Microsoft.Azure.Functions.Worker.Http;
using Microsoft.Azure.WebJobs;
using Microsoft.Extensions.Logging;

namespace AffiniusPipeline_General
{
    public class GetCountries
    {
        [FunctionName("GetCountries")]
        public static IActionResult Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route ="getCountries")]
            HttpRequest req,
            [SqlInput(commandText: "SELECT [hmy], [sName], [sCode] FROM Countries",
                commandType: System.Data.CommandType.Text,
                parameters: null,
                connectionStringSetting: "SqlConnectionString")]
            IEnumerable<CountryTest> countries)
        {
            return new OkObjectResult(countries.FirstOrDefault());
        }
    }

    public class CountryTest
    {
        public int hmy { get; set; }
        public string sName { get; set; }
        public string sCode { get; set; }
    }
}

One issue I know I am running into is changing Function to FunctionName. FunctionName is utilized by using Microsoft.Azure.Webjobs; which is a nuget package specific to the in-process model for Azure functions.

Is this type of HTTP trigger better to use with the in-process model even though it will be deprecated next year? How have you gotten around this seemingly incorrect Microsoft documentation to get it work with your code?

Other HTTP functions showing up when running locally for testing not using SQL input binding

• c#
• sql
• azure
• azure-functions
• -8.0

Share Improve this question Follow asked Mar 13 at 13:43 AlnewberAlnewber 1111 silver badge22 bronze badges 1

• Replace [FunctionName] with [Function], it’s the correct attribute for the isolated worker model. – Dasari Kamali Commented Mar 14 at 3:52

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

I have modified your code to use the [Function] attribute instead of [FunctionName] since it is an isolated worker model and added extra logging for debugging. The HTTP trigger function with the SQL input binding ran successfully and I got the expected output without any issues.

GetCountries.cs :

using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Azure.Functions.Worker.Extensions.Sql;
using Microsoft.Azure.Functions.Worker.Http;
using Microsoft.Extensions.Logging;

namespace FunctionApp37
{
    public static class GetCountries
    {
        [Function("GetCountries")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "getCountries")]
            HttpRequestData req,
            FunctionContext context,
            [SqlInput("SELECT [hmy], [sName], [sCode] FROM Countries",
                      "SqlConnectionString")]
            IEnumerable<CountryTest> countries)
        {
            var logger = context.GetLogger("GetCountries");
            var connectionString = Environment.GetEnvironmentVariable("SqlConnectionString");
            if (string.IsNullOrEmpty(connectionString))
            {
                logger.LogError("SqlConnectionString is null or empty. Check local.settings.json.");
                return new StatusCodeResult(500);
            }
            logger.LogInformation("SqlConnectionString is set and available.");

            if (countries == null || !countries.Any())
            {
                logger.LogWarning("No data returned from the SQL query.");
                return new NotFoundResult();
            }

            logger.LogInformation($"Fetched {countries.Count()} records from the database.");
            var response = countries.FirstOrDefault();
            logger.LogInformation($"First record: hmy={response.hmy}, sName={response.sName}, sCode={response.sCode}");
            return new OkObjectResult(response);
        }
        public class CountryTest
        {
            public int hmy { get; set; }
            public string sName { get; set; }
            public string sCode { get; set; }
        }
    }
}

local.settings.json :

{
    "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet-isolated",
    "SqlConnectionString": "<SQLConneString>"
  }
}

Browser Output :

Terminal Output :

本文标签： cSQL Input Binding w Azure Functions (isolated worker model)Stack Overflow