Is there a simpler way to query from MySQL in ASP.NET MVC?

Question

Currently to query my table of users I have to do the following in my controller.

using (MySqlConnection connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["test_schema"].ConnectionString))
{
    connection.Open();
    MySqlCommand command = new MySqlCommand("SELECT * FROM users", connection);
    MySqlDataReader reader = command.ExecuteReader();

    List<string> users = new List<string>();

    while (reader.Read())
    {
       users.Add(reader["id"] + "\t" + reader["first_name"]);
    }

    ViewBag.users = users;
    reader.Close();
}

Is it possible in C# to put the results in a dynamic object similar to how ViewBag works? I have some experience in Node.js Express and to write a query using the sequelize module all I have to do is write something like

Sequelize.query("SELECT * FROM users", { type: sequelize.QueryTypes.SELECT }).then(users => {
    // users attributes will be the columns of the user table
});

I left out the part of how to connect to a database in sequelize but I don't think it is relevant to the question.

Possible duplicate of How to return dynamic object from SQL query — Cee McSharpface
– Cee McSharpface, Commented Oct 3, 2018 at 23:53

Bradley Grainger · Accepted Answer · 2018-10-04 01:42:02Z

This can be done very easily with Dapper. It supports deserialising data rows to regular C# classes or dynamic objects.

using (MySqlConnection connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["test_schema"].ConnectionString))
{
    // the 'Query' method is provided by Dapper
    var users = connection.Query("SELECT id, first_name FROM users");
    // each object in 'users' will have .id and .first_name properties
    ViewBag.users = users;


    // to duplicate your sample code's behaviour of creating strings:
    var users = connection.Query("SELECT id, first_name FROM users")
        .Select(x => string.Concat(x.id, "\t", x.first_name))
        .ToList();
}

Alternatively, you can deserialise to an already-defined type:

class User
{
    public string Id { get; set; }
    public string FirstName { get; set; }
}

// here I use 'as FirstName' to change the retrieved column name so I
// can use "normal" C# property names on my User class
var users = connection.Query<User>("select id, first_name as FirstName from users");

Collectives™ on Stack Overflow

Is there a simpler way to query from MySQL in ASP.NET MVC?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related