c# - How to perform an action on one result at a time in a sql query return that should return multiple results?

Question

I am trying to send emails to all the addresses in a database that fit a certain description. My query will look something like this:

SELECT EmailAddress 
  FROM Customers` 
 WHERE EmailFlag = 'true'` 
   AND (Today'sDate - DateOfVisit) >= 90;

Not sure how I would do Today's Date - DateOfVisit, maybe you can help with this too?

My real question is how to take the results of this query pop one email address off of the top, use it to send an email, set it's flag to false, run the query again repeat until the query returns null.

Thanks in advance for your help!

Answer 1

Use a loop to iterate through the results of your query.

SELECT EmailAddress 
FROM Customers` 
WHERE EmailFlag = 'true'` 
AND DATEDIFF(day, GETDATE(),DateOfVisit) >= 90;

Replace day with other units you want to get the difference in, like second, minute etc.

c#:

foreach(DataRow dr in queryResult.Tables[0].Rows)
{
   string email = dr["EmailAddress"].ToString();
   // Code to send email
   //Execute Query UPDATE Customers SET EmailFlag = False WHERE EmailAddress = email 
}

This is just a draft. You should replace the comments with the actual code to make it work. No need to fetch from your initial query 1 result at a time.