How to insert database value or string in mail body

Question

I want to insert database content in Email body, index 0 of data reader which contain id number for each email address, I want to send mail using add id number in mail body for instance: The Employee id is 5. I need insert each id on each mail body which exist in same row. I have used this code to insert id in body but that didn't work

message.Body = "The Employee id is 5  "+reader[1].ToString();

Full code is:

MailMessage message = new MailMessage();
message.Subject = "Employee Access ";

message.From = new MailAddress("avvv@gmail.com");
var fromAddress = "avvv@gmail.com";
const string fromPassword="password";
var smtp = new System.Net.Mail.SmtpClient();
{
    smtp.Host = "smtp.gmail.com";
    smtp.EnableSsl = true;
    smtp.DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.Network;
    smtp.Credentials = new NetworkCredential(fromAddress, fromPassword);
    smtp.Timeout = 20000;


    OleDbCommand cmd = null;
    OleDbCommand cmd2 = null;

    string queryString = "select id,email,status from tableemail";
    using (OleDbConnection connection = new OleDbConnection("Provider = OraOLEDB.Oracle.1; Data Source = xe;
    Password=654321;User ID = xpress; unicode=true"))
    {
        OleDbCommand command = new OleDbCommand(queryString, connection);
        connection.Open();
        cmd = new OleDbCommand(queryString);
        cmd.Connection = connection;
        OleDbDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            MailAddress to = new MailAddress(reader[1].ToString());
            message.To.Add(to);   
        }

        message.Body = "The Employee id is 5  "+reader[1].ToString();
        smtp.Send(message);
        reader.Close();
    }
}

Can I insert multiple database field in body using data reader?

if performance is not a issue and data is not big. you can use datatable and then you can easily iterate through it and send mail — Ameya Deshpande, Oct 29 '18 at 07:26

Ameya Deshpande · Answer 1 · 2018-10-29T08:10:01.867

0

I am not sure exactly how your code needed this functionality however from my understanding you can do it something like this. using DataTable

    DataTable dataTable = new DataTable();
    MailMessage message = new MailMessage();
    message.Subject = "Employee Access ";

    message.From = new MailAddress("avvv@gmail.com");
    var fromAddress = "avvv@gmail.com";
    const string fromPassword = "password";


    OleDbCommand cmd = null;

    string queryString = "select id,email,status from tableemail";
    using (OleDbConnection connection = new OleDbConnection("Provider = OraOLEDB.Oracle.1; Data Source = xe; Password = 654321; User ID = xpress; unicode = true"))
    {
        OleDbCommand command = new OleDbCommand(queryString, connection);
        connection.Open();
        cmd = new OleDbCommand(queryString);
        cmd.Connection = connection;
        OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
        adapter.Fill(dataTable);
        adapter.Dispose();
    }

    foreach (DataRow dataRow in dataTable.Rows)
    {
        var smtp = new System.Net.Mail.SmtpClient();
        {
            smtp.Host = "smtp.gmail.com";
            smtp.EnableSsl = true;
            smtp.DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.Network;
            smtp.Credentials = new NetworkCredential(fromAddress, fromPassword);
            smtp.Timeout = 20000;

            MailAddress to = new MailAddress(dataRow[1].ToString());

            if (!message.To.Contains(to))
            {
                    message.To.Add(to);
            }

            message.Body = "The Employee id is 5  " + dataRow[0].ToString();
            smtp.Send(message);
        }
    }

edited Oct 29 '18 at 08:10

answered Oct 29 '18 at 07:38

Ameya Deshpande

3,580
4
30
46

the mail sent multiple time to user, i need user only one time receive mail for one execution – moonir01 Oct 29 '18 at 07:56
@moonir01 you want to send mail to single mail id with different content ? – Ameya Deshpande Oct 29 '18 at 07:57
yes i want to send mail to single mail id with different content . – moonir01 Oct 29 '18 at 08:00
@moonir01 can you show me some dummy data. what you are getting from database and what you want to send – Ameya Deshpande Oct 29 '18 at 08:02
ID EMAIL ---------- -------------------------------------------------- 1 abc1@outlook.com 2 abc2@gmail.com 3 abc3@yahoo.com – moonir01 Oct 29 '18 at 08:08
@moonir01 please rephrase your question. – Ameya Deshpande Oct 29 '18 at 08:09
may email table contain 2 field , one is id and another email i want to send e mail insert her id on body – moonir01 Oct 29 '18 at 08:11
last row id count using the code, same row id required , – moonir01 Oct 29 '18 at 09:40

score 0 · Answer 2 · answered Oct 29 '18 at 14:58

I think the issue is you are calling the DataReader after it has read the last record. Reader.Read() returned a false, meaning reader[1] should throw an error since there is nothing to read.

I don't know if you wanted every value in the body of the email or just the last one... assuming you wanted every one, something like this should work:

using (OleDbConnection connection = new OleDbConnection(cs))
{
    connection.Open();

    using (OleDbCommand cmd = new OleDbCommand(queryString, connection))
    {
        using (OleDbDataReader reader = cmd.ExecuteReader())
        {
            StringBuilder body = new StringBuilder();

            while (reader.Read())
            {
                string to = reader.GetString(0);
                message.To.Add(to);
                body.AppendFormat("The Employee id is 5 {0}<br>", to);
            }

            message.Body = body.ToString();
            reader.Close();
        }
    }
}
smtp.Send(message);

If you really only want the last one (as your original code implies), I would just save it in a local string within the loop and then add it after the reader loop.

On an unrelated matter, you are instantiating your OLE Command object twice. I eliminated the duplicate in the code snippet above.

How to insert database value or string in mail body

2 Answers2