Connect MySQL to ASP.Net Web App

Install MySQL.Data

Using the Nuget Package Manager install the following:

• MySql.Data;
• MySql.Data.MySqlClient;

Creating the connection

Now using the page you want to connect, open the Model `cs` code.

At the top add the following

using MySql.Data;
using MySql.Data.MySqlClient;

Just for this example we will be retrieving the details from the user table, so I created this class to store each record:

public class User
{
    public string username { get; set; }
    public string password { get; set; }
    public int status { get; set; }
}

Next I created a list of User to store the retrieved data:

public static List<User> users = new List<User>();

I have also created this string for the connection string:

string connection = "server=localhost;user=root;database=test;port=3306;password=usbw;";

Finally I created this method to get the connection:

        public MySqlConnection GetConnection {
            get
            {
                return new MySqlConnection(connection);
            }
        }

Getting the data

Now, in the constructor method I have added the following code:

        public IndexModel(ILogger<IndexModel> logger)
        {
            _logger = logger;

            using var connection = getconnection;

	    connection.Open();
            
            using var Command = new MySqlCommand("SELECT * FROM test;", connection );
            using var reader = Command.ExecuteReader();
            while (reader.Read())
            {
                User temp = new User();
                temp.username = reader.GetString(0);
				temp.password = reader.GetString(1);
				temp.status = reader.GetInt32(2);
				users.Add(temp);
            }
        }