Query result in HTML format

Hi Muhammad

 It is displaying like below but not in table format.

                
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

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

Hi Muhammad

  How htmltable be passed to body of Email

Thanks

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("");

// Add headers to the table
htmlTable.Append("");
htmlTable.Append("");
htmlTable.Append("");
htmlTable.Append("");
htmlTable.Append("");

while (!oRecordSet.EoF)
{
    htmlTable.Append("");
    htmlTable.Append("");
    htmlTable.Append("");
    htmlTable.Append("");
    htmlTable.Append("");

    oRecordSet.MoveNext();
}

htmlTable.Append("
Transaction ID
Doc. No
Doc. Date
" + oRecordSet.Fields.Item("TransId").Value.ToString() + "
" + oRecordSet.Fields.Item("DocNum").Value.ToString() + "
" + oRecordSet.Fields.Item("DocDate").Value.ToString() + "
");

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("");
            htmlTable.Append("");
            htmlTable.Append("");
            htmlTable.Append("");
            while (!oRecordSet.EoF)
            {
                htmlTable.Append("");
                htmlTable.Append("");
                htmlTable.Append("");
                htmlTable.Append("");
                htmlTable.Append("");

                oRecordSet.MoveNext();
            }
            htmlTable.Append("
Transaction ID
Doc. No
Doc. Date
" + oRecordSet.Fields.Item("TransId").Value.ToString() + "
" + oRecordSet.Fields.Item("DocNum").Value.ToString() + "
" + oRecordSet.Fields.Item("DocDate").Value.ToString() + "
");

Thanks

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 ?

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

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 = "[email protected];[email protected]";
var fromAddress = new MailAddress("[email protected]", "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!