Sending email through MS Sql Server

This sample is intended for MVC, C# code. The front end code consists of Kendo UI & jquery.

I will be using asynchronous mode to upload the file to a temporary destination path “FileUploads” in the server. Users need to be given access to this folder first.

[HttpPost]

public ActionResult SaveAttachment(HttpPostedFileBase ImageFile)

{

       imagepath = ImageFile.FileName;

       var destinationPath = Path.Combine(Server.MapPath("~/FileUploads"), imagepath);

       FileInfo files = new FileInfo(destinationPath);

       ImageFile.SaveAs(destinationPath);

       imagepath = destinationPath;

}

Once the file has been uploaded to the folder in server, our program needs a method to get the server IP. (We may hardcode the IP for server, but that will not be ideal.)

        private string GetIP()

        {

            IPHostEntry host;

            string localIP = "?";

            host = Dns.GetHostEntry(Dns.GetHostName());

            foreach (IPAddress ip in host.AddressList)

            {

                if (ip.AddressFamily == AddressFamily.InterNetwork)

                {

                    localIP = ip.ToString();

                }

            }

            return localIP;

        }

public ActionResult EMail([DataSourceRequest] DataSourceRequest request, Somemodel model)

{         

string ip = GetIP();

       string fileUploadPath = @"\\" + ip + "\\FileUploads\\";

String[] filecount = FileNames.Split(','); //Here get FileNames from FileUploads folder

//Handling multiple files in the attachment here, attachments must be separated by ;

       foreach (string filename in filecount)

       {

                     if (attachmentPath == "")

              {

                           attachmentPath = fileUploadPath + filename;

              }

              else

              {

                     attachmentPath = attachmentPath + ";" + fileUploadPath + filename;

                     }

              }

//Use some business entity to send the mail information to Data access layer

       SomeBusinessEntity mailMaster = new MailMaster

       {

                     Recipients = model.Recipients,

Subject = model.Subject,

                     Body = System.Web.HttpUtility.HtmlDecode(model.Body),

                     BodyFormat = model.BodyFormat,                       

                     FileAttachments = attachmentPath

                     CreatedDate = DateTime.Now

       };

       MailDAL.Email(mailMaster);

}

You may directly call the msdb.dbo.sp_send_dbmail stored procedure from DAL, or the other option is to write a separate stored procedure which will then call the system defined one.

If all code goes fine, your mail will be reaching the intended recipient.

This example also solves the issue "attachment file is invalid" – error 22051 as we use GetIP() to get IP of the server.