Home > other >  How do I show the last 10 rows in the table?
How do I show the last 10 rows in the table?

Time:12-01

private static IEnumerable<string> getExtrato(string query)
{
    using (var cn = new SqlConnection("Data Source=MAD-PC-023\\SQLEXPRESS;Database=bank;Trusted_Connection=True;"))
    {
        cn.Open();
        using (var cmd = new SqlCommand() { Connection = cn, CommandText = query })
        {
            var reader = cmd.ExecuteReader();
            var result = new List<string>();
            while (reader.Read() == true && result.Count <= 9 )
            {
                if (reader.GetString(1) == "0")
                {   //  "ficando assim com: "   reader.GetDecimal(3)
                    result.Add("\n  O cartão nº "   reader.GetString(0)   " levantou: "   reader.GetString(2)   " euros, "    " às: "   reader.GetDateTime(3));
                }
                else
                {
                    result.Add("\n  O cartão nº "   reader.GetString(0)   " depositou: "   reader.GetString(1)   " euros, "   " às: "   reader.GetDateTime(3));
                }
            }
            return result;
        }
    }
}
private static IEnumerable<string> extratoOperacao(string numeroCartao)
{
    return getExtrato($@"SELECT CardNumber, Deposit, Withdraw, DataHora FROM MoveInfo WHERE CardNumber = '{numeroCartao}'");
}

As I have is presenting me only the first 10 lines, but I need the last 10 by normal order, how do I do that? If anyone can help me, I'd be grateful

CodePudding user response:

private static IEnumerable<string> getExtrato(string query)
{
    using (var cn = new SqlConnection("Data Source=MAD-PC-023\\SQLEXPRESS;Database=bank;Trusted_Connection=True;"))
    {
        cn.Open();
        using (var cmd = new SqlCommand() { Connection = cn, CommandText = query })
        {
            var reader = cmd.ExecuteReader();
            var result = new List<string>();

            // Let's remove unused conditions
            while (reader.Read())
            {
                if (reader.GetString(1) == "0")
                {
                    result.Add("\n  O cartão nº "   reader.GetString(0)   " levantou: "   reader.GetString(2)   " euros, "    " às: "   reader.GetDateTime(3));
                }
                else
                {
                    result.Add("\n  O cartão nº "   reader.GetString(0)   " depositou: "   reader.GetString(1)   " euros, "   " às: "   reader.GetDateTime(3));
                }
            }

            // HERE IS THE MAGIC
            return result.TakeLast(10);
        }
    }
}

CodePudding user response:

If you use an ORDER BY in the query you can make sure which records are returned, and you can use TOP to restrict the quantity of records returned, so something like

return getExtrato($@"SELECT TOP 10 [CardNumber], [Deposit], [Withdraw], [DataHora], [Id] FROM [MoveInfo] WHERE [CardNumber] = '{numeroCartao}' ORDER BY [Id] DESC");

will return the desired records, and then you just need to read all of them and reverse the result in your code (there are other possibilities, but that might be simplest for now).

  • Related