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, it should be inside the subclass of `PageModel` :

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, it should be inside the subclass of `PageModel`:

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

You need to have a local webserver running with an installation of MySQL & PHP. I have used the USBWebServer from your SQL lessons. I have also created this connection string. The string below includes the name of the database, mine is just called `test`. The string should be inside the subclass of `PageModel`:

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

Finally I created this method to get the connection, it should be inside the subclass of `PageModel`:

        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;

    users.Clear(); // clear users before a load or reload

    using var connection = GetConnection; // this gets the connection

    connection.Open(); // you must open the connection
            
    using var Command = new MySqlCommand("SELECT * FROM test;", connection ); // you can add your SQL into a command
    using var reader = Command.ExecuteReader(); // you execute the query
    while (reader.Read())
    {
         User temp = new User(); // create user
         temp.username = reader.GetString(0);
         temp.password = reader.GetString(1);
         temp.status = reader.GetInt32(2);
         users.Add(temp); // add user to list
    }
}

Remember, here `test` in the SQL refers to the table name, so if you have a different table name you must change the SQL. To show the data in your page go back to the HTML code for the page and add:

		@foreach (var u in IndexModel.users)
		{
			<table class="table">
				<thead>
					<tr class="bg-primary">
						<th>User</th>
						<th>Password</th>
						<th>Status</th>

					</tr>
				</thead>
				<tbody>

					<tr>
						<td class="bg-info">@u.username</td>
						<td class="bg-info">@u.password</td>
						<td class="bg-info">@u.status</td>

					</tr>
	
				</tbody>
			</table>
		}

Insert data

Inside your subclass of `PageModel` add the following method, this will run a submit:

public IActionResult OnPost()
{
     User temp = new User();
     temp.username = Request.Form["user"];
     temp.password = Request.Form["password"];
     temp.status = Convert.ToInt32(Request.Form["status"]);

     using var connection = GetConnection;

     connection.Open();
     string sql = "insert into test values(@p1, @p2, @p3);";
     using var Command = new MySqlCommand(sql, connection);
     Command.Parameters.AddWithValue("@p1", temp.username);
     Command.Parameters.AddWithValue("@p2", temp.password);
     Command.Parameters.AddWithValue("@p3", temp.status);

     Command.ExecuteNonQuery();
     connection.Close();
	
     users.Add(temp);

     return Page();
}

In your html you need to create a form like this:

	<form method="post">
		<input type="Text" name="user">
		<input type="Text" name="password">
		<input type="number" name="status">
		<input type="submit">
	</form>