I am sure i have not the best practice solution like the most of u guys could program, but im new in the business so pls give me a chance :P
i created a table in my sqlite database and added a Employee to the Employee table. I Set the DataContext of my GridView to the returned List from my ModelView. But my grid is emtpy. i guess i have a logical error in the art i convert my dapperRow. Please take a look at it. i Dont get an error or something. My gridView is just empty.
public List<Employee> GetAllEmployees()
{
List<Employee> employees = new List<Employee>();
List<dynamic> results = _dataAccess.SelectAllEmployees();
List<IDictionary<string, object>> dapperRowDic = results.Select(x => (IDictionary<string, object>)x).ToList();
foreach(var dapperRow in dapperRowDic)
{
object id;
object firstName;
object lastName;
object experience;
object salary;
dapperRow.TryGetValue("ID", out id);
dapperRow.TryGetValue("FirstName", out firstName);
dapperRow.TryGetValue("LastName", out lastName);
dapperRow.TryGetValue("Experience", out experience);
dapperRow.TryGetValue("Salary", out salary);
employees.Add(new Employee((int)(long)id,(string)firstName,(string)lastName, (int)(long)experience, (int)(long)salary));
}
return employees;
}
public class SQLiteDataAccess : IDataAccess
{
public SQLiteDataAccess()
{
if (!File.Exists(SqLiteBaseRepository.DbFile))
CreateDatabase();
}
public void InsertEmployee(int id,string firstName, string lastName, int experience, int salary)
{
using (var con = SqLiteBaseRepository.SimpleDbConnection())
{
var parameters = new { FIRSTNAME = firstName, LASTNAME = lastName, EXPERIENCE = experience, SALARY = salary, ID = id };
con.Execute("insert into Employee (ID,FirstName,LastName,Experience,Salary) " +
"Values (@ID ,@FIRSTNAME, @LASTNAME, @EXPERIENCE,@SALARY)", parameters);
}
}
public List<dynamic> SelectAllEmployees()
{
using (var con = SqLiteBaseRepository.SimpleDbConnection())
{
return con.Query<dynamic>("SELECT * FROM Employee").ToList();
}
}
public partial class MainWindow : Window
{
private EmployeeViewModel _employeeViewModel;
public MainWindow()
{
InitializeComponent();
string mode = "SQLite";
_employeeViewModel = new EmployeeViewModel(DataAccessFactory.GetDataAccessObject(mode));
List<Employee> employees = _employeeViewModel.GetAllEmployees();
this.DataContext = employees;
this.Show();
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
int id = int.Parse(txtId.Text);
string firstName = txtFirstName.Text;
string lastName = txtLastName.Text;
int experience = int.Parse(txtExperience.Text);
int salary = int.Parse(txtSalary.Text);
_employeeViewModel.CreateEmployee(id, firstName,lastName,experience,salary);
this.DataContext = _employeeViewModel.GetAllEmployees();
}
}
<Window x:Class="Employee_List_SQLite_Dapper_Wpf.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:Employee_List_SQLite_Dapper_Wpf"
mc:Ignorable="d"
Title="" Height="450" Width="800">
<Grid>
<Grid.ColumnDefinitions>
<ColumnDefinition Width="auto"></ColumnDefinition>
<ColumnDefinition Width="200"></ColumnDefinition>
<ColumnDefinition Width="200"></ColumnDefinition>
<ColumnDefinition Width="200"></ColumnDefinition>
<ColumnDefinition Width="35"></ColumnDefinition>
</Grid.ColumnDefinitions>
<Grid.RowDefinitions>
<RowDefinition Height="auto"></RowDefinition>
<RowDefinition Height="auto"></RowDefinition>
<RowDefinition Height="auto"></RowDefinition>
<RowDefinition Height="auto"></RowDefinition>
<RowDefinition Height="auto"></RowDefinition>
<RowDefinition Height="auto"></RowDefinition>
<RowDefinition Height="*"></RowDefinition>
</Grid.RowDefinitions>
<Label FontSize="20" Grid.Row="0" Grid.Column="0">Employee</Label>
<Label Grid.Row="1" Grid.Column="0">ID:</Label>
<Label Grid.Row="2" Grid.Column="0">First Name:</Label>
<Label Grid.Row="3" Grid.Column="0">Last Name:</Label>
<Label Grid.Row="4" Grid.Column="0">Experience:</Label>
<Label Grid.Row="5" Grid.Column="0">Salary:</Label>
<TextBox x:Name="txtId" Grid.Row="1" Grid.Column="1" Margin="2"></TextBox>
<TextBox x:Name="txtFirstName" Grid.Row="2" Grid.Column="1" Margin="2"></TextBox>
<TextBox x:Name="txtLastName" Grid.Row="3" Grid.Column="1" Margin="2"></TextBox>
<TextBox x:Name="txtExperience" Grid.Row="4" Grid.Column="1" Margin="2"></TextBox>
<TextBox x:Name="txtSalary" Grid.Row="5" Grid.Column="1" Margin="2"></TextBox>
<Button Name="btnSave" Grid.Row="5" Grid.Column="2" Width="80" Click="btnSave_Click">Save</Button>
<ListView Grid.Row="6" Grid.ColumnSpan="5" ItemsSource="{Binding Employee}" Margin="5,20,20,20">
<ListView.View>
<GridView x:Name="gridEmployee">
<GridViewColumn Header="ID" DisplayMemberBinding="{Binding ID}" Width="30"/>
<GridViewColumn Header="First Name" DisplayMemberBinding="{Binding FirstName}" Width="200" />
<GridViewColumn Header="Last Name" DisplayMemberBinding="{Binding LastName}" Width="200"/>
<GridViewColumn Header="Experience" DisplayMemberBinding="{Binding Experience}" Width="100"/>
<GridViewColumn Header="Salary" DisplayMemberBinding="{Binding Salary}" Width="150"/>
</GridView>
</ListView.View>
</ListView>
</Grid>
Here's what your Dapper code should look like for retrieving all employees:
var employees = connection.GetAll<Employee>();
That's it. You can find out more about the GetAll<T>() method here.
Your Insert Employee code looks OK, but all you really need to do is:
var employee = new Employee
{
FirstName = firstName,
LastName = lastName, // etc.
}
connection.Insert(employee);
You will need to properly annotate your Employee class to make this work correctly. The Dapper.Contrib page I linked above explains how to do this.
For MVVM, I suggest a good tutorial, like this one.