如何在ASP.NET Core MVC中使用ADO.NET向数据库上传多张图片

1. 数据库设计 #

需要拆分出两个关联表：主实体表（以书籍为例）和图片关联表，通过外键建立一对多关系：

CREATE TABLE Books (
    BookId INT PRIMARY KEY IDENTITY(1,1),
    Title NVARCHAR(100) NOT NULL,
    Author NVARCHAR(50) NOT NULL
    -- 其他实体属性按需添加
)

CREATE TABLE BookImages (
    ImageId INT PRIMARY KEY IDENTITY(1,1),
    BookId INT NOT NULL,
    ImagePath NVARCHAR(255) NOT NULL -- 优先存图片路径，比二进制更高效
    -- 若需存储二进制：ImageData VARBINARY(MAX) NOT NULL, ImageContentType NVARCHAR(50)
    FOREIGN KEY (BookId) REFERENCES Books(BookId) ON DELETE CASCADE
)

2. 创建对应模型类 #

// 主实体模型
public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }
    // 关联的图片集合
    public List<BookImage> BookImages { get; set; } = new List<BookImage>();
}

// 图片关联模型
public class BookImage
{
    public int ImageId { get; set; }
    public int BookId { get; set; }
    public string ImagePath { get; set; }
    // 二进制存储用以下字段：
    // public byte[] ImageData { get; set; }
    // public string ImageContentType { get; set; }
}

3. 编写多文件上传表单（视图） #

注意必须添加enctype="multipart/form-data"才能支持文件上传：

@model Book

<form asp-action="Create" enctype="multipart/form-data" method="post">
    <div class="form-group">
        <label asp-for="Title"></label>
        <input asp-for="Title" class="form-control" />
    </div>
    <div class="form-group">
        <label asp-for="Author"></label>
        <input asp-for="Author" class="form-control" />
    </div>
    <div class="form-group">
        <label>上传书籍配图</label>
        <input type="file" name="bookImages" multiple accept="image/*" class="form-control-file" />
    </div>
    <button type="submit" class="btn btn-primary">提交</button>
</form>

4. 控制器处理逻辑（ADO.NET实现） #

注入IWebHostEnvironment获取wwwroot路径，先插入主实体，再批量插入关联图片：

public class BooksController : Controller
{
    private readonly string _connectionString;
    private readonly IWebHostEnvironment _webHostEnv;

    public BooksController(IConfiguration config, IWebHostEnvironment webHostEnv)
    {
        _connectionString = config.GetConnectionString("DefaultConnection");
        _webHostEnv = webHostEnv;
    }

    [HttpPost]
    public IActionResult Create(Book book, IFormFile[] bookImages)
    {
        if (!ModelState.IsValid)
        {
            return View(book);
        }

        // 1. 插入主实体，获取自动生成的ID
        int bookId;
        using (var conn = new SqlConnection(_connectionString))
        {
            conn.Open();
            var sql = "INSERT INTO Books (Title, Author) OUTPUT INSERTED.BookId VALUES (@Title, @Author)";
            using (var cmd = new SqlCommand(sql, conn))
            {
                cmd.Parameters.AddWithValue("@Title", book.Title);
                cmd.Parameters.AddWithValue("@Author", book.Author);
                bookId = (int)cmd.ExecuteScalar();
            }
        }

        // 2. 处理并插入图片（如果有上传文件）
        if (bookImages != null && bookImages.Length > 0)
        {
            var uploadDir = Path.Combine(_webHostEnv.WebRootPath, "images", "books");
            Directory.CreateDirectory(uploadDir); // 确保目录存在

            foreach (var file in bookImages)
            {
                if (file.Length <= 0) continue;

                // 生成唯一文件名避免冲突
                var fileName = $"{Guid.NewGuid()}{Path.GetExtension(file.FileName)}";
                var savePath = Path.Combine(uploadDir, fileName);

                // 保存文件到wwwroot
                using (var stream = new FileStream(savePath, FileMode.Create))
                {
                    file.CopyTo(stream);
                }

                // 插入图片路径到关联表
                using (var conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    var sql = "INSERT INTO BookImages (BookId, ImagePath) VALUES (@BookId, @ImagePath)";
                    using (var cmd = new SqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@BookId", bookId);
                        cmd.Parameters.AddWithValue("@ImagePath", $"/images/books/{fileName}"); // 存相对路径
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

        return RedirectToAction(nameof(Index));
    }

    // 示例：查询单实体及其所有关联图片
    public IActionResult Details(int id)
    {
        Book book = null;
        using (var conn = new SqlConnection(_connectionString))
        {
            conn.Open();
            // 查询主实体
            var bookSql = "SELECT * FROM Books WHERE BookId = @BookId";
            using (var cmd = new SqlCommand(bookSql, conn))
            {
                cmd.Parameters.AddWithValue("@BookId", id);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        book = new Book
                        {
                            BookId = (int)reader["BookId"],
                            Title = reader["Title"].ToString(),
                            Author = reader["Author"].ToString()
                        };
                    }
                }
            }

            // 查询关联图片
            if (book != null)
            {
                var imagesSql = "SELECT * FROM BookImages WHERE BookId = @BookId";
                using (var cmd = new SqlCommand(imagesSql, conn))
                {
                    cmd.Parameters.AddWithValue("@BookId", id);
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            book.BookImages.Add(new BookImage
                            {
                                ImageId = (int)reader["ImageId"],
                                BookId = (int)reader["BookId"],
                                ImagePath = reader["ImagePath"].ToString()
                            });
                        }
                    }
                }
            }
        }

        return View(book);
    }
}

5. 视图展示关联图片（Details视图） #

@model Book

<h1>@Model.Title</h1>
<p>作者：@Model.Author</p>

<h3>书籍配图</h3>
@if (Model.BookImages.Any())
{
    <div class="row">
        @foreach (var image in Model.BookImages)
        {
            <div class="col-md-3 mb-3">
                <img src="@image.ImagePath" alt="书籍配图" class="img-fluid rounded" />
            </div>
        }
    </div>
}
else
{
    <p class="text-muted">暂无配图</p>
}

关键注意事项 #

• 文件存储策略：优先将图片存到wwwroot目录，仅存储路径到数据库，比二进制存储更节省数据库空间，且更利于CDN缓存和前端加载。
• 验证与异常处理：实际项目中需添加文件类型、大小验证，以及数据库操作、文件写入的异常捕获与回滚逻辑。
• 文件名唯一性：用Guid生成文件名避免同名文件覆盖问题。

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