ASP.NET咖啡馆库存录入Web应用：如何将浏览器数据存入SQL Server

Hey there! Let's break down how to save your browser-side inventory data to SQL Server in your ASP.NET café app, based on your table structure. I'll walk you through the core steps, including handling the relationships between Item, Stock_Take, and Stock_Take_Item.

First, create entity classes that map directly to your SQL Server tables. This will let EF Core handle database interactions seamlessly—this is the most common approach in ASP.NET these days, so I’ll base the answer on it.

// Item.cs
public class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Category { get; set; }
    // Add other product details (e.g., unit price, supplier) as needed

    // Navigation property for linked stock take entries
    public ICollection<Stock_Take_Item> StockTakeItems { get; set; } = new List<Stock_Take_Item>();
}

// Stock_Take.cs
public class Stock_Take
{
    public int Id { get; set; }
    public DateTime TakeDate { get; set; }

    // Navigation property for linked stock take items
    public ICollection<Stock_Take_Item> StockTakeItems { get; set; } = new List<Stock_Take_Item>();
}

// Stock_Take_Item.cs
public class Stock_Take_Item
{
    public int Id { get; set; }
    public int StockTakeId { get; set; } // Foreign key to Stock_Take
    public int ItemId { get; set; } // Foreign key to Item
    public int Quantity { get; set; } // Count from the inventory check

    // Navigation properties for relationship mapping
    public Stock_Take StockTake { get; set; }
    public Item Item { get; set; }
}

Create a DbContext class to manage your database connection and define table relationships explicitly with Fluent API.

public class CafeInventoryDbContext : DbContext
{
    public CafeInventoryDbContext(DbContextOptions<CafeInventoryDbContext> options) : base(options) { }

    public DbSet<Item> Items { get; set; }
    public DbSet<Stock_Take> StockTakes { get; set; }
    public DbSet<Stock_Take_Item> StockTakeItems { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure composite primary key for the join table
        modelBuilder.Entity<Stock_Take_Item>()
            .HasKey(sti => new { sti.StockTakeId, sti.ItemId });

        // Map one-to-many relationships between parent and join tables
        modelBuilder.Entity<Stock_Take_Item>()
            .HasOne(sti => sti.StockTake)
            .WithMany(st => st.StockTakeItems)
            .HasForeignKey(sti => sti.StockTakeId);

        modelBuilder.Entity<Stock_Take_Item>()
            .HasOne(sti => sti.Item)
            .WithMany(i => i.StockTakeItems)
            .HasForeignKey(sti => sti.ItemId);
    }
}

Don’t forget to register the context in Program.cs with your SQL Server connection string:

builder.Services.AddDbContext<CafeInventoryDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("CafeInventoryDb")));

Use a view model to safely collect user input from the browser (never pass database entities directly to the frontend):

public class StockTakeViewModel
{
    [Required(ErrorMessage = "Please select a stock take date")]
    public DateTime TakeDate { get; set; }

    [Required(ErrorMessage = "At least one item must be added")]
    public List<StockTakeItemViewModel> Items { get; set; } = new List<StockTakeItemViewModel>();
}

public class StockTakeItemViewModel
{
    [Required]
    public int ItemId { get; set; }
    public string ItemName { get; set; } // For display only

    [Required(ErrorMessage = "Quantity is required")]
    [Range(0, int.MaxValue, ErrorMessage = "Quantity must be a non-negative number")]
    public int Quantity { get; set; }
}

Create a Razor view to let users input stock take data. Here’s a simple example with dynamic item rows:

@model StockTakeViewModel

<h1>New Inventory Stock Take</h1>

<form asp-action="SaveStockTake" method="post">
    <div class="form-group">
        <label asp-for="TakeDate"></label>
        <input asp-for="TakeDate" type="date" class="form-control" />
        <span asp-validation-for="TakeDate" class="text-danger"></span>
    </div>

    <h3>Items</h3>
    <div id="itemsContainer">
        @foreach (var item in Model.Items)
        {
            <partial name="_StockTakeItemRow" model="item" />
        }
    </div>
    <button type="button" id="addItemBtn" class="btn btn-secondary mt-2">Add Item</button>

    <button type="submit" class="btn btn-primary mt-3">Save Stock Take</button>
</form>

<!-- Partial view _StockTakeItemRow.cshtml -->
@model StockTakeItemViewModel
<div class="item-row mb-2">
    <select asp-for="ItemId" class="form-control">
        @foreach (var item in ViewBag.Items)
        {
            <option value="@item.Id">@item.Name</option>
        }
    </select>
    <input asp-for="Quantity" type="number" class="form-control mt-1" />
    <span asp-validation-for="Quantity" class="text-danger"></span>
    <button type="button" class="btn btn-danger mt-1 removeItemBtn">Remove</button>
</div>

<script>
    // Add dynamic item rows
    document.getElementById('addItemBtn').addEventListener('click', function() {
        fetch('/StockTake/GetEmptyItemRow')
            .then(response => response.text())
            .then(html => {
                document.getElementById('itemsContainer').insertAdjacentHTML('beforeend', html);
            });
    });

    // Remove item rows
    document.addEventListener('click', function(e) {
        if (e.target.classList.contains('removeItemBtn')) {
            e.target.closest('.item-row').remove();
        }
    });
</script>

Create controller actions to receive the view model, map it to database entities, and save with transactions to ensure data integrity.

public class StockTakeController : Controller
{
    private readonly CafeInventoryDbContext _context;

    public StockTakeController(CafeInventoryDbContext context)
    {
        _context = context;
    }

    // GET: StockTake/Create
    public IActionResult Create()
    {
        // Pass existing items to the view for selection
        ViewBag.Items = _context.Items.ToList();
        return View(new StockTakeViewModel());
    }

    // POST: StockTake/SaveStockTake
    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> SaveStockTake(StockTakeViewModel viewModel)
    {
        if (!ModelState.IsValid)
        {
            ViewBag.Items = _context.Items.ToList();
            return View("Create", viewModel);
        }

        // Use transaction to ensure all data saves or none does
        using var transaction = await _context.Database.BeginTransactionAsync();

        try
        {
            // Create and save the main stock take entry first to get its ID
            var stockTake = new Stock_Take
            {
                TakeDate = viewModel.TakeDate
            };
            _context.StockTakes.Add(stockTake);
            await _context.SaveChangesAsync();

            // Add each item entry to the join table
            foreach (var itemVm in viewModel.Items)
            {
                var stockTakeItem = new Stock_Take_Item
                {
                    StockTakeId = stockTake.Id,
                    ItemId = itemVm.ItemId,
                    Quantity = itemVm.Quantity
                };
                _context.StockTakeItems.Add(stockTakeItem);
            }

            await _context.SaveChangesAsync();
            await transaction.CommitAsync();

            TempData["SuccessMessage"] = "Stock take saved successfully!";
            return RedirectToAction("Index");
        }
        catch (Exception ex)
        {
            await transaction.RollbackAsync();
            ModelState.AddModelError(string.Empty, $"Error saving stock take: {ex.Message}");
            ViewBag.Items = _context.Items.ToList();
            return View("Create", viewModel);
        }
    }

    // Helper action to get empty item row partial
    public IActionResult GetEmptyItemRow()
    {
        ViewBag.Items = _context.Items.ToList();
        return PartialView("_StockTakeItemRow", new StockTakeItemViewModel());
    }
}

• Validation: Add client-side validation (e.g., JavaScript) alongside server-side checks to catch errors early.
• Performance: For large inventories, use bulk insert libraries (like EF Core Bulk Extensions) instead of adding items one by one.
• Security: Use [ValidateAntiForgeryToken] on POST actions to prevent cross-site request forgery (CSRF) attacks.

内容的提问来源于stack exchange，提问作者randomnamegenerator12