Click here to Skip to main content
16,017,857 members
Articles / Web Development / ASP.NET

Web Scheduler for managing timed jobs with ASP.NET pages

Rate me:
Please Sign up or sign in to vote.
4.76/5 (11 votes)
20 Jul 2011CPOL5 min read 94.9K   6.6K   78   34
Schedule timed tasks through ASP.NET administration pages.

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.

admin.jpg

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.
  2. Here is the structure of the JOBS database:

    job_struct.jpg

  3. 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.
  4. edit.jpg

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.

SQL
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:

SQL
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:

C#
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:

XML
<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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Website Administrator
Hungary Hungary
Working with Microsoft Dynamics NAV since 2007.

Comments and Discussions

 
AnswerRe: 1 Question Pin
Mihaly Sogorka22-Jul-11 20:07
Mihaly Sogorka22-Jul-11 20:07 
GeneralRe: 1 Question Pin
Xmen Real 22-Jul-11 21:38
professional Xmen Real 22-Jul-11 21:38 
GeneralRe: 1 Question Pin
Mihaly Sogorka23-Jul-11 10:45
Mihaly Sogorka23-Jul-11 10:45 
GeneralRe: 1 Question Pin
Xmen Real 23-Jul-11 14:39
professional Xmen Real 23-Jul-11 14:39 
GeneralMy vote of 5 Pin
Hoangitk20-Jul-11 22:02
professionalHoangitk20-Jul-11 22:02 
SuggestionFix the Code-format Pin
thatraja20-Jul-11 21:58
professionalthatraja20-Jul-11 21:58 
GeneralRe: Fix the Code-format Pin
Mihaly Sogorka20-Jul-11 22:08
Mihaly Sogorka20-Jul-11 22:08 
GeneralMy vote of 5 Pin
_Tushar Patil20-Jul-11 19:57
_Tushar Patil20-Jul-11 19:57 
Thank You Very much .........
i was Searching For this type of module....
Questioncan you plan to add schedule calendar feature? Pin
Alenty20-Jul-11 12:55
Alenty20-Jul-11 12:55 
AnswerRe: can you plan to add schedule calendar feature? Pin
Mihaly Sogorka20-Jul-11 22:13
Mihaly Sogorka20-Jul-11 22:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.