We help IT Professionals succeed at work.
Get Started
When I run a console application it does not return data from a stored procedure
I have a console application that when ran gets the zip code from a database table returns it to the application then with the zip code stored in the application it calls a second stored procedure that uses the zip code as a parameter for that stored procedure which builds an email. The email is sent out to people that are with in a 20 mile radius of the zip code. Below is my code and stored procedures.
Console app code
SQL Stored Proc to get data for the email - builds the email
Please help!
Console app code
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);
}
}
}
}
ALTER PROCEDURE [dbo].[sp_Auto_ZipCode]
AS
BEGIN
SET NOCOUNT ON;
SELECT A_ZIP_CD
FROM ASRD_AUTO_EMAIL
END
SQL Stored Proc to get data for the email - builds the email
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
Please help!
Why Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE