In this article I will explain a simple tutorial with an example, how to use
MySQL database with
MySQL Connector in ASP.Net Core MVC.
Download and Install the MySQL Connector
You will need to download and install the
MySQL Connector in order to connect to the
MySQL database in ASP.Net.
For details on how to download and install the
MySQL Connector, please refer:
DLL Download
Nuget
Database
I have made use of the following table Customers with the schema as follows.
I have already inserted few records in the table.
Note: You can download the database table SQL by clicking the download link below.
Namespace
You will need to import the following namespace.
using MySql.Data.MySqlClient;
Model
The Model class consists of following properties.
public class CustomerModel
{
public int CustomerId { get; set; }
public string Name { get; set; }
public string Country { get; set; }
}
Controller
The Controller consists of following Action method.
Action method for handling GET operation
Inside this Action method, a Generic List collection of CustomerModel class object is created.
Then, the records are fetched from the
Customers table of the
MySQL database using DataReader (
MySqlDataReader) and set to the Generic List collection of
CustomerModel class.
Finally, the Generic List collection of CustomerModel class is returned to the view.
public class HomeController : Controller
{
public IConfiguration Configuration { get; set; }
public HomeController(IConfiguration _configuration)
{
this.Configuration = _configuration;
}
public IActionResult Index()
{
List<CustomerModel> customers = newList<CustomerModel>();
string sql = "SELECT CustomerId, Name, Country FROM Customers";
string constr = this.Configuration.GetSection("ConnectionStrings")["MyConn"];
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand(sql, con))
{
con.Open();
using (MySqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(new CustomerModel
{
CustomerId = Convert.ToInt32(sdr["CustomerId"]),
Name = sdr["Name"].ToString(),
Country = sdr["Country"].ToString()
});
}
}
con.Close();
}
}
return View(customers);
}
}
View
HTML Markup
Inside the View, the CustomerModel is declared as IEnumerable which specifies that it will be available as a Collection.
For displaying the records, an HTML Table is used. A FOREACH loop will be executed over the Model which will generate the HTML Table rows with the Customer records.
@using MySql_Core.Models
@modelIEnumerable<CustomerModel>
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<table cellpadding="0" cellspacing="0">
<tr>
<th>Customer Id</th>
<th>Contact Name</th>
<th>Country</th>
</tr>
@foreach (CustomerModel customer in Model)
{
<tr>
<td>@customer.CustomerId</td>
<td>@customer.Name</td>
<td>@customer.Country</td>
</tr>
}
</table>
</body>
</html>
Screenshot
Downloads