Click here to Skip to main content
11,495,371 members (66,019 online)
Click here to Skip to main content

Web Based Job Scheduler

, 11 May 2007 LGPL3 95.2K 3K 112
Rate this:
Please Sign up or sign in to vote.
A complete VB.NET application to schedule DOS command tasks online.

Introduction

There are many ways to schedule a task on a web server. For example, you can use Windows Task Scheduler and SQL Server Agent. This application is unique in that you can:

  • Schedule jobs using a web browser.
  • Extend the scheduling application to include your own features.

Using the code

This application has three components:

  • A Windows Service that will run a job (DOS command text).
  • A Web Application that will let you schedule a job.
  • MS SQL Server database Stored Procedure to get the list of jobs that need to run.

Job and schedule information are stored in a MS SQL Server database. The web application lets you create a job with a private or a shared schedule. A shared schedule makes scheduling jobs more manageable. The scheduler supports seven schedule types:

Type Example
Hourly Run the schedule every hour
Daily Run job after a number of days
Weekly Run job on the following weekdays (Monday, Tuesday, etc.)
Weekly skip Run job on the following weekdays (Monday or Tuesday) skipping every other week
Week number Run job on the following weekdays (Monday or Tuesday); second week of month on the following months (January, February)
Calendar Run job on the following days (1st, 2nd, and the last day of the month); on the following months (January, February)
Run once

Each schedule type supports a time window with a start and end time.

Job Page.gif

Below is the Entity Relationship Diagram for the database:

ERD

The Windows Service has a timer that fires every minute. The event calls the GetJobsToRun Stored Procedure that lists the jobs that need to run. As each job runs, LastStatus and LastRunTime are updated. Also, the JobHistory table keeps the history of every time a job runs.

Below is the code for the GetJobsToRun Stored Procedure:

CREATE PROCEDURE GetJobsToRun
AS

SELECT    JobId, JobType, JobName, CommandText
FROM    Job
WHERE    JobId in (

SELECT    j.JobId
FROM    Schedule s 
    INNER JOIN Job j ON s.ScheduleId = j.ScheduleId
WHERE    s.ScheduleType = 'Hourly'
    and GetDate() between DATEADD(Hour, s.StartHour, 
        DATEADD(minute, s.StartMin, s.StartDate)) 
        and Coalesce(s.EndDate,'12/12/2078') 
    and (j.LastRunTime is null 
        or DATEDIFF(minute, j.LastRunTime, GetDate()) >= 
        (EveryHour*60) + EveryMinute)

UNION

SELECT    j.JobId
FROM    Schedule s 
    INNER JOIN Job j ON s.ScheduleId = j.ScheduleId
WHERE    s.ScheduleType = 'Daily'
    and GetDate() between s.StartDate and Coalesce(s.EndDate,'12/12/2078') 
    and (j.LastRunTime is null or 
        DATEDIFF(Day, j.LastRunTime, GetDate()) > s.RepeatDays)
    and (s.StartHour*60) + s.StartMin = 
        (DatePart(hour,GetDate())*60) + DatePart(minute,GetDate())

UNION

SELECT    j.JobId
FROM    Schedule s 
    INNER JOIN Job j ON s.ScheduleId = j.ScheduleId
    INNER JOIN ScheduleWeek w ON w.ScheduleId = j.ScheduleId
WHERE    s.ScheduleType = 'Weekly'
    and GetDate() between s.StartDate and Coalesce(s.EndDate,'12/12/2078') 
    and DATEPART (weekday , GetDate()) = w.WeekDayId
    and ((s.StartHour*60) + s.StartMin) <= 
        (DatePart(hour,GetDate())*60) + DatePart(minute,GetDate()) 
    and (j.LastRunTime is null 
        or DATEDIFF(Day, j.LastRunTime, GetDate()) > 0) 

UNION

SELECT    j.JobId
FROM    Schedule s 
    INNER JOIN Job j ON s.ScheduleId = j.ScheduleId
    INNER JOIN ScheduleWeek w ON w.ScheduleId = j.ScheduleId
WHERE    s.ScheduleType = 'WeeklySkip'
    and GetDate() between s.StartDate and Coalesce(s.EndDate,'12/12/2078') 
    and DATEPART (weekday , GetDate()) = w.WeekDayId
    and DATEDIFF(week, j.LastRunTime, GetDate()) >= s.RepeatWeeks 
    and ((s.StartHour*60) + s.StartMin) <= 
        (DatePart(hour,GetDate())*60) + DatePart(minute,GetDate()) 
    and (j.LastRunTime is null 
        or DATEDIFF(Day, j.LastRunTime, GetDate()) > 0) 

UNION

SELECT    j.JobId
FROM    Schedule s 
    INNER JOIN Job j ON s.ScheduleId = j.ScheduleId
    INNER JOIN ScheduleWeek w ON w.ScheduleId = j.ScheduleId
    INNER JOIN ScheduleMonth m ON m.ScheduleId = j.ScheduleId
WHERE    s.ScheduleType = 'WeekNumber'
    and GetDate() between s.StartDate and Coalesce(s.EndDate,'12/12/2078') 
    and w.WeekDayId = DATEPART (weekday , GetDate())
    and m.MonthId = DATEPART (month , GetDate())
    and s.WeekOfMonth = 
        (datepart(ww,GetDate())) + 1 - 
        datepart(ww,dateadd(dd,-(datepart(dd,GetDate())-1),GetDate()))
    and ((s.StartHour*60) + s.StartMin) <= 
        (DatePart(hour,GetDate())*60) + DatePart(minute,GetDate()) 
    and (j.LastRunTime is null 
        or DATEDIFF(Day, j.LastRunTime, GetDate()) > 0)

UNION

SELECT    j.JobId
FROM    Schedule s 
    INNER JOIN Job j ON s.ScheduleId = j.ScheduleId
    INNER JOIN ScheduleMonth m ON m.ScheduleId = j.ScheduleId
    INNER JOIN ScheduleDay d ON d.ScheduleId = j.ScheduleId
WHERE    s.ScheduleType = 'Calendar'
    and GetDate() between s.StartDate and Coalesce(s.EndDate,'12/12/2078') 
    and DATEPART (month , GetDate()) = m.MonthId
    and (DATEPART (day , GetDate()) = d.DayId
        or (d.DayId = 32 and 
        DAY(DATEADD(d, -DAY(DATEADD(m,1,GetDate())),DATEADD(m,1,GetDate()))) 
            = DATEPART(day ,GetDate()))
        )
    and ((s.StartHour*60) + s.StartMin) <= 
        (DatePart(hour,GetDate())*60) + DatePart(minute,GetDate()) 
    and (j.LastRunTime is null or DATEDIFF(Day, j.LastRunTime, GetDate()) > 0) 

UNION

SELECT    j.JobId
FROM    Schedule s 
    INNER JOIN Job j ON s.ScheduleId = j.ScheduleId
WHERE    s.ScheduleType = 'Once'
    and GetDate() between s.StartDate and Coalesce(s.EndDate,'12/12/2078') 
    and ((s.StartHour*60) + s.StartMin) <= 
        (DatePart(hour,GetDate())*60) + DatePart(minute,GetDate()) 
    and (j.LastRunTime is null) -- run once

)

Below is the code for the RunDosCommand function:

Function RunDosCommand(ByVal sCommandText As String, _
    Optional ByVal iTimeOutSec As Integer = 1) As String

    Dim iPos As Integer = sCommandText.IndexOf(" ")
    Dim sFileName As String
    Dim sArguments As String = ""

    If iPos = -1 Then
        sFileName = sCommandText
    Else
        sFileName = sCommandText.Substring(0, iPos)
        sArguments = sCommandText.Substring(iPos + 1)
    End If

    Dim sRet As String
    Dim oProcess As Process = New Process

    oProcess.StartInfo.UseShellExecute = False
    oProcess.StartInfo.RedirectStandardOutput = True
    oProcess.StartInfo.FileName = sFileName
    oProcess.StartInfo.Arguments = sArguments
    oProcess.StartInfo.WindowStyle = ProcessWindowStyle.Hidden
    oProcess.StartInfo.CreateNoWindow = True
    oProcess.Start()

    oProcess.WaitForExit(1000 * iTimeOutSec)
    If Not oProcess.HasExited Then
        oProcess.Kill()
        Return "Timeout"
    End If

    sRet = oProcess.StandardOutput.ReadToEnd()

    If oProcess.ExitCode <> 0 And sRet = "" Then
        sRet = "ExitCode: " & oProcess.ExitCode
    End If

    oProcess.Close()

    Return sRet
End Function

A free third-party calendar component is used from dynarch.com.

Deployment

Here are the steps to deploy this application:

  1. Connect to SQL Server and create a database called JobScheduler.
  2. Add a user to the database that would have write access.
  3. Open the SQL folder. Run ScheduleSchema.sql against your database.
  4. Run GetJobsToRun.sql against your database.
  5. Copy the WebScheduler folder to C:\Inetpub\wwwroot\ and make it a virtual directory.
  6. Double click on the C:\Inetpub\wwwroot\WebScheduler\connect.udl file and point it to the JobScheduler database. Test the connection and click OK.
  7. Copy the WinJobService folder to your programs folder.
  8. Double click on the <Programs Folder>\WinJobService\connect.udl file and point it to the JobScheduler database. Test the connection and click OK. (You can also copy the file you made in step #6 to this location.)
  9. Open the WinJobService (WinJobService\WinJobService.sln) project in Microsoft Visual Studio .NET 2003 and compile it.
  10. Register the server by running the Service registration script: WinJobService\bin\Install.vbs. You can uninstall the service later by running Uninstall.vbs.

License

This article, along with any associated source code and files, is licensed under The GNU Lesser General Public License (LGPLv3)

Share

About the Author

Igor Krupitsky
Web Developer
United States United States
Igor is a business intelligence consultant working in Tampa, Florida. He has a BS in Finance from University of South Carolina and Masters in Information Management System from University of South Florida. He also has following professional certifications: MCSD, MCDBA, MCAD.

Comments and Discussions

 
GeneralWeb Based Scheduler for DotNet4 Framework w VB.NET Pin
kchamilto726-Jul-12 9:23
memberkchamilto726-Jul-12 9:23 
I'm currently working on a project of creating a Job Scheduler for my company. Mainly to run Stored Procedures at certain times. I noticed your web page here Web Based Job Scheduler.

I briefly reviewed the code from the webpage it looks good, but I did read some comments at the bottom where people had problems with it because it's in dot net 2 framework. I also noticed there was a Csharp Scheduler which I downloaded and ran from my VS2010, but noticed some red line references within the code behind the ASPX pages. Not really a big fan of Csharp and prefer VB.Net.

Do you have a more recent version or working on a version that would be for dot net 4 framework?
Questionhow to make it works with Visual Studio 2005 Pin
andrew_safwat8-Apr-12 1:59
memberandrew_safwat8-Apr-12 1:59 
AnswerRe: how to make it works with Visual Studio 2005 Pin
edwtie15-Jul-12 0:59
memberedwtie15-Jul-12 0:59 
GeneralMy vote of 4 Pin
vherway31-Mar-12 23:35
membervherway31-Mar-12 23:35 
QuestionScheduled job not invoking? Pin
Member 27532121-Mar-12 20:21
memberMember 27532121-Mar-12 20:21 
GeneralModified version of this project Pin
Erez Bibi19-Nov-08 7:47
memberErez Bibi19-Nov-08 7:47 
QuestionPHP/MySQL version ? Pin
franck34214-Sep-08 7:18
memberfranck34214-Sep-08 7:18 
AnswerRe: PHP/MySQL version ? Pin
franck34216-Sep-08 9:04
memberfranck34216-Sep-08 9:04 
GeneralJob Service error Pin
muralintr7-Jul-08 21:28
membermuralintr7-Jul-08 21:28 
GeneralAdditions to your work Pin
Erez Bibi2-May-08 6:01
memberErez Bibi2-May-08 6:01 
GeneralRe: Additions to your work Pin
Member 91483420-Aug-08 20:59
memberMember 91483420-Aug-08 20:59 
GeneralRe: Additions to your work Pin
raghu_reddy84200210-Oct-08 16:52
memberraghu_reddy84200210-Oct-08 16:52 
GeneralAny way or idea to send a successful completion notification message to the user submitting the job Pin
Paul Fung22-Apr-08 10:30
memberPaul Fung22-Apr-08 10:30 
Generalhi Pin
Oseke Cilrh18-Nov-07 2:58
memberOseke Cilrh18-Nov-07 2:58 
QuestionSchedule a Task in Vista Pin
amitdg28-May-07 0:50
memberamitdg28-May-07 0:50 
QuestionProblem with WinJobService Pin
Aggie_Programmer24-May-07 4:20
memberAggie_Programmer24-May-07 4:20 
AnswerRe: Problem with WinJobService Pin
Aggie_Programmer24-May-07 8:37
memberAggie_Programmer24-May-07 8:37 
AnswerRe: Problem with WinJobService Pin
bimmer3311-Jun-07 15:14
memberbimmer3311-Jun-07 15:14 
Questionidea Pin
sides_dale19-May-07 12:22
membersides_dale19-May-07 12:22 
AnswerRe: idea Pin
Igor Krupitsky20-May-07 3:41
memberIgor Krupitsky20-May-07 3:41 
GeneralScheduling Options Pin
ComplexityChaos11-May-07 9:08
memberComplexityChaos11-May-07 9:08 
GeneralRe: Scheduling Options [modified] Pin
Igor Krupitsky11-May-07 14:26
memberIgor Krupitsky11-May-07 14:26 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 11 May 2007
Article Copyright 2007 by Igor Krupitsky
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid