locked
C# - How to update a SQLIte db using LINQ

    Question

  • I have a Win Store App that is using a SQLite DB to store orders. I have a listview that displays the order details, when I click on the item in the list view, the info is stored in text boxes. I then need to update a qty and push a button that will update the order line. Here is my button lick code:

    private void btnUpdateItem_Click(object sender, RoutedEventArgs e)
            {
                var orderPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "orders.db");
    
                var updateOrdersDB = new SQLite.SQLiteConnection(orderPath);
    
                var updateItemQuery = (from c in updateOrdersDB.Table<orderDetails>()
                                       where c.itemSeq == strItemSeq
                                       select c);
    
                orderDetails ordd = new orderDetails();
    
                ordd.itemQty = tbxQty.Text.ToString();
    
                updateOrdersDB.BeginTransaction();
                updateOrdersDB.Update(ordd);
                updateOrdersDB.Commit();
    
                updateOrdersDB.Dispose();
                updateOrdersDB.Close();
    
                
                fillList();
    
            }
    When I run it, nothing is updated in the database, what am I doing wrong? 

    Friday, October 3, 2014 8:21 PM

Answers

  • Hi GarlingBeard,

    You created a new orderDetails object and edited the itemQty property. It would not update to sqlite. Use the FirstOrDefault method of updateItemQuery array to get the orderDetails instance from the array. Change the code as the following would fix the issue.

      

    var orderPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "orders.db");
    
                var updateOrdersDB = new SQLite.SQLiteConnection(orderPath);
    
                var updateItemQuery = (from c in updateOrdersDB.Table<orderDetails>()
    
                                       where c.itemSeq == strItemSeq
    
                                       select c);
    
                orderDetails ordd = updateItemQuery.FirstOrDefault();
    
                if (ordd!=null)
    
                {
    
                    ordd.itemQty = "";
    
                    updateOrdersDB.BeginTransaction();
    
                    updateOrdersDB.Update(ordd);
    
                    updateOrdersDB.Commit();
    
                }
    
                updateOrdersDB.Dispose();
    
                updateOrdersDB.Close();
    

    If the code snippet doesn’t work, please feel free to let me know.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 6, 2014 3:27 AM
    Moderator

All replies

  • Hi GarlingBeard,

    You created a new orderDetails object and edited the itemQty property. It would not update to sqlite. Use the FirstOrDefault method of updateItemQuery array to get the orderDetails instance from the array. Change the code as the following would fix the issue.

      

    var orderPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "orders.db");
    
                var updateOrdersDB = new SQLite.SQLiteConnection(orderPath);
    
                var updateItemQuery = (from c in updateOrdersDB.Table<orderDetails>()
    
                                       where c.itemSeq == strItemSeq
    
                                       select c);
    
                orderDetails ordd = updateItemQuery.FirstOrDefault();
    
                if (ordd!=null)
    
                {
    
                    ordd.itemQty = "";
    
                    updateOrdersDB.BeginTransaction();
    
                    updateOrdersDB.Update(ordd);
    
                    updateOrdersDB.Commit();
    
                }
    
                updateOrdersDB.Dispose();
    
                updateOrdersDB.Close();
    

    If the code snippet doesn’t work, please feel free to let me know.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 6, 2014 3:27 AM
    Moderator
  • Thanks, that worked. 
    Monday, October 13, 2014 6:56 PM