Send a table in email

Question

I have a requirement to send the results of a query in emails. I am using two methods:

GetDataTable() : to execute the query and obtain datatable(which needs to be sent in email)

SendAutomatedEmail() : to send automated emails.

Problem: i need to send data table or html table in email, something like code below. this works fine for a string in place of dataTable

public static void Main(string[] args)
{
    DataTable datatable = GetDataTable();
    SendAutomatedEmail(datatable );
}

    public static DataTable GetDataTable(string CommandText)
    {
        string cnString = ConfigurationManager.ConnectionStrings["Connection2"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(cnString);

        string CommandText = "select * from dbo.fs010100 (nolock)";
        SqlCommand sqlCommand =  new SqlCommand( CommandText, sqlConnection);

        SqlDataAdapter sqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
        sqlDataAdapter.SelectCommand = sqlCommand;

        DataTable dataTable = new DataTable();
        dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;

        // Adds or refreshes rows in the DataSet to match those in the data source
        try
        {
            sqlDataAdapter.Fill(dataTable);
            sqlConnection.Close(dataTable );
        }
        catch (Exception _Exception)
        {
            sqlConnection.Close();
            //Console.WriteLine(_Exception.Message);
            return null;
        }

        return dataTable;
    }


    public static void SendAutomatedEmail(DataTable dt, string recipient = "user@domain.com")
    {
        try
        {
            string mailServer = "server.com";

            MailMessage message = new MailMessage(
                                                   "it@domain.com",
                                                   recipient,
                                                   "Test Email",
                                                   dt.ToString()
                                                   );
            SmtpClient client = new SmtpClient(mailServer);
            var AuthenticationDetails = new NetworkCredential("user@domain.com", "password");
            client.Credentials = AuthenticationDetails;
            client.Send(message);
        }
        catch (Exception e)
        {

        }

    }

Answer 1

Insert in the Body message this code:

public string GetHtmlTable(string title, DataTable table)
        {
            try
            {
                string messageBody = "<font> "+title+" </font><br><br>";

                if (table.Rows.Count == 0)
                    return messageBody;
                string htmlTableStart = "<table style=\"border-collapse:collapse; text-align:center;\" >";
                string htmlTableEnd = "</table>";
                string htmlHeaderRowStart = "<tr style =\"background-color:#6FA1D2; color:#ffffff;\">";
                string htmlHeaderRowEnd = "</tr>";
                string htmlTrStart = "<tr style =\"color:#555555;\">";
                string htmlTrEnd = "</tr>";
                string htmlTdStart = "<td style=\" border-color:#5c87b2; border-style:solid; border-width:thin; padding: 5px;\">";
                string htmlTdEnd = "</td>";

                messageBody += htmlTableStart;

                messageBody += htmlHeaderRowStart;
                
                foreach(DataColumn column in table.Columns)
                    messageBody += htmlTdStart + column + htmlTdEnd;

                messageBody += htmlHeaderRowEnd;

                foreach (DataRow row in table.Rows)
                {
                    messageBody +=  htmlTrStart;


                    foreach (string item in row.ItemArray)
                    {
                        messageBody += htmlTdStart;
                        messageBody += item;
                        messageBody += htmlTdEnd;
                    }
                    messageBody += htmlTrEnd;
                }
                messageBody += htmlTableEnd;


                return messageBody;
            }
            catch (Exception e)
            {
                return null;
            }
        }

Answer 2

Other dynamic function:

private string RenderDataTableToHtml(DataTable dtInfo)
    {
        StringBuilder tableStr = new StringBuilder();

        if(dtInfo.Rows != null && dtInfo.Rows.Count > 0)
        {
            int columnsQty = dtInfo.Columns.Count;
            int rowsQty = dtInfo.Rows.Count;

            tableStr.Append("<TABLE BORDER=\"1\">");
            tableStr.Append("<TR>");
            for (int j = 0; j < columnsQty; j++)
            {
                tableStr.Append("<TH>" + dtInfo.Columns[j].ColumnName + "</TH>");
            }
            tableStr.Append("</TR>");

            for (int i = 0; i < rowsQty; i++)
            {
                tableStr.Append("<TR>");
                for (int k = 0; k < columnsQty; k++)
                {
                    tableStr.Append("<TD>");
                    tableStr.Append(dtInfo.Rows[i][k].ToString());
                    tableStr.Append("</TD>");
                }
                tableStr.Append("</TR>");
            }

            tableStr.Append("</TABLE>");
        }            

        return tableStr.ToString();
    }
}

Answer 3

ok, try this now:

public static void Main(string[] args)
{
    DataSet dataSet = getDataSet();
    string htmlString= getHtml(dataSet);
    SendAutomatedEmail(htmlString, "email@domain.com");
}

public static DataSet getDataSet(string CommandText)
{
    string cnString = ConfigurationManager.ConnectionStrings["Connection2"].ConnectionString;
    SqlConnection sqlConnection = new SqlConnection(cnString);

    string CommandText = "select * from dbo.fs010100 (nolock)";
    SqlCommand sqlCommand =  new SqlCommand( CommandText, sqlConnection);

    SqlDataAdapter sqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
    sqlDataAdapter.SelectCommand = sqlCommand;

    DataSet dataSet = new DataSet();

    try
    {

        sqlDataAdapter.Fill(dataSet, "header");
        sqlConnection.Close();
    }
    catch (Exception _Exception)
    {
        sqlConnection.Close();

        return null;
    }

    return dataSet;

}


public static string getHtml(DataSet dataSet)
{
    try
    {
         string messageBody = "<font>The following are the records: </font><br><br>";

         if (dataSet.Tables[0].Rows.Count == 0)
             return messageBody;
         string htmlTableStart = "<table style=\"border-collapse:collapse; text-align:center;\" >";
         string htmlTableEnd = "</table>";
         string htmlHeaderRowStart = "<tr style =\"background-color:#6FA1D2; color:#ffffff;\">";
         string htmlHeaderRowEnd = "</tr>";
         string htmlTrStart = "<tr style =\"color:#555555;\">";
         string htmlTrEnd = "</tr>";
         string htmlTdStart = "<td style=\" border-color:#5c87b2; border-style:solid; border-width:thin; padding: 5px;\">";
         string htmlTdEnd = "</td>";

         messageBody+= htmlTableStart;
         messageBody += htmlHeaderRowStart;
         messageBody += htmlTdStart + "Column1 " + htmlTdEnd;
         messageBody += htmlHeaderRowEnd;

         foreach (DataRow Row in notShippedDataSet.Tables[0].Rows)
         {
             messageBody = messageBody + htmlTrStart;
             messageBody = messageBody + htmlTdStart + Row["fieldName"] + htmlTdEnd;
             messageBody = messageBody + htmlTrEnd;
         }
         messageBody = messageBody + htmlTableEnd;


         return messageBody;
     }
     catch (Exception ex)
     {
          return null;
     }
 }

public static void SendAutomatedEmail(string htmlString, string recipient = "user@domain.com")

{
 try
 {
     string mailServer = "server.com";

     MailMessage message = new MailMessage("it@domain.com", recipient);
     message .IsBodyHtml = true;
     message .Body = htmlString;
     message .Subject = "Test Email";

     SmtpClient client = new SmtpClient(mailServer);
     var AuthenticationDetails = new NetworkCredential("user@domain.com", "password");
     client.Credentials = AuthenticationDetails;
     client.Send(message);
 }
 catch (Exception e)
 {

 }

}

Answer 4

If you are wanting to do the same thing but loop thru the datatable via a DataAdapter look at this link for a quick example .. because you are pretty much doing the same thing this example shows with the exception you are trying to pass the entire datatable vs building the results into the email body.. How to use DataAdapter to DataTable via Email

Answer 5

In the past, I've made an object EmailGrid.cs which inherits from GridView. Then used a method like below to render the HTML into a string.

  public string RenderControl()
        {
            StringBuilder stringBuilder = new StringBuilder();
            StringWriter stringWriter = new StringWriter(stringBuilder);
            HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
            RenderControl(htmlTextWriter);

            return stringBuilder.ToString();
        }