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

don bradman 621 Reputation points
2022-04-27T15:38:58.183+00:00

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.

Windows Presentation Foundation
Windows Presentation Foundation
A part of the .NET Framework that provides a unified programming model for building line-of-business desktop applications on Windows.
2,710 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,317 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,624 questions
XAML
XAML
A language based on Extensible Markup Language (XML) that enables developers to specify a hierarchy of objects with a set of properties and logic.
789 questions
{count} votes

Accepted answer
  1. Hui Liu-MSFT 47,336 Reputation points Microsoft Vendor
    2022-04-29T09:01:51.64+00:00

    MainWindow.xaml:

       <StackPanel>  
            <DataGrid AutoGenerateColumns="False"  Height="200" Name="dg" Width="364"  
                      SelectedItem="{Binding Path=SelectedUser, Mode=TwoWay,UpdateSourceTrigger=PropertyChanged}"  
                     >  
                <DataGrid.Columns>  
                    <DataGridTextColumn Header="ID"  Binding="{Binding Id}"></DataGridTextColumn>  
                    <DataGridTextColumn Header="Name" Binding="{Binding Name}"></DataGridTextColumn>  
                    <DataGridTextColumn Header="Address"  Binding="{Binding Address}"></DataGridTextColumn>  
                    <DataGridTextColumn Header="Age"  Binding="{Binding Age}"></DataGridTextColumn>  
                </DataGrid.Columns>  
            </DataGrid>  
            <TextBox x:Name="tb" Text="{Binding MyDate,Mode=TwoWay,UpdateSourceTrigger=PropertyChanged ,StringFormat={}{0:MM/dd/yyyy}}" Height="40" Background="AliceBlue"/>  
            <Button Content="Button" Click="Button_Click"/>  
            <DataGrid AutoGenerateColumns="False" Height="200" Name="dg1" Width="364" >  
                <DataGrid.Columns>  
                    <DataGridTextColumn Header="ID"  Binding="{Binding Id}"></DataGridTextColumn>  
                    <DataGridTextColumn Header="Name" Binding="{Binding Name}"></DataGridTextColumn>  
                    <DataGridTextColumn Header="Address"  Binding="{Binding Address}"></DataGridTextColumn>  
                    <DataGridTextColumn Header="Age"  Binding="{Binding Age}"></DataGridTextColumn>  
                    <DataGridTextColumn Header="OtherData"  Binding="{Binding OtherData, Mode=TwoWay,UpdateSourceTrigger=PropertyChanged}"></DataGridTextColumn>  
                </DataGrid.Columns>  
            </DataGrid>  
        </StackPanel>  
    

    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<User> users = new ObservableCollection<User>();  
          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<string, object>  
        {  
            {"@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<string, object>  
        {  
              {"@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<string, object> 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:
    197724-11.gif


    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.

    0 comments No comments

0 additional answers

Sort by: Most helpful