Display SQL result in E-mail with C#

Question

I hope someone can help me...
Here is my problem:

So far I can generate a sql query result in a web datagrid with C#, so the grid in browser looks nice.
I need to somehow put the sql query result in email and send it with c# in my program
The program (or I should say one asp page) would be scheduled to run everyday. If sql query result exists, the program would send the email, if not, no email would be sent.

I have no idea how to put the sql query in the body of the email with c#.
I also have no idea how to check if the sql query result exists or not to determine send e-mail or not

I search online for whole day still cannot get related information.....

thanks!

Answer 1

Hi LadyCarol,

If you are using a SqlDataReader you can use SqlDataReader.HasRows to determine if the query returned any rows. You can also obtain the SQL query itself by using the CommandText property from your SqlCommandObject.

Sending mail is very easy - just look at System.Net.Mail

Answer 2

Hi
if i understand your question correctly.these are the steps to send the mail, with the record set as the body content

1.Convert the SQl query into XML as

SELECT 
1			as	Tag,
NULL			as	Parent,
s.stor_id		as	[store!1!Id],
s.stor_name 	        as      [store!1!Name],
NULL			as	[sale!2!OrderNo],
NULL			as	[sale!2!Qty]
FROM stores s
FOR XML EXPLICIT

2.Once you have the XML convert to xslt

3.Now send this style sheet  as an attachment in mail using the sql mail send.refer the links for
sending the mail

---

Don't forget to click "Mark as Answer" on the post that helped you. This credits that member, earns you a point and marks your thread as Resolved so everyone will know you have been helped.

Answer 3

Get your result set into a dataset. If the result set size is greater than 0, then serialize that dataset to an XML file ( DataSet.WriteXml() ) and attach it to your email. If you have permission issues with writing files out use isolated storage to hold your xml file.


if (DataSet.Tables["yourTable"].Rows.Count > 0 )
{
    // call methods to serialize, write out and attach to email
}

You can do the same with just a DataTable if you want.

Answer 4

Using

SmtpClient smtp = new SmtpClient();
                    string from = "[e-mail]";
                    string to = "[e-mail]";
                    string subject = "title";
                    string body = "";

make some table in sql to keep all sql query results.

<pre lang="x-c#">using System.Net.Mail;
.
.
.
SqlDataAdapter thisAdapter = new SqlDataAdapter("select sqlerros from erros", conn);
 DataSet DataSet = new DataSet(); thisAdapter.Fill(DataSet, "erros");
 foreach (DataRow Row in DataSet.Tables["erros"].Rows)
 {
 body = body + " " + Row["sqlerros"]; 
}

then make smtp credentials and send:

smtp.Host = "smtp.mail.yahoo.com.br";
                    smtp.Credentials = new NetworkCredential("[e-mail]", "password");
                    smtp.Send(from, to, subject, body);

done.
if have any questions about it tell me i can explain more clearily.

Answer 5

Using

SmtpClient smtp = new SmtpClient();

                    string from = "[e-mail]";

                    string to = "[e-mail]";

                    string subject = "title";

                    string body = "";

make some table in sql to keep all sql query results.

<pre lang="x-c#">using System.Net.Mail;

.

.

.

SqlDataAdapter thisAdapter = new SqlDataAdapter("select sqlerros from erros", conn);

 DataSet DataSet = new DataSet(); thisAdapter.Fill(DataSet, "erros");

 foreach (DataRow Row in DataSet.Tables["erros"].Rows)

 {

 body = body + " " + Row["sqlerros"]; 

}

then make smtp credentials and send:

smtp.Host = "smtp.mail.yahoo.com.br";

                    smtp.Credentials = new NetworkCredential("[e-mail]", "password");

                    smtp.Send(from, to, subject, body);

done.
if have any questions about it tell me i can explain more clearily.

It looks great and I am trying it! However, my visual studio at office is 2003, seem like System.Net.Mail doesn't exsit, what can I do?

Answer 6

I find out

using System.Web.Mail;

private void Page_Load(object sender, System.EventArgs e)
{
 MailMessage mail = new MailMessage();
 mail.To = "[e-mail]";
 mail.From = "[e-mail]";
 mail.Subject = "this is a test email.";
 mail.Body = "Some text goes here";
 mail.Fields.Add("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate", "1"); //basic authentication
 mail.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendusername", "my_username_here"); //set your username here
 mail.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendpassword", "super_secret"); //set your password here

 SmtpMail.SmtpServer = "mail.mycompany.com";  //your real server goes here
 SmtpMail.Send( mail );
}

This works! I try to combine with the codes you provide to "loop" my sql result

Answer 7

Using

SmtpClient smtp = new SmtpClient();



                    string from = "[e-mail]";



                    string to = "[e-mail]";



                    string subject = "title";



                    string body = "";

make some table in sql to keep all sql query results.

<pre lang="x-c#">using System.Net.Mail;



.



.



.



SqlDataAdapter thisAdapter = new SqlDataAdapter("select sqlerros from erros", conn);



 DataSet DataSet = new DataSet(); thisAdapter.Fill(DataSet, "erros");



 foreach (DataRow Row in DataSet.Tables["erros"].Rows)



 {



 body = body + " " + Row["sqlerros"]; 



}

then make smtp credentials and send:

smtp.Host = "smtp.mail.yahoo.com.br";



                    smtp.Credentials = new NetworkCredential("[e-mail]", "password");



                    smtp.Send(from, to, subject, body);

done.
if have any questions about it tell me i can explain more clearily.

It looks great and I am trying it! However, my visual studio at office is 2003, seem like System.Net.Mail doesn't exsit, what can I do?

foreach (DataRow Row in DataSet.Tables["erros"].Rows)

{

body = body + " " + Row["sqlerros"];

}

gives this error:
System.Web.Mail.MailMessage.body is inaccesible due to its protention level

Answer 8

in this code Lady, u searching in you database on the Table Erros, the column sqlerros. if u havnt this table and this column u can do this.
but this foreach only building the Body u can only write something.

body = "erro"; 


show me your all metod code then i can helpyou better.