Unable to connect Azure Function App with Database (using .net core 2.1)

Question

Please note (Environment): Function App: Version 2, Target Framework: .Net Core 2.1

I am developing a Function App, that will work like Web Api. This Function App will return the data from database tables, also it'll manipulate files in Azure storage(Blob). But I am stuck as I am unable to create ConnectionString from local.settings.json file. Ideally the connection string should be created by default as I followed some tutorials & no where mentioned any extra steps to create default connectionstring value, just need to create it in local.settings.json file.

following is my local.settings.json file content:-

    {
  "ConnectionStrings": {
    "mycs": "data source=servername;initial catalog=dbname;user id=XXXX;password=XXXX;MultipleActiveResultSets=True;"
  },
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet",
    "mycs": "data source=servername;initial catalog=dbname;user id=XXXX;password=XXXX;MultipleActiveResultSets=True;"
  }
}

following is my HttpTrigger file:

    namespace my_api
    {
        public class myDataContext : DbContext
        {
            public myDataContext() : base(GetConnectionString()) { }
            private static string GetConnectionString()
            {

                const string providerName = "System.Data.SqlClient";
                const string metadata = @"res://*/MYDB.csdl|res://*/MYDB.ssdl|res://*/MYDB.msl";
                try
                {
                    string connectString = ConfigurationManager.ConnectionStrings["mycs"].ToString();


                    // Initialize the connection string builder for the
                    // underlying provider.
                    SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder(connectString);

                    // Set the properties for the data source.
                    //sqlBuilder.IntegratedSecurity = true;
                    sqlBuilder.MultipleActiveResultSets = true;

                    // Build the SqlConnection connection string.
                    string providerString = sqlBuilder.ToString();

                    // Initialize the EntityConnectionStringBuilder.
                    EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();

                    //Set the provider name.
                    entityBuilder.Provider = providerName;

                    // Set the provider-specific connection string.
                    entityBuilder.ProviderConnectionString = providerString;

                    // Set the Metadata location.
                    entityBuilder.Metadata = metadata;

                    return entityBuilder.ConnectionString;
                }
                catch { }

                var connectionstring = Environment.GetEnvironmentVariable("mycs");
                return connectionstring;
            }

            public DbSet<flowerset> flowersets
            {
                get;
                set;
            }
        }
    }

Following is the code for :

namespace my_api

{ public static class helpService { [FunctionName("helpService_get")] public static async Task> Run( [HttpTrigger(AuthorizationLevel.Function, "get", Route = null)] HttpRequest req, ILogger log, ExecutionContext context) { log.LogInformation("C# HTTP trigger function processed a request helpService_get).");

        try {
            int page = 0;
            int pageSize = 20;

            myDataContext entity = new myDataContext();
            if (page == 0 && pageSize == 0)
            {
                return entity.helpsets.ToList();
            }
            if (pageSize <= 0) { pageSize = 20; }
            entity.helpsets.OrderByDescending(x => x.id).Skip((page - 1) * pageSize).Take(pageSize).ToList();
        }
        catch (Exception exx) {
            log.LogInformation("Exception changed (helpService_get): "+exx.Message);
        }
        return null;
    }

}//End of Class
}//End of Namespace

I am getting following error on line entity.helpsets.OrderByDescending(x => x.id).Skip((page - 1) * pageSize).Take(pageSize).ToList();:

Unable to determine the provider name for provider factory of type 'System.Data.SqlClient.SqlClientFactory'. Make sure that the ADO.NET provider is installed or registered in the application config.

Answer 1

According to my test, we can use System.Data.SqlClient to connect Azure SQL in Azure function V2.0. For example

1. Create an Azure Function with Visual Studio 2019
2. Install System.Data.SqlClient package(the version I sue is 4.5.1)
3. Develop the function

local.settings.json file content

  "ConnectionStrings": {
    "mycs": "Data Source="";Initial Catalog=DotNetAppSqlDb20190826105048_db;User Id="";Password="" "
  },

  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet"
  }
}

Code

[FunctionName("Function1")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            log.LogInformation("C# HTTP trigger function processed a request.");

            string name = req.Query["name"];

            string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
            dynamic data = JsonConvert.DeserializeObject(requestBody);
            name = name ?? data?.name;
            try
            {
                var connectionstring = System.Environment.GetEnvironmentVariable($"ConnectionStrings:mycs"); ;



                using (SqlConnection connection = new SqlConnection(connectionstring))
                {


                    connection.Open();
                    log.LogInformation(" sql login success");
                    StringBuilder sb = new StringBuilder();
                    sb.Append("select * from dbo.Todoes");

                    String sql = sb.ToString();

                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                log.LogInformation("{0} {1}", reader.GetInt32(0), reader.GetString(1));
                            }
                        }
                    }
                    connection.Close();
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
            }
            return name != null
                ? (ActionResult)new OkObjectResult($"Hello, {name}")
                : new BadRequestObjectResult("Please pass a name on the query string or in the request body");
        }

For more details, please refer to the document