Click here to Skip to main content
Click here to Skip to main content

Integrating Exchange Server 2010 Mail Attachments with SharePoint 2013 via C#.

, 18 Aug 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
Integrating Exchange Server 2010 Mail Attachments with SharePoint 2013 via C#.

Integrating Exchange Server 2010 Mail Attachments with SharePoint 2013 via C#.

In this article we are going to create a small c# windows service solution in Visual Studio 2012 to extract from your Exchange Server Inbox folder all documents attached to the mails and store them into a SharePoint 2013 Shared Document Library.

We assume that the reader already have set your playground and possess the credentials and the Query Analyzer Tool configured.

If that is not the case please refer to my previous article. You may also want to skip it and get your Playground account here.

Introduction

First we have the big picture of the solution as following:

Starting from the bottom we have in blue the C# Windows Service Application that we are going to develop. Note that the C# Application uses the ODBC Driver to communicate with the Server located in the Playground environment.

The Server and its additional components is responsible to establish the connection with Exchange Server and SharePoint respectively through a Connection String extracted from the Query Analyzer tool

The C# Application uses the System.Data.Odbc namespace in order to use the  ODBC Driver, which is a native C++ library registered into your Windows Operating System.

The C# Application additionally uses a file Database SQLite to keep information about last synchronization date time and configuration that might be useful to extend the project later on.

The main purpose of the Windows Service is to retrieve the binary representation of the attachments of the Exchange Mails in the Inbox Folder and store them in SharePoint 2013 Shared Document Library, moreover metadata information about the attachments will be retrieved to create simple business rules regarding in which folder the attachments will be placed according to its extension.

E.g. PDF files shall be stored under the Shared Document Library Folder PDF, Word Documents DOC/DOCX under the Folder DOCS and so on.

Before creating the skeleton of the Windows Service we should feel comfortable with the Query Analyzer tool and the schema “Database Model” of Exchange Server and SharePoint.

Run the Query Analyzer Tool and Connect to both “target systems” (Exchange Server and SharePoint).

Expand the tree view and get familiar with the Entities (Tables) and its properties (Columns) provided by the tool. Moreover verify the Stored Procedures provided, they provide out of the box functionality in both target systems.

In order to keep this article as short as possible we are going to summarize the basic SQL Statements that will be used by our C# application.

Following:

Exchange Connection

select ID from folder where displayname='Inbox';
select * from message where parentname = '{ID}' and hasattachment='true'; --ID of the folder
Exec SP_SELECT_ATTACHMENTS '{@InAttachmentID}'; --ID of the message
--this is going to return a table with the following columns
--FileName, ContentLocation, Address, IsInline, LastModifiedTime, Size
EXEC SP_SAVE_ATTACHMENT '{@InAttachmentID}'; --address of the attachment
--this is going to return a table with the following columns
--OutBuffer, OutSize, OutFileName, OutSucceded

The OutBuffer is the binary representation of the file that must be transferred to the SharePoint Shared Document Library, the additional column OutFileName will be used to create the rule while uploading to the SharePoint Folder.

SharePoint Connection

SELECT * FROM [Documents]; --standard shared document library created in SP2013
EXEC SP_ENSURE_FOLDER_PATH 'Documents', '/PDF'; --create the folder PDF if doesnt exist
EXEC SP_ENSURE_FOLDER_PATH 'Documents', '/DOC'; --create the folder DOC if doesnt exist
EXEC SP_ENSURE_FOLDER_PATH 'Documents', '/PICTURES'; --create the folder PICTURES if doesnt exist
EXEC SP_INSERT_SHAREDDOCUMENT 'Documents', 'test.txt', '/cbplayground/takahashi0000/Shared Documents/DOC', 'application/vnd.openxmlformats-officedocument.wordprocessingml.document', 'dGVzdA=='; --parameters : @sharedDocumentsTableName, @fileName, @relativePath, @mimeType, @data

--test to insert a document into sharepoint the @data is a base64 string and represent the text “Test”
Note that so far we haven’t even opened the Visual Studio to start creating the solution, which is exactly the next step in this article.

Creating the Visual Studio Windows Service Solution

You can download the entire project here

Open the Visual Studio 2012 and create a new Windows Service Project based on the standard templates

In our case we have created a project with the name CbExToSpAttachmentsPlaygound

I have renamed the standard Service1.cs file to ExToSpAttachmentService.cs.

Before switching to the source code lets organize the structure of the project and add the required references for the project.

  1. Create a folder called libs and 2 subfolders called x32 and x64 respectively
  2. Add into these folders the System.Data.SQLite.DLL for both architectures
  3. Reference the SQLite.DLL in your project according to your O.S archictecture (in my case x64). Alternatively, modify the source code to load the SQLite DLL by reflection according to the target operating system architecture (I will let this exercise for you J )
  4. Switch to the source code view in the ExToSpAttachmentService.cs, remove the unused namespaces and add the System.Data.Odbc
  5. You should end up with something like

The next step is to implement the “business logic” to synchronize the data between Exchange and SharePoint.

 

Business logic

Let’s try to keep it as simple as possible. Only the most relevant fragments of the source code will be published here with its explanation. The entire source code and documentation can be downloaded here for further adaptations.

Once again the focus the project is its simplicity, therefore I avoided to create several different classes complex data structures and patterns. Everything you need will be find in a single CS file.

The first step consists in the creation of the local SQLite database file, build its structure and fill with initial configuration values.

That can be found inside the method CreateDatabase() which is called once when the Windows Service Starts.

//Create the local SQLite Database to hold configuration settings
private static void CreateDatabase()
{
    try
    {
        var folder = Environment.GetFolderPath (Environment.SpecialFolder.ApplicationData);
        var db = Path.Combine (folder, "local.db");
        var exists = File.Exists(db);
 
        _dbSqLiteConnection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", db));
 
        using (var tran = new TransactionScope())
        {
            //create the database only if doesn't exist already
            if (!exists)
            {
                SQLiteConnection.CreateFile(db);                        
                _dbSqLiteConnection.Open();
                        
                var sql = "create table Configuration (property varchar(100), value varchar(255))";
                var command = new SQLiteCommand(sql, _dbSqLiteConnection);
                command.ExecuteNonQuery();
                //store the syncrhonization datetime
                sql = "insert into Configuration (property, value) values ('LastSyncDateTime', '0')";
                command = new SQLiteCommand(sql, _dbSqLiteConnection);
                command.ExecuteNonQuery();
                //synchronize every 10 seconds
                sql = "insert into Configuration (property, value) values ('RoundtripTimer', '10000')";
                command = new SQLiteCommand(sql, _dbSqLiteConnection);
                command.ExecuteNonQuery();
                //check if shared document libraries folders exists
                sql = "insert into Configuration (property, value) values ('SharePointFoldersExist', 'no')";
                command = new SQLiteCommand(sql, _dbSqLiteConnection);
                command.ExecuteNonQuery();
 
                _dbSqLiteConnection.Close();                        
            }                    
            tran.Complete();
        }
    }
    catch (Exception ex)
    {                
        throw new ApplicationException(@"Database creation failed. StackTrace: " + ex.StackTrace);
    }            
}

Afterwards we create the Shared Document Folders in SharePoint via ODBC Driver using the classes available in the System.Data.ODBC namespace and update the local SQLite Configuration Table with relevant information.
 

var cnSharePoint = new OdbcConnection(m_csSharePoint);
 
cnSharePoint.Open();
 
sql = "EXEC SP_ENSURE_FOLDER_PATH 'Documents', '/PDF'";
var spAdapter = new OdbcCommand(sql, cnSharePoint);
spAdapter.ExecuteNonQuery();
                               
sql = "EXEC SP_ENSURE_FOLDER_PATH 'Documents', '/DOC'";
spAdapter = new OdbcCommand(sql, cnSharePoint);
spAdapter.ExecuteNonQuery();
 
sql = "EXEC SP_ENSURE_FOLDER_PATH 'Documents', '/PICTURES'";
spAdapter = new OdbcCommand(sql, cnSharePoint);
spAdapter.ExecuteNonQuery();
 
cnSharePoint.Close();
 
_dbSqLiteConnection.Open();
sql = "UPDATE CONFIGURATION SET VALUE='yes' WHERE property='SharePointFoldersExist'";
var sqLiteCommand = new SQLiteCommand(sql, _dbSqLiteConnection);
sqLiteCommand.ExecuteNonQuery();
_dbSqLiteConnection.Close();

The next step consists in the core of the synchronization logic and can be found inside the method PerformSyncrhonization

The ODBC Connections are created to address Exchange Server and SharePoint and a Timer object is used to pooling data from them in a pre-defined time interval, configurable in the local SQLite database.

var sql = "Select [value] From Configuration Where property = 'RoundtripTimer'";
var sqLiteAdapter = new SQLiteDataAdapter(sql, _dbSqLiteConnection);
var dsTimer = new DataSet();
sqLiteAdapter.Fill(dsTimer);
 
int interval = int.Parse(dsTimer.Tables[0].Rows[0]["value"].ToString()); // every 10 seconds by default
 
var timer = new System.Timers.Timer(interval) {Enabled = true};
 
var cnExchange = new OdbcConnection(m_csExchange);
var cnSharePoint = new OdbcConnection(m_csSharePoint);
                                               
timer.Elapsed += (sender, args) =>
{
   // logic to synchronize
}

The next step consists in getting the relevant data from Exchange Server, that in our case are the attachments in the emails received.

To retrieve all attachments from mails received we must query the Exchange Server entity Message. However the Message entity must filter only messages that are located in the inbox folder.

In order to filter only the inbox messages we must get first the unique identifier that represents the folder inbox in Exchange with the following SQL statement

sql = @"select ID from folder where displayname='Inbox';";
var exAdapter = new OdbcDataAdapter(sql, cnExchange);
var dsExchangeFolder = new DataSet();
exAdapter.Fill(dsExchangeFolder);
var inboxFolderId = dsExchangeFolder.Tables[0].Rows[0]["ID"].ToString();

With the ID of the folder we can get all mails that contains attachments and that were received only after the last synchronization roundtrip was executed.
The LastModified field is used in this case to avoid querying for email attachments that were already inserted into SharePoint.

var sLastSyncDateTime = lastSyncDateTime.ToString("yyyy/MM/dd hh:mm:ss");
 
sql = string.Format(
    @"select ID from message where parentname = '{0}' and lastModified > '{1}' and hasattachment='true';",
    inboxFolderId, sLastSyncDateTime);
exAdapter = new OdbcDataAdapter(sql, cnExchange);
var dsExchangeMessage = new DataSet();
exAdapter.Fill(dsExchangeMessage);

Following we retrieve metadata information about all the attachments for each mail message retrieved from previous query.

That include information such as filename, size, etc.

var messageId = row["ID"].ToString();
sql = string.Format(@"Exec SP_SELECT_ATTACHMENTS '{0}';", messageId);
exAdapter = new OdbcDataAdapter(sql, cnExchange);
var dsExchangeAttachments = new DataSet();
exAdapter.Fill(dsExchangeAttachments);

Finally we retrieve the binary representation of the attachment that will be inserted/updated into SharePoint.

var attachmentAddress = row1["Address"].ToString();
var attachmentFileName = row1["FileName"].ToString();
 
//binary representation of the file
sql = string.Format(@"EXEC SP_SAVE_ATTACHMENT '{0}';", attachmentAddress);
exAdapter = new OdbcDataAdapter(sql, cnExchange);
var dsExchangeAttachmentBinary = new DataSet();
exAdapter.Fill(dsExchangeAttachmentBinary);
 
var binaryFile = dsExchangeAttachmentBinary.Tables[0].Rows[0]["OutBuffer"];

Once we have all data necessary from Exchange Server side we proceed with the SharePoint side by inserting/updating documents into the Shared Document Library folders according to its extension.

That is achieved by the logic in the method FolderPath(string filename).

This method will return the string that represents the location of the SharePoint Document Library Folder we would like to insert the document and basically chooses it according to the file extension name metadata retrieved from previous queries. This string will be passed as a SQL parameter to a SharePoint stored procedure to insert shared documents.

sql = "EXEC SP_INSERT_SHAREDDOCUMENT ?, ?, ?, ?, ?";
cnSharePoint.Open();
using (var cmd = new OdbcCommand(sql, cnSharePoint))
{
    cmd.Parameters.AddWithValue("@sharedDocumentsTableName", "Documents");
    cmd.Parameters.AddWithValue("@fileName", attachmentFileName);
    cmd.Parameters.AddWithValue("@relativePath", FolderPath(attachmentFileName));
    cmd.Parameters.AddWithValue("@mimeType",
        "application/vnd.openxmlformats-officedocument.wordprocessingml.document");
    cmd.Parameters.AddWithValue("@data", binaryFile);
    var res = new DataTable();
    res.Load(cmd.ExecuteReader());
 
    //info returned from SharePoint                                    
    var success = (bool) res.Rows[0]["Success"];
    var errorMessage = (string) res.Rows[0]["ErrorMsg"];
    Console.WriteLine(@"{0}, {1}", success, errorMessage);
}
cnSharePoint.Close();

private static string FolderPath(string fileName)
{
    var s = Path.GetExtension(fileName);
    if (s != null)
    {
        var extension = s.ToUpper();
        switch (extension)
        {
            case ".PDF":
                return @"/cbplayground/takahashi0000/Shared Documents/PDF";
            case ".DOC" :
            case ".DOCX":
                return @"/cbplayground/takahashi0000/Shared Documents/DOC";
            case ".JPG":
            case ".PNG":
                return @"/cbplayground/takahashi0000/Shared Documents/PICTURES";                        
        }
    }
    return @"/cbplayground/takahashi0000/Shared Documents";
}

And that’s it!!!

Additional Notes

The code provided does not cover updating documents into the Shared Document Library Folders in SharePoint.

Calling the Stored Procedure SP_INSERT_SHAREDDOCUMENT will return a recordset with 2 columns. Success and ErrorMsg respectively. If the document already exists in SharePoint the column Success will contains the value false and the description of the Error will be placed into the ErrorMsg column. I will let with you the task to extend the logic to perform updates properly.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author


Comments and Discussions

 
GeneralMy vote of 4 PinprofessionalChristian Amado12-Sep-14 4:40 
Questionmissing code? Pinmemberpeterbacsi20-Aug-14 1:33 
AnswerRe: missing code? PinmemberTom Olejnik20-Aug-14 4:26 
GeneralRe: missing code? Pinmemberpeterbacsi20-Aug-14 21:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411022.1 | Last Updated 18 Aug 2014
Article Copyright 2014 by Jefferson Takahashi
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid