JOB 创建包含步骤和计划的作业

//引用：

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Smo.Broker;
using Microsoft.SqlServer.Management.Smo.Internal;
using Microsoft.SqlServer.Management.Smo.Mail;
using Microsoft.SqlServer.Management.Smo.SqlEnum;

using System.Data.OleDb;
using System.IO;
using System.Data.SqlClient;

 /// <summary>
    /// 注释：使用SMO需要引用下面的dll文件，文件路径：C:/Program Files/Microsoft SQL Server/100/SDK/Assemblies/
    /// Microsoft.SqlServer.ConnectionInfo.dll
    /// Microsoft.SqlServer.Smo.dll
    /// Microsoft.SqlServer.Management.Sdk.Sfc.dll
    /// Microsoft.SqlServer.SqlEnum.dll
    /// </summary>

 

 

#region 创建包含步骤和计划的作业
        /// <summary>
        ///  创建包含步骤和计划的作业
        /// </summary>
        /// <param name="databaseName">数据库名</param>
        /// <param name="jobName">作业名称</param>
        /// <param name="description">作业说明</param>
        /// <param name="strSQl">SQL命令</param>
        /// <param name="startDate">开始日期，为null表示当前时间</param>
        /// <param name="endDate">截止日期，为null表示无截止日期</param>
        /// <param name="FrequencyType">计划频率，每天、每周、每月、只运行一次</param>
        public Guid Create(string databaseName, string jobName, string description, string strSQl,
                           DateTime startDate, DateTime endDate, FrequencyTypes FrequencyType)
        {
            #region 创建作业

            ServerConnection conn = new ServerConnection("dev-db", "sa", "sqlserver");
            Server myServer = new Server(conn);

            //调用删除job方法，传入job名称
            DeleteJob(jobName);
            Job jb = new Job(myServer.JobServer, jobName);
            jb.Description = description;
            jb.Create();
            #endregion 创建作业

            #region 作业步骤
            JobStep jbstp = new JobStep(jb, "作业步骤");

            //数据库
            jbstp.DatabaseName = databaseName;

            //计划执行的SQL命令  
            jbstp.Command = strSQl;

            //成功时执行的操作
            jbstp.OnSuccessAction = StepCompletionAction.QuitWithSuccess;

            //失败时执行的操作
            jbstp.OnFailAction = StepCompletionAction.QuitWithFailure;

            //创建 SQL 代理实例的作业步骤.
            jbstp.Create();
            #endregion 作业步骤

            #region 作业计划属性

            JobSchedule jbsch = new JobSchedule(jb, "作业计划");

            //计划频率，每几天一次
            //每天一次，指定时间运行
            //--Daily    [每天]
            //--Weekly   [每周]
            //--Monthly   [每月]
            //--Run Once  [只运行一次]           
            //jbsch.FrequencyTypes = FrequencyTypes.Daily;
            jbsch.FrequencyTypes = FrequencyType;
            jbsch.FrequencyInterval = 1;
            jbsch.FrequencySubDayTypes = FrequencySubDayTypes.Once;
            jbsch.ActiveStartTimeOfDay = new TimeSpan(12, 37, 0);
            //每天多次，每隔指定时间一次
            //jbsch.FrequencySubDayTypes = FrequencySubDayTypes.Hour;
            //jbsch.FrequencySubDayInterval = 1;                
            //jbsch.ActiveStartTimeOfDay = new TimeSpan(0, 0, 0);              
            //jbsch.ActiveEndTimeOfDay = new TimeSpan(23, 59, 59);

            //持续时间               
            if (startDate != null)
            {  //开始时间  
                jbsch.ActiveStartDate = startDate;
            }
            else
            {
                jbsch.ActiveStartDate = DateTime.Now;
            }
            if (endDate != null)
            {
                //结束时间，null表示无结束日期
                jbsch.ActiveEndDate = endDate;
            }

            //创建SQL代理实例的作业调度
            jbsch.Create();

            //创建成功后立刻执行一次开始
            jb.ApplyToTargetServer(myServer.JobServer.Name);
            jb.Start();

            //创建成功后立刻执行一次结束
            #endregion 作业计划属性

            //返回作业GUID
            return jb.JobID;
        }
        #endregion

        #region 删除job
        /// <summary>
        /// 删除job
        /// </summary>
        /// <param name="jobName">job名称</param>
        /// <returns></returns>
        public int DeleteJob(string jobName)
        {
            //记录返回值
            int count = 0;
            try
            {
                //获取Web.config数据库连接
                string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                SqlConnection con = new SqlConnection(ConnectionString);
                //打开
                con.Open();
                string strSQL = "EXEC msdb.dbo.sp_delete_job @job_name=" + jobName;
                SqlCommand cmd = new SqlCommand(strSQL, con);
                //执行命令
                count = cmd.ExecuteNonQuery();
                //关闭
                con.Close();
            }
            catch (Exception)
            {
                return count;
                throw;
            }
            return count;
        }
        #endregion

 

///

  #region 启动Agent服务
        /// <summary>
        /// 启动Agent服务,返回一个 int值，如果大于0成功，否则失败
        /// </summary>
        /// <returns></returns>
        public int StartServices()
        {
            int count = 0;
            try
            {
                //获取Web.config数据库连接
                string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                SqlConnection con = new SqlConnection(ConnectionString);
                //打开
                con.Open();
                //启动Agent
                string strSQL = "exec   master..xp_cmdshell 'net start SQLServerAgent '";
                SqlCommand cmd = new SqlCommand(strSQL, con);
                //执行命令
                count = cmd.ExecuteNonQuery();
                //关闭
                con.Close();
            }
            catch (Exception)
            {
                return count;
                throw;
            }
            return count;
        }
        #endregion