Reputation: 59
Exception thrown when setting DataGridView property
I have a DataGridView table that displays the properties of EmployeeModels. When compiling, this exception is thrown:
'ColumnCount property cannot be set on a data-bound DataGridView control.'
I have heard that setting .Datasource = null before binding to the actual list can help, but doing so has not resolved the issue. I tried deleting the DataGridView and recreating it in case there was a bug, but this didn't resolve the issue.
//CREATE EMPLOYEE TABLE
public void CreateEmployeeTable()
{
EmployeeGridView.DataSource = null;
EmployeeGridView.DataSource = globalEmployeeList;
EmployeeGridView.AutoGenerateColumns = false;
EmployeeGridView.ColumnCount = 9;
EmployeeGridView.Columns[0].HeaderText = "Employee ID";
EmployeeGridView.Columns[0].DataPropertyName = "ID";
EmployeeGridView.Columns[1].HeaderText = "First Name";
EmployeeGridView.Columns[1].DataPropertyName = "FirstName";
EmployeeGridView.Columns[2].HeaderText = "Last Name";
EmployeeGridView.Columns[2].DataPropertyName = "LastName";
EmployeeGridView.Columns[3].HeaderText = "Nickname";
EmployeeGridView.Columns[3].DataPropertyName = "Nickname";
EmployeeGridView.Columns[4].HeaderText = "Hire Date";
EmployeeGridView.Columns[4].DataPropertyName = "HireDate";
}
private void EmployeeGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
DataGridViewCell selectedEmployeeCell = EmployeeGridView.CurrentCell;
int selectedEmployeeRow = selectedEmployeeCell.RowIndex;
int selectedEmployeeID = (int)EmployeeGridView.Rows[selectedEmployeeRow].Cells[0].Value;
jobTitleListBox.DataSource = globalEmployeeList.Where(person => person.ID == selectedEmployeeID).ToList();
}
Here is the SQL (And yes, I'm going to make this a stored procedure eventually; I'm just not there yet.)
public async Task<List<EmployeeModel>> GetEmployeeList()
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString("WorkDeskDB")))
{
var sql = @"SELECT e.id, e.FirstName, e.LastName, e.Nickname,
em.EmployeeID, em.Address, em.Type,
e.JobTitleID, jt.id, jt.Name,
p.EmployeeID, p.Number, p.Type,
ect.EmployeeID, ect.NameID, ect.InitialDate, ect.ExpirationDate,
ct.id, ct.Name
FROM dbo.Employees e
LEFT JOIN dbo.Emails em
ON em.EmployeeID = e.id
LEFT JOIN dbo.JobTitles jt
ON e.JobTitleID = jt.id
LEFT JOIN Phones p
ON p.EmployeeID = e.id
LEFT JOIN dbo.EmployeeCertificationType ect
ON ect.EmployeeID = e.id
LEFT JOIN dbo.CertificationType ct
ON ect.NameID = ct.id";
var employees = await connection.QueryAsync<EmployeeModel, EmailModel, TitleModel, PhoneModel, CertificationModel, EmployeeModel>(sql, (e, em, t, p, c) =>
{
e.EmailList.Add(em);
e.JobTitle = t;
e.PhoneList.Add(p);
e.CertificationList.Add(c);
return e;
}, splitOn: "EmployeeID, JobTitleID, EmployeeID, EmployeeID");
var result = employees.GroupBy(e => e.ID).Select(g =>
{
var groupedEmployee = g.First();
groupedEmployee.EmailList = g.Select(e => e.EmailList.Single()).ToList();
groupedEmployee.PhoneList = g.Select(e => e.PhoneList.Single()).ToList();
groupedEmployee.CertificationList = g.Select(e => e.CertificationList.Single()).ToList();
return groupedEmployee;
});
return result.ToList();
}
}
Upvotes: 0
Views: 181
Answers (1)
Reputation: 30474
No, no, you should never ever fiddle with the cells that display your data.
Or in other words: separate your data from how it is displayed. Apart from that your code will be easier to read, it will also be easier to change. What, if for instance you want to display your data in a ListView, or maybe as a Graph.
Separating your data from how it is displayed also makes it easier to reuse it, to change the data and to test it.
Fetch the Employees
So, assuming you know your SQL, the following should be no problem.
(I leave out all the async-await stuff, that is not part of your problem).
private IEnumerable<Employee> FetchEmployees()
{
// TODO: implement, fetch employees from the database
}
The nice thing is, that you hid the structure of your database. So if you decide to change the tables of your database, then the users of this method (= code, not operators), won't have to change. They don't even have to change if you decide to save the data in an XML file, or in a Dictionary. The latter might be quite handy if you need to unit test your code: you don't have to fill a database for every test, a dictionary with Employees will do.
Display the Fetched Employees
So let's assume you have a method to fetch all employees in some smart way.
To display it, you could fiddle with the cells. But a much nice method would be to use the DataSource of the DatagridView
Suppose your Employees have properties Id and Name, and several others. You want to show your Employees row by row, and you want to show properties Id and Name as columns:
DataGridViewColumn columnEmployeeId new DataGridViewColumn();
DataGridViewColumn columnEmployeeName new DataGridViewColumn();
columnEmployeeId.DataPropertyName = nameof(Employee.Id);
columnEmployeeName.DataPropertyName = nameof(Employee.Name);
Property DataPropertyName contains the name of the property that it should show.
Now all you have to do is Fetch the employees, and assign them to the DataSource of the DataGridView:
var employees = this.FetchEmployees().ToList();
this.DatagridView1.DataSource = employees;
This is enough display your employees. Changes that the operator makes to the displayed data are not updated in object employees. If you want that, you should put your data in an object that implements IBindingList, like BindingList<T>
DataGridView1.DataSource = new BindingList<Employee>(FetchEmployees().ToList());
Now every change that the operator makes to the displayed Employees are automatically updated in the BindingList. Even if he adds or removes Employees.
The BindingList does not depend on how the data is displayed. So if you decide not to display column Id anymore, or if you decide to sort the displayed data by descending Name, this has no effect on the BindingList. If you look closely, you'll see that the BindingList in fact is not a List, but a Collection. There is no notion of Employee[3]. This is expected, because if the operator sorts the displayed data, this Employee might not be shown in the Row[3] anymore.
Proper design of your form would contain methods like:
IEnumerable<Employee> FetchEmployees() {... see above}
BindingList<Employee> DisplayedEmployees
{
get => (BindingList<Employee>)this.dataGridView1.DataSource;
set => this.dataGridView1.DataSource = value;
}
Employee CurrentEmployee =>
(Employee) this.datagridView1.SelectedCell?.OwningRow.DataBoundItem;
IEnumerable<Employee> SelectedEmployees =>
this.DatagridView1.SelectedRows
.Select(row => row.DataBoundItem)
.Cast<Employee>();
Putting it all together:
On form load: display all employees:
void Form_Load(object sender, ...)
{
this.DisplayedEmployees = this.FetchEmployees();
}
After ButtonRemoveClicked: remove all Selected Employees:
void ButtonRemove_Clicked(object sender, ...)
{
void employeestoRemove = SelectedEmployees.ToList();
if (employeesToRemove.Any())
{
// TODO: ask operator if remove employees is ok
this.Repository.RemoveEmployees(employeesToRemove);
}
}
After an edit, all items in the datasource are updated:
void OnButtonOk_Clicked(object sender, ...)
{
var displayedEmployees = this.DisplayedEmployees;
var originalEmployees = this.FetchEmployees;
// TODO: find out which employees were added / removed / changed
// and Add / Remove / Update the data
}
Conclusion
Because you separated the handling of the database from the display of the employees the procedures are much smaller, easier to understand, easier to reuse, test, change.
Your code doesn't have to worry about sorting, column reordening, change in how data is displayed (is Birthday displayed as month-day-year, or as 2021-01-15?), your Form class does not have to worry about this, and does not need big changes if this is changed.
Upvotes: 2