WinForms项目中如何遍历SQLite行，加载用户书籍至FlowLayoutPanel？

我正在开发一个模仿图书馆的WinForms项目，用户登录后可创建“书籍”（实为存储信息的自定义bookUserControl），并将其信息存入SQLite数据库，随后在FlowLayoutPanel中展示。程序重启后这些“书籍”控件会消失，需要在窗体加载时，将该用户的所有“书籍”从数据库取出并重新展示。

我的书籍数据库表包含创建者username字段，尝试用foreach遍历表（以username为关键字）逐行获取数据生成bookUserControl，代码如下：

using (IDbConnection cnn = new SQLiteConnection(SqliteDataAccess.loadConnectionString()))
{
    foreach(string username in booksTable)
    {
        bookUserControl createNewBook = new bookUserControl();

        createNewBook.guid = cnn.QuerySingle<String>("SELECT guid FROM booksTable WHERE username = " + username);
        createNewBook.username = username;
        createNewBook.title = cnn.QuerySingle<String>("SELECT title FROM booksTable WHERE username = " + username);
        createNewBook.author = cnn.QuerySingle<String>("SELECT author FROM booksTable WHERE username = " + username);
        createNewBook.date = cnn.QuerySingle<String>("SELECT date FROM booksTable WHERE username = " + username);
        createNewBook.desc = cnn.QuerySingle<String>("SELECT desc FROM booksTable WHERE username = " + username);
        createNewBook.photo = byteToImage(cnn.QuerySingle<byte[]>("SELECT image FROM booksTable WHERE username = " + username);); 
        //image is stored as an array of bytes so I need to convert it from byte[] to Image

        //creates the "book" inside the flowLayoutPanel
        flowLayoutPanel1.Controls.Add(createNewBook);
    }
}

但不知道如何将数据库表作为foreach的参数，网上大多使用List等类型，不符合需求，求更好的实现方法。

1. 定义Book实体类 #

先创建一个和数据库表字段对应的实体类，方便ORM直接映射数据：

public class Book
{
    public string Guid { get; set; }
    public string Username { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }
    public string Date { get; set; }
    public string Desc { get; set; }
    public byte[] Image { get; set; }
}

2. 一次性查询并生成控件 #

使用参数化查询（避免SQL注入），一次性获取当前登录用户的所有书籍记录，再遍历生成控件：

// 假设当前登录用户的用户名存储在currentUser变量中
string currentUser = "当前登录的用户名";

using (IDbConnection cnn = new SQLiteConnection(SqliteDataAccess.loadConnectionString()))
{
    // 参数化查询，防止SQL注入风险
    string query = "SELECT guid, username, title, author, date, desc, image FROM booksTable WHERE username = @Username";
    List<Book> userBooks = cnn.Query<Book>(query, new { Username = currentUser }).ToList();

    // 遍历查询到的书籍列表，逐个生成控件
    foreach (var book in userBooks)
    {
        bookUserControl newBookCtrl = new bookUserControl();
        newBookCtrl.guid = book.Guid;
        newBookCtrl.username = book.Username;
        newBookCtrl.title = book.Title;
        newBookCtrl.author = book.Author;
        newBookCtrl.date = book.Date;
        newBookCtrl.desc = book.Desc;
        newBookCtrl.photo = byteToImage(book.Image);

        flowLayoutPanel1.Controls.Add(newBookCtrl);
    }
}

关键改进点 #

• 减少数据库交互：原来的代码每个字段单独查询一次数据库，现在一次查询所有字段，大幅提升运行效率。
• 避免SQL注入：用@Username占位符代替字符串拼接，彻底规避注入风险。
• 简化数据映射：通过cnn.Query<Book>直接将数据库记录转为实体对象，代码更简洁易维护。
• 逻辑修正：不再错误遍历username，而是遍历当前用户的所有书籍实体，符合一个用户多本书的实际场景。

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