We help IT Professionals succeed at work.
Get Started

When I run a console application it does not return data from a stored procedure

78 Views
Last Modified: 2016-02-01
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
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);
            }
        }
    }
}

Open in new window

SQL Stored Proc Zip Code - gets the zip code
ALTER PROCEDURE [dbo].[sp_Auto_ZipCode]
	
AS
BEGIN
	
	SET NOCOUNT ON;

    SELECT A_ZIP_CD
	FROM ASRD_AUTO_EMAIL 
END

Open in new window


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

Open in new window


Please help!
Comment
Watch Question
Software Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 19 Comments.
See Answer
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