如何在 while 循环中将值从数据读取器传递到 C# 中的方法
本文关键字：方法 读取 while 循环 数据 | 更新日期: 2023-09-27 18:30:52
我在数据库中有 3 个表，我正在使用 SQL 语句迭代这些表。它搜索需要续订合同的应用程序。我使用 SQL 日期数学来检查是否需要通知经理续签合同。如果今天的日期 = 通知日期字段中的日期，则控制台应用应向列为该应用程序的分析师/经理的人员发送电子邮件。这是我到目前为止的代码：
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);
}
}
}
SQL 语句按预期工作。它遍历表中的行，并使用 notificationDate = 当前日期拉取合约。我在使用数据读取器时遇到问题。它遍历 SQL 语句拉取的协定，但只将读取的最后一个值存储到字符串中。我需要它来存储它提取的任何和所有值，以便在有多个人需要通知时向每个人发送电子邮件。
数据读取器不是为保存数据而设计的。它只是迭代数据。如果要存储 "读取"的结果，将数据添加到 ArrayList 或其他一些数据结构中，这将允许您对数据执行进一步的工作。
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();
您正在循环 dataSet，但将值存储在字符串而不是某种列表中。这就是为什么只存储最后一个值的原因
//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
}
首先，将SendMail方法更改为更通用。
接收参数，其中包含应接收邮件的人员的信息
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);
}
现在在你的循环中，当你从读取器读取数据时，调用上面的方法传递适当的值
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)
}
}
当然，您也可以将从 DataReader 检索到的值存储在某种形式的对象集合中（其中 EMail 是一个包含三个参数的类List<Email>），但这会导致双循环（一个用于读取，一个用于发送电子邮件），因此，如果实际上不需要在内存中存储所有这些电子邮件地址， 我建议在阅读读者时发送邮件。
一种解决方案是创建一个Person类
public class Person
{
public bool IsManager { get; set; }
public string AnalystEmail { get; set; }
public string ManagerEmail { get; set; }
}
然后在Program类中，您需要声明一个List<Person> _personList = new List<Person>();
最后，在你的while loop：
while (dataReader.Read())
{
_personList.Add(new Person {
IsManager = Convert.ToBool(dataReader["isManager"]),
AnalystEmail = Convert.ToString(dataReader["analystEmail"]),
ManagerEmail = Convert.ToString(dataReader["managerEmail"])
});
}
之后，您可以使用foreach(var person in _personList)或类似内容发送电子邮件。
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();
}
无论您迭代
多少次这些值，您的值每次都会更新，并且您只会获得上次更新的值。 因为对于每次迭代，您都会为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"]);
}
我认为您应该使用某种集合来存储您的值。
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"]));
}
创建一个struct来保存所有详细信息，然后创建一个List<yourstruct>。
循环中填充struct。
当您退出循环时，调用您的电子邮件方法并通过列表。
在您的电子邮件方法中foreach您的列表每次发送电子邮件