Send a table in email
52,675
Solution 1
ok, try this now:
public static void Main(string[] args)
{
DataSet dataSet = getDataSet();
string htmlString= getHtml(dataSet);
SendAutomatedEmail(htmlString, "[email protected]");
}
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 = "[email protected]")
{
try
{
string mailServer = "server.com";
MailMessage message = new MailMessage("[email protected]", recipient);
message .IsBodyHtml = true;
message .Body = htmlString;
message .Subject = "Test Email";
SmtpClient client = new SmtpClient(mailServer);
var AuthenticationDetails = new NetworkCredential("[email protected]", "password");
client.Credentials = AuthenticationDetails;
client.Send(message);
}
catch (Exception e)
{
}
}
Solution 2
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();
}
Author by
14578446
Updated on July 19, 2022Comments
-
14578446 almost 2 years
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 = "[email protected]") { try { string mailServer = "server.com"; MailMessage message = new MailMessage( "[email protected]", recipient, "Test Email", dt.ToString() ); SmtpClient client = new SmtpClient(mailServer); var AuthenticationDetails = new NetworkCredential("[email protected]", "password"); client.Credentials = AuthenticationDetails; client.Send(message); } catch (Exception e) { } }
-
14578446 over 12 yearsi am doing this in console application, do not have system.web.dll dere
-
MethodMan over 12 yearsare you sending email via console app or web app either way you can add to the using at the top of the code using System.Web.Mail;
-
14578446 over 12 yearsbut i am not able to find referrence to system.web dll
-
kuklei almost 3 yearswhere does this loop the columns? Is this a table with just one column?