/// <summary>
/// Get the current operator. If we are creating this will be a new operator. If we are modifying
/// an existing operator it will be the existing operator, and will throw if the operator has been
/// deleted.
/// </summary>
/// <returns>Operator object</returns>
private Operator GetCurrentOperator()
{
JobServer jobServer = GetJobServer();
Operator currentOperator = jobServer.Operators[this.originalOperatorName];
this.createMode = (currentOperator == null);
if (this.createMode)
{
currentOperator = new Microsoft.SqlServer.Management.Smo.Agent.Operator(jobServer, this.name);
}
return(currentOperator);
}
public static void UpdateDataSet(this MSMOA.Operator operatorX, Data.ApplicationDataSet.JSOperatorsRow dataRow)
{
// NB. operator is a keyword so use operatorX
try
{
//dataRow.X = operatorX.X;
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 7);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
}
}
private static void Update(MSMOA.Operator operatorX, SQLInformation.Data.ApplicationDataSet.JSOperatorsRow dataRow)
{
try
{
operatorX.UpdateDataSet(dataRow);
UpdateDatabaseWithSnapShot(dataRow, "");
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 6);
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
}
private static SQLInformation.Data.ApplicationDataSet.JSOperatorsRow Add(MSMOA.Operator operatorX, SQLInformation.Data.ApplicationDataSet.JobServersRow jobServerRow)
{
SQLInformation.Data.ApplicationDataSet.JSOperatorsRow dataRow = null;
try
{
dataRow = Common.ApplicationDataSet.JSOperators.NewJSOperatorsRow();
dataRow.ID = Guid.NewGuid();
dataRow.JobServer_ID = jobServerRow.ID;
dataRow.Name_JSOperator = operatorX.Name;
dataRow.CategoryName = operatorX.CategoryName;
dataRow.EmailAddress = operatorX.EmailAddress;
dataRow.Enabled = operatorX.Enabled;
dataRow.ID_Operator = operatorX.ID;
dataRow.LastEmailDate = operatorX.LastEmailDate;
dataRow.LastNetSendDate = operatorX.LastNetSendDate;
dataRow.LastPagerDate = operatorX.LastPagerDate;
dataRow.NetSendAddress = operatorX.NetSendAddress;
dataRow.PagerAddress = operatorX.PagerAddress;
dataRow.PagerDays = operatorX.PagerDays.ToString();
// TODO(crhodes): Is there a better datatype/
dataRow.SaturdayPagerEndTime = operatorX.SaturdayPagerEndTime.ToString();
dataRow.SaturdayPagerStartTime = operatorX.SaturdayPagerStartTime.ToString();
dataRow.SundayPagerEndTime = operatorX.SaturdayPagerEndTime.ToString();
dataRow.SundayPagerStartTime = operatorX.SaturdayPagerStartTime.ToString();
dataRow.WeekdayPagerEndTime = operatorX.WeekdayPagerEndTime.ToString();
dataRow.WeekdayPagerStartTime = operatorX.WeekdayPagerStartTime.ToString();
dataRow.SnapShotDate = DateTime.Now;
dataRow.SnapShotError = "";
Common.ApplicationDataSet.JSOperators.AddJSOperatorsRow(dataRow);
Common.ApplicationDataSet.JSOperatorsTA.Update(Common.ApplicationDataSet.JSOperators);
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 5);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
return(dataRow);
}
/// <summary>
/// load the notifiaction history for the operator
/// </summary>
private void LoadHistoryData()
{
if (this.historyInitialized)
{
return;
}
if (this.createMode)
{
LoadHistoryDefaults();
return;
}
Microsoft.SqlServer.Management.Smo.Agent.Operator currentOperator = GetCurrentOperator();
this.lastEmailDate = currentOperator.LastEmailDate;
this.lastPagerDate = currentOperator.LastPagerDate;
}
/// <summary>
/// load data for the general tab. This can be called multiple times but will only load the data once intially
/// or after a reset
/// </summary>
private void LoadGeneralData()
{
if (this.generalInitialized)
{
return;
}
// load defaults if we're creating
if (createMode)
{
LoadGeneralDefaults();
return;
}
// lookup the operator this will throw if it has been deleted.
Microsoft.SqlServer.Management.Smo.Agent.Operator currentOperator = GetCurrentOperator();
// setup the members
this.name = currentOperator.Name;
this.enabled = currentOperator.Enabled;
this.emailAddress = currentOperator.EmailAddress;
this.pagerAddress = currentOperator.PagerAddress;
this.pagerDays = currentOperator.PagerDays;
this.weekdayStartTime = ConvertAgentTime(currentOperator.WeekdayPagerStartTime);
this.weekdayEndTime = ConvertAgentTime(currentOperator.WeekdayPagerEndTime);
this.saturdayStartTime = ConvertAgentTime(currentOperator.SaturdayPagerStartTime);
this.saturdayEndTime = ConvertAgentTime(currentOperator.SaturdayPagerEndTime);
this.sundayStartTime = ConvertAgentTime(currentOperator.SundayPagerStartTime);
this.sundayEndTime = ConvertAgentTime(currentOperator.SundayPagerEndTime);
this.generalInitialized = true;
}
/// <summary>
/// apply any changes to the operator. If the operator does not exist create it.
/// </summary>
public void ApplyChanges(AgentOperatorInfo operatorInfo)
{
// do nothing if we are read only
if (this.readOnly)
{
return;
}
JobServer jobServer = GetJobServer();
// get the operator. This will create a new one if it does not already exist
Microsoft.SqlServer.Management.Smo.Agent.Operator currentOperator = GetCurrentOperator();
// general tab
currentOperator.Enabled = operatorInfo.Enabled;
if (!string.IsNullOrWhiteSpace(operatorInfo.EmailAddress))
{
currentOperator.EmailAddress = operatorInfo.EmailAddress;
}
if (!string.IsNullOrWhiteSpace(operatorInfo.PagerAddress))
{
currentOperator.PagerAddress = operatorInfo.PagerAddress;
}
currentOperator.PagerDays = this.pagerDays;
if ((operatorInfo.PagerDays & Contracts.WeekDays.WeekDays) > 0)
{
TimeSpan weekdayPagerStartTime = default(TimeSpan);
if (!string.IsNullOrWhiteSpace(operatorInfo.WeekdayPagerStartTime))
{
weekdayPagerStartTime = TimeSpan.Parse(operatorInfo.WeekdayPagerStartTime);
}
TimeSpan weekdayPagerEndTime = default(TimeSpan);
if (!string.IsNullOrWhiteSpace(operatorInfo.WeekdayPagerEndTime))
{
weekdayPagerEndTime = TimeSpan.Parse(operatorInfo.WeekdayPagerEndTime);
}
currentOperator.WeekdayPagerStartTime = weekdayPagerStartTime;
currentOperator.WeekdayPagerEndTime = weekdayPagerEndTime;
}
if ((operatorInfo.PagerDays & Contracts.WeekDays.Saturday) > 0)
{
TimeSpan saturdayPagerStartTime = default(TimeSpan);
if (!string.IsNullOrWhiteSpace(operatorInfo.SaturdayPagerStartTime))
{
saturdayPagerStartTime = TimeSpan.Parse(operatorInfo.SaturdayPagerStartTime);
}
TimeSpan saturdayPagerEndTime = default(TimeSpan);
if (!string.IsNullOrWhiteSpace(operatorInfo.SaturdayPagerEndTime))
{
saturdayPagerEndTime = TimeSpan.Parse(operatorInfo.SaturdayPagerEndTime);
}
currentOperator.SaturdayPagerStartTime = saturdayPagerStartTime;
currentOperator.SaturdayPagerEndTime = saturdayPagerEndTime;
}
if ((operatorInfo.PagerDays & Contracts.WeekDays.Sunday) > 0)
{
TimeSpan sundayPagerStartTime = default(TimeSpan);
if (!string.IsNullOrWhiteSpace(operatorInfo.SundayPagerStartTime))
{
sundayPagerStartTime = TimeSpan.Parse(operatorInfo.SundayPagerStartTime);
}
TimeSpan sundayPagerEndTime = default(TimeSpan);
if (!string.IsNullOrWhiteSpace(operatorInfo.SundayPagerEndTime))
{
sundayPagerEndTime = TimeSpan.Parse(operatorInfo.SundayPagerEndTime);
}
currentOperator.SundayPagerStartTime = sundayPagerStartTime;
currentOperator.SundayPagerEndTime = sundayPagerEndTime;
}
if (this.createMode)
{
// create the object
currentOperator.Create();
this.originalOperatorName = this.name;
}
else
{
// alter the object
currentOperator.Alter();
}
// only set this up if the notifications has been set
if (this.alertNotifications != null)
{
SqlServer.Management.Smo.Agent.NotifyMethods notifyMethods;
for (int i = 0; i < alertNotifications.Count; ++i)
{
notifyMethods = 0;
if (alertNotifications[i].NotifyEmail)
{
notifyMethods |= SqlServer.Management.Smo.Agent.NotifyMethods.NotifyEmail;
}
if (alertNotifications[i].NotifyPager)
{
notifyMethods |= SqlServer.Management.Smo.Agent.NotifyMethods.Pager;
}
bool alertAlreadyNotifiesOperator = false;
// if we're not creating see if the current alert already notifies this operator
if (!createMode)
{
DataTable notifications = alertNotifications[i].Alert.EnumNotifications(this.originalOperatorName);
if (notifications.Rows.Count > 0)
{
alertAlreadyNotifiesOperator = true;
}
}
// either update or clear existing notifications
if (alertAlreadyNotifiesOperator)
{
if (notifyMethods != SqlServer.Management.Smo.Agent.NotifyMethods.None)
{
alertNotifications[i].Alert.UpdateNotification(this.originalOperatorName, notifyMethods);
}
else
{
alertNotifications[i].Alert.RemoveNotification(this.originalOperatorName);
}
}
else if (notifyMethods != SqlServer.Management.Smo.Agent.NotifyMethods.None)
{
// add a new notification
alertNotifications[i].Alert.AddNotification(this.originalOperatorName, notifyMethods);
}
}
}
// see if we need to rename. This has to be done last otherwise any scripts generated will be incorrect.
if (!this.createMode && currentOperator.Name != this.originalOperatorName)
{
currentOperator.Rename(this.name);
if (this.dataContainer.Server.ConnectionContext.SqlExecutionModes != SqlExecutionModes.CaptureSql)
{
this.originalOperatorName = this.name;
}
}
// update state if we aren't scripting
if (this.createMode && this.dataContainer.Server.ConnectionContext.SqlExecutionModes != SqlExecutionModes.CaptureSql)
{
this.createMode = false;
}
}
/// <summary>
/// Load alerts that notify this operator
/// </summary>
private void LoadAlertNotificationData()
{
if (this.alertNotifications != null)
{
return;
}
// defaults in create ode
if (createMode)
{
LoadAlertNotificationDefaults();
return;
}
this.alertNotifications = new List <AgentAlertNotificationHelper>();
Microsoft.SqlServer.Management.Smo.Agent.Operator agentOperator = GetCurrentOperator();
JobServer jobServer = GetJobServer();
// see all alerts that notifuy this operator
DataTable notifications = agentOperator.EnumNotifications();
DataRow alertRow;
bool notifyEmail;
bool notifyPager;
AgentAlertNotificationHelper alertNotification;
// Add every alert to the structure
foreach (Alert alert in jobServer.Alerts)
{
alertRow = null;
// see if the alert notifies us already
foreach (DataRow row in notifications.Rows)
{
if ((string)row["AlertName"] == alert.Name)
{
alertRow = row;
break;
}
}
// set if the current alert notifies this operator
// if so so how
if (alertRow != null)
{
notifyEmail = (bool)alertRow["UseEmail"];
notifyPager = (bool)alertRow["UsePager"];
}
else
{
notifyEmail = false;
notifyPager = false;
}
alertNotification = new AgentAlertNotificationHelper(alert.Name
, notifyEmail
, notifyPager
, alert);
this.alertNotifications.Add(alertNotification);
}
}
private static SQLInformation.Data.ApplicationDataSet.JSOperatorsRow GetInfoFromSMO(MSMOA.Operator operatorX, SQLInformation.Data.ApplicationDataSet.JobServersRow jobServerRow)
{
#if TRACE
long startTicks = VNC.AppLog.Trace4(string.Format("Enter ({0})", operatorX.Name), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
#endif
SQLInformation.Data.ApplicationDataSet.JSOperatorsRow dataRow = null;
try
{
var dbs = from db in Common.ApplicationDataSet.JSOperators
where db.JobServer_ID == jobServerRow.ID
select db;
var dbs2 = from db2 in dbs
where db2.Name_JSOperator == operatorX.Name
select db2;
if (dbs2.Count() > 0)
{
dataRow = dbs2.First();
Update(operatorX, dataRow);
}
else
{
dataRow = Add(operatorX, jobServerRow);
}
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 3);
}
#if TRACE
VNC.AppLog.Trace4("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 4, startTicks);
#endif
return(dataRow);
}