12.5. Database Mail

The SMO SqlMail class gives you programmatic access to configure and monitor the SQL Server Database Mail subsystem. The SqlMail class does not give you access to mailboxes or the ability to send or receive mail messages.

For security reasons, Database Mail is inactive by default. You must use either the SQL Server Surface Area Configuration tool or the Database Mail Configuration Wizard to enable Database Mail.

A Database Mail profile is a collection of accounts. A Database Mail account contains information for email servers. An account can be part of one or more profiles.

Applications send email by using profiles rather than accounts. This improves both flexibility and reliability because accounts can be added to and removed from profiles without changing the application or its configuration. Profiles can be configured to automatically failover. Users and applications can have access to one or more profiles.

Profiles are either public or private. Public profiles are defined at the server level and are available to users in all host databases for sending and receiving email. Private profiles are defined in a specific database, and access is generally restricted to specific users and roles for sending email using the profile. Profiles are private by default.

Figure 12-10. Partial results for trace replay example

Figure 12-11 shows the relationship between SMO Database Mail classes.

The SMO Database Mail classes are described in Table 12-6. These classes are in the Microsoft.SqlServer.Management.Smo.Mail namespace.

Figure 12-11. SMO Database Mail class hierarchy

Table 12-6. SMO classes for Database Mail

Class	Description
ConfigurationValue	Represents a SQL Server Database Mail configuration option.
ConfigurationValueCollection	Represents a collection of SQL Server Database Mail configuration options as ConfigurationValue objects. The ConfigurationValues property of the SqlMail class returns the collection of options defined on the SQL Server Database Mail subsystem.
MailAccount	Represents a SQL Server Database Mail account.
MailAccountCollection	Represents a collection of SQL Server Database Mail accounts as MailAccount objects. The Accounts property of the SqlMail class returns the collection of mail accounts defined on the SQL Server Database Mail subsystem.
MailProfile	Represents a SQL Server Database Mail profile.
MailProfileCollection	Represents a collection of SQL Server Database Mail profiles as MailProfile objects. The Profiles property of the SqlMail class returns the collection of mail profiles defined on the SQL Server Database Mail subsystem.
MailServer	Represents a SQL Server Database Mail server.
MailServerCollection	Represents a collection of SQL Server Database Mail servers as MailServer objects. The MailServers property of the MailAccount class returns the collection of mail servers associated with a mail account.
SqlMail	Represents the SQL Server Database Mail subsystem.

The following example enumerates the SQL Server Database Mail profiles and the accounts within each profile:

    using System;
    using System.Data;

    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Smo.Mail;

    class Program
    {
        static void Main(string[] args)
        {
            Server server = new Server("localhost");
            SqlMail mail = server.Mail;

            foreach (MailProfile mp in mail.Profiles)
            {
                Console.WriteLine(mp.Name);
                DataTable dt = mp.EnumAccounts(  );
                foreach (DataRow row in dt.Rows)
                    for (int i = 0; i < dt.Columns.Count; i++)
                        Console.WriteLine("  " + dt.Columns[i].ColumnName +
                            ": " + row[i]);
                Console.WriteLine(  );
            }

            Console.WriteLine("Press any key to continue.");
            Console.ReadKey(  );
        }
    }

Results are shown in Figure 12-12.

Figure 12-12. Results for enumerating SQL Server Database Mail example

The results show that the example SQL Server instance has one profile, named Test profile, that has one account, named Test account.

The Mail property of the Server class returns a SqlMail object that represents the SQL Server Database Mail subsystem for the server. The Profiles property of the SqlMail class returns a collection of MailProfile objects representing the mail profiles defined on the mail subsystem. The EnumAccounts( ) method of the MailProfile class returns a DataTable object containing information about the email accounts associated with the profile.