Running a SQL Script through windows service c#

Question

I am trying to schedule a task in my windows service, to run the script every week. I don't want to do through sql jobs. Any idea?

Answer 1

You could use the Quartz scheduling library for this. It supports CRON triggers. So you could define a simple job like:

public class Sqljob : IJob
{
    #region Constructors

    public Sqljob()
    {       
    }

    #endregion

    #region IJob members

    public void Execute(IJobExecutionContext context)
    {
        // Perform your SQl work here
    }

    #endregion
}

And have a scheduler to instantiate and schedule the job along with the trigger:

public class TaskScheduler : ITaskScheduler
{
    #region Private fields

    private readonly IScheduler _scheduler;

    #endregion

    #region Constructors

    public TaskScheduler(IScheduler scheduler)
    {
        _scheduler = scheduler;
    }

    #endregion

    #region ITaskScheduler members

    public string Name
    {
        get { return this.GetType().Name; }
    }

    public void Run()
    {
        ScheduleSqlJob();

        _scheduler.Start();
    }

    public void Stop()
    {
        if (_scheduler != null) _scheduler.Shutdown(true);
    }

    #endregion

    #region Private methods

    private void ScheduleSqlJob()
    {
        var jobDetails = JobBuilder.Create<Sqljob>()
                                   .WithIdentity("Sqljob")
                                   .Build();
        var trigger = TriggerBuilder.Create()
                                    .StartNow()
                                    .WithCronSchedule("// Get your CRON expression here"))
                                    .Build();
        _scheduler.ScheduleJob(jobDetails, trigger);
    }       

    #endregion
}

And your service can call the scheduler like:

public partial class MyService : ServiceBase
{
    #region Fields

    private readonly ITaskScheduler _taskScheduler;

    #endregion

    public MyService(ITaskScheduler taskScheduler)
    {
        InitializeComponent();
        _taskScheduler = taskScheduler;
    }

    protected override void OnStart(string[] args)
    {
        _taskScheduler.Run();
    }

    protected override void OnStop()
    {
        _taskScheduler.Stop();
    }
}