Harishteotia blogs: 8/31/08

-------------------Scheduler to Send mail---------------------

1. create a console Application using VS. Write Code in Code file as mentioned

Program.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Net.Mail;
namespace DFIDSchedular
{
class Program
{
static void Main(string[] args)
{
// get all the contract whose end date is after 15 days
GetContractsDetails(16);
// get all the contract whose end date is after 7 days
GetContractsDetails(8);
// get all the contract whose end date is after 3 days
GetContractsDetails(4);
// get all the contract whose end date today
UpdateContractsStatus();
}
private static void UpdateContractsStatus()
{
SqlConnection myConn=null;
try
{
SqlCommand cmd;
myConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
myConn.Open();
cmd = new SqlCommand("UpdateContractStatus", myConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{ }
finally
{
myConn.Close();
}
}
private static void GetContractsDetails(int Days)
{
SqlConnection myConn;
SqlDataAdapter myAdap;
SqlDataAdapter myAdap1;
DataSet myDataSet;
DataSet myDataSet1;
SqlCommand cmd;
SqlCommand cmd1;
string body = string.Empty;
string MailTo = string.Empty;
//get the connection string from the database
myConn = new SqlConnection(ConfigurationManager.AppSettings["DFIDConnectionString"]);
cmd = new SqlCommand("GetContracts", myConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@NoOfDays", SqlDbType.Int);
cmd.Parameters[0].Value = Days;
myAdap = new SqlDataAdapter(cmd);
myDataSet = new DataSet();
myAdap.Fill(myDataSet);

//To see information
FileInfo t = new FileInfo("c:\\Collin.txt");
StreamWriter Tex = t.CreateText();
Tex.WriteLine("MailTo");
Tex.WriteLine("AAAAAAA");
Tex.WriteLine("body");
Tex.Write(Tex.NewLine);
Tex.Close();
Console.WriteLine(" The Text file named Collin is created ");
//end

for (int iCount = 0; iCount < contractcode="string.Empty;" contracttitle="string.Empty;" enddate="string.Empty;" contractofficer="string.Empty;" contractcode="myDataSet.Tables[0].Rows[iCount][" contracttitle="myDataSet.Tables[0].Rows[iCount][" enddate="myDataSet.Tables[0].Rows[iCount][" cmd1 =" new" commandtype =" CommandType.StoredProcedure;" value =" myDataSet.Tables[0].Rows[iCount][" myadap1 =" new" mydataset1 =" new" mailto1 =" new"> 0)
{
if (myDataSet1.Tables[0].Rows[0]["Email_Id"].ToString()!= "not-applicable")
{
MailTo = myDataSet1.Tables[0].Rows[0]["Email_Id"].ToString() + "@" + ConfigurationManager.AppSettings["Email"].ToString();
}
}
if (myDataSet1.Tables[1].Rows.Count > 0)
{
if (myDataSet1.Tables[1].Rows[0]["Email_Id"].ToString()!= "not-applicable")
{
MailTo = MailTo + "," + myDataSet1.Tables[1].Rows[0]["Email_Id"].ToString() + "@" + ConfigurationManager.AppSettings["Email"].ToString();
}
}
if (myDataSet1.Tables[2].Rows.Count > 0)
{
if (myDataSet1.Tables[2].Rows[0]["Email_Id"].ToString()!= "not-applicable")
{
MailTo =MailTo+","+ myDataSet1.Tables[2].Rows[0]["Email_Id"].ToString() + "@" + ConfigurationManager.AppSettings["Email"].ToString();
}
}
if (myDataSet1.Tables[3].Rows.Count > 0)
{
if (myDataSet1.Tables[3].Rows[0]["Email_Id"].ToString()!= "not-applicable")
{
MailTo = MailTo + "," + myDataSet1.Tables[3].Rows[0]["Email_Id"].ToString() + "@" + ConfigurationManager.AppSettings["Email"].ToString();
}

}
if (myDataSet1.Tables[4].Rows.Count > 0)
{
if (myDataSet1.Tables[4].Rows[0]["Email_Id"].ToString()!= "not-applicable")
{
ContractOfficer=myDataSet1.Tables[4].Rows[0]["Name"].ToString();
MailTo = MailTo + "," + myDataSet1.Tables[4].Rows[0]["Email_Id"].ToString() + "@" + ConfigurationManager.AppSettings["Email"].ToString();

}
}
MailMessage message = new MailMessage();
message.IsBodyHtml = true;
message.From = new MailAddress(ConfigurationManager.AppSettings["EmailFrom"]);
if (MailTo != null && MailTo != "")
{
message.To.Add("harisht@enterpriseinfotech.com");
if (Days != 3)
{
message.Subject = ContractCode + " - Contract Expiry Alert";
body = "Contract Code " + ContractCode + " - " + ContractTitle + " is ending on " + EndDate + ". Should this require an extension or amendment please contact http://10.20.0.25/login.asp. " + ContractOfficer + " .";
}
else
{
message.Subject = ContractCode + " - Performance Evaluation ";
body = "Contract Code " + ContractCode + " - " + ContractTitle + " is ending on " + EndDate + ". Please complete a Performance Evaluation form on CMS http://10.20.0.25/login.asp." +
"Please contact CaPAS for further guidance if required.";
}

message.Body = body;
//FileInfo t = new FileInfo("c:\\Collin.txt");
//StreamWriter Tex = t.CreateText();
//Tex.WriteLine(MailTo);
//Tex.WriteLine(message.Subject);
//Tex.WriteLine(body);
//Tex.Write(Tex.NewLine);
//Tex.Close();
Console.WriteLine(" The Text file named Collin is created ");
SmtpClient emailClient = new SmtpClient(ConfigurationManager.AppSettings["MailServer"]);
System.Net.NetworkCredential cred = new System.Net.NetworkCredential(ConfigurationManager.AppSettings["UserName"], ConfigurationManager.AppSettings["Password"]);
emailClient.Credentials = cred;
emailClient.Send(message);
MailTo = "";
}
}
}
}
}

--------------------------App.Config File------------------------

Setting in AppSetting tag

add key="DFIDConnectionString" value="Data Source=SUMIT;Initial Catalog=cms; User ID=sa;Password=pass@word1"
add key="Email" value=" "
add key="MailServer" value=" "
add key="UserName" value="mailto:value=Harisht@gmail.com"
add key="Password" value="ramayan"
add key="EmailFrom" value="mailto:value=Harisht@gmail.com"

--------------------------------------------------------------------------------

Now Run the project this will create a .exe file in Bin-->Debug-->Sheduler.exe .

Copy this .exe file and then schedule to it in windows services to send mail alerts.

----------------------------------------------------------------------------------------

2. How to Schedule .exe in windows servvices

open program--> Accessories -->SystemTools --> Schedule Task

Now click on Add Sheduled Task.
.