Azure App Services
.NET Core Azure Azure App Services Azure Web Job C# Dependency Injection PaaS SQL Visual Studio

How to Extend an Azure Web Job to Access SQL Data

Welcome to today’s post.

In a previous post I showed how to create a basic .NET Core console Web Job application using Visual Studio 2019.

In today’s post I will be showing how we can extend a basic .NET Core console Web Job application to enable access to a local SQL database.

When our Web Job console application is deployed to Azure as an App Service, the Web Job trigger function will access an Azure SQL database.

To achieve our goal to configuring data access we will require completing the following tasks:

  1. Create custom service(s) for our database access.
  2. Create a class to initialize container instances that will be used by our custom service.
  3. Enable dependency injection.
  4. Inject our application configuration, data context, and logging into the custom services.

The custom data interface is shown below:

namespace BookLoan.WebJob.Interfaces
{
    public interface ICustomDbService
    {
        public void RunDBProcess();
        public int GetResult { get;  }
    }
}

and the service is shown below:

using System;
using System.Linq;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using BookLoan.WebJob.Interfaces;
using BookLoan.WebJob.Data;

namespace BookLoan.WebJob.Services
{
    public class CustomDBService: ICustomDbService 
    {
        private readonly ILogger _logger;
        private readonly ApplicationDbContext _db;

        private int _result;

        public int GetResult => _result;

        public CustomDBService(IConfiguration configuration, 
            ILogger<CustomDBService> logger, ApplicationDbContext db)
        {
            this._logger = logger;
            this._db = db;
        }

        public void RunDBProcess()
        {
            Console.WriteLine("Running custom DB service within Web Job");
            _logger.LogInformation(
                $"The BookLoan Web Job custom DB service has been run.");
            _result = _db.Books.Count();
            UpdateFirstBook();
        }

        private void UpdateFirstBook()
        {
            var book = _db.Books.Where(b => b.ID == 1).SingleOrDefault();
            if (book != null)
            {
                book.DateUpdated = DateTime.Now;
                _db.SaveChanges();
            }
        }
    }
}

To be able to use the above custom service within out application, we will need to inject it into a service collection, then retrieve the custom service from the service provider.

A console application uses a generic host, so it is not a HTTP based .NET Core application, and we will not be able to bootstrap Startup() from the program.cs Main() method as we could with an ASP.NET Core application. In this case we can create an equivalent startup class that initializes the service collection of all our services and their dependencies (data context, logging, application configuration settings etc.).

The startup class Startup.cs is shown below:

using System;
using System.IO;

using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.EntityFrameworkCore;

using BookLoan.WebJob.Services;
using BookLoan.WebJob.Interfaces;
using BookLoan.WebJob.Data;

namespace BookLoan.WebJob
{
    #region Startup
    public class Startup
    {
        private IConfiguration _configuration;
        private readonly IServiceProvider _provider;

        public IServiceProvider serviceProvider
        {
            get { return this._provider; }
        }
        public IConfiguration configuration
        {
            get { return this._configuration; }
        }

        public Startup()
        {
            var services = new ServiceCollection();
            this.ConfigureServices(services);
            _provider = services.BuildServiceProvider();
        }

        public void ConfigureServices(IServiceCollection services)
        {
            this._configuration = config;

            services.AddLogging(config =>
            {
                config.AddConsole(opts => opts.IncludeScopes = true);
            });

            var environment = "";

            var hostBuilder = new HostBuilder()
                .UseContentRoot(Directory.GetCurrentDirectory())
                .ConfigureAppConfiguration((hostingContext, config) =>
                {
                    environment = hostingContext.HostingEnvironment.EnvironmentName;
                });

            var configBuilder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json", optional: true, reloadOnChange: false);
            if (environment.Length > 0)
                configBuilder.AddJsonFile(
                $"appsettings.{environment}.json", 
                    optional: false, reloadOnChange: true);
            configBuilder.AddEnvironmentVariables();

            IConfiguration config = configBuilder.Build();

            services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlServer(config.GetConnectionString("AppDbContext")));

            services.AddSingleton(config);
            services.AddScoped<ICustomDbService, CustomDBService>();
        }
    }
    #endregion
}

The startup class’s ConfigureServices() method sets up the dependencies within our service collection and includes the custom data service and configuration settings.

Remember the reason why our configuration should look for appsettings.json when running under the Development environment, and appsettings.production.json when running under the Production environment. If we don’t include the production app settings file in our configuration collection as shown above, then when we run the Web Job in Azure the connection strings for our Azure SQL database will not be found and we will get a SQL error when our custom data service is run!

Recall that WebJob functions are static so we cannot directly inject any dependencies into the Functions class! How do the get around this limitation?

Notice that we added the custom data service into our collection with a scoped lifetime. We could also use a singleton lifetime to achieve the same outcome as we will be instantiating the custom service class in the scope of the trigger method.

Here is how we instantiate and call our custom service methods:

public class Functions
{
    private const string QUEUE_NAME = "[ Azure storage queue name]";

    public static void ProcessQueueMessage([QueueTrigger(QUEUE_NAME)] string message,  
  	        ILogger logger)
    {
        logger.LogInformation(message);

        Startup startup = new Startup();
        var dbService = startup.serviceProvider.GetRequiredService<ICustomDbService>();
        dbService.RunDBProcess();

        logger.LogInformation($"Number of books in catalog is {dbService.GetResult}.");
    }
}

This is quite straightforward.

We used the IServiceProvider extension method GetRequiredService() to retrieve a reference to our custom service. Then we called a method within the custom service.

When the WebJob application is run we add a new entry to the queue:

After the entry is added, the web job trigger will run, and if our database connection is valid then we should see the query run successfully and return a result from our custom service back to the trigger method:

In the console output, we should see the log outputs consistent with our application logger commands and the query results:

From this example we have seen how to expand our basic web job and introduce other useful tasks such as email, which can increase the flexibility of our App Service WebJobs within Azure.

In a future post, I will show how to deploy this .NET Core WebJob application to Azure App Services and how to configure it.

That’s all for today’s post. I hope you found this post useful and informative.

Social media & sharing icons powered by UltimatelySocial