Nsryjdtyk

So far, managed to:

1. Create a C# Azure WebJob project via Visual Studio and publish it to a web app, that:


2. Connects to an Azure SQL Database and executes SQL query (via SqlDataReader)


3. Adds SqlDataReader results into the email body


4. Sends email

In addition to the above, somewhere between above points 3 and 4, I need to:

5. Create a .CSV file


6. Populate the .CSV file from the SqlDataReader


7. Send the .CSV file via email as attachment

Result set from SqlDataReader to populate CSV looks like:

asdasd@gmail.com ,11/19/2018

asdasdasd@gmail.com ,11/19/2018

asdasdasasdas@live.co.uk ,11/19/2018

asdasddsa@hotmail.com ,11/19/2018

asdasd@hotmail.com ,11/19/2018

asdasddsa@hotmail.com ,11/19/2018

asdasasd@gmail.com ,11/18/2018

Below is what I have so far:

    public static void Main(string args)

    {

        SmtpClient smtp = new SmtpClient();

        int SMTP_PORT = 587; 

        Int32.TryParse(ConfigurationManager.AppSettings["SMTP_PORT"], out SMTP_PORT);

        smtp.Port = SMTP_PORT;

        smtp.Credentials = new NetworkCredential(ConfigurationManager.AppSettings["SMTP_USERNAME"], ConfigurationManager.AppSettings["SMTP_PASSWORD"]);

        smtp.Host = ConfigurationManager.AppSettings["SMTP_HOST"];

        string mailFrom = ConfigurationManager.AppSettings["SMTP_MAIL_FROM"];

        string mailSubject = ConfigurationManager.AppSettings["SMTP_MAIL_SUBJECT"];  



        using (SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["AzureDBConnString"]))

        {

            connection.Open();



            var queryString = @"SELECT * FROM MyTable WHERE Status = 1";



            using (SqlCommand command = new SqlCommand(queryString, connection))

            {

                command.CommandTimeout = 120;

                SqlDataReader reader = command.ExecuteReader();



                if (reader.HasRows)

                {

                    while (reader.Read()) // loop each user and send email

                    {

                        bool emailSentSuccess = true;

                        using (MailMessage mail = new MailMessage())

                        {

                            try

                            {

                                mail.From = new MailAddress(mailFrom);

                                mail.To.Add(new MailAddress(reader["EmailAddress"].ToString()));

                                mail.IsBodyHtml = true;

                                mail.Subject = mailSubject;

                                mail.Body = reader["EmailBody"].ToString();

                                smtp.Send(mail);

                            }

                            catch (Exception ex)

                            {

                                emailSentSuccess = false;

                            }

                        }

                    }

                }

            }

        }

    }

Question: How can I achieve points 5, 6, 7?

You could refer to the following code in your SqlCommand.

using (SqlCommand command = new SqlCommand(queryString, connection))

{

    command.CommandTimeout = 120;

    SqlDataReader reader = command.ExecuteReader();

    if (reader.HasRows)

    {

        StringBuilder sb = new StringBuilder();

        List<string> columnNames = new List<string>();

        List<string> rows = new List<string>();

        for (int i = 0; i < reader.FieldCount; i++)

        {

            string tmpColumnName = reader.GetName(i);

            columnNames.Add(tmpColumnName);

        } 

        sb.Append(string.Join(",", columnNames.ToArray())).Append("rn");

        List<string> currentRow = new List<string>();

        while (reader.Read())

        {

            for (int i = 0; i < reader.FieldCount; i++)

            {

                object item = reader[i];

                sb.Append(item.ToString().Replace(",", ";") + ',');

            }

            sb.Append("rn");

        }

        bool emailSentSuccess = true;

        using (MailMessage mail = new MailMessage())

        {

            try

            {

                using (MemoryStream stream = new MemoryStream(Encoding.ASCII.GetBytes(sb.ToString())))

                {

                    Attachment attachment = new Attachment(stream, new ContentType("text/csv"));

                    attachment.Name = "hello.csv";

                    mail.Attachments.Add(attachment);

                    mail.From = new MailAddress(mailFrom);

                    mail.To.Add(new MailAddress(reader["EmailAddress"].ToString()));

                    mail.IsBodyHtml = true;

                    mail.Subject = mailSubject;

                    mail.Body = reader["EmailBody"].ToString();

                    smtp.Send(mail);

                }

            }

            catch (Exception ex)

            {

                emailSentSuccess = false;

            }

        }

    }

}

The output I test is as below: