Download emails with SQLServer

In this blog post I’ll describe how to use Mail.dll .NET email component with SQL Server to download emails.

First, you need to create a regular .NET assembly with definition of SQL stored procedure. Remember to add reference to Mail.dll.

In this example the assembly name is SqlEmailDownloader.dll:

// C#

using System.Data;
using Microsoft.SqlServer.Server;
using Limilabs.Client.IMAP;
using Limilabs.Mail;

public class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetEmails()
    {
        SqlDataRecord record = new SqlDataRecord(new[]
             {
                 new SqlMetaData("UID", SqlDbType.BigInt),
                 new SqlMetaData("Subject", SqlDbType.NVarChar, 128)
             });

        using (Imap client = new Imap())
        {
            client.Connect("server"); // or ConnectSSL
            client.UseBestLogin("user", "password");
            client.SelectInbox();

            SqlContext.Pipe.SendResultsStart(record);
            foreach (long uid in client.Search(Flag.Unseen))
            {
                var eml = client.PeekHeadersByUID(uid);
                IMail email = new MailBuilder().CreateFromEml(eml);

                record.SetSqlInt64(0, uid);
                record.SetSqlString(1, email.Subject);
                SqlContext.Pipe.SendResultsRow(record);
            }
            client.Close();
            SqlContext.Pipe.SendResultsEnd();
        }
    }
} ;

The code above:

  • connects to the IMAP server,
  • finds all unseen emails,
  • downloads headers and
  • passes the results to SQL server.

Here’s the VB.NET version:

' VB.NET

Imports System.Data
Imports Microsoft.SqlServer.Server
Imports Limilabs.Client.IMAP
Imports Limilabs.Mail

Public Class StoredProcedures
	<microsoft.SqlServer.Server.SqlProcedure> _
	Public Shared Sub GetEmails()
		Dim record As New SqlDataRecord(New () { _
			New SqlMetaData("UID", SqlDbType.BigInt), _
			New SqlMetaData("Subject", SqlDbType.NVarChar, 128)})

		Using client As New Imap()
			client.Connect("server") ' or ConnectSSL
			client.UseBestLogin("user", "password")
			client.SelectInbox()

			SqlContext.Pipe.SendResultsStart(record)
			For Each uid As Long In client.Search(Flag.Unseen)
				Dim eml = client.PeekHeadersByUID(uid)
				Dim email As IMail = New MailBuilder() _
					.CreateFromEml(eml)

				record.SetSqlInt64(0, uid)
				record.SetSqlString(1, email.Subject)
				SqlContext.Pipe.SendResultsRow(record)
			Next
			client.Close()
			SqlContext.Pipe.SendResultsEnd()
		End Using
	End Sub
End Class

In SQL Server Management Studio you need to execute following script (HelloWorld is a sample database):

USE HelloWorld
GO

EXEC sp_configure 'clr enabled' , '1'
GO
RECONFIGURE
GO

Alter Database HelloWorld Set TrustWorthy On
GO

CREATE ASSEMBLY [System.Windows.Forms] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll'
With Permission_Set=Unsafe
GO
CREATE ASSEMBLY SqlEmailDownloaderAssembly FROM 'D:\1\SqlEmailDownloader\bin\Debug\SqlEmailDownloader.dll'
With Permission_Set=Unsafe
GO

CREATE PROCEDURE GetEmails
AS EXTERNAL NAME SqlEmailDownloaderAssembly.StoredProcedures.GetEmails
GO

Finally you can execute the procedure:

EXEC GetEmails

…and see the results:

Click here to download Mail.dll IMAP component

Tags: , , , , ,

10 Responses to “Download emails with SQLServer”

  1. Bill Stickers Says:

    I think it is pretty common to want to download emails and store them in a database. Is there a preferred way to do this for mail.dll or a recommended ADO model structure which corresponds to the IMail interface?

  2. Limilabs support Says:

    @Bill

    In our practice the most important thing is to save the original, raw eml data for later reference.

    True it takes space, but if you have raw data, at any point of time,
    you can check S/MIME digital signatures, DKIM signatures, and
    if you forgot to include some important detail in your DB structure,
    you are able to recreate it from original data.

    As for the DB structure we can’t give you any good advice,
    as it greatly depends on what are you planning to do with the data:
    Searching? Are attachments important? Do attachments need to be searchable?
    Are there any headers that are specially important to you?

  3. Mike Says:

    Just having some problems. I have created my DLL based of yours but i get an error message when i run the sql query to create the stored procedure.
    Here is my code bellow

    Imports System.Data
    Imports Microsoft.SqlServer.Server
    Imports Limilabs.Client.IMAP
    Imports Limilabs.Mail
    Public Class Class1
            <Microsoft.SqlServer.Server.SqlProcedure> _
            Public Shared Sub GetEmails()
                Dim record As New SqlDataRecord({ _
                    New SqlMetaData("UID", SqlDbType.BigInt), _
                    New SqlMetaData("Subject", SqlDbType.NVarChar, 128)})
    
                Using client As New Imap()
                    client.ConnectSSL("Imap.gmail.com")
                    client.Login("username", "password")
                    client.SelectInbox()
    
                    SqlContext.Pipe.SendResultsStart(record)
                    For Each uid As Long In client.Search(Flag.Unseen)
                        Dim eml = client.PeekHeadersByUID(uid)
                        Dim email As IMail = New MailBuilder() _
                        .CreateFromEml(eml)
    
                        record.SetSqlInt64(0, uid)
                        record.SetSqlString(1, email.Subject)
                        SqlContext.Pipe.SendResultsRow(record)
                    Next
                    client.Close()
                    SqlContext.Pipe.SendResultsEnd()
                End Using
            End Sub
        End Class
    

    Here is the query

    USE HelloWorld
    GO
    EXEC sp_configure 'clr enabled' , '1'
    
    GO
    
    RECONFIGURE
    
    GO
    Alter Database HelloWorld Set TrustWorthy On
    
    GO
    CREATE ASSEMBLY [System.Windows.Forms] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll'
    With Permission_Set=Unsafe
    
    GO
    
    CREATE ASSEMBLY SqlEmailDownloaderAssembly FROM 'E:\mail\SqlEmailDownloader.dll'
    
    With Permission_Set=Unsafe
    
    GO
    
    CREATE PROCEDURE GetEmails
    
    AS EXTERNAL NAME SqlEmailDownloaderAssembly.Class1.GetEmails
    
    GO
    

    Thanks
    Mike

  4. Limilabs support Says:

    @Mike

    What error do you get? What is the exception message, stack trace?
    Can you enable logging?

  5. jesil james Says:

    I also get an error when i run the sql query ..the error is
    CREATE ASSEMBLY for assembly ‘Email_SQL’ failed because the assembly is built for an unsupported version of the Common Language Runtime

  6. jesil james Says:

    Sorry the above error was fixed which was a.net framwork issue. which i changed it to 3.5. Now when I am running the SP, I am getting the error -Could not find Type ‘StoredProcedures’ in assembly ‘SqlEmailDownloader’.

  7. jesil james Says:

    I am getting the Following error in SQL Server when I run the procedure

    Msg 6522, Level 16, State 1, Procedure GetEmails1, Line 0
    A .NET Framework error occurred during execution of user-defined routine or aggregate “GetEmails1”:
    Limilabs.Client.ServerException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 74.125.134.108:143 […]

  8. Limilabs support Says:

    @Jesil

    You simply can not connect. You are using incorrect server address or/and port number or your server is down. This connection attempt failed article may help you.

  9. Dushyant Says:

    In the stored procedure I am recieving the following error:

    Msg 6501, Level 16, State 7, Line 1
    CREATE ASSEMBLY failed because it could not open the physical file “D:\1\SqlEmailDownloader\bin\Debug\SqlEmailDownloader.dll”: 3(failed to retrieve text for this error. Reason: 15105).
    Msg 6528, Level 16, State 1, Procedure GetEmails, Line 2
    Assembly ‘SqlEmailDownloaderAssembly’ was not found in the SQL catalog of database ‘HelloWorld’.
    Msg 2812, Level 16, State 62, Line 2
    Could not find stored procedure ‘GetEmail’.

  10. Limilabs support Says:

    @Dushyant,

    Have you created this assembly, compiled it? Is the file in this folder. Does your SQL have enough permissions to read this file?

Questions?

Consider using our Q&A forum for asking any questions.