CODEWITHSUNDEEP
c#azureazure-sql-databaseazure-functionsazure-functions-runtime
Filippo
Filippo

Reputation: 55

Azure Function beta/v2 - Integration Dependensies - SQL/MySql

I'm using the beta version of the Azure Functions because I'm using the Graph API and I would like to connect a database (MySQL/SQL).

Just using the latest beta version/v2 with the following guide from Microsoft to connect a SQL Database I get an error.

I know that there are a few differences regarding the integration of the dependencies. I mean the use and the structure of the file "project.json" instead of "function.proj"

What I have to do to make it work?

This is the returned error. I remind that it's with the beta version because in my real project I use the Graph Api as well:

2018-08-08T06:43:59  Welcome, you are now connected to log-streaming service.
2018-08-08T06:44:06.613 [Information] Executing 'Functions.TimerTriggerCSharp1' (Reason='This function was programmatically called via the host APIs.', Id=a3eb2b63-b336-4f56-bc3e-a1862b8f2d06)
2018-08-08T06:44:06.671 [Information] C# Timer trigger function executed at: 8/8/2018 6:44:06 AM
2018-08-08T06:44:06.786 [Error] System.Private.CoreLib: Exception while executing function: Functions.TimerTriggerCSharp1. System.Private.CoreLib: Exception has been thrown by the target of an invocation. f-TimerTriggerCSharp1__-1020707796: Object reference not set to an instance of an object.
2018-08-08T06:44:06.906 [Error] Executed 'Functions.TimerTriggerCSharp1' (Failed, Id=a3eb2b63-b336-4f56-bc3e-a1862b8f2d06)
2018-08-08T06:45:07.294 [Information] Executing 'Functions.TimerTriggerCSharp1' (Reason='Timer fired at 2018-08-08T06:45:07.2936684+00:00', Id=2a28db1e-f70f-4919-b7c9-078d39fb12a6)
2018-08-08T06:45:07.300 [Information] C# Timer trigger function executed at: 8/8/2018 6:45:07 AM
2018-08-08T06:45:07.303 [Error] System.Private.CoreLib: Exception while executing function: Functions.TimerTriggerCSharp1. System.Private.CoreLib: Exception has been thrown by the target of an invocation. f-TimerTriggerCSharp1__-1020707796: Object reference not set to an instance of an object.
2018-08-08T06:45:07.893 [Error] Executed 'Functions.TimerTriggerCSharp1' (Failed, Id=2a28db1e-f70f-4919-b7c9-078d39fb12a6)

This instead is my "run.csx" file:

#r "System.Configuration"
#r "System.Data"


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

public static void Run(TimerInfo myTimer, ILogger log)
{
    log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");

    var str = ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(str))
    {
        conn.Open();
        //Just a test
        var text = "UPDATE Persons SET FirstName = 'Alfred Schmidt', LastName= 'Frankfurt'";

        using (SqlCommand cmd = new SqlCommand(text, conn))
        {
            // Execute the command and log the # rows affected.
            //var rows = await cmd.ExecuteNonQueryAsync();
            //log.Info($"{rows} rows were updated");
            log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");

        }
    }
}

Update

I'm trying to connect a MySQL database instead of a SQL and I get the following error: 

2018-08-08T12:54:46.569 [Error] run.csx(15,27): error CS0246: The type or namespace name 'MySqlConnection' could not be found (are you missing a using directive or an assembly reference?)

These are my dependencies: 

#r "System.Configuration" 
#r "System.Data" 
using System; 
using System.Configuration; 
using MySql.Data.MySqlClient; 
using System.Threading.Tasks; 

var str = Environment.GetEnvironmentVariable("MYSQLCONNSTR_MySqlConnection");

Upvotes: 3

Views: 443

Answers (1)

Jerry Liu
Jerry Liu

Reputation: 17790

ConfigurationManager is no longer supported in v2 function, see this relate comment.

In 2.0, we have moved to the new ASP.NET Core configuration model. We intend to support binding to an IConfiguration instance that will provide a similar API, but in the meantime, the workaround is to read environment variables as opposed to relying on the ConfigurationManager and its APIs.

So you could use following statement to get connection string. Prefix SQLAZURECONNSTR_ is added by Azure for SQLAzure connection string set under Connection strings part.

var str = Environment.GetEnvironmentVariable("SQLAZURECONNSTR_SqlConnection");

Update for MySql Connection

Go to https://<functionappname>.scm.azurewebsites.net/dev/wwwroot/, it shows content of function app. Right click on your function folder, New file, create function.proj as below. Adding this dependency file imports related assembly for you to reference.

<Project Sdk="Microsoft.NET.Sdk">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
    </PropertyGroup>
    <ItemGroup>
        <PackageReference Include="MySql.Data" Version="8.0.12" />
    </ItemGroup>
</Project>

Upvotes: 3

Related Questions