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:
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.