根据我从数据库中获得的另一个类属性给类属性提供数据

本文关键字：属性 另一个 提供数据 数据库 | 更新日期: 2023-09-27 18:19:04

我从数据库的两个表中获取数据。

表1 - Members:

        public List<Member> ListMembers()
        {
            List<Member> Members = new List<Member>();
            string SELECT = "SELECT * FROM Members ORDER BY Id";
            using (sqlConnection = new SqlConnection(sqlConnectionString_WORK))
            {
                sqlConnection.Open();
                using (SqlCommand sqlCommand = new SqlCommand(SELECT, sqlConnection))
                {
                    var sqlReader = sqlCommand.ExecuteReader();
                    while (sqlReader.Read())
                    {
                        var member = new Member
                        {
                            Id = Convert.ToInt32(sqlReader["Id"]),
                            Name = sqlReader["Name"].ToString(),
                            Surname = sqlReader["Surname"].ToString(),
                            EntryDate = Convert.ToDateTime(sqlReader["EntryDate"])
                        };
                        if (member.EntryDate < DateTime.Today)
                        {
                            member.Status = Status.Unpaid.ToString();
                        }
                        else
                        {
                            member.Status = Status.Paid.ToString();
                        }
                        Members.Add(member);
                    }
                }
            }
            return Members;
        }

表2 -付款:

        public List<Payment> ListPayments(Payment entity)
        {
            List<Payment> Payments = new List<Payment>();
            string SELECT = "SELECT * FROM Payments WHERE Id = @Id";
            using (sqlConnection = new SqlConnection(sqlConnectionString_WORK))
            {
                sqlConnection.Open();
                using (SqlCommand sqlCommand = new SqlCommand(SELECT, sqlConnection))
                {
                    sqlCommand.Parameters.Add("@Id", SqlDbType.Int).Value = entity.Id;
                    var sqlReader = sqlCommand.ExecuteReader();
                    while (sqlReader.Read())
                    {
                        var payment = new Payment
                        {
                            Id = Convert.ToInt32(sqlReader["Id"]),
                            Amount = Convert.ToDecimal(sqlReader["Amount"]),
                            StartDay = Convert.ToDateTime(sqlReader["StartDay"]),
                            EndDay = Convert.ToDateTime(sqlReader["EndDay"])
                        };
                        Payments.Add(payment);
                    }
                }
            }
            return Payments;
        }

付款的ID等于特定成员的ID。(如果会员ID为100，他/她的所有付款都将使用相同的ID 100)。

现在…在ListMembers()中，我根据他们的EntryDate给状态"paid"answers"paid"，但我想根据他们的LAST Payment给他们status。所以，不是:

if (member.EntryDate < DateTime.Today)
{
     member.Status = Status.Unpaid.ToString();
}

应该是:

if (lastPayment < DateTime.Today) // lastPayment is just a word I'm using, It's not declared anywhere, I don't know how to get that value.
{
     member.Status = Status.Unpaid.ToString();
}
我真的迷路了，有人能帮帮我吗?我不知道该怎么办。

根据我从数据库中获得的另一个类属性给类属性提供数据

获取会员付款:

public List<Payment> GetMemberPaymnets(int memberId)
    {
        List<Payment> Payments = new List<Payment>();
        string SELECT = "SELECT ID,AMOUNT,STARTDAY,ENDDAY FROM Payments WHERE Id = @Id order by endday desc";
        using (sqlConnection = new SqlConnection(sqlConnectionString_WORK))
        {
            sqlConnection.Open();
            using (SqlCommand sqlCommand = new SqlCommand(SELECT, sqlConnection))
            {
                sqlCommand.Parameters.Add("@Id", SqlDbType.Int).Value = entity.Id;
                var sqlReader = sqlCommand.ExecuteReader();
                while (sqlReader.Read())
                {
                    var payment = new Payment
                    {
                        Id = Convert.ToInt32(sqlReader["Id"]),
                        Amount = Convert.ToDecimal(sqlReader["Amount"]),
                        StartDay = Convert.ToDateTime(sqlReader["StartDay"]),
                        EndDay = Convert.ToDateTime(sqlReader["EndDay"])
                    };
                    Payments.Add(payment);
                }
            }
        }
        return Payments;
    }

代替

if (member.EntryDate < DateTime.Today)
{
     member.Status = Status.Unpaid.ToString();
}

你可以这样写

 foreach(Member memeber in memeberList)
 {
        List<Payment> memberPayments=GetMemberPayments(member.Id);
        //also you need to check that memberPayments.Count>0
        if(memberPayments[0].EndDay<DateTime.Today)
        {
           member.Status = Status.Unpaid.ToString();
        }
 }

但是如果你不需要对成员支付进行一些操作，你可以创建一个数据库方法，该方法将根据成员id返回成员支付状态状态。

您可以尝试使用Member查询获取最新付款日期。而不是

string SELECT = "SELECT * FROM Members ORDER BY Id";

试

string SELECT = "SELECT m.Id, m.Name, 
m.Surname, m.EntryDate, MAX(p.EndDay) AS EndDay FROM Members m
JOIN Payments p ON p.Id = m.Id
ORDER BY m.Id";

，然后替换这个:

if (member.EntryDate < DateTime.Today)
{
     member.Status = Status.Unpaid.ToString();
}
与这个:

var endDate = Convert.ToDateTime(sqlReader["EndDay"])
if (endDate < DateTime.Today)
{
    member.Status = Status.Unpaid.ToString();
}

这假设总是有一个成员的支付。如果可能存在没有付款的成员，则必须进行null检查和左连接。

相关文章：