mohamed soliman mohamed soliman - 4 months ago 48
Vb.net Question

how to Flip dataset and display in datagridview

I try to flip dataset to display column as rows by using this code but it does not work :

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button.Click
Dim ds2 As New DataSet
Dim dt2 As New DataTable
Dim com1 As String = "select col1,col2,col3 from table1"
ds2 = FlipDataSet(ds2)
Dim dp As New SqlDataAdapter(com1, conn)
dp.Fill(dt2)
DGV_lev1.DataSource = dt2.DefaultView
End Sub


and use this function to flip dataset :

Private Function FlipDataSet(old_DataSet As DataSet) As DataSet

Dim ds As New DataSet()
For Each dt As DataTable In old_DataSet.Tables
Dim table As New DataTable()
For i As Integer = 0 To dt.Rows.Count
table.Columns.Add(Convert.ToString(i))
table.Columns(0).ColumnName = "Fields"
If i = 0 Then
Continue For
Else

table.Columns(i).ColumnName = "Customer " & i
End If
Next
Dim r As DataRow
For k As Integer = 0 To dt.Columns.Count - 1
r = table.NewRow()
r(0) = dt.Columns(k).ToString()
For j As Integer = 1 To dt.Rows.Count
r(j) = dt.Rows(j - 1)(k)
Next
table.Rows.Add(r)
Next
ds.Tables.Add(table)
Next
Return ds
End Function


to make datagirdview display from this :

enter image description here

to this :

enter image description here

can anyone help me

thank you

Answer

Try this, it worked in a quick test I did:

Private Function Transpose(ByVal table As DataTable) As DataTable
    Dim flippedTable As New DataTable
    'creates as many columns as rows in source table
    flippedTable.Columns.AddRange(
        table.Select.Select(
            Function(dr) New DataColumn("col" & table.Rows.IndexOf(dr), GetType(Object))
            ).ToArray)
    'iterates columns in source table
    For Each dc As DataColumn In table.Columns
        'get array of values of column in each row and add as new row in target table
        flippedTable.Rows.Add(table.Select.Select(Function(dr) dr(dc)).ToArray)
    Next
    Return flippedTable
End Function
Comments