How to Update row in sqlite.net pcl in windows 10 C#?

Question

I have ID of Row then I will update other values.

I don't know how to update my values! My Table:

 class MyTable
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public string Date { get; set; }
    public string Volumes { get; set; }
    public string Price { get; set; }
}

other information:

 string path;
    SQLite.Net.SQLiteConnection conn;

    public updatepage()
    {
        this.InitializeComponent();
        path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "ccdb.sqlite");

        conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);

        conn.CreateTable<MyTable>();
    }

Inception

Please reference this article blog.tpcware.com/2014/05/universal-app-with-sqlite-part-2 though it says to Universal app but i think it works with windows 10 app too. – Caltrop

Answer 1

I recently started working with UWP apps, and also came across this problem. So, how to update a row using SQLite in UWP you ask? Here ya go!

using (var dbConn = new SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
    {
        var existingUser = dbConn.Query<User>("select * from User where Id = ?", user.Id).FirstOrDefault();
        if (existingUser != null)
        {
            existingUser.Name = user.Name;
            existingUser.Email = user.Email;
            existingUser.Username = user.Username;
            existingUser.Surname = user.Surname;
            existingUser.EmployeeNumber = user.EmployeeNumber;
            existingUser.Password = user.Password;
            dbConn.RunInTransaction(() =>
            {
                dbConn.Update(existingUser);
            });
        }
    }

The App.DB_PATH is the same as your 'path' variable. I realize that there are many different areas of interest in this answer, so if you have any further questions, feel free to ask.

Elaelaborate

You can also use LinQ to get an existing record from db: var existingUser= await db.Table<User>().FirstOrDefaultAsync(u => u.Id == id); – Octane

Answer 2

To only update a specific set of values in a row, then executing a raw SQL query would be simpler:

conn.Execute("UPDATE MyTable SET Price = ? Where Id = ?", 1000000, 2);

This assumes the input you are passing to the execute statement has been cleaned.

Holmberg

then immediately I need to update this to viewmodel object? any best way? – Kurtzman