how can I view data using MySqlDataReader in MVC?

Question

In a Connection class.cs after entering my connection string, the Open.Connection and Close.Connection methods, I created the following method:

public void LibriList(Libro lista)
        {
            if (this.OpenConnection() == true)
                try
                {

                    string query = "SELECT * FROM libri_schema.libri";
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    MySqlDataReader rdr = cmd.ExecuteReader();
                    List<Libro> model = new List<Libro>();
                    while (rdr.Read())
                    {
                        var details = new Libro();
                        details.Titolo = rdr["Titolo"].ToString();
                        details.Autore = rdr["Autore"].ToString();
                        details.Editore = rdr["Editore"].ToString();
                        details.ISBN = rdr["ISBN"].ToString();
                        /* details.Prezzo = rdr["Prezzo"].ToString();
                         details.Pagine = rdr["Pagine"].ToString();
                         details.Quantità = rdr["Quantità"].ToString();*/
                        model.Add(details);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    this.CloseConnection();
                }

this is my controller:

[HttpGet]
public ActionResult Libri()
{
    return View();
}

//public ViewResult Registrationorm()
[HttpPost]
public ViewResult Libri(Libro Lista)
{
    if (ModelState.IsValid)
    {
        var connection = new Connection();
        connection.LibriList(Lista);
        return View("Libri");
    }
    else
    {

        ViewBag.ErrorMessage = "Nessun Libro disponibile";

    }
    return View();
}

This is a Model:

        public class Libro
        {
            public string Titolo { get; set; }
            //string Autore da cambiare in Lista
            public string Autore { get; set; }
            public string Editore { get; set; }
            public string ISBN { get; set; }
            public int Pagine { get; set; }
            public decimal Prezzo { get; set; }
            public int Quantità { get; set; }

In mySQL Workbench I created a table with some books, how can I now view it through a view?

On an unrelated note, it would almost certainly be worth looking into something like Dapper, your entire method can basically be shortened to return connection.Query<Libro>("SELECT Titolo, Autore, Editore, ISBN FROM libri_schema.libri");, which is much more maintainable and a lot less boilerplate code — GarethD
– GarethD, Commented Mar 1, 2022 at 12:31

David · Accepted Answer · 2022-03-01 12:23:11Z

You have to return the data from the method so you can supply it to the view. For example, change the method's return type:

public List<Libro> LibriList(Libro lista)

And return the data:

List<Libro> model = new List<Libro>();
while (rdr.Read())
{
  //...
}
return model; // <--- here

You may also need to add more return statements or throw statements to your method, depending on the logic. For example, if this return is inside a condition then you'd need some default for when the condition is not met. What you do in those situations is up to you. You can return some default (like an empty list), return null, or perhaps throw an exception if that condition shouldn't happen.

Once you're returning from the method, supply that data to the view:

var connection = new Connection();
var model = connection.LibriList(Lista);
return View("Libri", model);

Then in your Libri view you would declare the model type at the top of the view:

@model List<Libro>

And can use the Model property of that type anywhere in the view. (Which is covered by MVC tutorials and examples.)

As an aside, this is an anti-pattern:

catch (Exception ex)
{
    throw ex;
}

The statement throw ex is stripping useful information from the exception, information that could help you diagnose and resolve errors. If you need to re-throw the original exception as-is, just use throw; by itself. But if the catch block isn't doing anything and just re-throws the original exception, get rid of the catch block entirely.

I'm trying to make these changes, but can you help me in the Html code to write in the view?
@Ludema63: If you've written specific code in your view and have encountered a specific problem, that would be a good candidate for a separate Stack Overflow question. It needs to be more concrete than just "can you help me" though. Please see Why is “Can someone help me?” not an actual question?

Collectives™ on Stack Overflow

how can I view data using MySqlDataReader in MVC?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related