{"id":1106,"date":"2010-09-10T16:17:46","date_gmt":"2010-09-10T14:17:46","guid":{"rendered":"http:\/\/www.limilabs.com\/blog\/?p=1106"},"modified":"2016-09-09T10:35:13","modified_gmt":"2016-09-09T08:35:13","slug":"download-emails-with-sqlserver","status":"publish","type":"post","link":"https:\/\/www.limilabs.com\/blog\/download-emails-with-sqlserver","title":{"rendered":"Download emails with SQLServer"},"content":{"rendered":"<p>In this blog post I&#8217;ll describe how to use <a href=\"\/mail\">Mail.dll .NET email component<\/a> with SQL Server to download emails.<\/p>\n<p>First, you need to create a <strong>regular .NET assembly<\/strong> with definition of <strong>SQL stored procedure<\/strong>. Remember to add reference to Mail.dll.<\/p>\n<p>In this example the assembly name is SqlEmailDownloader.dll:<\/p>\n<pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\r\n\/\/ C#\r\n\r\nusing System.Data;\r\nusing Microsoft.SqlServer.Server;\r\nusing Limilabs.Client.IMAP;\r\nusing Limilabs.Mail;\r\n\r\npublic class StoredProcedures\r\n{\r\n    &#x5B;Microsoft.SqlServer.Server.SqlProcedure]\r\n    public static void GetEmails()\r\n    {\r\n        SqlDataRecord record = new SqlDataRecord(new&#x5B;]\r\n             {\r\n                 new SqlMetaData(&quot;UID&quot;, SqlDbType.BigInt),\r\n                 new SqlMetaData(&quot;Subject&quot;, SqlDbType.NVarChar, 128)\r\n             });\r\n\r\n        using (Imap client = new Imap())\r\n        {\r\n            client.Connect(&quot;server&quot;); \/\/ or ConnectSSL\r\n            client.UseBestLogin(&quot;user&quot;, &quot;password&quot;);\r\n            client.SelectInbox();\r\n\r\n            SqlContext.Pipe.SendResultsStart(record);\r\n            foreach (long uid in client.Search(Flag.Unseen))\r\n            {\r\n                var eml = client.PeekHeadersByUID(uid);\r\n                IMail email = new MailBuilder().CreateFromEml(eml);\r\n\r\n                record.SetSqlInt64(0, uid);\r\n                record.SetSqlString(1, email.Subject);\r\n                SqlContext.Pipe.SendResultsRow(record);\r\n            }\r\n            client.Close();\r\n            SqlContext.Pipe.SendResultsEnd();\r\n        }\r\n    }\r\n} ;\r\n<\/pre>\n<p>The code above:<\/p>\n<ul>\n<li>connects to the IMAP server,<\/li>\n<li>finds all unseen emails,<\/li>\n<li>downloads headers and<\/li>\n<li>passes the results to SQL server.<\/li>\n<\/ul>\n<p>Here&#8217;s the VB.NET version:<\/p>\n<pre class=\"brush: vb; title: ; notranslate\" title=\"\">\r\n' VB.NET\r\n\r\nImports System.Data\r\nImports Microsoft.SqlServer.Server\r\nImports Limilabs.Client.IMAP\r\nImports Limilabs.Mail\r\n\r\nPublic Class StoredProcedures\r\n\t&lt;microsoft.SqlServer.Server.SqlProcedure&gt; _\r\n\tPublic Shared Sub GetEmails()\r\n\t\tDim record As New SqlDataRecord(New () { _\r\n\t\t\tNew SqlMetaData(&quot;UID&quot;, SqlDbType.BigInt), _\r\n\t\t\tNew SqlMetaData(&quot;Subject&quot;, SqlDbType.NVarChar, 128)})\r\n\r\n\t\tUsing client As New Imap()\r\n\t\t\tclient.Connect(&quot;server&quot;) ' or ConnectSSL\r\n\t\t\tclient.UseBestLogin(&quot;user&quot;, &quot;password&quot;)\r\n\t\t\tclient.SelectInbox()\r\n\r\n\t\t\tSqlContext.Pipe.SendResultsStart(record)\r\n\t\t\tFor Each uid As Long In client.Search(Flag.Unseen)\r\n\t\t\t\tDim eml = client.PeekHeadersByUID(uid)\r\n\t\t\t\tDim email As IMail = New MailBuilder() _\r\n\t\t\t\t\t.CreateFromEml(eml)\r\n\r\n\t\t\t\trecord.SetSqlInt64(0, uid)\r\n\t\t\t\trecord.SetSqlString(1, email.Subject)\r\n\t\t\t\tSqlContext.Pipe.SendResultsRow(record)\r\n\t\t\tNext\r\n\t\t\tclient.Close()\r\n\t\t\tSqlContext.Pipe.SendResultsEnd()\r\n\t\tEnd Using\r\n\tEnd Sub\r\nEnd Class\r\n<\/pre>\n<p>In SQL Server Management Studio you need to execute following script (HelloWorld is a sample database):<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nUSE HelloWorld\r\nGO\r\n\r\nEXEC sp_configure 'clr enabled' , '1'\r\nGO\r\nRECONFIGURE\r\nGO\r\n\r\nAlter Database HelloWorld Set TrustWorthy On\r\nGO\r\n\r\nCREATE ASSEMBLY &#x5B;System.Windows.Forms] FROM 'C:\\Windows\\Microsoft.NET\\Framework\\v2.0.50727\\System.Windows.Forms.dll'\r\nWith Permission_Set=Unsafe\r\nGO\r\nCREATE ASSEMBLY SqlEmailDownloaderAssembly FROM 'D:\\1\\SqlEmailDownloader\\bin\\Debug\\SqlEmailDownloader.dll'\r\nWith Permission_Set=Unsafe\r\nGO\r\n\r\nCREATE PROCEDURE GetEmails\r\nAS EXTERNAL NAME SqlEmailDownloaderAssembly.StoredProcedures.GetEmails\r\nGO\r\n<\/pre>\n<p>Finally you can execute the procedure:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nEXEC GetEmails\r\n<\/pre>\n<p>&#8230;and see the results:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full\" title=\"Mail.dll with SQLServer\" alt=\"\" src=\"\/blog\/wp-content\/uploads\/2010\/09\/Maildll_SQLServer.png\" width=\"473\" height=\"253\" \/><\/p>\n<p>Click here to <a href=\"\/mail\">download Mail.dll IMAP component<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this blog post I&#8217;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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[15,33,28,77,52,57],"class_list":["post-1106","post","type-post","status-publish","format-standard","hentry","category-mail-dll","tag-c","tag-email-component","tag-imap","tag-imap-component","tag-sql","tag-vb-net"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/posts\/1106"}],"collection":[{"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/comments?post=1106"}],"version-history":[{"count":14,"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/posts\/1106\/revisions"}],"predecessor-version":[{"id":5065,"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/posts\/1106\/revisions\/5065"}],"wp:attachment":[{"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/media?parent=1106"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/categories?post=1106"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/tags?post=1106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}