Hello @DPlug
Note that the following are not plug in code solution but instead paths to lead you to what you want.
Following along with the following Microsoft code sample. Then for a start see the following TechNet article and code which does not have a ComboBox nor DataGrid but instead a ListBox, don't get hung up on the ListBox.
The screenshot below is for the TechNet article, ignore VB.NET as the actual code has one project for C#, one for VB.NET
DataGrid
MainWindow Modified source
protected override async void OnContentRendered(EventArgs e)
{
base.OnContentRendered(e);
if (_hasShown)
{
return;
}
_hasShown = true;
var employeeCollection = new ObservableCollection<Employees>();
//await Task.Run(async () =>
//{
// employeeCollection = new ObservableCollection<Employees>(await _context.Employees.ToListAsync());
//});
await Task.Delay(1);
employeeCollection = new ObservableCollection<Employees>(EmployeesOperations.List());
EmployeeGrid.ItemsSource = employeeCollection;
employeeCollection.CollectionChanged += EmployeeCollection_CollectionChanged;
DataContext = employeeCollection;
/*
* Find employee by last name, if found
* select and scroll into view in the DataGrid
*/
var employee = (employeeCollection)
.FirstOrDefault(emp => emp.LastName == "Russell");
if (employee == null) return;
EmployeeGrid.SelectedItem = employee;
EmployeeGrid.ScrollIntoView(employee);
SaveButton.IsEnabled = true;
}
Data reader
Code added, used above. Note extension class DbExtensions should be in it's own file
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using WpfApp1.Models;
namespace WpfApp1.Classes
{
public class EmployeesOperations
{
public static string ConnectionString =
"Data Source=.\\SQLEXPRESS;database=HumanResources;Integrated Security=True";
public static List<Employees> List()
{
var list = new List<Employees>() ;
var selectStatement =
@"
SELECT
employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, manager_id, department_id
FROM
dbo.employees;
";
using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement})
{
cn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var employee = new Employees()
{
EmployeeId = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
Email = reader.GetString(3),
PhoneNumber = reader.SafeGetString(4),
HireDate = reader.GetDateTime(5),
JobId = reader.GetInt32(6),
Salary = reader.GetDecimal(7),
ManagerId = reader["manager_id"].DbCast<int>(),
DepartmentId = reader.GetInt32(9),
Manager = new Employees(),
InverseManager = new List<Employees>()
};
list.Add(employee);
}
}
}
return list;
}
}
public static class DbExtensions
{
public static T? DbCast<T>(this object dbValue) where T : struct
{
switch (dbValue)
{
case null:
case DBNull _:
return null;
}
T? value = dbValue as T?;
if (value != null)
{
return value;
}
if (dbValue is IConvertible conv)
{
value = (T)conv.ToType(typeof(T), CultureInfo.InvariantCulture);
}
return value;
}
public static string SafeGetString(this SqlDataReader reader, int colIndex)
{
if (!reader.IsDBNull(colIndex))
{
return reader.GetString(colIndex);
}
return string.Empty;
}
}
}
Have you considered using Entity Framework to read data from your database? If interested see the following TechNet article. Screenshot using Entity Framework, here is where data is setup. This could be adapted to using a data reader and if this is .NET Core you need to add the SqlClient NuGet package.
I am not really a fan of entity framework, I like seeing how my code is doing what I ask it to do. I like writing my data access codes my way, in a simplified manner so I understand what is happening should in case of exceptions. So please can I get it in pure ADO. NET?
I can surely provide that but has to be later as I'm heading out for the day. On a side note regarding what I did in the article, the Entity Framework code was done with a tool along with INotifyPropertyChanged implementation was done with a Visual Studio add-in so there was very little actual code to write but I do understand some people are not a fan of Entity Framework while in my case I gravitate to Entity Framework as I find it simpler than using ADO.NET and only use ADO.NET for forum questions.
Please I am standing by for it, I'll be glad to have it from you. Thanks in advance.
Hello @DPlug
See my first reply which I've added modifications to use a DataReader, left the Entity Framework code there but commented out so Entity Framework is not used.
Sign in to comment