Second Life of a Hungarian SharePoint Geek

October 15, 2012

How to list all SharePoint incoming mail aliases for a site or web application?

Filed under: Incoming email, Reflection, SP 2010 — Tags: , , — Peter Holpar @ 21:21

In my recent samples I’ve illustrated how to check whether an alias is reserved and how to get details of mapping.

This time I provide you the code that helps to enumerate all aliases for a site or the entire web application. To use this code you need the EmailAliasRecord wrapper struct and the extension methods as introduced in the former post.

Note: the code below uses non-public API calls and accesses SharePoint database directly, so it is not a supported approach. Use this sample at you own risk and preferably only in test environments.

The information we need is accessible through the proc_EnumEmailAliases and proc_EnumEmailAliasesBySite stored procedure in the SharePoint content database of the give web application.

To access these procedures, I first introduced the ExecuteReader extension method:

  1. public static SqlDataReader ExecuteReader(this SPContentDatabase database, SqlCommand command, CommandBehavior behavior)
  2. {
  3.     SqlDataReader result = null;
  4.  
  5.     string sqlSessionTypeName = "Microsoft.SharePoint.Utilities.SqlSession";
  6.     Type spContentDatabaseType = typeof(SPContentDatabase);
  7.  
  8.     // hack to get the Microsoft.SharPoint assembly
  9.     Assembly sharePointAssembly = typeof(SPWeb).Assembly;
  10.     // and a reference to the type of the SqlSession internal class
  11.     Type sqlSessionType = sharePointAssembly.GetType(sqlSessionTypeName);
  12.  
  13.     System.Reflection.PropertyInfo pi_SqlSession = spContentDatabaseType.GetProperty("SqlSession", BindingFlags.NonPublic | BindingFlags.Instance);
  14.  
  15.     if (pi_SqlSession != null)
  16.     {
  17.         // sqlSession will be of type internal class
  18.         // Microsoft.SharePoint.Utilities.SqlSession
  19.         // defined in Microsoft.SharePoint assembly
  20.         object sqlSession = pi_SqlSession.GetValue(database, null);
  21.  
  22.         MethodInfo mi_ExecuteReader = sqlSessionType.GetMethod("ExecuteReader", BindingFlags.Public | BindingFlags.Instance, null,
  23.                                             new Type[] { typeof(SqlCommand), typeof(CommandBehavior) }, null);
  24.  
  25.         if (mi_ExecuteReader != null)
  26.         {
  27.             result = mi_ExecuteReader.Invoke(sqlSession, new Object[] { command, behavior }) as SqlDataReader;
  28.         }
  29.     }
  30.  
  31.     return result;
  32. }

Having this method and the code from the previous post, the methods below can be used to display aliases mapped for lists in a specific site, in a content database or in a web application:

  1. private void DisplayEmailAliases(SPWebApplication webApp)
  2. {
  3.     foreach (SPContentDatabase database in webApp.ContentDatabases)
  4.     {
  5.         DisplayEmailAliases(database, null);
  6.     }
  7. }
  8.  
  9. private void DisplayEmailAliases(SPSite site)
  10. {
  11.     SPContentDatabase database = site.ContentDatabase;
  12.     DisplayEmailAliases(database, site.ID);
  13. }
  14.  
  15. private void DisplayEmailAliases(SPContentDatabase database, Guid? siteId)
  16. {
  17.     SqlCommand command;
  18.     List<EmailAliasRecord> list = new List<EmailAliasRecord>();
  19.     if (!siteId.HasValue)
  20.     {
  21.         command = new SqlCommand("proc_enumEmailAliases");
  22.     }
  23.     else
  24.     {
  25.         command = new SqlCommand("proc_enumEmailAliasesBySite");
  26.         command.Parameters.Add("@SiteId", SqlDbType.UniqueIdentifier).Value = siteId.Value;
  27.     }
  28.     command.CommandType = CommandType.StoredProcedure;
  29.  
  30.     using (SqlDataReader reader = database.ExecuteReader(command, CommandBehavior.CloseConnection))
  31.     {
  32.         while (reader.Read())
  33.         {
  34.             EmailAliasRecord ear = new EmailAliasRecord(reader);
  35.             Console.WriteLine(ear.ToString());
  36.         }
  37.     }
  38.  
  39. }

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: