Results 1 to 5 of 5

Thread: Cast to DataTable

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Cast to DataTable

    I came across this code example and would like use it, but need to get the result into a DataTable.

    Code:
    Dim restrictions() As String = {Nothing, Nothing, Nothing, Nothing, tdf.Name}
    Dim indexDetails As DataTable = con.GetSchema("INDEXES", restrictions)
    Dim columnsIWant = From row In indexDetails.AsEnumerable()
                        Select
                        TableName = row.Field(Of String)("TABLE_NAME"),
                        IndexName = row.Field(Of String)("INDEX_NAME"),
                        ColumnOrdinal = row.Field(Of Int64)("ORDINAL_POSITION"),
                        ColumnName = row.Field(Of String)("COLUMN_NAME")
                        Order By
                        TableName, IndexName, ColumnOrdinal, ColumnName
    How can I cast columnsIWant to a DataTable? If that is not possible, then how can I iterate through columnsIWant and work with its data (e.g., IndexName)?
    Last edited by Mark@SF; Sep 13th, 2021 at 05:48 PM.

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Cast to DataTable

    Here's an extension for anonymous IEnumerables...

    Code:
    Module EnumerableExtensions
    	<System.Runtime.CompilerServices.Extension()>
    	Public Function CopyToDataTable(ByVal data As IEnumerable(Of Object)) As DataTable
    		Dim parseDT = New DataTable()
    		data.Select(Function(r, i) New With {
    			Key .Key = i,
    	Key .Value = r
    		}).ToList().ForEach(Sub(r)
    								If r.Key = 0 Then
    									r.Value.GetType().GetProperties().ToList().ForEach(Sub(p)
    																						   parseDT.Columns.Add(p.Name, p.PropertyType)
    																					   End Sub)
    								End If
    								Dim row = parseDT.NewRow()
    								r.Value.GetType().GetProperties().ToList().ForEach(Sub(p)
    																					   row(p.Name) = p.GetValue(r.Value, Nothing)
    																				   End Sub)
    								parseDT.Rows.Add(row)
    
    							End Sub)
    		Return parseDT
    	End Function
    End Module
    Code:
    Dim restrictions() As String = {Nothing, Nothing, Nothing, Nothing, tdf.Name}
            Dim indexDetails As DataTable = con.GetSchema("INDEXES", restrictions)
            Dim columnsIWant = (From row In indexDetails.AsEnumerable()
                                Select
                               TableName = row.Field(Of String)("TABLE_NAME"),
                               IndexName = row.Field(Of String)("INDEX_NAME"),
                               ColumnOrdinal = row.Field(Of Int64)("ORDINAL_POSITION"),
                               ColumnName = row.Field(Of String)("COLUMN_NAME")
                                Order By
                               TableName, IndexName, ColumnOrdinal, ColumnName).CopyToDataTable

  3. #3
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Cast to DataTable

    Are you trying to create an empty DataTable from your Schema? That would be a different method...

  4. #4
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Cast to DataTable

    If you just want an empty datatable, you already know the columns you want, as indexDetails just looks to be schema details…

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: Cast to DataTable

    @paul -

    Thank you for your help with my question, including the extension method.

    Yes, I know that the indexDetails has the schema details. I was looking for how to work with columnsIWant data (a subset of the indexDetails table).

    The code in post #1 includes LINQ which I'm not very familiar with. I've also never dealt with an OrderedEnumerableRowCollection(Of 'a).

    I need to do some more work to get up-to-speed with these concepts.

    Thanks again for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width