通过电子邮件发送表格
本文关键字：表格 电子邮件 | 更新日期: 2023-09-27 18:19:53
我需要在电子邮件中发送查询结果。我使用两种方法：
GetDataTable（）：执行查询并获取数据表（需要通过电子邮件发送）
SendAutomatedEmail（）：发送自动电子邮件。
问题：我需要在电子邮件中发送数据表或html表，类似下面的代码。这对于字符串代替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)
{
}
}
好的，现在就试试这个：
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)
{
}
}
过去，我制作了一个从GridView继承的对象EmailGrid.cs。然后使用如下方法将HTML呈现为字符串。
public string RenderControl()
{
StringBuilder stringBuilder = new StringBuilder();
StringWriter stringWriter = new StringWriter(stringBuilder);
HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
RenderControl(htmlTextWriter);
return stringBuilder.ToString();
}
如果您想做同样的事情，但通过DataAdapter循环数据表，请查看此链接以获取快速示例。。因为您几乎正在做与本例相同的事情，只是您试图将整个数据表与将结果构建到电子邮件正文中进行比较。。如何通过电子邮件使用DataAdapter到DataTable
其他动态功能：
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();
}
}
在正文消息中插入此代码：
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;
}
}