how to use mysql database in c# asp.net
Yes, absolutely! You can use MySQL with C# ASP.NET. Here's how to set it up:
1. Install MySQL Connector/NET
First, install the MySQL NuGet package:
Package Manager Console:
bash
Install-Package MySql.Data
Or .NET CLI:
bash
dotnet add package MySql.Data
2. Configuration (appsettings.json)
json
Copy
Download
{ "ConnectionStrings": { "MySqlConnection": "server=localhost;database=your_database;user=root;password=your_password;" } }
3. Basic Setup in Program.cs
For ASP.NET Core 6+:
csharp
Copy
Download
using MySql.Data.MySqlClient; var builder = WebApplication.CreateBuilder(args); // Add services builder.Services.AddControllers(); builder.Services.AddEndpointsApiExplorer(); builder.Services.AddSwaggerGen(); // Register MySQL connection builder.Services.AddTransient<MySqlConnection>(_ => new MySqlConnection(builder.Configuration.GetConnectionString("MySqlConnection"))); var app = builder.Build(); // Configure pipeline if (app.Environment.IsDevelopment()) { app.UseSwagger(); app.UseSwaggerUI(); } app.UseHttpsRedirection(); app.UseAuthorization(); app.MapControllers(); app.Run();
4. Create a Model Class
csharp
Copy
Download
public class PlacesCondition { public int Serial { get; set; } public string? OrgLevel { get; set; } public DateTime FromDate { get; set; } public string? Dsc { get; set; } public int? PlacesRef { get; set; } }
5. Controller Example
csharp
Copy
Download
[ApiController] [Route("api/[controller]")] public class PlacesController : ControllerBase { private readonly MySqlConnection _connection; public PlacesController(MySqlConnection connection) { _connection = connection; } [HttpPost("list")] public async Task<IActionResult> GetPlacesList([FromBody] PlacesRequest request) { try { await _connection.OpenAsync(); var records = new List<PlacesCondition>(); int totalCount = 0; // Call stored procedure using var command = new MySqlCommand("sp_WPSPlacesConditionList2", _connection) { CommandType = CommandType.StoredProcedure }; // Add parameters command.Parameters.AddWithValue("@APlacesRef", request.OrderId); command.Parameters.AddWithValue("@limt", request.Limit); command.Parameters.AddWithValue("@offset", (request.Page - 1) * request.Limit); // Output parameter for row count var rowCountParam = new MySqlParameter("@RowCount", MySqlDbType.Int32) { Direction = ParameterDirection.Output }; command.Parameters.Add(rowCountParam); // Execute and read results using var reader = await command.ExecuteReaderAsync(); // Read first result set (records) while (await reader.ReadAsync()) { records.Add(new PlacesCondition { Serial = reader.GetInt32("Serial"), OrgLevel = reader.GetString("OrgLevel"), FromDate = reader.GetDateTime("FromDate"), Dsc = reader.GetString("Dsc"), PlacesRef = reader.IsDBNull("PlacesRef") ? null : reader.GetInt32("PlacesRef") }); } // Get the row count from output parameter await reader.CloseAsync(); totalCount = Convert.ToInt32(rowCountParam.Value); return Ok(new { Page = request.Page, Limit = request.Limit, TotalRecords = totalCount, TotalPages = (int)Math.Ceiling((double)totalCount / request.Limit), CurrentCount = records.Count, List = records }); } catch (Exception ex) { return StatusCode(500, new { error = ex.Message }); } finally { await _connection.CloseAsync(); } } } // Request model public class PlacesRequest { public int OrderId { get; set; } public int Page { get; set; } = 1; public int Limit { get; set; } = 10; }
6. Alternative Approach with Dapper (Recommended)
Install Dapper for simpler data access:
bash
Copy
Download
Install-Package Dapper
Repository with Dapper:
csharp
Copy
Download
public class PlacesRepository { private readonly MySqlConnection _connection; public PlacesRepository(MySqlConnection connection) { _connection = connection; } public async Task<(List<PlacesCondition> Records, int TotalCount)> GetPlacesAsync(int orderId, int page, int limit) { var parameters = new DynamicParameters(); parameters.Add("@APlacesRef", orderId); parameters.Add("@limt", limit); parameters.Add("@offset", (page - 1) * limit); parameters.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.Output); await _connection.OpenAsync(); try { // Execute stored procedure var records = (await _connection.QueryAsync<PlacesCondition>( "sp_WPSPlacesConditionList2", parameters, commandType: CommandType.StoredProcedure )).ToList(); var totalCount = parameters.Get<int>("@RowCount"); return (records, totalCount); } finally { await _connection.CloseAsync(); } } }
7. Updated Controller with Dapper
csharp
Copy
Download
[ApiController] [Route("api/[controller]")] public class PlacesController : ControllerBase { private readonly PlacesRepository _repository; public PlacesController(PlacesRepository repository) { _repository = repository; } [HttpPost("list")] public async Task<IActionResult> GetPlacesList([FromBody] PlacesRequest request) { try { var (records, totalCount) = await _repository.GetPlacesAsync( request.OrderId, request.Page, request.Limit ); if (!records.Any()) { return Ok(new { message = "No Record Found" }); } return Ok(new { Page = request.Page, Limit = request.Limit, TotalRecords = totalCount, TotalPages = (int)Math.Ceiling((double)totalCount / request.Limit), CurrentCount = records.Count, List = records }); } catch (Exception ex) { return StatusCode(500, new { error = ex.Message }); } } }
Key Benefits:
Cross-platform - Works on Windows, Linux, macOS
High performance - MySQL is very fast
Entity Framework Support - You can also use Entity Framework with MySQL
Mature ecosystem - Well-supported and documented
Additional Packages You Might Want:
bash
Copy
Download
# Entity Framework Core with MySQL Install-Package Pomelo.EntityFrameworkCore.MySql # Dapper for micro-ORM Install-Package Dapper
MySQL is a great choice for ASP.NET applications, especially if you need cross-platform compatibility or want to avoid SQL Server licensing costs!
نظرات (۰)
هیچ نظری هنوز ثبت نشده است