Object data not showing in DataGridView after binding

Question

Beginner! I'm trying to display only certain data from an object into a data grid view, but I don't get anything after running in Winforms. Is there any way I could successfully implement this or any alternatives?

I was trying to follow the template here but attempt not successfull

// Object
public class Student
    {
        public string StudentNumber { get; set; }
        public string StudentName { get; set; }
        public string StudentNameChinese { get; set; }
        public string StudentNameShort { get; set; }
        public string Gender { get; set; }
        public string IdPlaceDescription { get; set; }
        public string MobileNumber { get; set; }
        public string Major { get; set; }
        public string RoomNumber { get; set; }
        public string CheckInDate { get; set; }
        public string CheckOutDate { get; set; }
        public string RoomNoBefore { get; set; }
        public string ChangeDate { get; set; }
        public DateTime BirthDate { get; set; }
        public string Province { get; set; }
        public string Email { get; set; }
        public string OtherEmail { get; set; }
        public byte[] ProfileImage { get; set; }
    }

In Form class

private void StudentsForm_Load(object sender, EventArgs e)
        {
            BindDataToDataGrid();
        }

// Get data from the database into a Student object class
        public Collection<Student> GetData()
        {
            // Initialize collection of students
            var collection = new Collection<Student>();

            // string sqlQuery = "select * from ProfileTable";
            string sqlQuery = "select StudentNo, StudentName, StudentNameChinese, " +
                "StudentNameShort, Gender, IdPlaceDesc, MobileNo, MajorDesc, RoomNo," +
                "CheckInDate, CheckOutDate, RoomNoBefore, ChangeDate, BirthDate," +
                "ProvinceDesc, Email, OtherEmail, Image from ProfileTable";
            using (SqlCommand command = new SqlCommand(sqlQuery, connection))
            {
                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // Update student variables with values from ProfileTable column
                        Student student = new Student
                        {
                            StudentNumber = (string)reader["StudentNo"],
                            StudentName = (string)reader["StudentName"],
                            StudentNameChinese = (string)reader["StudentNameChinese"],
                            StudentNameShort = (string)reader["StudentNameShort"],
                            Gender = (string)reader["Gender"],
                            IdPlaceDescription = (string)reader["IdPlaceDesc"],
                            MobileNumber = (string)reader["MobileNo"],
                            Major = (string)reader["MajorDesc"],
                            RoomNumber = (string)reader["RoomNo"],
                            CheckInDate = (string)reader["CheckInDate"],
                            CheckOutDate = (string)reader["CheckOutDate"],
                            RoomNoBefore = (string)reader["RoomNoBefore"],
                            ChangeDate = (string)reader["ChangeDate"],
                            BirthDate = (DateTime)reader["BirthDate"],
                            Province = (string)reader["ProvinceDesc"],
                            Email = (string)reader["Email"],
                            OtherEmail = (string)reader["OtherEmail"],
                            ProfileImage = (byte[])reader["Image"]
                        };

                        // Add student to collection
                        collection.Add(student);
                    }
                }
                connection.Close();
            }
            return collection;
        }

Edit: Below is what I only want to see in my data grid view, thus just using the datatable which is an exact replica of what is in the database is what I'm avoiding

// Bind data from collections to datagrid columns
        void BindDataToDataGrid()
        {
            var students = GetData();
            var bind = from student in students
                       select new
                       {
                           // Values are assigned to Datagrid columns
                           studentNumber = student.StudentNumber,
                           nameShort = student.StudentNameShort,
                           room = student.RoomNumber,
                           gender = student.Gender,
                           major = student.Major,
                           number = student.MobileNumber,
                           email = student.Email,
                           profile = student.ProfileImage
                       };
            dataGridViewStudents.DataSource = bind;
        }

winforms

Result after running

This is what the datatable in the database look like

Answer 1

Change this:

dataGridViewStudents.DataSource = bind;

To this:

dataGridViewStudents.DataSource = bind.ToArray();

Yep; you can't bind the output of a LINQ Query to a grid, but you can if you convert it to an array or list etc

---

TBH, I'd have probably made my life easier sooner. All that code you've written can more or less be replaced with these 4 lines :

using (SqlDataAdapter da = new SqlDataAdapter (sqlQuery, connection))
{
  DataTable dt = new DataTable();
  da.Fill(dt);
  datagridViewWhatever.DataSource = dt;
}

Or install Dapper, a device that will take your query and your Student class, and populate a list of Student from the db. Here's what the 2 line Dapper variant of your code looks like:

using(var c = new SqlConnection(connstr))
  datagridViewWhatever.DataSource = (await c.QueryAsync<Student>(sqlQuery)).ToList();

Aye.. all those hundreds of lines of while(reader.Read()) .. (cast)reader["this"] .. (cast)reader["that"] code you slaved over*.. Whoosh! Gone! Done by Dapper automagically looking at the names outputted by your query and your property names of your object, and auto-wiring them together (note: name them the same e.g. make your query SELECT MobileNo as MobileNumber ... or rename your C# property). http://dapper-tutorial.net

*it should be a punishment, like writing lines after class for chewing gum..

---

Edit;

You've tagged on a supplementary question of why your grid shows too many columns - that should really be a new question but your grid (probably) shows more than you expect because AutoGenerateColumns is true, so the grid is finding everything it can display and adding columns for it (one column per property of the object bind'd to). To solve this you can:

• turn off auto generation of columns and manually build a column collection that is the columns you want
• turn on auto generate columns, bind the grid then go through the columns removing the ones you don't want
• turn on auto generate and supply an object for binding that has fewer properties
• a mix of the above

Answer 2

If you replace var bind, with the datatype that you actually attempt to assign to the DataSource, you find immediately the source of the problem. (Hint: if you don't see the error, use your debugger to see the type of bind)

Put the Students in a BindingList

Create a BindingList<Student> and put the fetched Students into this BindingList:

ICollection<Student> fetchedStudents = this.GetData();
BindingList<Student> bindingStudents = new BindingList<Student>(fetchedStudents.ToList():

this.DataGridView.DataSource = bindingStudents;

Little tip: if you want to Sort the Students, just by clicking on the column header of the DataGridView, if you want automatic updates if any of the Cells of the DataGridView is edited, consider using Nuget BindingListView:

var students = ...
BindingListView<Student> view = new BindingListView<Student>(students.ToList());
dataGridView1.DataSource = view;

And presto! You've got automatic sorting if operator clicks column header.

Another feature, is easy filtering. If you (temporarily) only want to show older Students:

view.ApplyFilter( student => student.BirthDay.Year < 2000);

All Students are still in the view, if you remove the filter, they will be visible again.

Efficiency Improvement

It is a bit of a waste to let GetData return a Collection. Suppose the caller only wants to know if there is any Student, or maybe he only wants the first Student:

var firstStudent = GetData().FirstOrDefault();

If would be a shame to put all Students in a Collection.

Consider the following:

public IEnumerable<Student> GetStudents()
{
    const string sqlQuery = "select ...";

    using (SqlCommand command = new SqlCommand(sqlQuery, connection))
    {
        connection.Open();
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Student student = new Student
                {
                    ...
                };
                yield return Student();       //  <=== 
            }
        }
    }
}

Usage:

var firstFiveStudents = this.GetStudents().Take(5).ToList();

This won't create a collection with all Students, the while (reader.Read()) is executed 5 times.