[toc]

Introduction

In our previous article we have already seen that how we can use Quartz.Net scheduler in our Asp.Net core console application and then run it as a windows service. The problem with that approach is that, the scheduler information will be lost once the system restarts or something bad happens. To rectify that problem we can actually save all the scheduler information in a database, luckily the Quartz.Net scheduler supports this and we can easily implement it. I hope the introduction is clear and let’s jump on to the implementation.

Source Code

The source code can be found in this GitHub repository. Please feel free to star, fork or do whatever you wish. You can also consider follow me on GitHub. LOL.

Setting Up Database

Before we start the coding, let us set up our database. You can choose any database as you wish. I am choosing SQL Server Express.

Install SQL Server Express

Enable sa Login

We should enable the sa login which we will use in our Quartz configuration. If you try to configure the Quartz with windows authentication, you will get an error as below.

2019-08-29 11:34:02.3570|INFO|Quartz.Core.QuartzScheduler|JobFactory set to: Backup.Service.Helpers.CustomJobFactory
2019-08-29 11:34:12.6801|INFO|Quartz.Impl.AdoJobStore.JobStoreTX|ConnectionAndTransactionHolder passed to RollbackConnection was null, ignoring
2019-08-29 11:34:12.6892|ERROR|Quartz.Impl.AdoJobStore.JobStoreTX|Failure occurred during job recovery: Failed to obtain DB connection from data source ‘default’: System.Data.SqlClient.SqlException (0x80131904): Cannot open database “BackupClientSchedules” requested by the login. The login failed.
Login failed for user ‘WORKGROUP\DESKTOP-3EF5B65$’.
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Quartz.Impl.AdoJobStore.JobStoreSupport.GetConnection() in C:\projects\quartznet\src\Quartz\Impl\AdoJobStore\JobStoreSupport.cs:line 323
ClientConnectionId:2efd5ab8-71aa-4598-b5ca-63c5aaa6afbe
Error Number:4060,State:1,Class:11

Yes, that is such a log error, and we don’t want any errors right. So let us configure sa login first. I have already explained how you can do that , in one of my StackOverflow answer, you can see that here.

Create Database and Tables Needed

Now we need to create a database and some tables where we can save the scheduler and trigger information. You can always see the table schema here in this official Quartz GitHub repository. You can also find the same file in our source code.

Before you run that query in your SQL Server Management Studio, make sure to create a database first and the use the same.

CREATE database BackupClientSchedules
USE BackupClientSchedules

Now id you see inside the database BackupClientSchedules, you should see some tables with no data inside.

Configure Quartz.Net to Use Database Instead of RAM

As we have already set up our database, now we can start configuring the service. If you are new Quartz.Net scheduler, I strongly recommend you to read my previous article.

Edit App.config

Let’s change the App.config file as preceding.

Note that we have set quartz.dataSource.default.provider as Sqlserver and also given the quartz.dataSource.default.connectionString. You should change these values with your provider and connection string.

Install the Nuget Package

We should install the Nuget package Quartz.Serialization.Json in our application.

Setup the Scheduler

In our previous article, we have see how we can setup the scheduler and we were using a function called GetScheduler for the same. Now we can edit the code of that function as preceding.

Please note the comments in the above code block.

Install the Service

Once we finished the above steps, we can run dotnet publish command with release configuration and install the service as preceding.

Again, read my previous article, if you need a detailed explanations about this step.

Output

Once the service started successfully, you should be able to see two log files backupclientlogfile_backupservice.txt and backupclientlogfile_helperservice.txt in our win7-x64 folder. You can see all the logs in this files.

You should also be able to see some scheduling information in our tables in our database. Some of the table’s screenshots are given below.

Conclusion

Wow!. Now we have learned,

• about Windows Service and Asp.Net Console Application
• about How to create a Windows Service using Asp.Net Core
• about How to use Quartz scheduler with database

Your turn. What do you think?

Thanks a lot for reading. Did I miss anything that you may think which is needed in this article? Could you find this post as useful? Kindly do not forget to share me your feedback.

Kindest Regards
Sibeesh Venu