In an isolated azure function how to execute a SQL NonQuery store procedure and return the rows updated

Question

We now have to migrate some azure functions to the isolated storage worker, but cannot change deployed client software and would prefer not to change the SQL server. The functions are connected to an SQL database that inserts using stored procedures. So we have functions that access the database using the SqlConnection and SqlCommand objects. These cannot be used anymore in isolated worker as far as I understand it. The clients expect the number of rows updated to come back from the function.

So how can I change the function so it executes the stored procedure and returns the rowcount updated under the isolated worker model. i.e. the following line as that is where we get the return value that we need? The microsoft examples don't seem to cover that type of stored procedure.

 updated = command.ExecuteNonQuery();

[FunctionName("SetCounts")]
public static async Task<IActionResult> CreateMessages(
[HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "AddMultipleCounts")] HttpRequest req,
       ILogger log,
       ExecutionContext context)
   {
       string json = await new StreamReader(req.Body).ReadToEndAsync();
       int updated = SetSqldata(json, log);
       return new OkObjectResult($"added {updated}");
   }
        private static int SetSqldata(string json, ILogger log)
        {
            string SqlConnectionString = Environment.GetEnvironmentVariable("SqlConnectionString");
            int updated = 0;
            using (SqlConnection conn = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    using (SqlCommand command = new SqlCommand("sp_insertMessagesJson", conn))
                    {
                        conn.Open();
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@json", json);
                        updated = command.ExecuteNonQuery();
                    }
                }
                catch (Exception e)
                {
                    log.LogError(e, "SetSqldata");
                    throw;
#if DEBUG
                    Console.WriteLine(e.ToString());
#endif
                }
                return updated;
            }
        }

Answer 1

@Adrian Leeming ,

In Azure Functions running in the isolated worker model, you can still interact with SQL databases, but the approach is slightly different compared to the traditional model. You can use the Microsoft.Data.SqlClient package to execute SQL commands, including stored procedures.

Could you try the below code and let me know if this is working ,

using System;
using System.Data;
using System.IO;
using System.Threading.Tasks;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Azure.Functions.Worker.Http;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
public class SetCountsFunction
{
    private readonly ILogger<SetCountsFunction> _logger;
    public SetCountsFunction(ILogger<SetCountsFunction> logger)
    {
        _logger = logger;
    }
    [Function("SetCounts")]
    public async Task<HttpResponseData> CreateMessages(
        [HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "AddMultipleCounts")] HttpRequestData req)
    {
        try
        {
            string json = await new StreamReader(req.Body).ReadToEndAsync();
            int updated = await SetSqldata(json);
            var response = req.CreateResponse(System.Net.HttpStatusCode.OK);
            await response.WriteStringAsync($"added {updated}");
            return response;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error in CreateMessages");
            var errorResponse = req.CreateResponse(System.Net.HttpStatusCode.InternalServerError);
            await errorResponse.WriteStringAsync("Internal Server Error");
            return errorResponse;
        }
    }
    private async Task<int> SetSqldata(string json)
    {
        string connectionString = Environment.GetEnvironmentVariable("SqlConnectionString");
        int updated = 0;
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand("sp_insertMessagesJson", conn))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@json", json);
                await conn.OpenAsync();
                updated = await command.ExecuteNonQueryAsync();
            }
        }
        return updated;
    }
}

Ensure your stored procedure is designed to return the number of rows affected by the INSERT, UPDATE, or DELETE statements. If it doesn't explicitly do this, ExecuteNonQuery might return -1 or other values. You might need to use @@ROWCOUNT within the stored procedure. For example:

CREATE PROCEDURE sp_insertMessagesJson 
    @json NVARCHAR(MAX),
    @RowsAffected INT OUTPUT
AS
BEGIN
    -- Your INSERT/UPDATE/DELETE logic here using @json
    UPDATE YourTable
    SET SomeColumn = 'NewValue'
    WHERE SomeCondition = @json;
    -- Capture the row count
    SET @RowsAffected = @@ROWCOUNT;
END;