ashwin.nprabhu (11/4/2010)
The article is very interesting, but just wanted to ask if the same thing can be explained using C# as reference for people like me.
I don't think I would code it this way, but here's a more-or-less exact translation into C#. One issue is that the SMTP port is an integer, not a string. I'm not sure how that's working in VB.NET, guess is that VB implicitly casts it to the correct type. Don't know if you need namespace in a CLS proc either, but the code in the class should be "right".
This is .NET 4.0, 2.0 or 3.5 should be pretty easy to translate into if you need to.
using System;
using System.Net;
using System.Net.Mail;
namespace YourNamespaceHere
{
public class SQLCLREmail
{
[Microsoft.SqlServer.Server.SqlProcedure]
public void SendMail(string recipients, string CC, string BCC, string subject, string from, string body, string strAttachments, string strSMTPServer, int intSMTPServerPort, string strSMTPServerUser, string strSMTPServerPwd)
{
using (MailMessage MailMsg = new MailMessage())
{
MailMsg.From = new MailAddress(from);
MailMsg.Subject = subject;
MailMsg.Body = body;
MailMsg.IsBodyHtml = true;
if (!String.IsNullOrWhiteSpace(recipients))
{
string[] strTo = recipients.Split(';');
foreach (string strRecip in strTo)
{
MailMsg.To.Add(new MailAddress(strRecip));
}
}
if (!String.IsNullOrWhiteSpace(CC))
{
string[] strCCTo = CC.Split(';');
foreach (string strCCRecip in strCCTo)
{
MailMsg.To.Add(new MailAddress(strCCRecip));
}
}
if (!String.IsNullOrWhiteSpace(BCC))
{
string[] strBCCTo = BCC.Split(';');
foreach (string strBCCRecip in strBCCTo)
{
MailMsg.To.Add(new MailAddress(strBCCRecip));
}
}
if (!String.IsNullOrWhiteSpace(strAttachments))
{
string[] strAttach = strAttachments.Split(';');
foreach (string strFile in strAttach)
{
MailMsg.Attachments.Add(new Attachment(strFile.Trim()));
}
}
if (!String.IsNullOrWhiteSpace(strSMTPServer))
{
SmtpClient smtp = new SmtpClient();
if (!String.IsNullOrWhiteSpace(strSMTPServerUser))
{
smtp.UseDefaultCredentials = false;
smtp.Credentials = new NetworkCredential(strSMTPServerUser, strSMTPServerPwd);
smtp.Host = strSMTPServer;
smtp.Port = intSMTPServerPort;
smtp.Send(MailMsg);
}
else
{
smtp.Host = "localhost";
smtp.Port = 25;
smtp.Send(MailMsg);
}
}
}
}
}
}