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.
dotnet add package MySql.Data{
"ConnectionStrings": {
"SqlConnection": "server=localhost;port=3306;database=ApplicationDb;uid=sa;password=123456"
}
}
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
}
}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; }
}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.