Better way to send a list of items trough WebApi

I have a Web API configured to send a POST to the SQL server. I also have a server app (SignalR) that sends a list of strings to this POST API.

The problem: The Post only receives one item per time, so I send a request multiple times inside a loop, and with each iteration, a new item is sent.

It works but I believe there's an optimized way to do this, and if something goes wrong inside an iteration, the correct thing to do was canceling the transaction, but with this loop method, it is not possible. I'm accepting tips on how to handle this better.

WebApi:

VisitaItemControl.cs

public class VisitaItemControl
{
    public string ItemID { get; set; }
    public string VisitaID { get; set; }
}

VisitaItemControlController.cs

[Route("api/[controller]")]
    [ApiController]
    public class VisitaItemControlController : ControllerBase
    {
        private readonly IConfiguration _configuration;
        public VisitaItemControlController(IConfiguration configuration)
        {
            _configuration = configuration;
        }

    [HttpPost]
    public JsonResult Post(VisitaItemControl visitaItemControl)
    {
        string query = @"INSERT INTO VisitaItemControl (
                        ItemID,
                        VisitaID)
                        VALUES (
                        @ItemID,
                        @VisitaID
                        )";
        DataTable dt = new DataTable();
        string sqlDataSource = _configuration.GetConnectionString("connectionstring");
        SqlDataReader sqlDataReader;
        using (SqlConnection sqlConnection = new SqlConnection(sqlDataSource))
        {
            sqlConnection.Open();

            using (SqlCommand cmd = new SqlCommand(query, sqlConnection))
            {
                cmd.Parameters.AddWithValue(@"ItemID", visitaItemControl.ItemID);
                cmd.Parameters.AddWithValue(@"VisitaID", visitaItemControl.VisitaID);
                sqlDataReader = cmd.ExecuteReader();
                dt.Load(sqlDataReader);
                sqlDataReader.Close();
                sqlConnection.Close();
            }
        }

        return new JsonResult("Saved!");
    }
}

SignalR app:

    foreach (var item in addedItems)
    {
        var postObject = new VisitaItemControl() 
        {
        ItemID = item.ItemID,
        VisitaID = empObj.VisitaID,
        };
        var request2 = new HttpRequestMessage(HttpMethod.Post, config["API_POST"]);
        request2.Content = new StringContent(JsonSerializer.Serialize(postObject), null, "application/json");
        var client2 = ClientFactory.CreateClient();
        var response2 = await client.SendAsync(request2);
        using var responseStream2 = await response2.Content.ReadAsStreamAsync();
        string res2 = await JsonSerializer.DeserializeAsync<string>(responseStream2);
    }

    await JS.InvokeVoidAsync("alert", "Saved!");
    await refreshList();
    uriHelper.NavigateTo("/", forceLoad: true);
}

Solution

Here's the basics of a structured approach to what you're trying to do.

I've used Entity Framework to manage the database and the InMemory Implemnentation for demo purposes. I've implemented everything in a Blazor Server project so we can test and manage the data in the UI and use Postman for interacting with the API.

Project Packages:

<ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.Components.Web" Version="6.0.4" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.4" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.4">
        <PrivateAssets>all</PrivateAssets>
        <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="6.0.4" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.4" />
    <PackageReference Include="System.Net.Http.Json" Version="6.0.0" />
</ItemGroup>

My InMemory Db Context:

public class InMemoryDbContext : DbContext
{
    public DbSet<VisitaItemControl>? VisitaItemControl { get; set; }
    public InMemoryDbContext(DbContextOptions<InMemoryDbContext> options) : base(options) { }
}

We'll use a DBContextFactory service to manage the DB connections that will use this as it's source DbContext.

My Data Broker Interface - this will normally implement all the CRUD processes. We use an interface to decouple the application from the data store.

public interface IDataBroker
{
    public ValueTask<bool> AddItems<TRecord>(IEnumerable<TRecord> items) where TRecord : class;
    public ValueTask<IEnumerable<TRecord>> GetItems<TRecord>(int count) where TRecord : class;
}

And my Server implementation - note I inject the DbContextFactory to manage my database connections.

public class ServerDataBroker : IDataBroker
{
    private readonly IDbContextFactory<InMemoryDbContext> database;
    
    public ServerDataBroker(IDbContextFactory<InMemoryDbContext> db)
        => this.database = db;

    public async ValueTask<bool> AddItems<TRecord>(IEnumerable<TRecord> items) where TRecord : class
    {
        var result = false;

        using var dbContext = database.CreateDbContext();
        foreach (var item in items)
            dbContext.Add(item);

        var rowsAdded = await dbContext.SaveChangesAsync();
        if (rowsAdded == items.Count())
            result = true;

        //  Do something if not all rows are added
        return result;
    }

    public async ValueTask<IEnumerable<TRecord>> GetItems<TRecord>(int count) where TRecord : class
    {
        using var dbContext = database.CreateDbContext();
        return await dbContext.Set<TRecord>()
            .Take(count)
            .ToListAsync();
    }
}

For the UI I've built a very simple View Service to hold and manage the data:

public class VisitaItemControlService
{
    private IDataBroker _broker;

    public event EventHandler? ListUpdated;
    public IEnumerable<VisitaItemControl> Records { get; protected set; } = new List<VisitaItemControl>();

    public VisitaItemControlService(IDataBroker dataBroker)
        => _broker = dataBroker;

    public async ValueTask<bool> AddItems(IEnumerable<VisitaItemControl> items)
    {
        var result = await _broker.AddItems<VisitaItemControl>(items);

        if (result)
        {
            await this.GetItems(1000);
            this.ListUpdated?.Invoke(this, EventArgs.Empty);
        }

        return result;
    }

    public async ValueTask GetItems(int count)
        => this.Records = await _broker.GetItems<VisitaItemControl>(count);
}

And here's my Index page to test the system.

@page "/"
@inject VisitaItemControlService service;
<PageTitle>Index</PageTitle>

<h1>Hello, world!</h1>
<div>
    <button class="btn btn-primary" @onclick=AddItems>Add Some Items</button>
</div>

@if (loaded)
{
    @foreach (var item in this.service.Records)
    {
        <div  class="p-2">
            <span>
                Item : @item.ItemID
            </span>
            <span>
                Visita : @item.VisitaID
            </span>
        </div>
    }
}

@code {
    private bool loaded = false;

    protected async override Task OnInitializedAsync()
    {
        await this.service.GetItems(1000);
        this.service.ListUpdated += this.OnListUpdated;
        this.loaded = true;
    }

    private async Task AddItems()
    {
        var addList = new List<VisitaItemControl> {
        new VisitaItemControl { ItemID = Guid.NewGuid().ToString(), VisitaID = "AA" },
        new VisitaItemControl { ItemID = Guid.NewGuid().ToString(), VisitaID = "BB" },
        new VisitaItemControl { ItemID = Guid.NewGuid().ToString(), VisitaID = "CC" }
        };

        await this.service.AddItems(addList);
    }

    private void OnListUpdated(object? sender, EventArgs e)
        => this.InvokeAsync(StateHasChanged);
}

Note the use of events to notify the UI that the list has changed and trigger a re-render.

Here's my API controller:

[ApiController]
public class VisitaItemControlController : ControllerBase
{
    private IDataBroker _dataBroker;

    public VisitaItemControlController(IDataBroker dataBroker)
        => _dataBroker = dataBroker;

    [Route("/api/[controller]/list")]
    [HttpGet]
    public async Task<IActionResult> GetRecordsAsync()
    {
        var list = await _dataBroker.GetItems<VisitaItemControl>(1000);
        return Ok(list);
    }

    [Route("/api/[controller]/addlist")]
    [HttpPost]
    public async Task<bool> AddRecordsAsync([FromBody] IEnumerable<VisitaItemControl> records)
        => await _dataBroker.AddItems(records);
}

And finally Program to configure all the services and middleware.

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllersWithViews();
builder.Services.AddRazorPages();
builder.Services.AddServerSideBlazor();
builder.Services.AddDbContextFactory<InMemoryDbContext>(options => options.UseInMemoryDatabase("TestDb"));
builder.Services.AddSingleton<IDataBroker, ServerDataBroker>();
builder.Services.AddScoped<VisitaItemControlService>();

builder.Services.AddSingleton<WeatherForecastService>();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}

app.UseHttpsRedirection();

app.UseStaticFiles();

app.UseRouting();

app.MapControllers();
app.MapBlazorHub();
app.MapFallbackToPage("/_Host");

app.Run();

Some postman screen captures:

And the project structure: