I'm totally new to MVVM and I've been going through a lot of online posts regarding how do I implement CRUD operations and properly bind SQLite database data to WPF controls like Datagrid, Combobox, Textbox, Button etc. but struggling to find what I need. Also, for someone who is new to MVVM pattern it seems pretty intimidating to say the least.
Anyways, coming to the point, I've found this 
and the relevant files as follows:
StudentRepository.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using TestWpfMVVM.Model;
namespace TestWpfMVVM.DataAccess
{
public class StudentRepository
{
private StudentEntities studentContext = null;
public StudentRepository()
{
studentContext = new StudentEntities();
}
public Student Get(int id)
{
return studentContext.Students.Find(id);
}
public List<Student> GetAll()
{
return studentContext.Students.ToList();
}
public void AddStudent(Student student)
{
if (student != null)
{
studentContext.Students.Add(student);
studentContext.SaveChanges();
}
}
public void UpdateStudent(Student student)
{
var studentFind = this.Get(student.stdId);
if (studentFind != null)
{
studentFind.stdName = student.stdName;
studentFind.stdContact = student.stdContact;
studentFind.stdAge = student.stdAge;
studentFind.stdAddress = student.stdAddress;
studentContext.SaveChanges();
}
}
public void RemoveStudent(int id)
{
var studObj = studentContext.Students.Find(id);
if (studObj != null)
{
studentContext.Students.Remove(studObj);
studentContext.SaveChanges();
}
}
}
}
Student.cs
using System;
namespace TestWpfMVVM.Model
{
public class Student
{
public int stdId { get; set; }
public string stdName { get; set; }
public int stdAge { get; set; }
public string stdAddress { get; set; }
public string stdContact { get; set; }
}
}
StudentRecord.cs
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TestWpfMVVM.ViewModel;
namespace TestWpfMVVM.Model
{
public class StudentRecord : ViewModelBase
{
private int _id;
public int Id
{
get
{
return _id;
}
set
{
_id = value;
OnPropertyChanged("Id");
}
}
private string _name;
public string Name
{
get
{
return _name;
}
set
{
_name = value;
OnPropertyChanged("Name");
}
}
private int _age;
public int Age
{
get
{
return _age;
}
set
{
_age = value;
OnPropertyChanged("Age");
}
}
private string _address;
public string Address
{
get
{
return _address;
}
set
{
_address = value;
OnPropertyChanged("Address");
}
}
private string _contact;
public string Contact
{
get
{
return _contact;
}
set
{
_contact = value;
OnPropertyChanged("Contact");
}
}
private ObservableCollection<StudentRecord> _studentRecords;
public ObservableCollection<StudentRecord> StudentRecords
{
get
{
return _studentRecords;
}
set
{
_studentRecords = value;
OnPropertyChanged("StudentRecords");
}
}
}
}
RelayCommand.cs
using System;
using System.Windows.Input;
namespace TestWpfMVVM.ViewModel
{
public class RelayCommand : ICommand
{
private readonly Action<object> _execute;
private readonly Predicate<object> _canExecute;
public RelayCommand(Action<object> execute)
: this(execute, null)
{
}
public RelayCommand(Action<object> execute, Predicate<object> canExecute)
{
if (execute == null)
throw new ArgumentNullException("execute");
_execute = execute;
_canExecute = canExecute;
}
public bool CanExecute(object parameter)
{
return _canExecute == null ? true : _canExecute(parameter);
}
public event EventHandler CanExecuteChanged
{
add { CommandManager.RequerySuggested = value; }
remove { CommandManager.RequerySuggested -= value; }
}
public void Execute(object parameter)
{
_execute(parameter);
}
}
}
StudentViewModel.cs
using System;
using System.Collections.ObjectModel;
using System.Windows;
using System.Windows.Input;
using TestWpfMVVM.DataAccess;
using TestWpfMVVM.Model;
namespace TestWpfMVVM.ViewModel
{
public class StudentViewModel
{
private ICommand _saveCommand;
private ICommand _resetCommand;
private ICommand _editCommand;
private ICommand _deleteCommand;
private StudentRepository _repository;
private Student _studentEntity = null;
public StudentRecord StudentRecord { get; set; }
public ICommand ResetCommand
{
get
{
if (_resetCommand == null)
_resetCommand = new RelayCommand(param => ResetData(), null);
return _resetCommand;
}
}
public ICommand SaveCommand
{
get
{
if (_saveCommand == null)
_saveCommand = new RelayCommand(param => SaveData(), null);
return _saveCommand;
}
}
public ICommand EditCommand
{
get
{
if (_editCommand == null)
_editCommand = new RelayCommand(param => EditData((int)param), null);
return _editCommand;
}
}
public ICommand DeleteCommand
{
get
{
if (_deleteCommand == null)
_deleteCommand = new RelayCommand(param => DeleteStudent((int)param), null);
return _deleteCommand;
}
}
public StudentViewModel()
{
_studentEntity = new Student();
_repository = new StudentRepository();
StudentRecord = new StudentRecord();
GetAll();
}
public void ResetData()
{
StudentRecord.Name = string.Empty;
StudentRecord.Id = 0;
StudentRecord.Address = string.Empty;
StudentRecord.Contact = string.Empty;
StudentRecord.Age = 0;
}
public void DeleteStudent(int id)
{
if (MessageBox.Show("Confirm delete of this record?", "Student", MessageBoxButton.YesNo)
== MessageBoxResult.Yes)
{
try
{
_repository.RemoveStudent(id);
MessageBox.Show("Record successfully deleted.");
}
catch (Exception ex)
{
MessageBox.Show("Error occured while saving. " ex.InnerException);
}
finally
{
GetAll();
}
}
}
public void SaveData()
{
if (StudentRecord != null)
{
_studentEntity.stdName = StudentRecord.Name;
_studentEntity.stdAge = StudentRecord.Age;
_studentEntity.stdAddress = StudentRecord.Address;
_studentEntity.stdContact = StudentRecord.Contact;
try
{
if (StudentRecord.Id <= 0)
{
_repository.AddStudent(_studentEntity);
MessageBox.Show("New record successfully saved.");
}
else
{
_studentEntity.stdId = StudentRecord.Id;
_repository.UpdateStudent(_studentEntity);
MessageBox.Show("Record successfully updated.");
}
}
catch (Exception ex)
{
MessageBox.Show("Error occured while saving. " ex.InnerException);
}
finally
{
GetAll();
ResetData();
}
}
}
public void EditData(int id)
{
var model = _repository.Get(id);
StudentRecord.Id = model.stdId;
StudentRecord.Name = model.stdName;
StudentRecord.Age = (int)model.stdAge;
StudentRecord.Address = model.stdAddress;
StudentRecord.Contact = model.stdContact;
}
public void GetAll()
{
StudentRecord.StudentRecords = new ObservableCollection<StudentRecord>();
_repository.GetAll().ForEach(data => StudentRecord.StudentRecords.Add(new StudentRecord()
{
Id = data.stdId,
Name = data.stdName,
Address = data.stdAddress,
Age = Convert.ToInt32(data.stdAge),
Contact = data.stdContact
}));
}
}
}
ViewModelBase.cs
using System.ComponentModel;
namespace TestWpfMVVM.ViewModel
{
public class ViewModelBase : INotifyPropertyChanged
{
public event PropertyChangedEventHandler PropertyChanged;
protected void OnPropertyChanged(string propertyName)
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
}
}
MainWindow.xaml.cs
using System.Windows;
using TestWpfMVVM.ViewModel;
namespace TestWpfMVVM.View
{
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
this.DataContext = new StudentViewModel();
}
}
}
MainWindow.xaml
<Window x:Class="TestWpfMVVM.View.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:local="clr-namespace:TestWpfMVVM.View"
mc:Ignorable="d"
Title="MainWindow" Height="450" Width="800">
<StackPanel Orientation="Vertical">
<GroupBox Header="Student Form" Margin="10">
<Grid Height="150">
<Grid.RowDefinitions>
<RowDefinition Height="1*"/>
<RowDefinition Height="1*"/>
<RowDefinition Height="1*"/>
<RowDefinition Height="1*"/>
</Grid.RowDefinitions>
<Grid.ColumnDefinitions>
<ColumnDefinition Width="100"/>
<ColumnDefinition Width="*"/>
</Grid.ColumnDefinitions>
<Label Content="Name" HorizontalAlignment="Left"
VerticalContentAlignment="Center" Grid.Column="0" Grid.Row="0"/>
<TextBox Grid.Row="0" Grid.Column="1" x:Name="TextBoxName" Height="27"
Text="{Binding Path=StudentRecord.Name, Mode=TwoWay}" Margin="5" Width="300" HorizontalAlignment="Left"/>
<Label Content="Age" HorizontalAlignment="Left" VerticalContentAlignment="Center"
Grid.Row="1" Grid.Column="0"/>
<TextBox Grid.Row="1" Grid.Column="1" x:Name="TextBoxAge" Height="27"
Text="{Binding Path=StudentRecord.Age, Mode=TwoWay}" Margin="5" Width="70" HorizontalAlignment="Left"/>
<TextBlock Grid.Row="1" Grid.Column="1" x:Name="TextBlockId"
Visibility="Hidden" Text="{Binding Path=StudentRecord.Id, Mode=TwoWay}"/>
<Label Content="Address" HorizontalAlignment="Left" VerticalContentAlignment="Center"
Grid.Row="2" Grid.Column="0" />
<TextBox Grid.Row="2" Grid.Column="1" x:Name="TextBoxAddress" Height="27"
Text="{Binding Path=StudentRecord.Address, Mode=TwoWay}" Margin="5" Width="300" HorizontalAlignment="Left"/>
<Label Content="Contact" HorizontalAlignment="Left" VerticalContentAlignment="Center"
Grid.Row="3" Grid.Column="0" />
<TextBox Grid.Row="3" Grid.Column="1" x:Name="TextBoxContact" Height="27"
Text="{Binding Path=StudentRecord.Contact, Mode=TwoWay}" Margin="5" Width="300" HorizontalAlignment="Left"/>
</Grid>
</GroupBox>
<StackPanel Height="40" Orientation="Horizontal" HorizontalAlignment="Right">
<Button x:Name="ButtonSave" Content="Save" Height="30" Width="80"
Command="{Binding SaveCommand}"/>
<Button x:Name="ButtonCancel" Content="Cancel" Height="30" Width="80"
Command="{Binding ResetCommand}" Margin="5,0,10,0"/>
</StackPanel>
<StackPanel Height="210">
<DataGrid x:Name="DataGridStudents" AutoGenerateColumns="False"
ItemsSource="{Binding StudentRecord.StudentRecords}" CanUserAddRows="False" Height="200" Margin="10">
<DataGrid.Columns>
<DataGridTextColumn Header="Name" Binding="{Binding Path=Id}" Visibility="Hidden"/>
<DataGridTextColumn Header="Name" Binding="{Binding Path=Name}" Width="100" IsReadOnly="True"/>
<DataGridTextColumn Header="Age" Binding="{Binding Path=Age}" Width="50" IsReadOnly="True"/>
<DataGridTextColumn Header="Address" Binding="{Binding Path=Address}" Width="180" IsReadOnly="True"/>
<DataGridTextColumn Header="Contact" Binding="{Binding Path=Contact}" Width="125" IsReadOnly="True"/>
<DataGridTemplateColumn Width="50">
<DataGridTemplateColumn.CellTemplate>
<DataTemplate>
<Button Content="Select" x:Name="ButtonEdit" CommandParameter="{Binding Path=Id}"
Command="{Binding Path=DataContext.EditCommand,RelativeSource={RelativeSource FindAncestor,
AncestorType=Window}}"/>
</DataTemplate>
</DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>
<DataGridTemplateColumn Width="50">
<DataGridTemplateColumn.CellTemplate>
<DataTemplate>
<Button Content="Delete" x:Name="ButtonDelete" CommandParameter="{Binding Path=Id}"
Command="{Binding Path=DataContext.DeleteCommand, RelativeSource={RelativeSource FindAncestor,
AncestorType=Window}}"/>
</DataTemplate>
</DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>
</DataGrid.Columns>
</DataGrid>
</StackPanel>
</StackPanel>
</Window>
Now in the post, inside the Model folder, they added an ADO.NET Entity Data Model that connects to the Students table in the database and named it StudentModel while changing connectionstring name to StudentEntities.
But, I have a local SQLite database, how do I change that and what other things I need to change to make this app work. Currently I have only the error Error CS0246 The type or namespace name 'StudentEntities' could not be found (are you missing a using directive or an assembly reference?) which is understandable.
I know this may be a lot to ask but any help will be highly appreciated as I'm trying to make this work for quite some time now!
EDIT
After further checking I've updated StudentRepository as below
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using TestWpfMVVM.Model;
namespace TestWpfMVVM.DataAccess
{
public class StudentRepository
{
public void ExecuteWrite(string query, Dictionary<string, object> args)
{
//setup the connection to the database
using (var con = new SQLiteConnection(@"Data Source=./Students.db;"))
{
con.Open();
//open a new command
using (var cmd = new SQLiteCommand(query, con))
{
//set the arguments given in the query
foreach (var pair in args)
{
cmd.Parameters.AddWithValue(pair.Key, pair.Value);
}
cmd.ExecuteNonQuery();
}
}
}
public DataTable Execute(string query, Dictionary<string, object> args)
{
if (string.IsNullOrEmpty(query.Trim()))
return null;
using (var con = new SQLiteConnection(@"Data Source=./Students.db;"))
{
con.Open();
using (var cmd = new SQLiteCommand(query, con))
{
foreach (KeyValuePair<string, object> entry in args)
{
cmd.Parameters.AddWithValue(entry.Key, entry.Value);
}
var da = new SQLiteDataAdapter(cmd);
var dt = new DataTable();
da.Fill(dt);
da.Dispose();
return dt;
}
}
}
public void AddStudent(Student student)
{
const string query = "INSERT INTO tabStud(Name, Age, Address, Contact) VALUES(@stdName, @stdAge, @stdAddress, @stdContact)";
var args = new Dictionary<string, object>
{
{"@stdName", student.stdName},
{"@stdAge", student.stdAge},
{"@stdAddress", student.stdAddress},
{"@stdContact", student.stdContact}
};
ExecuteWrite(query, args);
}
public void UpdateStudent(Student student)
{
const string query = "UPDATE tabStud SET Name = @stdName, Age = @stdAge, Address = @stdAddress, Contact = @stdContact WHERE Id = @stdId";
var args = new Dictionary<string, object>
{
{"@stdId", student.stdId},
{"@stdName", student.stdName},
{"@stdAge", student.stdAge},
{"@stdAddress", student.stdAddress},
{"@stdContact", student.stdContact}
};
ExecuteWrite(query, args);
}
public void RemoveStudent(Student student)
{
const string query = "Delete from tabStud WHERE Id = @stdId";
var args = new Dictionary<string, object>
{
{"@stdId", student.stdId}
};
ExecuteWrite(query, args);
}
public Student Get(int id)
{
var student = new Student();
var query = "SELECT * FROM tabStud WHERE Id = @stdId";
var args = new Dictionary<string, object>
{
{"@stdId", student.stdId}
};
DataTable dt = Execute(query, args);
if (dt == null || dt.Rows.Count == 0)
{
return null;
}
student = new Student
{
stdId = Convert.ToInt32(dt.Rows[0]["Id"]),
stdName = Convert.ToString(dt.Rows[0]["Name"]),
stdAge = Convert.ToInt32(dt.Rows[0]["Age"]),
stdAddress = Convert.ToString(dt.Rows[0]["Address"]),
stdContact = Convert.ToString(dt.Rows[0]["Contact"])
};
return student;
}
public List<Student> GetAll()
{
List<Student> students = new List<Student>();
var student = new Student();
var query = "SELECT * FROM tabStud";
var args = new Dictionary<string, object>
{
{"@stdId", student.stdId}
};
DataTable dt = Execute(query, args);
if (dt == null || dt.Rows.Count == 0)
{
return null;
}
student = new Student
{
stdId = Convert.ToInt32(dt.Rows[0]["Id"]),
stdName = Convert.ToString(dt.Rows[0]["Name"]),
stdAge = Convert.ToInt32(dt.Rows[0]["Age"]),
stdAddress = Convert.ToString(dt.Rows[0]["Address"]),
stdContact = Convert.ToString(dt.Rows[0]["Contact"])
};
students.Add(student);
return students;
}
}
}
and StudentViewModel as under
using System;
using System.Collections.ObjectModel;
using System.Windows;
using System.Windows.Input;
using TestWpfMVVM.DataAccess;
using TestWpfMVVM.Model;
namespace TestWpfMVVM.ViewModel
{
public class StudentViewModel
{
private ICommand _saveCommand;
private ICommand _resetCommand;
private ICommand _editCommand;
private ICommand _deleteCommand;
private StudentRepository _repository;
private Student _studentEntity = null;
public StudentRecord StudentRecord { get; set; }
public ICommand ResetCommand
{
get
{
if (_resetCommand == null)
_resetCommand = new RelayCommand(param => ResetData(), null);
return _resetCommand;
}
}
public ICommand SaveCommand
{
get
{
if (_saveCommand == null)
_saveCommand = new RelayCommand(param => SaveData(), null);
return _saveCommand;
}
}
public ICommand EditCommand
{
get
{
if (_editCommand == null)
_editCommand = new RelayCommand(param => EditData((int)param), null);
return _editCommand;
}
}
public ICommand DeleteCommand
{
get
{
if (_deleteCommand == null)
_deleteCommand = new RelayCommand(param => DeleteStudent((int)param), null);
return _deleteCommand;
}
}
public StudentViewModel()
{
_studentEntity = new Student();
_repository = new StudentRepository();
StudentRecord = new StudentRecord();
GetAll();
}
public void ResetData()
{
StudentRecord.Name = string.Empty;
StudentRecord.Id = 0;
StudentRecord.Address = string.Empty;
StudentRecord.Contact = string.Empty;
StudentRecord.Age = 0;
}
public void DeleteStudent(int id)
{
if (MessageBox.Show("Confirm delete of this record?", "Student", MessageBoxButton.YesNo)
== MessageBoxResult.Yes)
{
try
{
var model = _repository.Get(id);
_repository.RemoveStudent(model);
MessageBox.Show("Record successfully deleted."); ;
}
catch (Exception ex)
{
MessageBox.Show("Error occured while saving. " ex.InnerException);
}
finally
{
GetAll();
}
}
}
public void SaveData()
{
if (StudentRecord != null)
{
_studentEntity.stdName = StudentRecord.Name;
_studentEntity.stdAge = StudentRecord.Age;
_studentEntity.stdAddress = StudentRecord.Address;
_studentEntity.stdContact = StudentRecord.Contact;
try
{
if (StudentRecord.Id <= 0)
{
_repository.AddStudent(_studentEntity);
MessageBox.Show("New record successfully saved.");
}
else
{
_studentEntity.stdId = StudentRecord.Id;
_repository.UpdateStudent(_studentEntity);
MessageBox.Show("Record successfully updated.");
}
}
catch (Exception ex)
{
MessageBox.Show("Error occured while saving. " ex.InnerException);
}
finally
{
GetAll();
ResetData();
}
}
}
public void EditData(int id)
{
var model = _repository.Get(id);
StudentRecord.Id = model.stdId;
StudentRecord.Name = model.stdName;
StudentRecord.Age = (int)model.stdAge;
StudentRecord.Address = model.stdAddress;
StudentRecord.Contact = model.stdContact;
}
public void GetAll()
{
StudentRecord.StudentRecords = new ObservableCollection<StudentRecord>();
_repository.GetAll().ForEach(data => StudentRecord.StudentRecords.Add(new StudentRecord()
{
Id = data.stdId,
Name = data.stdName,
Address = data.stdAddress,
Age = Convert.ToInt32(data.stdAge),
Contact = data.stdContact
}));
}
}
}
Now I'm able to run the project and also can add data to the table using the Save button. But after window loading I can only see the first row in the datagrid and not all of the rows. Furthermore, when I click on the Select and Delete button I get the errors respectively
I believe there is some problem in the Get(id) method but not sure how to fix...
Also, this is what my database table looks like
CodePudding user response：
You should reimplement your own version of StudentRepository and delete the StudentEntities and use SqLite instead. start by experimenting with some code to connect to the database, then select a full table and map it to a student object. SqLite has the ability to use generic and can fill you Student object if the Properties match with the name of the column in your database.
CodePudding user response：
Ok Jayanta,
below is extract of my own code that you can get inspiration of and report it to your student repo:
public IEnumerable<Equity> GetListOfStocksOfMarket(string exchange)
{
string query = string.Format("SELECT * from " Tables.ASSETS_TABLE " WHERE ExchangeMic='{0}'", exchange);
Log(query);
var command = _connection.CreateCommand(query);
var equities = command.ExecuteQuery<Equity>();
IEnumerable<Equity> ret = equities.OrderBy(x=>x.InstrumentName).ToList();
return ret;
}
private SQLiteConnection _connection;
public bool Connect()
{
_connection = new SQLiteConnection(DATABASE_FILE_NAME);
_isConnected = true;
RepoYield = new RepoYield(_connection);
return true;
}
CodePudding user response：
I offered some code that utilizes the SQLite namespaces / context here SQL statement for creating new data into three tables at once c#
Like you creating some sort of wrapper repository, adjust as you need. I have example context of ensuring data types / sizes, etc.
You could even apply GENERICS based on each table structure you wanted to apply CRUD to. You can see the querying to the connected database context also simplifies pulling data using PARAMETERIZED QUERIES vs string manipulated which could allow SQL-Injection.
Data types as bound per your example would not need to be "converted" because the binding to the underlying structure that may be numeric based would only allow the value to be stored if it WERE numeric to begin with. I could expand more if you had any questions, but hopefully with the view model, and view context you have already, creating your own wrapper/repository using SQLite directly might help a bit more.