C# .NET MAUI中SQLite删除创建对象时索引越界问题求助

相关代码 #

MainPage.xaml.cs #

public partial class MainPage : ContentPage
{
    public static List<Term> termList = new List<Term>();
    public static string dbLocation = Path.Combine(FileSystem.AppDataDirectory, "MyData.db");
    public MainPage()
    {
        File.Delete(dbLocation);
        dbActions.initiateDbTables();
        InitializeComponent();
        db_refresh();
        generateInterface();
    }
    protected override void OnAppearing()
    {
        AppShell.SetBackgroundColor(this, Color.FromRgb(0, 0, 0));
        db_refresh();
        generateInterface();
    }
    public static void db_refresh()
    {
        termList.Clear();
        var dataBase = new SQLiteConnection(dbLocation);
        var queriedTerm = dataBase.Query<Term>("SELECT * FROM Terms");
        foreach (Term eachTerm in queriedTerm)
        {
           termList.Add(eachTerm);
        }
    }
    public void generateInterface()
    {

        termView.Children.Clear();

        foreach (Term myTerm in termList)
        {
            Button termNameButton = new Button
            {
                Text = myTerm.termName,
                BackgroundColor = Colors.Green,
                TextColor = Colors.White,
                FontAttributes = FontAttributes.Bold,
            };
            termNameButton.Clicked += async (sender, e) =>
            {
                await Navigation.PushAsync(new TermPage(myTerm.termId));
            };
            termView.Children.Add(termNameButton);
        }

        Button termAddButton = new Button()
        {
            Text = "Add Term",
            BackgroundColor = Colors.Black,
            TextColor = Colors.White,
            FontAttributes = FontAttributes.Bold,
        };
        termAddButton.Clicked += void (sender, args) => clickNewTerm();
        termView.Children.Add(termAddButton);
    }
    public void clickNewTerm()
    {
        dbActions.addNewDbTerm();
        generateInterface();
    }
}

TermPage相关代码 #

public Term currentTerm;
public SQLiteConnection dbConnect = new SQLiteConnection(MainPage.dbLocation);
public TermPage(int termId)
{
    InitializeComponent();
    ButtonAdd();
    Term myTerm = MainPage.termList[termId - 1];
    currentTerm = myTerm;
    termName.Text = currentTerm.termName;
}
private void ButtonAdd()
{
    Button deleteTermButton = new Button()
    {
        Text = "Delete Term",
        BackgroundColor = Colors.Red,
        FontAttributes = FontAttributes.Bold,
    };
    deleteTermButton.Clicked += void (sender, args) => actionDeleteTerm();
    TermPageView.Children.Add(deleteTermButton);
}
public async void actionDeleteTerm()
{
    dbConnect.Delete(currentTerm);
    MainPage.db_refresh();
    await Navigation.PopAsync();
}

dbActions类代码 #

public class dbActions
{
    public static void initiateDbTables()
    {
        var dbLocation = Path.Combine(FileSystem.AppDataDirectory, "MyData.db");
        var dbConnect = new SQLiteConnection(dbLocation);
        dbConnect.CreateTable<Term>();
    }
    public static void addNewDbTerm()
    {
        var dbConnect = new SQLiteConnection(MainPage.dbLocation);
        var dbQuery = dbConnect.Query<Term>($"SELECT * FROM Terms ORDER BY termId DESC LIMIT 1");
        if (dbQuery.Count != 0)
        {
            Term topTerm = dbQuery.First();
            string termName = "Term " + (topTerm.termId + 1).ToString();
            Term newTerm = new Term(termName, DateTime.Now, DateTime.Now.AddDays(60));
            dbConnect.Insert(newTerm);
            MainPage.db_refresh();
        }
        else
        {
            Term newTerm = new Term("Term1", DateTime.Now, DateTime.Now.AddDays(60));
            dbConnect.Insert(newTerm);
            MainPage.db_refresh();
        }
    }
}

核心问题根源 #

问题不在db_refresh方法，而是错误地将SQLite主键termId当作列表索引使用：

• SQLite的主键termId是自增的，删除数据后不会重新编号（比如删除termId=2的记录后，新添加的记录termId会是4）。
• termList是查询结果的列表，索引是连续的0、1、2...，长度等于数据库中现有记录数。
• 当删除中间记录后，新记录的termId会远大于termList.Count-1，此时用termId-1作为索引访问termList必然触发索引越界。

修复方案 #

方案1：直接传递Term对象（推荐） #

彻底避免索引依赖，跳转时直接传入Term对象：

1. 修改MainPage中按钮点击事件：

termNameButton.Clicked += async (sender, e) =>
{
    await Navigation.PushAsync(new TermPage(myTerm));
};

2. 修改TermPage的构造函数：

public Term currentTerm;
public SQLiteConnection dbConnect = new SQLiteConnection(MainPage.dbLocation);
public TermPage(Term term)
{
    InitializeComponent();
    ButtonAdd();
    currentTerm = term;
    termName.Text = currentTerm.termName;
}

方案2：通过TermId查找对象（兼容现有逻辑） #

如果必须使用TermId，改用LINQ查找而非索引访问，同时处理对象不存在的情况：
修改TermPage构造函数：

public TermPage(int termId)
{
    InitializeComponent();
    ButtonAdd();
    Term myTerm = MainPage.termList.FirstOrDefault(t => t.termId == termId);
    if (myTerm == null)
    {
        // 处理对象不存在的异常
        Device.BeginInvokeOnMainThread(async () => 
        {
            await DisplayAlert("错误", "找不到指定的学期记录", "确定");
            await Navigation.PopAsync();
        });
        return;
    }
    currentTerm = myTerm;
    termName.Text = currentTerm.termName;
}

方案3：优化db_refresh方法 #

简化列表刷新逻辑，无需手动循环添加：

public static void db_refresh()
{
    using(var dataBase = new SQLiteConnection(dbLocation))
    {
        // 直接赋值查询结果，替代Clear+循环
        termList = dataBase.Table<Term>().ToList();
    }
}

方案4：规范数据库连接管理 #

使用using语句确保数据库连接及时释放，避免资源泄漏：

// 示例：修改dbActions的addNewDbTerm方法
public static void addNewDbTerm()
{
    using(var dbConnect = new SQLiteConnection(MainPage.dbLocation))
    {
        var dbQuery = dbConnect.Table<Term>().OrderByDescending(t => t.termId).Take(1).ToList();
        if (dbQuery.Count != 0)
        {
            Term topTerm = dbQuery.First();
            string termName = "Term " + (topTerm.termId + 1).ToString();
            Term newTerm = new Term(termName, DateTime.Now, DateTime.Now.AddDays(60));
            dbConnect.Insert(newTerm);
        }
        else
        {
            Term newTerm = new Term("Term1", DateTime.Now, DateTime.Now.AddDays(60));
            dbConnect.Insert(newTerm);
        }
        MainPage.db_refresh();
    }
}

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