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