Programming Policy-Based Management with SMO – Part 3 – ExpressionNode and Policy

This is part three of a series on programming policy-based management. The series starts here.

So, to initialize my Condition's ExpressionNode I need more than just a string. It doesn't look like, at this time, every ExpressionNode CAN initialized with a string. But we can use the subclasses. The ones I need here are Operator, Attribute, and Function. I need: DatabaseMailEnabled (Attribute), Equals (Operator) and false (Function). BTW, ExpressionNodeFunction appears to be what you are programming when you use the "Advanced Functions" dialog in the SSMS Condition dialog.

Putting one expression together first looks like this:

ExpressionNode exp1 = new ExpressionNodeOperator(OperatorType.EQ,
    new ExpressionNodeAttribute("DatabaseMailEnabled"),
    new ExpressionNodeFunction(ExpressionNodeFunction.Function.False)
    );

Note that, unlike in SSMS, you don't need '@' before the attribute name. But I need to specify both DatabaseMail and SqlMail. That's just a little more complex.

ExpressionNode exp1 = new ExpressionNodeOperator(OperatorType.EQ,
    new ExpressionNodeAttribute("DatabaseMailEnabled"),
    new ExpressionNodeFunction(ExpressionNodeFunction.Function.False)
    );
ExpressionNode exp2 = new ExpressionNodeOperator(OperatorType.EQ,
    new ExpressionNodeAttribute("SqlMailEnabled"),
    new ExpressionNodeFunction(ExpressionNodeFunction.Function.False)
    );
ExpressionNode exp_both = new ExpressionNodeOperator(OperatorType.AND, exp1, exp2);

con1.ExpressionNode = exp_both;
con1.Create();

And, nice as you please. Looks just like the one defined in SSMS using the GUI. Make sure that you remove (or comment out) the original ExpressionNode.Parse statement.

Now, on to the policy.

// Create a policy that uses the condition we just created
Policy p1 = new Policy(ps, "OffByDefaultSMO");
p1.Condition = "MailOffSMO";
p1.AutomatedPolicyExecutionMode = AutomatedPolicyExecutionMode.None;
p1.Enabled = false;
p1.Create();

Looks like the one created by the GUI and works like it too.

This is a pretty simple policy, because it can only be applied at the instance level. Next, we'll look at a policy that can be applied to a set of database objects, e.g. All tables in a particular database. This requires that we investigate ObjectSets.

Cavaet. It's pretty easy to make a mistake, sans docs. Some mistakes produce a generic "I can't do this"-type message. So ALWAYS drill into the INNER exception if you get an error. That is:

try
{
    con1.ExpressionNode = exp_both;
    con1.Create();
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    if (ex.InnerException != null)
    {
        Console.WriteLine(ex.InnerException.Message);
    }
}

Stack trace might even be helpful.