无法将Azure Function App与数据库连接（使用. net core 2.1）

Question

请注意(环境):函数App:版本2，目标框架:。网络核心2.1

我正在开发一个函数应用程序，它将像Web Api一样工作。这个函数应用程序将返回数据库表中的数据，还将操作Azure存储（Blob）中的文件。但我被卡住了，因为我无法从local.settings创建ConnectionString。json文件。理想情况下，连接字符串应该在默认情况下创建，就像我遵循一些教程一样

以下是我的local.settings.json文件内容:-

    {
  "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;"
  }
}

以下是我的HttpTrigger文件：

    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;
            }
        }
    }

以下是以下代码：

namespace my_api

{ 公共静态类 helpService { [FunctionName（“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

我在entity.helpsets行中遇到以下错误。OrderByDescending（x=

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

根据我的测试，我们可以使用<代码>系统。Data.SqlClient来连接Azure函数V2.0中的Azure SQL。例如

1. 使用Visual Studio 2019创建Azure功能
2. 安装System.Data。SqlClient软件包（版本为4.5.1）
3. 开发功能

local.settings.json文件内容

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

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

密码

[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");
        }