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
