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)
{
}
}