namespace AutoEmail
{
class Program
{
#region Globals
string _zip = string.Empty;
string _miles = string.Empty;
string _aFName = string.Empty;
string _aLName = string.Empty;
string _aAddress = string.Empty;
string _aCity = string.Empty;
string _aState = string.Empty;
string _aZip = string.Empty;
string _aNumber = string.Empty;
string _aEmail = string.Empty;
string _cFName = string.Empty;
string _cLName = string.Empty;
string _cAddress = string.Empty;
string _cCity = string.Empty;
string _cState = string.Empty;
string _cZip = string.Empty;
string _cHomePhone = string.Empty;
string _cAltPhone = string.Empty;
string _cEmail = string.Empty;
string _cPrefContact = string.Empty;
string _cTravelTime = string.Empty;
string _cTravelDist = string.Empty;
string _cAddedDate = string.Empty;
string _cZipCode = string.Empty;
string _aNumber1 = string.Empty;
#endregion
static void Main(string[] args)
{
GetZip();
SendEmail();
}
public static void GetZip()
{
#region Connection
var lspAutoZip = new SqlConnection("Data Source=DataseSourceHere\\DatabaseSource; Initial Catalog=Database; User ID=DatabaseIDHere; Password=DatabasePasswordHere");
var command = new SqlCommand("sp_Auto_ZipCode", lspAutoZip);
#endregion
#region Globals
string _zipCode = string.Empty;
#endregion
try
{
command.CommandType = CommandType.StoredProcedure;
var dt = new DataTable();
lspAutoAgentZip.Open();
using (var sda = new SqlDataAdapter(command))
sda.Fill(dt);
DataSet dataSet = new DataSet();
// Create the dictionary to hold the email data for each individual email. This allows us
// to group all of the books due for an individual together. We will use the email address
// as the key for the dictionary instead of CustomerID in case the user has given us two
// different email addresses.
Dictionary<string, List<DataRow>> List = new Dictionary<string, List<DataRow>>();
// Iterate over the dataset and populate the dictionary
foreach (DataRow row in dt.Rows)
{
_zipCode = Convert.ToString(row["A_ZIP_CD"]);
if (!List.ContainsKey(_zipCode))
{
List.Add(_zipCode, new List<DataRow>());
}
//add the data row for the email
List[_zipCode].Add(row);
}
}
catch { }
}
public static void SendEmail()
{
#region Connection
var lspAutoEmail = new SqlConnection("Data Source=DatabaseSourceHere\\DatabaseSource; Initial Catalog=Database; User ID=DatabaseIDHere; Password=DatabasePasswordHere");
var command = new SqlCommand("sp_Auto_ZipCode", lspAutoEmail);
var commandsql = new SqlCommand("sp_Auto_Email", lspAutoEmail);
#endregion
#region Globals
string _aFName = string.Empty;
string _aLName = string.Empty;
string _aAddress = string.Empty;
string _aCity = string.Empty;
string _aState = string.Empty;
string _aZip = string.Empty;
string _aNumber = string.Empty;
string _aEmail = string.Empty;
string _cFName = string.Empty;
string _cLName = string.Empty;
string _cAddress = string.Empty;
string _cCity = string.Empty;
string _cState = string.Empty;
string _cZip = string.Empty;
string _cHomePhone = string.Empty;
string _cAltPhone = string.Empty;
string _cEmail = string.Empty;
string _cPrefContact = string.Empty;
string _cTravelTime = string.Empty;
string _cTravelDist = string.Empty;
//DateTime _canidateAddedDate;
//DateTime _canidateAddedDate1;
string _canidateAddedDate = string.Empty;
string _zipCode = string.Empty;
string _agentNumber1 = string.Empty;
#endregion
//GetZip();
commandsql.CommandType = CommandType.StoredProcedure;
//commandsql.Parameters.AddWithValue("@ZipCode", _zipCode);
var dt = new DataTable();
lspAutoAgentEmail.Open();
[b] using (var sda = new SqlDataAdapter(command))
sda.Fill(dt);[/b]
DataSet dataSet = new DataSet();
// Create the dictionary to hold the email data for each individual email. This allows us
// to group all of the books due for an individual together. We will use the email address
// as the key for the dictionary instead of CustomerID in case the user has given us two
// different email addresses.
Dictionary<string, List<DataRow>> List = new Dictionary<string, List<DataRow>>();
// Iterate over the dataset and populate the dictionary
foreach (DataRow row in dt.Rows)
{
_zipCode = Convert.ToString(row["A_ZIP_CD"]);
if (!List.ContainsKey(_zipCode))
{
List.Add(_zipCode, new List<DataRow>());
}
//add the data row for the email
List[_zipCode].Add(row);
}
foreach (string zip in List.Keys)
{
#region Connection
var lspAutoEmail = new SqlConnection("Data Source=DatabaseSourceHere\\DatabaseSource; Initial Catalog=Database; User ID=DatabaseIDHere; Password=DatabasePasswordHere");
var command = new SqlCommand("sp_Auto_ZipCode", lspAutoEmail);
var commandsql = new SqlCommand("sp_Auto_Email", lspAutoEmail);
#endregion
commandsql.CommandType = CommandType.StoredProcedure;
commandsql.Parameters.AddWithValue("@ZipCode", _zipCode);
var table = new DataTable();
using (var sda = new SqlDataAdapter(commandsql))<---------- this is where the error occurs -----
sda.Fill(table); < -------This is where the error message occurs ---------
DataSet ds = new DataSet();
Dictionary<string, List<DataRow>> emailList = new Dictionary<string, List<DataRow>>();
// Iterate over the dataset and populate the dictionary
foreach (DataRow row in table.Rows)
{
_aFName = Convert.ToString(row["A_FIRST_NM"]);
_aLName = Convert.ToString(row["A_LAST_NM"]);
_aAddress = Convert.ToString(row["A_ADDRESS_CD"]);
_aCity = Convert.ToString(row["A_CITY_CD"]);
_aState = Convert.ToString(row["A_STATE_CD"]);
_aZip = Convert.ToString(row["A_ZIP_CD"]);
_aNumber = Convert.ToString(row["A_AGENT_NBR"]);
_aEmail = Convert.ToString(row["A_EMAIL_NBR"]);
_cFName = Convert.ToString(row["FIRST_NM"]);
_cLName = Convert.ToString(row["LAST_NM"]);
_cAddress = Convert.ToString(row["ADDRESS_TXT"]);
_cCity = Convert.ToString(row["CITY"]);
_cState = Convert.ToString(row["STATE_CD"]);
_cZip = Convert.ToString(row["ZIP"]);
_cHomePhone = Convert.ToString(row["HOME_PHONE_NBR"]);
_cAltPhone = Convert.ToString(row["ALT_PHONE_NBR"]);
_cEmail = Convert.ToString(row["EMAIL"]);
_cPrefContact = Convert.ToString(row["PREF_CONTACT_METHOD_CD"]);
_cTravelTime = Convert.ToString(row["TRAVEL_TIME_MINS_NBR"]);
_cTravelDist = Convert.ToString(row["TRAVEL_DISTANCE_MLS_NBR"]);
_cAddedDate = Convert.ToString(row["ADDED_DT"]);
if (!emailList.ContainsKey(_agentNumber))
{
emailList.Add(_agentNumber, new List<DataRow>());
}
//add the data row for the email
emailList[_agentNumber].Add(row);
}
_agentNumber1 = _agentNumber;
foreach (string agentNum in emailList.Keys)
{
try
{
using (var sda = new SqlDataAdapter(commandsql))
sda.Fill(table);
var App = string.Format("http://toapplication");
var recepientEmail = _agentEmail;
// create a string builder to build up the body of the email
StringBuilder body = new StringBuilder();
body.Append("<html>");
body.Append("<body>");
body.AppendFormat("<b><i>West Central Recruitment</i></b><hr/><br/>");
// assume the first/last name will be the same for each row, so just get the
// name information from the first row to build the opening line of the email
DataRow firstRow = emailList[agentNum][0];
body.AppendFormat("<p>Hello {0} {1}: </p>", firstRow["A_FIRST_NM"].ToString(), firstRow["A_LAST_NM"].ToString());
body.AppendFormat("</p>"); ;
body.Append("<table>");
body.AppendFormat("<p>This email contains important information regarding new candidate that has been entered with in the last 30 days to the Database.</p></td></tr>");
body.AppendFormat("<tr><td>New C - was added on <b>" + _cAddedDate + "</b> New C Name - <b> " + " " + _cFName + " " + _cLName + "</b></td></tr>"); //+ <br/> was added on _cAddedDate
body.AppendFormat("<tr><td><br/>The c information is listed below:<br/><br/>New c address : <b>" + _cAddress + " </b></p></td></tr>");
body.AppendFormat("<tr><td><br/>New c city : <b>" + _cCity + " </b></p></td></tr>");
body.AppendFormat("<tr><td><br/>New c state : <b> " + _cState + " </b></p></td></tr>");
body.AppendFormat("<tr><td><br/>New c zipcode : <b> " + _cZip + " </b></p></td></tr>");
body.AppendFormat("<tr><td><br/>New c home telephone number : <b> " + _cHomePhone + " </b></p></td></tr>");
body.AppendFormat("<tr><td><br/>New c alt telephone number : <b> " + _cAltPhone + " </b></p></td></tr>");
body.AppendFormat("<tr><td><br/>New c email : <b>" + _cEmail + " </b></p></td></tr>");
body.AppendFormat("<tr><td><br/>New c prefered contact method : <b> " + _cPrefContact + " </b></p></td></tr>");
body.AppendFormat("<tr><td><br/>New c willing travel time : <b>" + _cTravelTime + " </b></p></td></tr>");
body.AppendFormat("<tr><td><br/>New c wiling travel distance : <b>" + _cTravelDist + "</b></p></td></tr>");
body.AppendFormat("<br/><tr><td> If you are interested click on the link to learn more: <b>" + App + "</b></td></tr>");
body.AppendFormat("<br/><tr><td> Thank you! </td></tr></table>");
body.Append("</body>");
body.Append("</html>");
foreach (DataRow rows in emailList[agentNum])
{
_agentFName = Convert.ToString(rows["A_FIRST_NM"]);
_agentLName = Convert.ToString(rows["A_LAST_NM"]);
_agentNumber = Convert.ToString(rows["A_AGENT_NBR"]);
}
// if (_agentFName != "")
{
//if (_agentLName != "")
{
//if (_agentLName != "")
{
SendHtmlFormattedEmail(agentNum, body.ToString());
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("ERROR MESSAGE: " + ex.Message);
lspAutoAgentEmail.Close();
}
}
}
}
public static void SendHtmlFormattedEmail(string recepientEmail, string body)
{
using (MailMessage mail = new MailMessage())
{
//initialize the entry for email
mail.From = new MailAddress("CompanyA@companyA.com");
mail.Subject = "****ATTENTION**** A new c has been added!****ATTENTION****";
mail.IsBodyHtml = true;
mail.To.Add(new MailAddress(recepientEmail));
mail.BodyEncoding = System.Text.Encoding.UTF32;
mail.Body = body;
new SmtpClient();
new MailMessage();
var smtp = new SmtpClient { Host = "HostServiceHere", Port = PortNumberHere };
smtp.Send(mail);
}
}
}
}
SQL Stored Proc Zip Code - gets the zip codeALTER PROCEDURE [dbo].[sp_Auto_ZipCode]
AS
BEGIN
SET NOCOUNT ON;
SELECT A_ZIP_CD
FROM ASRD_AUTO_EMAIL
END
ALTER PROCEDURE [dbo].[sp_Auto_Email]
(
@ZipCode NUMERIC(5, 0) = Null
)
AS
BEGIN
DECLARE @Latitude FLOAT(10)
DECLARE @Longitude FLOAT(10)
SET NOCOUNT ON;
---GET LAT/LONG FROM ZIP CODE table
SELECT @Latitude = S.Latitude, @Longitude = S.Longitude FROM wcr_ci.dbo.[State]S WHERE S.Zip = @ZipCode
SELECT S.Zip,
--S.Zip_Name,
3443.917*ACOS(COS(RADIANS(90-(@Latitude)))*COS(RADIANS(90-(Latitude)))+SIN(RADIANS(90-(@Latitude)))*SIN(RADIANS(90-(Latitude)))
*COS(RADIANS((@Longitude-Longitude)))) AS Miles,
AAE.A_FIRST_NM,
AAE.A_LAST_NM,
AAE.A_ADDRESS_CD,
AAE.A_CITY_CD,
AAE.A_STATE_CD,
AAE.A_ZIP_CD,
AAE.A_AGENT_NBR,
AAE.A_EMAIL_NBR,
AC.FIRST_NM,
AC.LAST_NM,
AC.ADDRESS_TXT,
AC.CITY,
AC.STATE_CD,
AC.ZIP,
STUFF(STUFF(STUFF(AC.HOME_PHONE_NBR,1,0,' ('),6,0,') '),11,0,'-') AS 'HOME_PHONE_NBR',
STUFF(STUFF(STUFF(AC.ALT_PHONE_NBR,1,0,' ('),6,0,') '),11,0,'-') AS 'ALT_PHONE_NBR',
AC.EMAIL,
AC.PREF_CONTACT_METHOD_CD,
AC.TRAVEL_TIME_MINS_NBR,
AC.TRAVEL_DISTANCE_MLS_NBR,
AC.ADDED_DT
FROM wcr_ci.dbo.[State] S
JOIN ASRD_AUTO_EMAIL AAE ON S.Zip = AAE.A_ZIP_CD
JOIN ASRD_C AC ON S.Zip = AC.ZIP
WHERE 3443.917*ACOS(COS(RADIANS(90-(@Latitude)))*COS(RADIANS(90-(Latitude)))+SIN(RADIANS(90-(@Latitude)))*SIN(RADIANS(90-(Latitude)))
*COS(RADIANS((@Longitude-Longitude)))) <= '20'
AND AC.ADDED_DT >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-180,0)
AND AAE.A_ZIP_CD = @ZipCode
ORDER BY miles
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.