public string GetAllProducts()
    {
        string connStr = "server=localhost;user=root;database=tp1;port=3306;password=";
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            Console.WriteLine("Connecting to MySQL...");
            conn.Open();

            string sql = "SELECT * FROM material";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            MySqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                return (rdr[0]   " -- "   rdr[1]   " -- "   rdr[2]   " -- "   rdr[3]);
            }
            rdr.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        conn.Close();
        Console.WriteLine("Done.");


        return "No records found";
    }

At this point I have on "material" table four entries. Only the first is returned.

This is the output:

SWAGGER OUTPUT

'material' content

CodePudding user response：

You have put a return inside the loop that reads the data. Loop thus does not execute in a looping fashion!

Really your code should look more like:

public List<Product> GetAllProducts()
{
    string connStr = "server=localhost;user=root;database=tp1;port=3306;password=";

    using var conn = new MySqlConnection(connStr);
    conn.Open();

    string sql = "SELECT * FROM material";
    using var cmd = new MySqlCommand(sql, conn);
    using var rdr = cmd.ExecuteReader();
    var products = new List<Product>();
    while (rdr.Read())
    {
         var p = new Product(
           rdr.GetInt32(0), //id
           rdr.GetString(1), //ref
           rdr.GetString(2), //name
           rdr.GetInt32(3) //qty
         );
         products.Add(p);
    }
    return products;
}

public record Product(int Id, string Ref, string Name, int Qty);

(try/catch removed for clarity; feel free to put it back in)

Note that this C# uses more recent features such as records and using var; if you get syntax errors, let me know what they are so I can suggest a different syntax that is compatible with your C# version

By the way, I find this way of getting data out of a DB is so incredibly tedious. You can cut it down a huge amount by installing a library called Dapper. With Dapper, all that code I wrote above is reduced to:

public List<Product> GetAllProducts()
{
    string connStr = "server=localhost;user=root;database=tp1;port=3306;password=";

    using var conn = new MySqlConnection(connStr);
    return conn.Query<Product>("SELECT * FROM material").ToList();
}

If you want to parameterize your queries:

public List<Product> GetAllProductsNamed(string n)
{
    string connStr = "server=localhost;user=root;database=tp1;port=3306;password=";

    using var conn = new MySqlConnection(connStr);
    return conn.Query<Product>(
      "SELECT * FROM material WHERE name = @theName", 
      new{ theName = n }
    ).ToList();
}

If you;re not going for some high powered ORM like EF, life is too short to not use Dapper (no affiliation) :)

CodePudding user response：

You are returning after the first Read(), try something like this:

var output = string.Empty;

while (rdr.Read())
{
   output  = (rdr[0]   " -- "   rdr[1]   " -- "   rdr[2]   " -- "   rdr[3]);
}
rdr.Close();

return output;