我對 MVVM 完全陌生，我已經瀏覽了很多關于如何實作 CRUD 操作并將 SQLite 資料庫資料正確系結到 Datagrid、Combobox、Textbox、Button 等 WPF 控制元件的在線帖子，但很難找到什么我需要。此外，對于不熟悉 MVVM 模式的人來說，至少可以說這似乎很嚇人。
無論如何，說到重點，我在網上找到了這篇
以及相關檔案如下：
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();
}
}
}
}
學生.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; }
}
}
學生記錄.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");
}
}
}
}
中繼命令.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));
}
}
}
}
主視窗.xaml.cs
using System.Windows;
using TestWpfMVVM.ViewModel;
namespace TestWpfMVVM.View
{
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
this.DataContext = new StudentViewModel();
}
}
}
主視窗.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>
現在在帖子中，在 Model 檔案夾中，他們添加了一個 ADO.NET 物體資料模型，該模型連接到資料庫中的學生表，并將其命名為 StudentModel，同時將連接字串名稱更改為 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
}));
}
}
}
現在我可以運行該專案，還可以使用“保存”按鈕將資料添加到表中。但在視窗加載后，我只能看到資料網格中的第一行，而不是所有行。此外，當我單擊選擇和洗掉按鈕時，我分別得到錯誤
我認為該方法存在一些問題，Get(id)但不確定如何解決...
另外，這就是我的資料庫表的樣子
uj5u.com熱心網友回復：
您應該重新實作您自己的 StudentRepository 版本并洗掉 StudentEntities 并改用 SqlLite。首先嘗試一些代碼來連接到資料庫，然后選擇一個完整的表并將其映射到一個學生物件。如果屬性與資料庫中列的名稱匹配，SqLite 可以使用泛型并且可以填充您的學生物件。
uj5u.com熱心網友回復：
奧賈揚塔，
下面是我自己的代碼的摘錄，您可以從中獲得靈感并將其報告給您的學生存盤庫：
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;
}
uj5u.com熱心網友回復：
我提供了一些代碼，這些代碼利用 SQLite 命名空間/背景關系在這里 SQL 陳述句用于一次將新資料創建到三個表中 c#
就像您創建某種包裝存盤庫一樣，根據需要進行調整。我有確保資料型別/大小等的示例背景關系。
您甚至可以根據要應用 CRUD 的每個表結構應用 GENERICS。您可以看到對連接的資料庫背景關系的查詢也簡化了使用 PARAMETERIZED QUERIES 和字串操作來提取資料，這可能允許 SQL 注入。
根據您的示例系結的資料型別不需要“轉換”，因為系結到可能基于數字的基礎結構將只允許存盤以數字開頭的值。如果您有任何問題，我可以展開更多，但希望使用您已經擁有的視圖模型和視圖背景關系，直接使用 SQLite 創建自己的包裝器/存盤庫可能會有所幫助。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/457745.html
上一篇：當條目不完全匹配時如何連接表
下一篇：強制轉換變數更改變數名稱-SQL