using SendGrid;
using SendGrid.Helpers.Mail;
using Serilog;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;



public void SendEmail()
{
	var sendGridAPIKey = "...";

	string dbConnectionString = "...";

	SendEmailBySendGrid(sendGridAPIKey, dbConnectionString).Wait();
}


private  async Task SendEmailBySendGrid(string sendGridAPIKey, string dbConnectionString)
{

	List<SqlParameter> parameterList = new List<SqlParameter>() { ... };
	DataSet dsEmails = GetDataSetFromDB(dbConnectionString, parameterList, "foo_stored_procedure");
	
	var client = new SendGridClient(sendGridAPIKey);
	
	if (dsEmails != null && dsEmails.Tables.Count > 0)
	{
		foreach (var dr in dsEmails.Tables[0].Rows)
		{
			var from = new EmailAddress(Convert.ToString(dr["from"]));
			var subject = Convert.ToString(dr["subject"]);
			var plainTextContent = Convert.ToString(dr["body"]);
			var to = Convert.ToString(dr["to"]);
			var cc = Convert.ToString(dr["cc"]);
			var bcc = Convert.ToString(dr["bcc"]);
			
			var msgs = new SendGridMessage()
			{
				From = from,
				Subject = subject,
				PlainTextContent = plainTextContent,
			};

			var toIds = to?.Split(';');
			List<EmailAddress> recipients = new List<EmailAddress>();
			foreach (var toId in toIds)
			{
				if (!String.IsNullOrEmpty(toId)){
					recipients.Add(new EmailAddress(toId));
				}
			}
			if (recipients.Any()){
				msgs.AddTos(recipients);
			}
			

			var ccIds = cc?.Split(';');
			List<EmailAddress> ccRecipients = new List<EmailAddress>();
			foreach (var ccId in ccIds)
			{
				if (!String.IsNullOrEmpty(ccId)){
					ccRecipients.Add(new EmailAddress(ccId));
				}
			}
			if (ccRecipients.Any()){
				msgs.AddCcs(ccRecipients);
			}


			string[] bccIds = bcc?.Split(';');
			List<EmailAddress> bccRecipients = new List<EmailAddress>();
			foreach (var bccId in bccIds)
			{
				if (!String.IsNullOrEmpty(bccId)){
					bccRecipients.Add(new EmailAddress(bccId));
				}
			}
			if (bccRecipients.Any()){
				msgs.AddBccs(bccRecipients);
			}
				
			var response = await client.SendEmailAsync(msgs);

			if (response.StatusCode == System.Net.HttpStatusCode.Accepted)
			{

				List<SqlParameter> parameterList = new List<SqlParameter>() { ... };
				var result = UpdateDBByExecutingStoredProcedure(dbConnectionString, parameterList, "foo_stored_procedure");
				
				if(result != 1){
					Log.Error("Database query error");
				}
			}
			else
			{
				Log.Error("SendGrid error");
			}
		}
	}
}