In this article, we'll explore how to establish a connection between a .NET Core application and MySQL, and retrieve data from MySQL using .NET Core versions 5, 6, 7, or 8.
- ASP .Net Core CRUD operations without using Entity Framework Step By Step Guide
- Crud operation in Asp .Net Core using Razor Pages Step By Step
- Crud operation in Asp.Net Core Mvc using jquery datatable with entity framework core database first
1.Install MySQL Connector/NET:
This is the official MySQL data provider for .NET. You can download and install it from the MySQL website or install it via NuGet package manager.
dotnet add package MySql.DataIn your ASP.NET Core application, you need to configure the connection string to connect to your MySQL database.
The connection string contains information like server address, database name, user credentials, etc.In appsettings.json or appsettings.Development.json (for development environment), add your connection string:
{
"ConnectionStrings": {
"SqlConnection": "server=localhost;port=3306;database=ApplicationDb;uid=sa;password=123456"
}
}
You'll need to register the MySQL database context in the dependency injection container of your ASP.NET Core application.
In the Startup.cs file and in case of .NET 7 OR .NET 8 write that code in program.cs.
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<BlogDataContext>(options =>
options.UseMySQL(Configuration.GetConnectionString("MySqlConnection")));
// Other configurations and services
}
}In .NET greater then >6, Not having Startup.cs file by default,in that you need case configure your application without a Startup.cs file.
Below is an example of how you can configure your application using the new Minimal APIs approach:
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
var connectionString = builder.Configuration.GetConnectionString("SqlConnection");
builder.Services.AddDbContext<BlogDataContext>(options =>
options.UseMySQL(connectionString));
Create Database Context Class: You need to create a database context class that inherits from DbContext and represents your MySQL database.
csharp
Copy code
using Microsoft.EntityFrameworkCore;
public class BlogDataContext : DbContext
{
public BlogDataContext(DbContextOptions<BlogDataContext> options) : base(options)
{
}
// DbSet properties representing your database tables
public DbSet<Employee> Employees { get; set; }
}Now you can inject your BlogDataContext class wherever you need to interact with the database in your ASP.NET Core application.
You can perform CRUD operations using Entity Framework Core.
For example, here we have created an employee controller for performing CRUD operations on the employee table.
using Microsoft.AspNetCore.Http; using Microsoft.AspNetCore.Mvc; using DempApplication.Models; using DempApplication.Models.DTO; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace DempApplication.Controllers { [Route("api/[controller]/[action]")] [ApiController] public class EmployeeController : ControllerBase { //api/Employee/GetAllEmployee [HttpGet] public IActionResult GetAllEmployee() { ResponseDTO responseDTO = new ResponseDTO(); responseDTO.Success = false; try { using (BlogDataContext db = new BlogDataContext()) { var employees = db.Employees.ToList(); responseDTO.Success = true; responseDTO.Data = employees; return Ok(responseDTO); } } catch (Exception ex) { responseDTO.Error = ex.Message; return Ok(responseDTO); } } //api/Employee/GetEmployeeById [HttpGet] public IActionResult GetEmployeeById(int id) { ResponseDTO responseDTO = new ResponseDTO(); responseDTO.Success = false; try { using (BlogDataContext db = new BlogDataContext()) { var employee = db.Employees.FirstOrDefault(a => a.Id == id); if (employee != null) { responseDTO.Success = true; responseDTO.Data = employee; return Ok(responseDTO); } else { responseDTO.Error = "Employee Id not found in the database, please try again!"; return Ok(responseDTO); } } } catch (Exception ex) { responseDTO.Error = ex.Message; return Ok(responseDTO); } } // api/Employee/CreateEmployee [HttpPost] public async Task<IActionResult> CreateEmployee(AddEmployeeDTO employeeDTO) { ResponseDTO responseDTO = new ResponseDTO(); responseDTO.Success = false; try { using (BlogDataContext db = new BlogDataContext()) { Employee employee = new Employee { FirstName = employeeDTO.FirstName, LastName = employeeDTO.LastName, Email = employeeDTO.Email, PhoneNumber = employeeDTO.PhoneNumber, CreatedAt = DateTime.UtcNow, IsMaster = false, }; db.Employees.Add(employee); db.SaveChanges(); responseDTO.Success = true; responseDTO.Data = employee; return Ok(responseDTO); } } catch (Exception ex) { responseDTO.Error = ex.Message; } return Ok(responseDTO); } // api/Employee/EditEmployee [HttpPut] public async Task<IActionResult> EditEmployee(int id, EditEmployeeDTO editEmployeeDTO) { ResponseDTO responseDTO = new ResponseDTO(); responseDTO.Success = false; try { using (BlogDataContext db = new BlogDataContext()) { var employee = db.Employees.FirstOrDefault(a => a.Id == id); if (employee != null) { if (!employee.IsMaster) { employee.FirstName = editEmployeeDTO.FirstName; employee.LastName = editEmployeeDTO.LastName; employee.Email = editEmployeeDTO.Email; employee.PhoneNumber = editEmployeeDTO.PhoneNumber; db.SaveChanges(); } else { responseDTO.Error = "Please add your own employee object, and then attempt to update it to observe the changes in values.It will not update the pre-added master data"; } responseDTO.Success = true; responseDTO.Data = employee; } else { } return Ok(responseDTO); } } catch (Exception ex) { responseDTO.Error = ex.Message; } return Ok(responseDTO); } //api/Employee/DeleteEmployee [HttpDelete] public IActionResult DeleteEmployee(int id) { ResponseDTO responseDTO = new ResponseDTO(); responseDTO.Success = false; try { using (BlogDataContext db = new BlogDataContext()) { var employee = db.Employees.FirstOrDefault(a => a.Id == id); if (employee != null) { if (!employee.IsMaster) { db.Employees.Remove(employee); db.SaveChanges(); } else { responseDTO.Error = "Please add your own employee object using post api, and then attempt to delete it.It will not delete the pre-added master data"; } responseDTO.Success = true; responseDTO.Data = employee; return Ok(responseDTO); } else { responseDTO.Error = "Employee Id not found in the database, please try again!"; return Ok(responseDTO); } } } catch (Exception ex) { responseDTO.Error = ex.Message; return Ok(responseDTO); } } } }It is an open-source DBMS, so anyone can use it for free. Websites like Facebook, Google, and Twitter also use it.MySQL is software with which you store and manage data. This data can be anything. It could be names and addresses of individuals, or information about the sales and production of a company. Regardless of the type of data, MySQL is necessary to store and access it.It is a relational database. A relational database stores all data in different tables instead of a single table. It then identifies the relations between these tables and processes queries based on them. To achieve speed, database structures are organized as physical files.SQL is used to interact with MySQL. SQL (Structured Query Language) is the most common and standard language for working with databases. You can use SQL directly or with any programming or scripting language.MySQL is open-source software. This means the software is available to you for free, and you can modify it according to your needs.
