How do you pass values from a datareader in a while loop to a method in C#

Question

I have 3 tables in a database that I am iterating through using a SQL statement. It searches for applications that need to have contracts renewed. I use SQL date math to check whether managers need to be notified of a contract renewal. If today's date = the date in the notificationDate field, the console app should send an email to the person listed as the analyst/manager of that application. Here is my code so far:

namespace ContractApp
{
    class Program
    {
        //initializes strings for storing information from the table
        static string aretheyManager;
        static string listedanalystEmail;
        static string listedmanagerEmail;

    static void Main(string[] args)
    {
        int warningWindow = 10;

        try
        {
            //connects to the AppInfo_dev table in the database
            SqlConnection conn = new SqlConnection("server=10.52.2.169\\sqlcluster,1206;uid=TannerAppsWriter;pwd=TannerAppsWriter;database=AppInfo_dev;");
            conn.Open();

            //sets up a sequal command called selectedValues
            SqlCommand selectValues = conn.CreateCommand();

            //Pulls information from three tables in the database (AppInfo_dev, SoftwareApp, IT_Personnel)
            //Takes the AppID from the Contracts list and compares it to AppID in the Apps list and displays matches
            //Then it finds employee information related to the Primary Analyst that is listed for that application
            //Adds a field called "notificationDate" that is filled by subtracting the "warningWindow" and "TerminationWindow" from the RenewalDate
            //Finds contracts listed that have a "notificationDate" that is the same as the current date
            //Takes the eMail fields and appends "@tanner.org" to the end of the text in the field
            selectValues.CommandText = "My SQL statement goes here...it works so I didn't bother posting it since it is really long"

            //Reads values in specified columns in the database
            using (SqlDataReader dataReader = selectValues.ExecuteReader())
            {
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        //Converts the values in the tables to strings
                        aretheyManager = Convert.ToString(dataReader["isManager"]);
                        listedanalystEmail = Convert.ToString(dataReader["analystEmail"]);
                        listedmanagerEmail = Convert.ToString(dataReader["managerEmail"]);
                    }
                }
            }
        }
        //If there is an error, catch it
        catch (SqlException e)
        {
        Console.WriteLine(e.Message);
        }
    }

    private void sendEmailNotification()
    {


        //Create an email to send notifying of contract termination
        MailMessage message = new MailMessage();

        //Check to see if the listed analyst is a manager
        //If they are, send the email to them
        //If they are not, send they email to their manager.
        if (aretheyManager == "True")
        {
            message.To.Add(listedanalystEmail);
        }
        else
        {
            message.To.Add(listedmanagerEmail);
        }

        message.Subject = "This contract requires your attention!";
        message.From = new MailAddress("no response email address goes here");
        message.Body = "There is an application contract that is in need of renewal.";

        message.Priority = MailPriority.Normal;

        SmtpClient client = new SmtpClient("client info goes here");
        client.Send(message);
    }
}

}

The SQL statements works as expected. It iterates through the rows in the table and pulls contracts with a notificationDate = the current date. I am having trouble with the datareader. It iterates through the contracts pulled by the SQL statement, but only stores the last value it reads into the strings. I need it to store any and all values it pulls so an email gets sent to each person if there are multiple people that need to be notified.

If there is a thread on this site that you believe to be an answer to my question please let me know. I search google and this site for close to an hour and have not found anything that has worked yet so that is why I posted. — user2433662
– user2433662, Commented Sep 10, 2013 at 14:47
You don't have to close or dispose of dataReader, the using {} block takes care of that. You also don't need the if...HasRows block. If there aren't any rows, the while {} block will just jump to the end of the block. — LarsTech
– LarsTech, Commented Sep 10, 2013 at 14:48
Thank you for your comment. I have taken out the close and dispose lines. — user2433662
– user2433662, Commented Sep 10, 2013 at 14:50
You should use/consume the IDatareader AS QUICKLY AS POSSIBLE. Do not "hold onto it". As others have stated, put the values in a List<string> or other collection. Unless you have a billion rows, then you'll need to rethink it. — granadaCoder
– granadaCoder, Commented Sep 10, 2013 at 14:53
Thanks everyone for their assistance (suggestions/comments). I see that most of you suggested I just a list which was an idea that I had at one point until I read about passing the parameters. Again, thanks everyone for your help! — user2433662
– user2433662, Commented Sep 10, 2013 at 15:15

Greg · Accepted Answer · 2013-09-10 14:50:45Z

2

A datareader is not designed to hold data. It just iterates over data. If you want to store the results of the "read", add the data into an ArrayList or some other data structure that will allow you to perform further work on the data.

 con.Open();

    ArrayList al = new ArrayList();
    SqlDataReader dr = cmd.ExecuteReader();

    while(dr.Read()) {
        object[] values = new object[dr.FieldCount];
        dr.GetValues(values);
        al.Add(values);
    }

    dr.Close();
    con.Close();

answered Sep 10, 2013 at 14:50

Greg

2,6783 gold badges38 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

granadaCoder Over a year ago

I'd go with a modern "generic" instead of ArrayList (if on FW 2.0 or above.) List<MyDtoObject>

Anand · Accepted Answer · 2013-09-10 14:54:47Z

2

You are looping over dataSet but storing the values in string rather than some kind of list. That's why only last value is stored

  //create a class to hold the value
class SomeDTO
{
   public string aretheyManager;
   public string listedanalystEmail;
   public string listedmanagerEmail;
}
  //in your main
 //Reads values in specified columns in the database
            List<SomeDTO> collection = new List<SomeDTO>();
            using (SqlDataReader dataReader = selectValues.ExecuteReader())
            {
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                       SomeDTO obj = new SomeDTO();
                        //Converts the values in the tables to strings
                        obj.aretheyManager = Convert.ToString(dataReader["isManager"]);
                        obj.listedanalystEmail = Convert.ToString(dataReader["analystEmail"]);
                        obj.listedmanagerEmail = Convert.ToString(dataReader["managerEmail"]);

                       collection.Add(obj);

                    }
                }
                dataReader.Close();
                dataReader.Dispose();
            }


//send email notification method
private void sendEmailNotification(List<SomeDTO> obj)
{
     //loop and send email
}

edited Sep 10, 2013 at 14:54

answered Sep 10, 2013 at 14:49

Anand

15k8 gold badges35 silver badges44 bronze badges

2 Comments

user2433662 Over a year ago

From what I have read, it can be done this way as long as I pass the values into the sendEmailNotification() method as it is iterating through. I am just having trouble passing the values into the method.

Anand Over a year ago

Yes, you could change the signature of your SendEmailNotification and pass the value directly or use the signature that i have written

Steve · Accepted Answer · 2013-09-10 14:57:55Z

First of all change your SendMail method to be more generic.
Receive parameters with the info on the people that should receive a mail

private void sendEmailNotification(string aretheyManager, 
                                  string listedanalystEmail, 
                                  string listedmanagerEmail)
{
    //Create an email to send notifying of contract termination
    MailMessage message = new MailMessage();

    //Check to see if the listed analyst is a manager
    //If they are, send the email to them
    //If they are not, send they email to their manager.
    if (aretheyManager == "True")
    {
        message.To.Add(listedanalystEmail);
    }
    else
    {
        message.To.Add(listedmanagerEmail);
    }

    message.Subject = "This contract requires your attention!";
    message.From = new MailAddress("no response email address goes here");
    message.Body = "There is an application contract that is in need of renewal.";

    message.Priority = MailPriority.Normal;

    SmtpClient client = new SmtpClient("client info goes here");
    client.Send(message);
}

Now in your loop while you read the data from the reader call the above method passing the appropriate values

    if (dataReader.HasRows)
    {
        while (dataReader.Read())
        {
            //Converts the values in the tables to strings
            aretheyManager = Convert.ToString(dataReader["isManager"]);
            listedanalystEmail = Convert.ToString(dataReader["analystEmail"]);
            listedmanagerEmail = Convert.ToString(dataReader["managerEmail"]);

            // For every record read, send the email
            sendEmailNotification(aretheyManager, 
                                  listedanalistEmail, 
                                  listedmanagerEmail)                        
        }
    }

Of course you could also store the retrieved values from the DataReader in some form of object collection (a List<Email> where EMail is a class that contains your three parameters) but this will lead to a double loop (one to read and one to send the emails) so, if it is not really required to store in memory all those email addresses, I suggest to send the mail while you read from your reader.

paul · Accepted Answer · 2013-09-10 14:56:22Z

0

One solution would be to create a Person class

public class Person
{
    public bool IsManager { get; set; }
    public string AnalystEmail { get; set; }
    public string ManagerEmail { get; set; }
}

Then in your Program class you'd need to declare a List<Person> _personList = new List<Person>();

Finally, in your while loop:

while (dataReader.Read())
{
    _personList.Add(new Person {
        IsManager = Convert.ToBool(dataReader["isManager"]),
        AnalystEmail = Convert.ToString(dataReader["analystEmail"]),
        ManagerEmail = Convert.ToString(dataReader["managerEmail"])
    });
}

After that, you could use foreach(var person in _personList) or the like for your emailing.

answered Sep 10, 2013 at 14:56

paul

22.1k1 gold badge56 silver badges54 bronze badges

Comments

adrianm · Accepted Answer · 2013-09-10 14:56:45Z

0

       using (var reader = selectValues.ExecuteReader()) {
           return reader.Cast<IDataRecord>()
                        .Select(record => new SomeDTO {
                                  aretheyManager = Convert.ToString(record["isManager"]),
                                  listedanalystEmail = Convert.ToString(record["analystEmail"]),
                                  listedmanagerEmail = Convert.ToString(record["managerEmail"])
                               })
                        .ToList();
       }

answered Sep 10, 2013 at 14:56

adrianm

14.8k6 gold badges60 silver badges110 bronze badges

Comments

Suraj Singh · Accepted Answer · 2013-09-10 15:19:24Z

no matter how manytimes you iterate over these your values will be updated each time and you will get only last updated values . because for each iteration you are assigning new values to your strings.

               while (dataReader.Read())
                {
                    //Converts the values in the tables to strings
                    aretheyManager = Convert.ToString(dataReader["isManager"]);
                    listedanalystEmail = Convert.ToString(dataReader["analystEmail"]);
                    listedmanagerEmail = Convert.ToString(dataReader["managerEmail"]);
                }

I think you should use some kind of collection for storing your values .

          List<string> aretheyManager = new List<string>(); 
          List<string> listedanalystEmail =new List<string>();  
         List<string> listedmanagerEmail = new List<string>();  


        while (dataReader.Read())
                {
                //Converts the values in the tables to strings
               aretheyManager.Add(Convert.ToString(dataReader["isManager"]));
               listedanalystEmail.Add( Convert.ToString(dataReader["analystEmail"]));
               listedmanagerEmail.Add(Convert.ToString(dataReader["managerEmail"]));
                }

SteveB · Accepted Answer · 2013-09-10 14:53:52Z

-1

Create a struct to hold all of your details and then create a List<yourstruct>.

Populate your struct within your loop.

When you drop out of your loop call your email method and pass the list.

In your email method foreach your list sending an email each time

answered Sep 10, 2013 at 14:53

SteveB

1,5141 gold badge13 silver badges22 bronze badges

2 Comments

Servy Over a year ago

An object like this should probably be a class not a struct, given that it doesn't logically represent a single value, the size of the object isn't going to be really small, value semantics aren't important for performance reasons, etc.

SteveB Over a year ago

Yep. My bad. I still thinks of structs as being stored on the stack and not the heap although I have learned this is not the case.

Collectives™ on Stack Overflow

How do you pass values from a datareader in a while loop to a method in C#

7 Answers 7

1 Comment

2 Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

2 Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related