if ((DateTime.Now.DayOfWeek== DayOfWeek.Saturday) &&(DateTime.Now.Hour==19) &&(DateTime.Now.Minute==15))
{
exportsqldatatoexcle();
emailattachment();
}
}
public void exportsqldatatoexcle()
{
string con1 = "Data Source=ADMIN\\SQLEXPRESS;Initial Catalog=PhysioCure; Integrated Security=true";
SqlConnection connection = new SqlConnection(con1);
connection.Open();
sda = new SqlDataAdapter("select PationName,RegistrationDate,ContactNo,Age,Sex,Chief_Complain,Investigation_Result,PastHistoryAny,Physical_Examination,Medications,Prognosis,Electro_Therapy,Neuro_Rehabilitation,Ortho_Rehabilitation,Cardio_Pulmonery_Rehabilitation,Sports_Rehabilitation from Physio_cureTable order by RegistrationDate desc", con1);
dt = new DataTable();
sda.Fill(dt);
DataColumnCollection dccollection = dt.Columns;
Microsoft.Office.Interop.Excel.ApplicationClass excelapp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelapp.Application.Workbooks.Add(Type.Missing);
for (int i = 1; i < dt.Rows.Count + 1; i++)
{
for (int j = 1; j < dt.Columns.Count + 1; j++)
{
if (i == 1)
{
excelapp.Cells[i, j] = dccollection[j - 1].ToString();
}
else
{
excelapp.Cells[i, j] = dt.Rows[i - 1][j - 1].ToString();
}
}
}
excelapp.ActiveWorkbook.SaveCopyAs("E:\\Winform n console\\PhysioCure\\PhysioCure\\PhysioCure\\Patient_Details.xls");
excelapp.ActiveWorkbook.Saved = true;
excelapp.Quit();
MessageBox.Show("Detail file successfully create!");
}
private void emailattachment()
{
//try
//{
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
mail.From = new MailAddress("My email id");
mail.To.Add("My frnd email id");
mail.Subject = "Sending you Patient_Details File - 1";
mail.Body = "Please Check Mail With Attachment";
System.Net.Mail.Attachment attachment;
attachment = new Attachment("E:\\Winform n console\\PhysioCure\\PhysioCure\\PhysioCure\\Patient_Details.xls");
mail.Attachments.Add(attachment);
SmtpServer.Port = 587;
SmtpServer.Credentials = new System.Net.NetworkCredential("My email id", "Password");
SmtpServer.EnableSsl = true;
SmtpServer.Send(mail);
MessageBox.Show("Details File send on your mail please check your mail");
Loading
Jignesh TrivediPosted Jan 21, 2013, 12:12 AM
To send mail to perticular date and time you may use SQL JOB or windows scheduler with console application.
please refer below link for Send Email from SQL Server
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/
and for create SQL job please refer
http://msdn.microsoft.com/en-us/library/ms190268%28v=sql.100%29.aspx
hope this will help you.