How to update SQLite tables using datagrid selected rows in WPF using c# ?

Question

I have created a table in a SQLite database having 5 columns namely Id, Name, Address, Age, OtherData where Id is unique, non-null and auto incremented.

Now when I display the table data in a datagrid I only show Name, Address and Age.

So what I want to do is when the user of the app selects some row or rows of the datagrid and hits a button after selecting, then the OtherData column of each selected rows in the actual SQLite table would be updated by adding some text like

Processed on dd.MM.YYYY

.

How do I do this?

I'm using System.Data.Sqlite nuget package for doing CRUD in a simple WPF app using c# and .Net 4.5 without using MVVM pattern.

Accepted Answer

MainWindow.xaml:

MainWindow.xaml.cs:

using System;  
using System.Collections.Generic;  
using System.Collections.ObjectModel;  
using System.ComponentModel;  
using System.Data;  
using System.Data.SQLite;  
using System.Runtime.CompilerServices;  
using System.Windows;  
  
namespace SQLiteCRUDdemo  
{  
  public partial class MainWindow : Window, INotifyPropertyChanged  
  {  
    public MainWindow()  
    {  
      InitializeComponent();  
      CreateTable();  
      DataContext=this;  
    }  
    private DateTime myDate = DateTime.Now;  
    public DateTime MyDate  
    {  
      get { return myDate; }  
      set  
      {  
        myDate = value;  
        OnPropertyChanged("MyDate");  
      }  
    }  
    private DataRowView selectedUser;  
public DataRowView SelectedUser  
{  
  get { return selectedUser; }  
  set  
  {  
    if (selectedUser != value)  
    {  
      selectedUser = value;  
      OnPropertyChanged("SelectedUser");  
    }  
  }  
}  
private void Button_Click(object sender, RoutedEventArgs e)  
{  
  SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite");  
  m_dbConnection.Open();  
  int userid = Convert.ToInt32(((DataRowView)dg.SelectedItem).Row["Id"]);  
  User u = new User();  
  u.Id=userid;  
  u.OtherData = MyDate;  
  MessageBox.Show(userid.ToString());  
  EditUser(u);  
  SQLiteCommand sqlCom = new SQLiteCommand("Select * From MyData", m_dbConnection);  
  SQLiteDataAdapter sda = new SQLiteDataAdapter(sqlCom);  
  DataTable dt = new DataTable();  
  sda.Fill(dt);  
  if (dt.Rows.Count > 0)  
  {  
    dg1.ItemsSource = dt.DefaultView;  
  }  
  m_dbConnection.Close();  
}  
    public void CreateTable()  
    {  
      SQLiteConnection.CreateFile("MyDatabase.sqlite");  
      SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite");  
      m_dbConnection.Open();  
      string sql = "create table MyData ( Id INTEGER PRIMARY KEY AUTOINCREMENT, Name varchar(20),Address varchar(20), Age INTEGER ,OtherData TEXT   ) ";  
      SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);  
      command.ExecuteNonQuery();  
  
      ObservableCollection users = new ObservableCollection();  
      users.Add(new User() { Name = "user1", Age = 23, Address = "addr1", OtherData = DateTime.Now });  
      users.Add(new User() { Name = "user2", Age = 25, Address = "addr3", OtherData = DateTime.Now });  
      users.Add(new User() { Name = "user3", Age = 27, Address = "addr5", OtherData = DateTime.Now });  
      foreach (var user in users)  
      {  
        AddUser(user);  
      }  
      SQLiteCommand sqlCom = new SQLiteCommand("Select * From MyData", m_dbConnection);  
      SQLiteDataAdapter sda = new SQLiteDataAdapter(sqlCom);  
      DataTable dt = new DataTable();  
      sda.Fill(dt);  
      if (dt.Rows.Count > 0)  
      {  
        dg.ItemsSource = dt.DefaultView;  
      }  
      m_dbConnection.Close();  
    }  
    private int EditUser(User user)  
    {  
      const string query = "UPDATE MyData SET OtherData = @OtherData WHERE Id = @id";  
      var args = new Dictionary  
    {  
        {"@id", user.Id},  
        {"@OtherData", user.OtherData.ToString("dd.MM.yyyy")}  
    };  
      return ExecuteWrite(query, args);  
    }  
    private int AddUser(User user)  
    {  
      const string query = "INSERT INTO MyData(Name,Address, Age,OtherData) VALUES(@Name, @Address,@Age,@OtherData)";  
      var args = new Dictionary  
    {  
          {"@Name", user.Name},  
          {"@Address", user.Address},  
          {"@Age", user.Age},  
          {"@OtherData", user.OtherData.ToString("dd.MM.yyyy")},  
    };  
      return ExecuteWrite(query, args);  
    }  
    private int ExecuteWrite(string query, Dictionary args)  
    {  
      int numberOfRowsAffected;  
  
      using (var con = new SQLiteConnection("Data Source=MyDatabase.sqlite"))  
      {  
        con.Open();  
        using (var cmd = new SQLiteCommand(query, con))  
        {  
          foreach (var pair in args)  
          {  
            cmd.Parameters.AddWithValue(pair.Key, pair.Value);  
          }  
          numberOfRowsAffected = cmd.ExecuteNonQuery();  
        }  
        return numberOfRowsAffected;  
      }  
    }  
    public event PropertyChangedEventHandler PropertyChanged;  
    protected void OnPropertyChanged([CallerMemberName] string name = null)  
    {  
      PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(name));  
    }  
  }  
  public class User  
  {  
    public int Id { get; set; }  
    public string Name { get; set; }  
    public int Age { get; set; }  
    public string Address { get; set; }  
    public DateTime OtherData { get; set; }  
  }  
}

The result:

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

How to update SQLite tables using datagrid selected rows in WPF using c# ?

0 additional answers