Programming SQL Server 2005

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.

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-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.

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.

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.