This article will cover how to implement Restful Web Api based on .Net 8.0 and how to import and save MariaDb data as Web Api.
Development environment
- OS : Windows 11
- .Net Version : 8.0
- Development Tool : Visual Studio Code
- MariaDb 11.5
- EntiryFramework
1. Create Project
Open Powershell, go to the folder where you want to create the project, and run the dotnet new command as shown below.
\>dotnet new webapi --use-controllers -o TodoApi
- --use-controllers | -controllers : Controller-based Web API creation
- -o : Target Output
2. Open project and add package
Open the project in Visual Studio Code and install Microsoft EntityFramework and MySql EntityFramework Package. When you click Ctrl+`, a terminal window opens at the bottom of Visual Studio Code. The command is as follows.
\> dotnet add package Microsoft.EntityFrameworkCore
\> dotnet add package MySql.EntityFrameworkCore
Search for SQLTools in Visual Studio Code Extensions and install SQLTools MySQL/MariaDB/TiDB Extension.
You can connect to MariaDb with this extension.
3. Model/DbContext creation and service registration
First, the MariaDb table schema is as follows.
db: tutorial, table : todo
| column | type | 비고 |
| id | varchar(50) | PK |
| date | date | PK |
| title | text | |
| is_complete | tinyint(1) |
|
[Table("todo", Schema = "tutorial")]
public class TodoItem
{
[Column("id")]
public string Id { get; set; } = "";
[Column("date")]
public DateTime Date { get; set; }
[Column("title")]
public string Title { get; set; } = "";
[Column("is_complete")]
public bool IsComplete { get; set; }
}
public class TutorialDbContext : DbContext
{
public TutorialDbContext(DbContextOptions options) : base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<TodoItem>().HasKey(x => new { x.Id, x.Date });
}
public DbSet<TodoItem> TodoItems { get; set; }
}
- Register ConnectionString in appsettings.Development.json
"ConnectionStrings": {
"Tutorial": "server=localhost; port=3306; database=tutorial; user=root; password=********"
}
- Register DbContext in Program.cs
builder.Services.AddDbContext<TutorialDbContext>(
option => option.UseMySQL(
builder.Configuration.GetConnectionString("Tutorial")!));
4. Create Service class and register it in Program.cs
Create a service class that uses TutorialDbContext.
public class TodoService
{
private readonly TutorialDbContext _dbContext;
public TodoService(TutorialDbContext dbContext)
{
_dbContext = dbContext;
}
public async Task<List<TodoItem>> GetTodoItems()
{
var list = await _dbContext.TodoItems.ToListAsync();
return list;
}
public async Task<TodoItem?> GetTodoItem(string id, DateTime date)
{
var sql = "SELECT * FROM todo WHERE id = ?id AND date = ?date";
var parameters = new MySqlParameter[2];
parameters[0] = new MySqlParameter() { Value = id, ParameterName = "id" };
parameters[1] = new MySqlParameter() { Value = date, ParameterName = "date" };
var list = await _dbContext.TodoItems.FromSqlRaw(sql, parameters).ToListAsync();
return list.FirstOrDefault();
}
public async Task PostTodoItem(TodoItem item)
{
var savedItem = await GetTodoItem(item.Id, item.Date);
if (savedItem == null)
{
_dbContext.Add(item);
}
else
{
_dbContext.Entry(savedItem).CurrentValues.SetValues(item);
}
await _dbContext.SaveChangesAsync();
}
}
Register the created TodoService as a service in Program.cs.
builder.Services.AddScoped<TodoService>();
5. Create Controller and Write Http Method
Create a TodoApiController class that inherits the ControllerBase class.
[ApiController]
[Route("api/[controller]")]
public class TodoApiController : ControllerBase
{
private readonly TodoService _todoService;
public TodoApiController(TodoService todoService)
{
_todoService = todoService;
}
[HttpGet]
[Route("todoitems")]
public async Task<ActionResult<List<TodoItem>>> GetTodoItems()
{
var list = await _todoService.GetTodoItems();
return Ok(list);
}
[HttpGet]
[Route("todoitem/{id}/{date}")]
public async Task<ActionResult<TodoItem>> GetTodoItem(string id, DateTime date)
{
var item = await _todoService.GetTodoItem(id, date);
return Ok(item);
}
[HttpPost]
public async Task<ActionResult<TodoItem>> PostTodoItem(TodoItem item)
{
await _todoService.PostTodoItem(item);
return CreatedAtAction(nameof(GetTodoItem), new { id = item.Id, date = item.Date }, item);
}
}
6. Build and test
Run the command below in Powershell.
\>dotnet watch run
Execute and test each method on the Swagger screen.
👆
Reference
Comments
Post a Comment