locked
copytodatatable error RRS feed

  • Question

  •  

    Hi All,

     

    When i try to use Copytodatatable i am getting following error.

     

    'copytodatatable' is not a member of 'System.Collections.Generic.IEnumerable(Of linq_classes.ItemType)'.

     

    Thanks.

    Masi.

    Thursday, January 15, 2009 12:42 AM

All replies

  • Hi, would like to bump this.
    Any solutions. I have the same error and stuck for two days.
    Help is appreciated

    Monday, September 14, 2009 12:50 AM
  • There is no .CopyToDataTable or .ToDataTable method in IEnumerable, at least in .NET Framework 3.5.
    I´ve readed that the method was included previously in some beta version of the .NET framework, but for some reason it was deprecated.
    However, this can bo done "manually" by coding an Extension of IEnumerable.

    You must check this 3 options to do it (in order of complexity):
    http://www.chinhdo.com/20090402/convert-list-to-datatable/
    http://grrargh.com/blog/playing-with-extension-methods-todatatable/ 
    http://blogs.msdn.com/aconrad/archive/2007/09/07/science-project.aspx

    Also, all of the above have a little "bug" if you are using a previous version than .NET framework 3.5 SP1, and that is the Order of the Columns:
    You could not get the columns in the order that these were projected.
    Perhaps that is the reason because they deprecated the method...

    Even though this is apparently solved in the SP1 of the .NET framework 3.5, I have to recommend you that use this method that I've made to avoid that bug:

    Imports System.Reflection
    Imports System.Runtime.CompilerServices
    
    <HideModuleName()> _
    Public Module ConvertDataTable_Extension
    
        <Extension()> _
        Public Function ToDataTable(Of T)(ByVal value As IEnumerable(Of T), ByVal ParamArray ColumnOrder As String()) As DataTable
            Dim returnTable As New DataTable
            For Each pi As PropertyInfo In _GetProps(GetType(T), ColumnOrder)
                Dim colType As Type = pi.PropertyType
                If (colType.IsGenericType) AndAlso colType.GetGenericTypeDefinition().Equals(GetType(Nullable(Of ))) Then
                    colType = colType.GetGenericArguments(0)
                End If
                returnTable.Columns.Add(New DataColumn(pi.Name, colType))
            Next
            For Each result As T In value
                Dim nr = returnTable.NewRow
                For Each pi In result.GetType.GetProperties
                    nr(pi.Name) = pi.GetValue(result, Nothing)
                Next
                returnTable.Rows.Add(nr)
            Next
            Return returnTable
        End Function
    
        Private Function _GetProps(ByVal t As Type, ByVal ParamArray ColumnOrder As String()) As IEnumerable(Of PropertyInfo)
            Dim props = t.GetProperties()
            Dim colOrder = ColumnOrder.Select(Function(s) s.ToLower()).ToArray
            If colOrder.Length > 0 Then
                If colOrder.Length <> props.Count Then
                    Throw New ArgumentException("Incorrect ColumnOrder count: " & props.Count & ".", "ColumnOrder")
                End If
                Dim q = (From p In colOrder _
                        Where Not (From pp In props Where p.Equals(pp.Name.ToLower())).Any() _
                        Select p).FirstOrDefault()
                If q IsNot Nothing Then
                    Throw New ArgumentException("Missing column in ColumnOrder: " & q & ".", "ColumnOrder")
                End If
                Return From pr In props _
                       Order By Array.IndexOf(colOrder, pr.Name.ToLower())
            Else
                Return props
            End If
        End Function
    
    End Module
    And here is 2 examples on how to use it; the first one avoid the mentioned bug by explicitly passing the name of the columns in the desired order, and
    the second one works exactly as the first link (http://www.chinhdo.com/20090402/convert-list-to-datatable/), so probably it will take the columns in wrong order:

            Dim q = From f In System.IO.Directory.GetFiles("C:\") _
                    Select New With {.Name = f, .Extension = System.IO.Path.GetExtension(f)}
    
            Dim dt As DataTable = q.ToDataTable("Name", "Extension")
    And the not recommended way:

            Dim q = From f In System.IO.Directory.GetFiles("C:\") _
                    Select New With {.Name = f, .Extension = System.IO.Path.GetExtension(f)}
    
            Dim dt As DataTable = q.ToDataTable()
    



    So.. I recommend you to always pass the column names to the ToDataTable extension method.

    Good luck and sorry for my english...

    Federico Colombo
    • Proposed as answer by thepirat000 Tuesday, September 15, 2009 1:47 AM
    Tuesday, September 15, 2009 1:45 AM
  • I have spent the better part of the day looking for the answer for this before coming up with the solution myself.  Since there is a lot of confusing information about using LINQ to SQL and stored procedures out there, I thought I would share this simple example (VB) of calling a "SelectAll" SPROC from a LINQ to SQL interface:

    1. Set up a LINQ object in App_Code that references the stored procedure
    2. Add a LinqDataSource in your .aspx file that includes an OnSelecting reference to the OnSelecting method shown in step 4 below:

      <asp:LinqDataSource ID="lds_GridView1" runat="server" OnSelecting="lds_GridView1_Selecting"></asp:LinqDataSource>
    3. Add the following assembly references to the top of your code-behind file:

      Imports System.Collections.Generic
      Imports System.Linq
    4. Add the following OnSelecting method for your LinqDataSource to your code-behind file:

      Protected Sub lds_GridView1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) Handles lds_GridView1.Selecting
              Dim dbContext As New MyDatabase_DataContext
              Dim result As IEnumerable(Of MyDatabaseTable_SelectAllResult) = CType(dbContext.MyDatabaseTable_SelectAll(), IEnumerable(Of MyDatabaseTable_SelectAllResult))
              e.Result = result.ToList()
          End Sub
    What makes this work:

    • The result variable is defined as IEnumerable(Of Type) where the Type in this case is exactly the same as the stored procedure result Type automatically defined in the Linq.Designer.vb (or .cs) file
    • The dbContext.MyDatabaseTable_SelectAll() return value is converted from ISingleResult(of Type) (which, in my opinion, is WORTHLESS) into IEnumerable(of Type) , which has a ToList method that is necessary for binding the data to your UI control (in this instance, a GridView)
    Thursday, December 3, 2009 6:34 PM