/// <summary>
/// Save any changes
/// </summary>
/// <returns>True if any changes were commited</returns>
public bool ApplyChanges()
{
bool changesMade = UpdateSourceSchedule();
// save the changes.
if (this.alreadyCreated)
{
source.Alter();
}
else
{
source.Create();
// retrieving source.ID after creation would throw if the
// server was in CaptureSql mode. This is because the schedule
// id is not generated while capturing sql. Thus, we only query
// id and set the created flag to true only when the smo object
// is actually created and not scripted.
Microsoft.SqlServer.Management.Smo.Server svr = null;
if (this.parentJob != null && this.parentJob.Parent != null && this.parentJob.Parent.Parent != null)
{
svr = this.parentJob.Parent.Parent as Microsoft.SqlServer.Management.Smo.Server;
}
if (svr == null || SqlExecutionModes.CaptureSql != (SqlExecutionModes.CaptureSql & svr.ConnectionContext.SqlExecutionModes))
{
this.id = source.ID;
this.Created = true;
}
}
return(changesMade);
}
private void TgGenerateJob()
{
if (_DwServer.JobServer.Jobs.Contains(TgJobNameExecStoredProcedureETL))
{
_TgJob = _DwServer.JobServer.Jobs[TgJobNameExecStoredProcedureETL];
_TgJob.Drop();
}
_TgJob = new Job(_DwServer.JobServer, TgJobNameExecStoredProcedureETL);
_TgJob.Create();
JobStep aJobStep = new JobStep(_TgJob, "Execute ETL");
aJobStep.DatabaseName = DwDbName;
aJobStep.SubSystem = AgentSubSystem.TransactSql;
aJobStep.Command = string.Format("Exec {0}", TgExecStoredProcedureETL);
aJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
aJobStep.OnFailAction = StepCompletionAction.QuitWithFailure;
aJobStep.Create();
JobSchedule SQLSchedule = new JobSchedule(_TgJob, "Execute ETL daily at night");
SQLSchedule.FrequencyTypes = FrequencyTypes.Daily;
SQLSchedule.FrequencySubDayTypes = FrequencySubDayTypes.Once;
SQLSchedule.ActiveStartTimeOfDay = new TimeSpan(0, 16, 0);
SQLSchedule.FrequencyInterval = 1;
SQLSchedule.ActiveStartDate = DateTime.Now;
SQLSchedule.ActiveEndDate = new DateTime(9999, 12, 30);
SQLSchedule.ActiveEndTimeOfDay = new TimeSpan(23, 59, 0);
SQLSchedule.Create();
_TgJob.ApplyToTargetServer(DwDbServerName);
_TgJob.Refresh();
}
static void Main(string[] args)
{
Server server = new Server(".");
// Get instance of SQL Agent SMO object
JobServer jobServer = server.JobServer;
Job job = null;
JobStep step = null;
JobSchedule schedule = null;
// Create a schedule
schedule = new JobSchedule(jobServer, "Schedule_1");
schedule.FrequencyTypes = FrequencyTypes.OneTime;
schedule.ActiveStartDate = DateTime.Today;
schedule.ActiveStartTimeOfDay = new TimeSpan(DateTime.Now.Hour, (DateTime.Now.Minute + 2), 0);
schedule.Create();
// Create Job
job = new Job(jobServer, "Job_1");
job.Create();
job.AddSharedSchedule(schedule.ID);
job.ApplyToTargetServer(server.Name);
// Create JobStep
step = new JobStep(job, "Step_1");
step.Command = "SELECT 1";
step.SubSystem = AgentSubSystem.TransactSql;
step.Create();
}
public void CreateJob(WAMSQLJob wamJob, string server = "localhost")
{
Server srv = null;
if (server == "localhost")
{
srv = ConnectToLocalDatabase(_username, _password);
}
else
{
srv = ConnectToRemoteDatabase(server, _username, _password);
}
if (srv != null)
{
try
{
Job job = new Job(srv.JobServer, wamJob.Name);
job.Description = wamJob.Description;
job.Create();
JobStep jobStep = new JobStep(job, wamJob.JobStepName);
jobStep.Command = wamJob.JobStepCommand;
jobStep.OnSuccessAction = StepCompletionAction.GoToStep;
jobStep.OnSuccessStep = 2;
jobStep.OnFailAction = StepCompletionAction.GoToStep;
jobStep.OnFailStep = 3;
jobStep.Create();
JobStep successStep = new JobStep(job, "ReportSuccess");
successStep.Command = $"INSERT INTO WAM.dbo.JobHistory VALUES(NEWID(),{wamJob.JobStepName},'SQL',GETDATE(),'Success',null)";
successStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
successStep.OnFailAction = StepCompletionAction.GoToStep;
successStep.OnFailStep = 3;
successStep.Create();
JobStep failStep = new JobStep(job, "ReportFailure");
failStep.Command = $"INSERT INTO WAM.dbo.JobHistory VALUES(NEWID(),{wamJob.JobStepName},'SQL',GETDATE(),'Failure','Not Available'";
failStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
failStep.OnFailAction = StepCompletionAction.QuitWithFailure;
failStep.Create();
JobSchedule jobSched = new JobSchedule(job, wamJob.ScheduleName);
jobSched.FrequencyTypes = wamJob.ScheduleFrequencyType;
jobSched.FrequencyRecurrenceFactor = wamJob.ScheduleFrequencyRecurrenceFactor;
jobSched.FrequencyInterval = wamJob.ScheduleFrequencyInterval;
jobSched.ActiveStartTimeOfDay = wamJob.ScheduleActiveStartTimeOfDay;
jobSched.Create();
} catch (Exception ex)
{
throw new Exception("Failed To Create Job: " + wamJob.Name + " - " + ex.Message);
}
}
else
{
throw new Exception("Failed To Connect to SQL Job Server");
}
}
public void CreateJob(WAMSQLJob wamJob, string username, string password, string server = "localhost")
{
Server srv = null;
if (server == "localhost")
{
srv = ConnectToLocalDatabase(username, password);
}
else
{
srv = ConnectToRemoteDatabase(server, username, password);
}
if (srv != null)
{
try
{
Operator op = new Operator(srv.JobServer, "WAM_Operator");
op.NetSendAddress = "WAM_Server";
op.Create();
Job job = new Job(srv.JobServer, wamJob.Name);
job.Description = wamJob.Description;
job.Create();
JobStep jobStep = new JobStep(job, wamJob.JobStepName);
jobStep.Command = wamJob.JobStepCommand;
jobStep.OnFailAction = StepCompletionAction.QuitWithFailure;
jobStep.Create();
JobSchedule jobSched = new JobSchedule(job, wamJob.ScheduleName);
jobSched.FrequencyTypes = wamJob.ScheduleFrequencyType;
jobSched.FrequencyRecurrenceFactor = wamJob.ScheduleFrequencyRecurrenceFactor;
jobSched.FrequencyInterval = wamJob.ScheduleFrequencyInterval;
jobSched.ActiveStartTimeOfDay = wamJob.ScheduleActiveStartTimeOfDay;
jobSched.Create();
}
catch (Exception ex)
{
throw new Exception("Failed To Create Job: " + wamJob.Name + " - " + ex.Message);
}
}
else
{
throw new Exception("Failed To Connect to SQL Job Server");
}
}
static void Main(string[] args)
{
Server server = new Server("localhost");
JobServer jobServer = server.JobServer;
foreach (Job serverJob in jobServer.Jobs)
{
Console.WriteLine("Job: {0} - Owner: {1}", serverJob.Name, serverJob.OwnerLoginName);
}
Job job = new Job(jobServer, "Clean PO Table");
job.Create();
JobStep jobStep = new JobStep(job, "Delete old records");
jobStep.Command = "DELETE FROM dbo.PurchaseOrders WHERE OrderDateTime < DATEADD(DAY, -30, GETDATE())";
jobStep.DatabaseName = "SmoDemoDatabase";
jobStep.Create();
JobSchedule jobSchedule = new JobSchedule(job, "Nightly Run");
jobSchedule.ActiveStartTimeOfDay = new TimeSpan(3, 0, 0);
jobSchedule.FrequencyInterval = 1;
jobSchedule.FrequencyTypes = FrequencyTypes.Daily;
jobSchedule.FrequencySubDayTypes = FrequencySubDayTypes.Once;
jobSchedule.Create();
Console.WriteLine();
Console.WriteLine();
Console.WriteLine("Finished");
Console.ReadKey();
}
/// <summary>
/// 创建包含步骤和计划的作业，返回 string jobid
/// </summary>
/// <param name="KeyId">对象ID</param>
/// <returns>返回 string</returns>
public string CreateBilling(string KeyId, string JobType, string KillJobID)
{
try
{
#region 定义变量
//数据库名称
string strDatabaseName = string.Empty;
//作业名称
string strJobName = string.Empty;
//作业ID
string strJobID = string.Empty;
//作业说明
string strDescription = string.Empty;
//开始时间
DateTime dtStartDate = System.DateTime.Now;
//计划频率，Daily：每天、Weekly：每周、Monthly：每月、Run Once：只运行一次
FrequencyTypes FrequencyType = FrequencyTypes.Daily;
//开始运行时间 如：10:30:00
string strRunStartDate = string.Empty;
//结束运行时间，如：11:05:00
string strRunEndDate = string.Empty;
//数据库服务名
string strSQLServer = string.Empty;
//数据库登录用户
string strUser = string.Empty;
//数据库登录密码
string strPassword = string.Empty;
//时
int hours = 0;
//分
int minutes = 0;
//秒
int seconds = 0;
//命令行
string JobCommandString = string.Empty;
vSchedulerEmailBackup vSchedulerEmailBackupObj = null;
#endregion
//返回vSchedulerEmailBackup 对象，根据id查询
vSchedulerEmailBackupObj = this.FindvSchedulerEmailBackupById(KeyId);
if (vSchedulerEmailBackupObj != null)
{
#region 变量赋值
//strDatabaseName = "ServiceDirectDB";
strDatabaseName = this.GetConfig("strDatabaseName");
strJobName = vSchedulerEmailBackupObj.TaskName;
if (vSchedulerEmailBackupObj.JobID != null)
{
strJobID = vSchedulerEmailBackupObj.JobID.ToString();
}
//描述：保存的是KillJob的ID
strDescription = KillJobID;
dtStartDate = Convert.ToDateTime(vSchedulerEmailBackupObj.StartTime);
switch (vSchedulerEmailBackupObj.ScheduleType)
{
case "Daily":
FrequencyType = FrequencyTypes.Daily;
break;
case "Run Once":
FrequencyType = FrequencyTypes.OneTime;
break;
}
DateTime dtRunOnlyStart = Convert.ToDateTime(vSchedulerEmailBackupObj.RunOnlyStart);
DateTime dtRunOnlyEnd = Convert.ToDateTime(vSchedulerEmailBackupObj.RunOnlyEnd);
strRunStartDate = dtRunOnlyStart.ToLongTimeString();
strRunEndDate = dtRunOnlyEnd.ToLongTimeString();
//开始运行时间 时：分：秒：
hours = dtRunOnlyStart.Hour;
minutes = dtRunOnlyStart.Minute;
seconds = dtRunOnlyStart.Second;
//获取自定义config的值
strSQLServer = this.GetConfig("strSQLServer");
strUser = this.GetConfig("strUser");
strPassword = this.GetConfig("strPassword");
#endregion
#region 创建作业
ServiceDirectDBEntities objDB = new ServiceDirectDBEntities();
//获取数据库名
string strdatabase = objDB.Connection.Database;
//获取数据库服务器名
string strDataSource = objDB.Connection.DataSource;
ServerConnection conn = null;
Server myServer = null;
conn = new ServerConnection(strSQLServer, strUser, strPassword);
myServer = new Server(conn);
//调用删除Billing 的Job
//DeleteJob(strJobID);
Job jb = new Job(myServer.JobServer, strJobName);
jb.Description = strDescription;
//更改JOB状态
if (JobType == "enable")
{
jb.IsEnabled = true;
}
switch (JobType)
{
case "enable":
jb.IsEnabled = true;
break;
case "disable":
jb.IsEnabled = false;
break;
case "Insert":
jb.IsEnabled = true;
break;
}
//创建JOB
jb.Create();
#endregion 创建作业
if (jb.JobID != null)
{
//获取命令行
JobCommandString = this.GetJobCommandString(vSchedulerEmailBackupObj, KeyId, jb);
}
#region 作业步骤
Steps steps = new Steps();
//创建步骤
steps.CreateStep(jb, strDatabaseName, JobCommandString, strRunEndDate);
#endregion 作业步骤
#region 作业计划属性
JobSchedule jbsch = new JobSchedule(jb, "ScheduleBilling");
//计划频率，每几天一次
jbsch.FrequencyTypes = FrequencyType;
if (vSchedulerEmailBackupObj.ScheduleType.Equals("Daily"))
{
//执行间隔 (天)
jbsch.FrequencyInterval = 1;
//当天只执行一次
jbsch.FrequencySubDayTypes = FrequencySubDayTypes.Once;
}
if (strRunStartDate != string.Empty)
{
//开始执行时间
jbsch.ActiveStartTimeOfDay = new TimeSpan(hours, minutes, seconds);
}
//持续时间
if (dtStartDate != null)
{
//开始时间
jbsch.ActiveStartDate = dtStartDate;
}
else
{
jbsch.ActiveStartDate = DateTime.Now;
}
//创建SQL代理实例的作业调度
jbsch.Create();
jb.ApplyToTargetServer(myServer.JobServer.Name);
//创建成功后立刻执行一次开始
//jb.Start();
//创建成功后立刻执行一次结束
#endregion 作业计划属性
//返回作业GUID
return(jb.JobID.ToString());
}
else
{
return("");
}
}
catch (Exception)
{
return("");
}
}
private bool UpdateSQLJob(int nPeriod, bool bEnable, int nFreq, ref string err)
{
ServerConnection conn = null;
try
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["SQLJob"].ConnectionString;
System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connstring);
conn = new ServerConnection(sqlconn);
Server server = new Server(conn);
// Get instance of SQL Agent SMO object
JobServer jobServer = server.JobServer;
Job job = jobServer.Jobs["EmailDispatch"];
if (job == null)
{
// Create Job
job = new Job(jobServer, "EmailDispatch");
job.Create();
//job.AddSharedSchedule(schedule.ID);
//job.ApplyToTargetServer(server.Name);
}
JobStep step = job.JobSteps["EmailDispatchProcess"];
if (step == null)
{
// Create JobStep
step = new JobStep(job, "EmailDispatchProcess");
step.Command = "EXEC P_EmailDispatchForSchedule";
step.SubSystem = AgentSubSystem.TransactSql;
step.DatabaseName = conn.DatabaseName;
step.Create();
}
JobSchedule schedule = job.JobSchedules["MinuteDispatch"];
if (schedule == null)
{
// Create a schedule
schedule = new JobSchedule(job, "MinuteDispatch");
schedule.ActiveStartDate = DateTime.Today;
schedule.FrequencyTypes = FrequencyTypes.Daily;
schedule.FrequencyInterval = 1;
if (nFreq == 0)
schedule.FrequencySubDayTypes = FrequencySubDayTypes.Hour;
else if(nFreq == 1)
schedule.FrequencySubDayTypes = FrequencySubDayTypes.Minute;
schedule.FrequencySubDayInterval = nPeriod;
//schedule.ActiveStartTimeOfDay = new TimeSpan(DateTime.Now.Hour, (DateTime.Now.Minute + 2), 0);
schedule.Create();
job.AddSharedSchedule(schedule.ID);
job.ApplyToTargetServer("(local)");
}
else
{
if (nFreq == 0)
schedule.FrequencySubDayTypes = FrequencySubDayTypes.Hour;
else if (nFreq == 1)
schedule.FrequencySubDayTypes = FrequencySubDayTypes.Minute;
schedule.FrequencySubDayInterval = nPeriod;
schedule.Alter();
}
job.IsEnabled = bEnable;
job.Alter();
conn.Disconnect();
return true;
}
catch (Exception ex)
{
err = ex.Message;
if(conn != null)
conn.Disconnect();
return false;
}
}
/// <summary>
/// 创建包含步骤和计划的作业，返回 string jobid, Kill的JOB
/// </summary>
/// <param name="KeyId">对象ID</param>
/// <returns>返回 string</returns>
public string Create(string KeyId, string JobType)
{
try
{
#region 定义变量
//数据库名称
string strDatabaseName = string.Empty;
//作业名称
string strJobName = string.Empty;
//作业ID
string strJobID = string.Empty;
//作业说明
string strDescription = string.Empty;
//开始时间
DateTime dtStartDate = System.DateTime.Now;
//计划频率，Daily：每天、Weekly：每周、Monthly：每月、Run Once：只运行一次
FrequencyTypes FrequencyType = FrequencyTypes.Daily;
//开始运行时间 如：10:30:00
string strRunStartDate = string.Empty;
//结束运行时间，如：11:05:00
string strRunEndDate = string.Empty;
//数据库服务名
string strSQLServer = string.Empty;
//数据库登录用户
string strUser = string.Empty;
//数据库登录密码
string strPassword = string.Empty;
//时
int hours = 0;
//分
int minutes = 0;
//秒
int seconds = 0;
//命令行
string JobCommandString = string.Empty;
vSchedulerEmailBackup vSchedulerEmailBackupObj = null;
#endregion
//返回vSchedulerEmailBackup 对象，根据id查询
vSchedulerEmailBackupObj = this.FindvSchedulerEmailBackupById(KeyId);
if (vSchedulerEmailBackupObj != null)
{
#region 变量赋值
//strDatabaseName = "ServiceDirectDB";
strDatabaseName = this.GetConfig("strDatabaseName");
string[] strJobNames = vSchedulerEmailBackupObj.TaskName.Split('-');
strJobName = strJobNames[0].ToString() + "-Kill";
if (vSchedulerEmailBackupObj.JobID != null)
{
strJobID = vSchedulerEmailBackupObj.JobID.ToString();
}
strDescription = "KillUTP";
dtStartDate = Convert.ToDateTime(vSchedulerEmailBackupObj.RunOnlyEnd);
switch (vSchedulerEmailBackupObj.ScheduleType)
{
case "Daily":
FrequencyType = FrequencyTypes.Daily;
break;
case "Run Once":
FrequencyType = FrequencyTypes.OneTime;
break;
}
DateTime dtRunOnlyStart = Convert.ToDateTime(vSchedulerEmailBackupObj.RunOnlyStart);
DateTime dtRunOnlyEnd = Convert.ToDateTime(vSchedulerEmailBackupObj.RunOnlyEnd);
strRunStartDate = dtRunOnlyStart.ToLongTimeString();
strRunEndDate = dtRunOnlyEnd.ToLongTimeString();
//开始运行时间 时：分：秒：
hours = dtRunOnlyEnd.Hour;
//结束时间减去5分钟之后在运行Kill的JOB
minutes = dtRunOnlyEnd.Minute - 5;
seconds = dtRunOnlyEnd.Second;
//获取自定义config的值
strSQLServer = this.GetConfig("strSQLServer");
strUser = this.GetConfig("strUser");
strPassword = this.GetConfig("strPassword");
#endregion
#region 创建作业
ServiceDirectDBEntities objDB = new ServiceDirectDBEntities();
//获取数据库名
string strdatabase = objDB.Connection.Database;
//获取数据库服务器名
string strDataSource = objDB.Connection.DataSource;
ServerConnection conn = null;
Server myServer = null;
conn = new ServerConnection(strSQLServer, strUser, strPassword);
myServer = new Server(conn);
string KillID = string.Empty;
//调用删除job方法，传入job名称
//DeleteJob(KillID);
DeleteJob(strJobID);
Job jb = new Job(myServer.JobServer, strJobName);
jb.Description = strDescription;
//更改JOB状态
if (JobType == "enable")
{
jb.IsEnabled = true;
}
switch (JobType)
{
case "enable":
jb.IsEnabled = true;
break;
case "disable":
jb.IsEnabled = false;
break;
case "Insert":
jb.IsEnabled = true;
break;
}
//创建JOB
jb.Create();
#endregion 创建作业
#region 作业步骤
Steps steps = new Steps();
//创建步骤
steps.CreateStepKill(jb, strDatabaseName, KeyId);
#endregion 作业步骤
#region 作业计划属性
JobSchedule jbsch = new JobSchedule(jb, "ScheduleKill");
//计划频率，每几天一次
jbsch.FrequencyTypes = FrequencyType;
if (vSchedulerEmailBackupObj.ScheduleType.Equals("Daily"))
{
//执行间隔 (天)
jbsch.FrequencyInterval = 1;
//当天只执行一次
jbsch.FrequencySubDayTypes = FrequencySubDayTypes.Once;
}
if (strRunStartDate != string.Empty)
{
//开始执行时间
jbsch.ActiveStartTimeOfDay = new TimeSpan(hours, minutes, seconds);
}
//持续时间
if (dtStartDate != null)
{
//开始时间
jbsch.ActiveStartDate = dtStartDate;
}
else
{
jbsch.ActiveStartDate = DateTime.Now;
}
//创建SQL代理实例的作业调度
jbsch.Create();
jb.ApplyToTargetServer(myServer.JobServer.Name);
//创建成功后立刻执行一次开始
//jb.Start();
//创建成功后立刻执行一次结束
#endregion 作业计划属性
string KillJobID = jb.JobID.ToString();
string BillinJobID = string.Empty;
BillinJobID = this.CreateBilling(KeyId, JobType, KillJobID);
//返回作业GUID
return BillinJobID;
}
else
{
return "";
}
}
catch (Exception)
{
return "";
}
}