Azure Function app V3 - how to access Azure SQL db?

Question

I have a V1 Function app that connects to a Azure SQL database like this:

#r "Newtonsoft.Json"
#r "Twilio"
#r "System.Data.SqlClient"
#r "Microsoft.Azure.WebJobs.Extensions.Twilio"
#r "SendGrid"
#r "System.Configuration"
#r "System.Data"

using System;
using System.Net;
using System.Configuration;
using System.Threading.Tasks;
using System.Data.SqlClient;

var sqlConnection = Environment.GetEnvironmentVariable("conString");
    var sqlConnection1 = "myconnection string";

    using (SqlConnection conn = new SqlConnection(sqlConnection1))
    {
        conn.Open();
        SqlCommand sqlCmdSupply = new SqlCommand();
        SqlDataReader readerSupply;
        sqlCmdSupply.CommandText = "SELECT TOP 1 Id FROM Sck WHERE sckt LIKE '%Su%' AND timeStamp > DATEADD(mi, -30, GETUTCDATE()) ORDER BY timeStamp DESC";
        sqlCmdSupply.Connection = conn;
        readerSupply = sqlCmdSupply.ExecuteReader();
        messageTw = null;
        messageSg = null;
        if (!readerSupply.HasRows)
        {
            do something
        }
    }

When I do this in a V3 function app I get this error: [Error] Executed 'Functions.WcTimerTrigger1' (Failed, Id=00f4c10a-9d03-4ce2-a970-918834ad982f, Duration=56ms)Unable to load DLL 'sni.dll' or one of its dependencies: The specified module could not be found. (0x8007007E)

I have found several posts asking about sni.dll, and similar issues with System.Data.SqlClient. No solutions offered, beyond use Microsoft.Data.SqlClient which does not work either.

What is the magic here?

Answer 1

Hi @wavemaster

I have gone through our official documentation on this matter and didn't run into any issues using the System.Data.SqlClient with version 4.5.1.

Below is a working sample where I pull a list of characters (strings) from an Azure SQL DB in an HttpTriggered Function App:

using System;  
using System.Threading.Tasks;  
using Microsoft.AspNetCore.Mvc;  
using Microsoft.Azure.WebJobs;  
using Microsoft.Azure.WebJobs.Extensions.Http;  
using Microsoft.AspNetCore.Http;  
using Microsoft.Extensions.Logging;  
using Newtonsoft.Json;  
using System.Data.SqlClient;  
using System.Collections.Generic;  
  
namespace murnunapp  
{  
    public static class HttpTrigger  
    {  
        [FunctionName("HttpTrigger")]  
        public static async Task<IActionResult> Run(  
            [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,  
            ILogger log)  
        {  
            log.LogInformation("C# HTTP trigger function processed a request.");  
  
            var connStr = Environment.GetEnvironmentVariable("SQLConnectionString");  
  
            List<Character> characters = new List<Character>();  
  
            using (SqlConnection conn = new SqlConnection(connStr))  
            {  
                conn.Open();  
                var text = "SELECT * FROM [users]";  
  
                using (SqlCommand cmd = new SqlCommand(text, conn))  
                {  
                    using (SqlDataReader reader = cmd.ExecuteReader())  
                    {  
                        while (reader.Read())  
                        {  
                            characters.Add(new Character(){ Firstname = (string) reader.GetValue(1), Lastname = (string) reader.GetValue(2) });  
                        }  
                    }  
                }  
            }  
  
            dynamic data = JsonConvert.SerializeObject(characters);  
              
            return new OkObjectResult(data);  
        }  
    }  
  
    public class Character  
    {  
        public string Firstname { get; set; }  
        public string Lastname { get; set; }  
    }  
}  

Which returns a JSON response of the following:

I hope this is useful, and the sample code above can be leveraged to resolve the issue that you're encountering. Just let me know if you had any further questions.