Sqlite Window Phone 8 中的更新记录

2021-09-26 00:00:00 sqlite xaml windows-phone-8

在我的数据库中，我有一个名为 question1 的表，初始默认状态分配为 = 0，然后每次我想更新时我都希望状态 = 1，我使用命令

In my database I have table named question1, with the initial default status assigned = 0, then every time I want to update I want the status = 1, I use the command

await conn.ExecuteAsync("UPDATE question1 SET Status = '1' WHERE id =" + ques1.id);

但它不起作用.谁知道这里出了什么问题?下面是读取数据和更新表的代码......

but it is not working. Who knows what is wrong here? Here's the code to read the data and update the table......

public async Task<question1> get_data_question1()
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection("Database");
    var query = conn.Table<question1>().Where(x => x.Status == 0);
    var result = await query.ToListAsync();
    question1 ques1 = new question1();
    foreach (var item in result)
    {
        ques1.id = item.id;
        ques1.Status = item.Status;
    }
    // Update
    await conn.ExecuteAsync("UPDATE question1 SET Status = '1' WHERE id =" + ques1.id);
    return ques1;
}

推荐答案

这就是我设置简单数据库的方式.

This is how I would set up your simple database.

它将创建 3 个条目，所有状态 = 0.

It will create 3 entries with all Status = 0.

在代码中我说的那一行处设置一个断点；

Put a break point at the line where I said so in the code;

您会在该查询之后看到所有 0 值都已更新为 1.

You will see after that query all 0 values has been updated to 1.

using SQLite;
using System.IO;
using System.IO.IsolatedStorage;
using System.Threading.Tasks;

public class Question
{
    [SQLite.PrimaryKey, SQLite.AutoIncrement]
    public int Id { get; set; }
    public int Status { get; set; }
} 

public partial class MainPage : PhoneApplicationPage
{
    string dbPath = "";
    public MainPage()
    {
        InitializeComponent();
        dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");                        

        CreateDBTable();
        InsertDB();         // only call this once (comment it out the next time you deploy)
        UpdateDB();

    }
}

// from the MSDN example
private async Task<bool> FileExists(string fileName)
{
    var result = false;
    try
    {
        var store = await Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(fileName);
        result = true;
    }
    catch
    {
    }
    return result;
}

public void CreateDBTable()
{
    if (!FileExists("db.sqlite").Result)
    {
        using (var db = new SQLiteConnection(dbPath))
        {
            db.CreateTable<Question>();
        }
    } 
}

public void UpdateDB()
{
    using (var db = new SQLiteConnection(dbPath))
    {
        var existing = db.Query<Question>("SELECT * FROM Question WHERE Status = 0");
        if (existing != null)
        {
            foreach(Question q in existing)
            {
                db.Execute("UPDATE Question SET Status = 1 WHERE Id = ?", q.Id);
            }
        }


        // query again to see if has changed
        existing.Clear();

        existing = db.Query<Question>("SELECT * FROM Question WHERE Status = 1");
        if (existing != null)
        {
            int breakpoint_here = 1;
        }

    } 
}

private void InsertDB()
{
    using (var db = new SQLiteConnection(dbPath))
    {
        db.RunInTransaction(() =>
        {
            db.Insert(new Question() { Status = 0 });
            db.Insert(new Question() { Status = 0 });
            db.Insert(new Question() { Status = 0 });
        });
    }
}

相关文章