Web Scheduler for managing timed jobs with ASP.NET pages

• Download jobs - 1.57 MB
• Download JOBS_db - 163 KB

Introduction

This article introduces a web based job scheduler where you can add and manage your daily or monthly administration or report sending tasks. There are some other available approaches to do this, but I found none of them fully satisfying so I decided to write my own job scheduler. On one hand, you can use the Windows Task Scheduler for running some scripts, and on the other hand, you can use the MS SQL Server Agent job scheduler to execute some of your Stored Procedures to handle your database maintenance jobs. This project intends to combine these two approaches so they can be handled through an easily manageable ASP.NET page.

Features

• Daily and weekly jobs (planning to improve this feature in the future)
• Two kinds of job steps: Run script, Run Stored Procedure
• For one job, you can add multiple steps
• Enable/disable a job
• Run job instantly

Background

The main goal of this project is to send weekly reports and to centralize jobs that need to be automated somehow, like backup jobs and other database maintenance tasks. I didn't want to tumble the Windows Task Scheduler or the Job scheduler in MS SQL Server, so I decided to create my own scheduler which has a clear administration page where jobs can easily be managed.

Using the code

The project has two main parts:

1. MS SQL Server database (file: JOBS_db.zip): this contains a few tables and some Stored Procedures. Basically, this is the framework of the system. It stores the data of your tasks and other relevant information. Besides, there are Stored Procedures that manage your jobs. E.g.: inserting, editing, and deleting jobs or job steps. These procedures then are invoked through the .NET application.

Here is the structure of the JOBS database:



2. Visual Studio project (file: jobs.zip): this solution holds the administration page of the project. It has some SQL execution routines that are fired when inserting, editing, or deleting jobs or job steps. It gives a clear overview of your tasks and also some extra features like enabling/disabling or running a job immediately.

Program logic

The main question about this project is how we can accomplish executing a job in the background at its own scheduled time without the need to start up the browser or anything else. The answer is very simple: you just need to create a job within the SQL Server Agent that runs daily every 10 seconds, for example. It's like an infinite loop and does nothing else; just starts up a Stored Procedure which executes and takes care of your tasks that are stored in the JOBS table. This way, all of your predefined jobs can be executed silently in the background at the right time. The Stored Procedure that needs to be placed in the loop job is called begin_jobs (also included in the zip files). It creates a CURSOR for the JOBS table and walks through the job records that must be executed at the right time and then fires them step-by-step.

CREATE PROCEDURE [dbo].[begin_jobs]
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @job_id int

  DECLARE jobCursor CURSOR FOR
  SELECT id
  FROM JOBS
  WHERE [enabled] = 1 AND
        valid = 1 AND
        job_is_running = 0 AND
        next_run <= GETDATE()
  OPEN jobCursor
  FETCH FROM jobCursor INTO @job_id
  WHILE (@@FETCH_STATUS = 0)
    BEGIN
      EXEC [dbo].[execute_job] @job_id
      FETCH FROM jobCursor INTO @job_id
    END
  CLOSE jobCursor
  DEALLOCATE jobCursor
END

Once the database structure is built, we are going to need some more Stored Procedures to manage our jobs. The following example will fully describe the insertion of a job through an ASP.NET page. The Stored Procedure that inserts a new line into the JOBS table is as follows:

CREATE PROCEDURE [dbo].[insert_job]
@scheduler_type varchar(1024),
@description varchar(1024),
@monday bit,
@tuesday bit,
@wednesday bit,
@thursday bit,
@friday bit,
@saturday bit,
@sunday bit,
@occurs_at datetime
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @schedule_type_id int,
          @job_id int
  SET @schedule_type_id = (SELECT TOP 1 id
                           FROM SCHEDULE_TYPES
                           WHERE UPPER([description]) = UPPER(@schedule_type))
  IF @description = '' OR @description IS NULL OR @schedule_type_id IS NULL
    RETURN
  INSERT INTO JOBS (schedule_type_id, [description])
  VALUES (@schedule_type_id, @description)
  SET @job_id = (SELECT @@IDENTITY)
  INSERT INTO SCHEDULES (job_id, monday, tuesday, wednesday, 
              thursday, friday, saturday, sunday, occurs_at)
  VALUES (@job_id, @monday, @tuesday, @wednesday, @thursday, 
          @friday, @saturday, @sunday, @occurs_at)

  -- UPDATE next_run
  EXEC [dbo].[update_next_run] @job_id
  -- UPDATE schedule_description
  EXEC [dbo].[update_schedule_description] @job_id
END

The ASP.NET application will call the above procedure when clicking on the Create job button like this:

using System.Data;
using System.Data.SqlClient;
using HelperLib;

private string connStringJobs = ConfigurationManager.ConnectionStrings["jobs"].ToString();
protected void btnCreate_Click(object sender, EventArgs e)
{
  //...some security checks
  SqlHelper mySqlHelper = new SqlHelper(connStringJobs, 
    CommandType.StoredProcedure, "insert_job",
    new SqlParameter("@schedule_type", dropListScheduleTypes.SelectedItem.Text),
    new SqlParameter("@description", txtDescription.Text),
    new SqlParameter("@monday", monday),
    new SqlParameter("@tuesday", tuesday),
    new SqlParameter("@wednesday", wednesday),
    new SqlParameter("@thursday", thursday),
    new SqlParameter("@friday", friday),
    new SqlParameter("@saturday" saturday),
    new SqlParameter("@sunday", sunday),
    new SqlParameter("@occurs_at", occurs_at));
  mySqlHelper.ExecuteNonQuery();
  mySqlHelper.Close();
  Response.Redirect("Default.aspx");
}

The connStringJobs variable takes its value from the web.config file:

<add name="jobs" 
  connectionString="server=xxx;database=JOBS;UID=userreader;PWD=nopass" 
  providerName="System.Data.SqlClient"/>

It has a hard coded username and password that also needs to be created in SQL Server as a database user login. My advice is to give admin permissions to itbecause this user will execute all the jobs within the JOB table. This is not the best security approach but if you plan to also run scripts in your own tasks, then you might encounter some permission problems when trying to run external scripts without giving admin rights to that user. If you just run Stored Procedures, then execute permission should be enough on related databases including the JOBS database. Execute permission is a must on the JOBS database.

The SqlHelper class should also be explained because it is not a standard C# class. It's my own collection of SQL tools. It simplifies the connection to a SQL Server and has many other helpful functions and methods that makes coding shorter. If anyone needs the source code for it, just ask me.

Installation

1. Restore the JOBS.bak (JOBS_db.zip) database into your SQL Server database. You can use the Microsoft SQL Server Management Studio Restore wizard for this. Right click on Databases and then click on "Restore database...". Then follow the on screen instructions. After you have successfully restored the database, you will see the above mentioned tables and Stored Procedures inside your JOBS database where you will also find some sample tasks which can be deleted later on in the administration page.
2. Create a database user login inside the JOBS database and give admin permissions to it. Name it userreader, and for password, give nopass.
3. Open the jobs.sln (jobs.zip) solution with Visual Studio and modify the two connection strings in the web.config file to meet your needs, then publish it to your site.
4. Open your browser and navigate to the jobs admin page which you have just published and start creating and managing your own jobs.
5. Create a job in Microsoft SQL Server Management Studio which contains only one step that runs the following Stored Procedure: [JOBS].[dbo].[begin_jobs], and create a schedule for this job that occurs every day in every 10 seconds.