C# integration of Dropbox and Exchange example

We decided to write a short blog for any C# developers who would like to develop an integration solution between systems such as Dropbox, Exchange or many others. We created a sample short application which serves as demo of how simple such integration can be accomplished using the Connect Bridge platform.

You can scan the article to understand the concept of the Connect Bridge platform. Moreover, we also provide free trial license of the software upon request in case you would like to play with it on your own.

The integration scenario required here is to create a backup from your exchange emails attachments to your Dropbox folder. Could be useful to have a backup, or to keep your exchange usage to minimum or some other reasons.

No matter what is the integration solution you are required to implement, there are basic easy to follow 3 steps that you need to perform. Those steps are covered next.

First step, is to ensure that you are able to connect to the target system (Exchange & Dropbox in our scenario); easiest way to do that is via CB Query Analyzer. Here I already configured my ConnectBridge server via the Administration tool to connect to MS Exchange 365 and to my Dropbox by creating the required groups and users. I created a username called “martin” with password “1234”. This user is having rights to connect to Exchange365 and Dropbox. Now from Query Analyzer I will create 2 connections to each target system and ensure that I can connect successfully.

As shown above we have a successful configuration and connection to both target systems. Now we can test our statements.

On my exchange account, I have a couple of emails, with only 3 emails that have attachments as shown below

To download the attachments from exchange, we need to follow 3 steps:

1. Get list of email IDs with attachments:
Executing the below statement should give us 3 IDs as we have only 3 emails with attachments, as shown in Figure 4 above. For testing I am going to take the ID of the email which has 2 attachments and will get the list of its email attachments in the next step.

SELECT ID FROM Message WHERE HasAttachment = true;

Figure 5 below shows the output of executing the above statement.

2. Get list of attachments from each email:

Executing the below statement, should give us 2 rows, one for the readme.txt and one for the logo.jpg, as shown in Figure 4 above. Each attachment will have a field called address which will be used in the next step to download the attachment

EXEC SP_SELECT_ATTACHMENTS 'AAMkADljZGY4ZjYzLWY2MDUtN…………';

Figure 6 below shows the output of executing the above statement.

3. Get the attachments:
Now, I will download the logo.jpg attachment using its address that I got from the previous step

EXEC SP_SAVE_ATTACHMENT 'AAMkADljZGY4ZjYzLWY2MDUtNDBjOC0…….';

Figure 7 below shows the output of executing the above statement.

Note: we used a table and 2 stored procedures offered by Exchange Connector, for more information about tables and stored procedures offered by the connector, please refer to the “Exchange Connector Reference” document.

This is one straight forward step. To upload a file to Dropbox, we will execute a stored procedure “SP_UPLOADFILE” that calls the required APIs on Dropbox to upload a file. For simplicity we are going to upload a text file.

SP_UPLOADFILE:

EXEC SP_UPLOADFILE '@path', '@filename', '@content';

The above stored procedure is expecting the content of the file in bytes.
I would like to upload a text file called “first.txt” to the root directory of Dropbox. The content of the file, will be “Hello World”; as agreed, we need to convert that message “Hello World” to bytes via your own code or any online converter.

EXEC SP_UPLOADFILE '/', 'first.txt', ‘SGVsbG8gV29ybGQ=’;

Note: For more information about tables and stored procedures offered by the Dropbox connector, please refer to the “Dropbox Connector Reference” document.

Figure 8 and Figure 9 below shows the execution and the output of the above stored procedure.

Now we know that we are able to download attachments from Exchange and we also know that we can upload documents to Dropbox. We also tested our SQL statements. What we need do now is to copy the connection string from Query Analyzer and our tested statements to our C# application.

To copy the connection from query analyzer, we just need to right click the connection, click Edit and go to Advanced tab and copy the text from there as shown below in Figure 10.

It’s sufficient to create a simple console application. Our solution doesn’t require any external reference or any third party package deployment. The solution is based on ODBC; therefore, we just need to import the required namespaces to our class.

using System.Data;
using System.Data.Odbc;

1. Specify the connection string

string connectionString = "Driver={Media Gateway ODBC Driver};IMPL=CORBA;HOST='localhost';PORT='8087';ACC='ACC_EXCH365_CU7';UID='martin';PWD='1234'";

2. Create & Open the connection:

using (OdbcConnection connection = new OdbcConnection(connectionString))
{
    connection.Open();
    …………………………
}

1. Download the IDs of Exchange emails with attachments to a DataTable “messageIDs”. we are going to use a DataAdapter “messagesAdapter” to connect and download the IDs.

using (OdbcDataAdapter messagesAdapter = new OdbcDataAdapter("SELECT ID FROM Message where HasAttachment = true;", connection))
{
    messagesAdapter.Fill(messageIDs);
}

2. For each email ID in the “messageIDs” table, we will get a list of attachments “info only” and save them to another DataTable “documentsListTable”. We are going to use another DataAdapter “documentsListAdapter” to get that list of attachments.

foreach (DataRow messageIDRow in messageIDs.Rows)
{
     string sqlQueryString = string.Format("EXEC SP_SELECT_ATTACHMENTS '{0}';", 
                             messageIDRow["ID"]);
     using (OdbcDataAdapter documentsListAdapter = new OdbcDataAdapter 
                                                  (sqlQueryString, connection))
            documentsListAdapter.Fill(documentsListTable);
}

3. For each attachment in the “documentsListTable”, we will get the address and use it to get the actual attachment and then add that attachment to a third table “documentsTable”. We are going to use another DataAdapter “documentsAdapter” to get the attachments/documents.

foreach (DataRow documentInfoRow in documentsListTable.Rows)
{
    string sqlQueryString = string.Format("EXEC SP_SAVE_ATTACHMENT '{0}';", 
                            documentInfoRow["Address"]);
    using (documentsAdapter = new OdbcDataAdapter(sqlQueryString, connection))
          documentsAdapter.Fill(documentsTable);
}

4. For each successfully download attachment from exchange, display a message to the user using the attachment file name.

foreach (DataRow documentRow in documentsTable.Rows)
    Console.WriteLine(string.Format("{0} downloaded!", 
                                    documentRow["OutFileName"]));

if (connection.State == ConnectionState.Open)
        connection.Close();

1. Specify the connection string

string connectionString = "Driver={Media Gateway ODBC Driver};IMPL=CORBA;HOST='localhost';PORT='8087';ACC='ACC_DRBX_CBD';UID='martin';PWD='1234'";

2. Create & Open the connection:

using (OdbcConnection connection = new OdbcConnection(connectionString))
{
    connection.Open();
    …………………………
}

For each attachment file in the “documentsTable”, we will get the file name “OutFileName” and the file contents “OutBuffer” and save the file to the root of Dropbox folder “/”.

We don’t need a new DataAdapter, we just need an OdbcCommand based on “SP_UPLOADFILE” stored procedure discussed earlier, and we need to add the required parameters to it, so we can pass the required values to the stored procedure.

foreach (DataRow documentRow in documentsTable.Rows)
{
     using (OdbcCommand command = new OdbcCommand("Exec SP_UploadFile ?, ?, ?", 
           connection))
     {
       command.CommandType = CommandType.StoredProcedure;
       command.Parameters.AddWithValue("@path", "/");
       command.Parameters.AddWithValue("@filename", documentRow["OutFileName"]);
       command.Parameters.AddWithValue("@content", documentRow["OutBuffer"]);
       fileUploadStatus = (command.ExecuteNonQuery() > 0) ? "Uploaded!" : 
                          "Not Uploaded!";
       Console.WriteLine("{0} : {1}", documentRow["OutFileName"], 
                                      fileUploadStatus);
     }
}

if (connection.State == ConnectionState.Open)
        connection.Close();

We tried to keep the application logic as much simple as possible, however the logic could be improved by saving attachments from each exchange email to a separate folder, where the folder name could be a part of the message ID or any other unique identifier.

For simplicity we ignored many essentials coding standards including but not limited to:

1. Having connection strings in a configuration file
2. Hashing and encrypting passwords using a hash code and a salt
3. Implementing design patterns like Single Responsibility Principle or even better, Dependency Inversion Principle

using System;
using System.Data;
using System.Data.Odbc;

namespace MySolution
{
    class Program
    {
        static void Main(string[] args)
        {
            //provide connection
            string connectionString = string.Empty;
            string sqlQueryString = string.Empty;
            OdbcConnection connection;
            OdbcCommand command;
            DataTable documentsTable = new DataTable();
            OdbcDataAdapter messagesAdapter, documentsListAdapter, documentsAdapter;
            DataTable messageIDs = new DataTable();
            DataTable documentsListTable = new DataTable();

            //creating connection to Exchange
            connectionString = "Driver={Media Gateway ODBC Driver};IMPL=CORBA;HOST='localhost';PORT='8087';ACC='ACC_EXCH365_CU7';UID='martin';PWD='1234'";
            using (connection = new OdbcConnection(connectionString))
            {
                //open connection
                connection.Open();

                //load message ids for messages with attachements
                using (messagesAdapter = new OdbcDataAdapter("SELECT ID FROM Message where HasAttachment = true;", connection))
                {
                    //fill messageIDs table with messages
                    messagesAdapter.Fill(messageIDs);
                }

                //for each message using message id, get list of attachments
                foreach (DataRow messageIDRow in messageIDs.Rows)
                {
                    sqlQueryString = string.Format("EXEC SP_SELECT_ATTACHMENTS '{0}';", messageIDRow["ID"]);
                    using (documentsListAdapter = new OdbcDataAdapter(sqlQueryString, connection))
                        documentsListAdapter.Fill(documentsListTable);
                }

                //get each attachement and save it to documentsTable
                foreach (DataRow documentInfoRow in documentsListTable.Rows)
                {
                    sqlQueryString = string.Format("EXEC SP_SAVE_ATTACHMENT '{0}';", documentInfoRow["Address"]);
                    using (documentsAdapter = new OdbcDataAdapter(sqlQueryString, connection))
                        documentsAdapter.Fill(documentsTable);
                }

                //give feedback to user that those files were downloaded
                foreach (DataRow documentRow in documentsTable.Rows)
                    Console.WriteLine(string.Format("{0} downloaded!", documentRow["OutFileName"]));

                //close connection to Exchange
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }

            //creating connection to DropBox
            connectionString = "Driver={Media Gateway ODBC Driver};IMPL=CORBA;HOST='localhost';PORT='8087';ACC='ACC_DRBX_CBD';UID='martin';PWD='1234'";
            using (connection = new OdbcConnection(connectionString))
            {
                //open connection
                connection.Open();

                //document upload status placeholder
                string fileUploadStatus = string.Empty;

                //for each document we have in our documentsTable
                foreach (DataRow documentRow in documentsTable.Rows)
                {
                    //call a stored procedure to upload the file
                    using (command = new OdbcCommand("Exec SP_UploadFile ?, ?, ?", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@path", "/");
                        command.Parameters.AddWithValue("@filename", documentRow["OutFileName"]);
                        command.Parameters.AddWithValue("@content", documentRow["OutBuffer"]);

                        //check the status and display on screen
                        fileUploadStatus = (command.ExecuteNonQuery() > 0) ? "Uploaded!" : "Not Uploaded!";
                        Console.WriteLine("{0} : {1}", documentRow["OutFileName"], fileUploadStatus);
                    }
                }

                //close connection to Dropbox
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }

            //give feedback to user
            Console.WriteLine("all done...");
            Console.ReadKey();
        }
    }
}