کانال نوحه و مداحی ناب

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!

 
 
 
 
 
 
 
کانال نوحه و مداحی ناب

نظرات (۰)
هیچ نظری هنوز ثبت نشده است

ارسال نظر

ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.
شما میتوانید از این تگهای html استفاده کنید:
<b> یا <strong>، <em> یا <i>، <u>، <strike> یا <s>، <sup>، <sub>، <blockquote>، <code>، <pre>، <hr>، <br>، <p>، <a href="" title="">، <span style="">، <div align="">
تجدید کد امنیتی