Transpost List of Objects in Datagridview

Question

Hallo,

I have a list of objects (Person) and I want to display the list in Datagrid view something below (columns to Rows and Rows to Columns). Is there a way to do it?. I will add more persons to the list so that table(dgv) should also add a column for each person.

Public Class Person  
  
    Public Property Name As String  
    Public Property Age As Integer  
    Public Property Sex As String  
    Public Property Email As String  
    Public Property Phone As String  
    Public Property Address As String  
  
End Class  

Answer 1

Hi
OK, here is the basis for exactly what you want.

' Form1 with DataGridView1
Option Strict On
Option Explicit On
Public Class Form1
    Dim Persons As New DataTable
    Dim BS As New BindingSource

    ' random just to aid making sample data
    Dim rand As New Random
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        With Persons
            .Columns.Add("Name", GetType(String))
            .Columns.Add("Age", GetType(Integer))
            .Columns.Add("Sex", GetType(String))
            .Columns.Add("Email", GetType(String))
            .Columns.Add("Phone", GetType(String))
            .Columns.Add("Address", GetType(String))
            ' make some random test data
            For i As Integer = 1 To 10
                .Rows.Add("Name " & i.ToString, rand.Next(9, 99), ({"Male", "Female", "N/A"})(rand.Next(0, 3)), "EMail" & i.ToString, "0" & rand.Next(123, 888).ToString & " " & rand.Next(1000000, 9999999).ToString, rand.Next(99).ToString & " Some Street")
            Next
        End With
        Dim ndt As DataTable = Transpose(Persons)
        BS.DataSource = ndt
        DataGridView1.DataSource = BS
        With DataGridView1
            .ColumnHeadersVisible = False
            .Rows(0).Cells(0).Value = String.Empty
        End With
    End Sub
    Function Transpose(dt As DataTable) As DataTable
        Dim dt2 As New DataTable()
        For i As Integer = 0 To dt.Rows.Count
            dt2.Columns.Add()
        Next
        For i As Integer = 0 To dt.Columns.Count - 1
            dt2.Rows.Add()
            dt2.Rows(i)(0) = dt.Columns(i).ColumnName
        Next
        For i As Integer = 0 To dt.Columns.Count - 1
            For j As Integer = 0 To dt.Rows.Count - 1
                dt2.Rows(i)(j + 1) = dt.Rows(j)(i)
            Next
        Next
        Return dt2
    End Function

    Public Class Person
        Public Property Name As String
        Public Property Age As Integer
        Public Property Sex As String
        Public Property Email As String
        Public Property Phone As String
        Public Property Address As String
    End Class
End Class

Answer 2

Hi
A very simple BindingNavigator example.

Just needs Form1 with BindingNavigator1 and 6 TextBoxes (1 per Peron field). There is no further code, but loading/saving data, filtering etc are all very straightforward.

' Form1 with TextBoxes (1-6)
' and BindingNavigator1
Option Strict On
Option Explicit On
Public Class Form1
  Dim Persons As New DataTable
  Dim BS As New BindingSource

  ' random just to aid making sample data
  Dim rand As New Random
  Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    With Persons
      .Columns.Add("Name", GetType(String))
      .Columns.Add("Age", GetType(Integer))
      .Columns.Add("Sex", GetType(String))
      .Columns.Add("Email", GetType(String))
      .Columns.Add("Phone", GetType(String))
      .Columns.Add("Address", GetType(String))
      ' make some random test data
      For i As Integer = 1 To 100
        .Rows.Add("Name " & i.ToString, rand.Next(9, 99), ({"Male", "Female", "N/A"})(rand.Next(0, 3)), "EMail" & i.ToString, "0" & rand.Next(123, 888).ToString & " " & rand.Next(1000000, 9999999).ToString, rand.Next(99).ToString & " Some Street")
      Next
    End With
    BS.DataSource = Persons
    BindingNavigator1.BindingSource = BS

    TextBox1.DataBindings.Add("Text", BS, "Name")
    TextBox2.DataBindings.Add("Text", BS, "Age")
    TextBox3.DataBindings.Add("Text", BS, "Sex")
    TextBox4.DataBindings.Add("Text", BS, "Email")
    TextBox5.DataBindings.Add("Text", BS, "Phone")
    TextBox6.DataBindings.Add("Text", BS, "Address")

  End Sub
  Public Class Person
    Public Property Name As String
    Public Property Age As Integer
    Public Property Sex As String
    Public Property Email As String
    Public Property Phone As String
    Public Property Address As String
  End Class
End Class

Answer 3

Your options are

• Query (if SQL-Server) using PIVOT.
• Write code to convert a DataTable to a pivot DataTable
• Use a third party DataGridView.