Query result in HTML format

Question

Hi

I have below code and i want to send it in HTML format through Email body

            string _StrSQL = "SELECT T0.\"TransId\", T0.\"DocNum\", T0.\"DocDate\" " +
            "FROM \"ATULAYA_NEW_LIVE\".OINV T0 " +
            "INNER JOIN CRD1 T3 ON T0.\"CardCode\" = T3.\"CardCode\"  " +
            "INNER JOIN INV12 T5 ON T5.\"DocEntry\" = T0.\"DocEntry\" + " +
            "where T0.\"CardCode\" = T3.\"CardCode\"  and T3.\"GSTRegnNo\" <> '' " +
            "and T5.\"LocGSTN\" <> T5.\"BpGSTN\" ";

Thanks

Muhammad Imran Ansari · Answer

Hello Ramco, I believe, your issue has been fixed through another question.

Ramco Ramco · Answer

Hi Muhammad It is displaying like below but not in table format. table { border-collapse: collapse; width: 100%; }th, td { border: 1px solid black; padding: 8px; text-align: left; }th { background-color: #f2f2f2; } Transaction ID Doc. No Doc. Date 1655069 5107214 1/8/2025 12:00:00 AM 1655912 5107222 1/8/2025 12:00:00 AM 210971 4700072 5/11/2024 12:00:00 AM 212022 4700073 5/11/2024 12:00:00 AM 825928 4600031 7/27/2024 12:00:00 AM 982973 4000078 9/27/2024 12:00:00 AM Thanks

Muhammad Imran Ansari · Answer

To pass htmlTable to the email body, simply append it to the existing body variable. Here's how you can do it: string body = $@" SQL Query Below is the SQL query: {_StrSQL} This query is used to retrieve data from the database. {htmlTable} "; // rest of the code will remain the same

Ramco Ramco · Answer

Hi Muhammad How htmltable be passed to body of Email Thanks

Muhammad Imran Ansari · Answer

Your HTML table structure is correct, but it might not display borders properly because of missing CSS styling. The border="1" attribute should usually work, but some email clients or browsers may require explicit CSS styling. Can you try below code: StringBuilder htmlTable = new StringBuilder(); htmlTable.Append(" "); htmlTable.Append("table { border-collapse: collapse; width: 100%; }"); htmlTable.Append("th, td { border: 1px solid black; padding: 8px; text-align: left; }"); htmlTable.Append("th { background-color: #f2f2f2; }"); htmlTable.Append(" "); htmlTable.Append(" "); // Add headers to the table htmlTable.Append(" "); htmlTable.Append(" Transaction ID "); htmlTable.Append(" Doc. No "); htmlTable.Append(" Doc. Date "); htmlTable.Append(" "); while (!oRecordSet.EoF) { htmlTable.Append(" "); htmlTable.Append(" " + oRecordSet.Fields.Item("TransId").Value.ToString() + " "); htmlTable.Append(" " + oRecordSet.Fields.Item("DocNum").Value.ToString() + " "); htmlTable.Append(" " + oRecordSet.Fields.Item("DocDate").Value.ToString() + " "); htmlTable.Append(" "); oRecordSet.MoveNext(); } htmlTable.Append(" ");

Ramco Ramco · Answer

Hi Sreenath I have below code Hi Imran It is displaying like below but i want Output to be displayed with Table borders SAPbobsCOM.Recordset oRecordSet = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset); oRecordSet.DoQuery(_StrSQL); StringBuilder htmlTable = new StringBuilder(); htmlTable.Append(" "); // Add headers to the table htmlTable.Append(" "); htmlTable.Append(" Transaction ID "); htmlTable.Append(" Doc. No "); htmlTable.Append(" Doc. Date "); htmlTable.Append(" "); while (!oRecordSet.EoF) { htmlTable.Append(" "); htmlTable.Append(" " + oRecordSet.Fields.Item("TransId").Value.ToString() + " "); htmlTable.Append(" " + oRecordSet.Fields.Item("DocNum").Value.ToString() + " "); htmlTable.Append(" " + oRecordSet.Fields.Item("DocDate").Value.ToString() + " "); htmlTable.Append(" "); oRecordSet.MoveNext(); } htmlTable.Append(" "); Thanks

Sreenath Kappoor · Answer

Hello Ramco, You need to fetch the results into a DataTable then format the DataTable as an HTML table. Can you please share your current code ?

Ramco Ramco · Answer

Hi Imran It is displaying like below but i want Output to be displayed with Table borders SAPbobsCOM.Recordset oRecordSet = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset); oRecordSet.DoQuery(_StrSQL); SELECT T0."TransId", T0."DocNum", T0."DocDate" FROM "LIVE".OINV T0 INNER JOIN CRD1 T3 ON T0."CardCode" = T3."CardCode" INNER JOIN INV12 T5 ON T5."DocEntry" = T0."DocEntry" where T0."CardCode" = T3."CardCode" and T0."PayToCode" = T3."Address" and T3."AdresType" = 'B' and T3."GSTRegnNo" '' This query is used to retrieve data from the database. Thanks

Muhammad Imran Ansari · Answer

Hello Ramco, To send the SQL query in an HTML-formatted email body, you need to wrap the query in HTML tags. You can use or tags to preserve the formatting of the SQL query and make it more readable in the email. Here's how you can modify your code to send the SQL query in the email body as HTML: Here is the sample code and you can add or modify accordingly: string _StrSQL = "SELECT T0.\"TransId\", T0.\"DocNum\", T0.\"DocDate\" " + "FROM \"ATULAYA_NEW_LIVE\".OINV T0 " + "INNER JOIN CRD1 T3 ON T0.\"CardCode\" = T3.\"CardCode\" " + "INNER JOIN INV12 T5 ON T5.\"DocEntry\" = T0.\"DocEntry\" " + "WHERE T0.\"CardCode\" = T3.\"CardCode\" AND T3.\"GSTRegnNo\" '' " + "AND T5.\"LocGSTN\" T5.\"BpGSTN\" "; var addresses = "test@gmail.com;ramesh@gmail.com"; var fromAddress = new MailAddress("sandeep.singh@gmail.com", "Sender"); const string fromPassword = "Password"; // Use app-specific password if 2FA is enabled string subject = "[Message] SQL Query"; string body = $@" SQL Query Below is the SQL query: {_StrSQL} This query is used to retrieve data from the database. "; var smtp = new SmtpClient { Host = "smtp.gmail.com", Port = 587, EnableSsl = true, DeliveryMethod = SmtpDeliveryMethod.Network, UseDefaultCredentials = false, Credentials = new NetworkCredential(fromAddress.Address, fromPassword) }; foreach (var address in addresses.Split(new[] { ";" }, StringSplitOptions.RemoveEmptyEntries)) { using (var message = new MailMessage(fromAddress, new MailAddress(address)) { Subject = subject, Body = body, IsBodyHtml = true // Set this to true to send the email as HTML }) { try { smtp.Send(message); Console.WriteLine($"Email sent to {address}"); } catch (Exception ex) { Console.WriteLine($"Failed to send email to {address}: {ex.Message}"); } } } Good Luck!

Query result in HTML format

Answers (9)