In this article I will explain a simple Tutorial with an example, how to use MySql Database with MySql Connector in ASP.Net MVC Razor.
By default, .Net Framework cannot connect to MySql Database and hence MySql Connector needs to be downloaded.
Database
I have made use of the following table Customers with the schema as follows.
![Using MySql Database with MySql Connector in ASP.Net MVC Razor Tutorial with example]()
I have already inserted few records in the table.
![Using MySql Database with MySql Connector in ASP.Net MVC Razor Tutorial with example]()
Note: You can download the database table SQL by clicking the download link below.
Installing and adding reference of MySql Connector
In order to install and add reference of MySql Connector, you will need to:-
1. Right Click the Project in Solution Explorer and click Manage NuGet Packages from the Context Menu.
![Using MySql Database with MySql Connector in ASP.Net MVC Razor Tutorial with example]()
2. Now you will need to look for MySql.Data package and once found, you need to click the Install Button.
![Using MySql Database with MySql Connector in ASP.Net MVC Razor Tutorial with example]()
MySql Connection String
Below is the connection string to the MySql Database.
<connectionStrings>
<add name="constr" connectionString="Data Source=localhost;port=3306;Initial Catalog=SampleDB;User Id=mudassar;password=12345"/>
</connectionStrings>
Namespaces
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
Model
The following Model class named CustomerModel consists of three properties.
public class CustomerModel
{
public int CustomerId { get; set; }
public string Name { get; set; }
public string Country { get; set; }
}
Controller
The Controller class consists of the Index Action method. Inside this Action method, the records from the Customers Table of the MySql database are fetched using MySqlDataReader and are added to a Generic List collection of the CustomerModel class objects.
Finally the Generic List collection are sent to the Model.
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
List<CustomerModel> customers = new List<CustomerModel>();
string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
string query = "SELECT CustomerId, Name, Country FROM Customers";
using (MySqlCommand cmd = new MySqlCommand(query))
{
cmd.Connection = 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
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 loop will be executed over the Model which will generate the HTML Table rows with the Customer records.
@using MySql_MVC.Models
@model IEnumerable<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
![Using MySql Database with MySql Connector in ASP.Net MVC Razor Tutorial with example]()
Downloads
