none
Convert LINQ to SQL result to DataTable or DataView VB.Net

    Question

  • Hi

    I need a temporary DataTable or DataView made by LINQ to SQL result. but i cant prepare them based on L2S method in VB.Net 2010. is it possible?

    And a basic question! What is the structure of L2S results? I was thinking it must be a DataTable!

    Please help me.

    Thanks in advance.
    Thursday, September 06, 2012 7:25 AM

Answers

  • Hi Kevin,

    Thank you for you reply my friend! would you please take a look to my problem that explained below?

    I created a Solution with 4 layers (project) in VB.net. In first layer i created some dbml file for accessing SQL tables. in Second layer, named "DataAccessLayer" i prepared some functions that they contain LINQ method and returning required data from first layer. For Example:

        Shared Function WorkOrderDetail()
            Dim db As New DataLinqToSql.dlqWorkOrderDataContext
            Return (From k In db.WorkOrderDetails Select k).AsEnumerable
        End Function

    According to your post, i created a new Module in "DataAccessLayer" and put "ToDataTable" function into it.

    Finally in the last layer of my Solution named "InterfaceLayer" for preparing a temporary Datatable i added this code.

     Dim MyDataTable as DataTable = (ClsDataAccessLayer.ClsdaWorkOrder.WorkOrderDetail).todatatable

    But in run time, i have a error that says: ToDataTable not found??!?

    (Layer defined for each other as reference.)

    Thank you again.



    The extension is here. The code should look like

    Shared Function WorkOrderDetail() As DataTable
        Dim db As New DataLinqToSql.dlqWorkOrderDataContext
        Return (From k In db.WorkOrderDetails Select xxxxxx).ToDataTable
    End Function

    Now we need to change the xxxxxx to column name such as shown in my first example. So we might have something like this.

    Shared Function WorkOrderDetail() As DataTable
        Dim db As New DataLinqToSql.dlqWorkOrderDataContext
        Return (From k In db.WorkOrderDetails Select OrderNumber = k.OrderNumber, ID = k.ID).ToDataTable
    End Function


    KSG

    • Marked as answer by R. Salehi Monday, September 10, 2012 10:41 AM
    Thursday, September 06, 2012 9:15 PM

All replies

  • I think you should use a RD before you start and then ILD that. 

    Success
    Cor

    Thursday, September 06, 2012 9:24 AM
  • I think you should use a RD before you start and then ILD that. 

    Success
    Cor

    Thanks for your reply. would you please explain it?
    Thursday, September 06, 2012 9:34 AM
  • I think you should use a RD before you start and then ILD that. 


    Success
    Cor

    Thanks for your reply. would you please explain it?

    Yes if you have a question then don't make a puzzle from it but write full what you mean with L2S. On English Google I could find it as a Estonia Soccer club.


    Success
    Cor

    Thursday, September 06, 2012 9:43 AM
  • I think you should use a RD before you start and then ILD that. 


    Success
    Cor

    Thanks for your reply. would you please explain it?

    Yes if you have a question then don't make a puzzle from it but write full what you mean with L2S. On English Google I could find it as a Estonia Soccer club.


    Success
    Cor

    I don't want to make a puzzle from my question. If you can help some one like me, do it. otherwise don't reply please!

    Thanks

    Thursday, September 06, 2012 10:21 AM
  • Hello,

    If we use this link as a point of reference for LINQ to SQL the following code will take a LINQ statement result as a DataTable. The underlying source is a SQL-Server database for NorthWind which is a sample database from Microsoft.

    Code will work for VS2008 or higher, VB.NET

    Dim db As New NwDataContext
    Dim Result As DataTable =
        (
            From T In db.Customers
            Where T.Country = "Mexico"
            Select Name = T.CompanyName, Contact = T.ContactName
        ).ToDataTable

    For this to work you will need to place the following language extension into a code module, not a class or form.

    <System.Diagnostics.DebuggerStepThrough()> _
    <System.Runtime.CompilerServices.Extension()> _
    Public Function ToDataTable(Of T)(ByVal value As IEnumerable(Of T)) As DataTable
        Dim returnTable As New DataTable
        Dim firstRecord = value.First
        For Each pi In firstRecord.GetType.GetProperties
            returnTable.Columns.Add(pi.Name, pi.GetValue(firstRecord, Nothing).GetType)
        Next
        For Each result 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


    KSG

    Thursday, September 06, 2012 12:27 PM
  • I have also put an example together for you and I have the (rather simple) code shown on a page of my site here. The data is from a datatable which contains U.S. Zip Codes and looks like this:

    As you see in the code, I have simply the word "Stop" which acts the same as putting a breakpoint in. If you want - and I would encourage you to - download the project folder which is zipped up and uploaded here.

    It contains the whole thing, database and all, and that way you can run it and when it stops, hover your mouse over each of the LINQ declarations to see just what it returned.

    I hope that helps. :)


    Please call me Frank :)

    Thursday, September 06, 2012 12:49 PM
  • Hi Kevin,

    Thank you for you reply my friend! would you please take a look to my problem that explained below?

    I created a Solution with 4 layers (project) in VB.net. In first layer i created some dbml file for accessing SQL tables. in Second layer, named "DataAccessLayer" i prepared some functions that they contain LINQ method and returning required data from first layer. For Example:

        Shared Function WorkOrderDetail()
            Dim db As New DataLinqToSql.dlqWorkOrderDataContext
            Return (From k In db.WorkOrderDetails Select k).AsEnumerable
        End Function

    According to your post, i created a new Module in "DataAccessLayer" and put "ToDataTable" function into it.

    Finally in the last layer of my Solution named "InterfaceLayer" for preparing a temporary Datatable i added this code.

     Dim MyDataTable as DataTable = (ClsDataAccessLayer.ClsdaWorkOrder.WorkOrderDetail).todatatable

    But in run time, i have a error that says: ToDataTable not found??!?

    (Layer defined for each other as reference.)

    Thank you again.



    Thursday, September 06, 2012 7:37 PM
  • Hi Kevin,

    Thank you for you reply my friend! would you please take a look to my problem that explained below?

    I created a Solution with 4 layers (project) in VB.net. In first layer i created some dbml file for accessing SQL tables. in Second layer, named "DataAccessLayer" i prepared some functions that they contain LINQ method and returning required data from first layer. For Example:

        Shared Function WorkOrderDetail()
            Dim db As New DataLinqToSql.dlqWorkOrderDataContext
            Return (From k In db.WorkOrderDetails Select k).AsEnumerable
        End Function

    According to your post, i created a new Module in "DataAccessLayer" and put "ToDataTable" function into it.

    Finally in the last layer of my Solution named "InterfaceLayer" for preparing a temporary Datatable i added this code.

     Dim MyDataTable as DataTable = (ClsDataAccessLayer.ClsdaWorkOrder.WorkOrderDetail).todatatable

    But in run time, i have a error that says: ToDataTable not found??!?

    (Layer defined for each other as reference.)

    Thank you again.



    The extension is here. The code should look like

    Shared Function WorkOrderDetail() As DataTable
        Dim db As New DataLinqToSql.dlqWorkOrderDataContext
        Return (From k In db.WorkOrderDetails Select xxxxxx).ToDataTable
    End Function

    Now we need to change the xxxxxx to column name such as shown in my first example. So we might have something like this.

    Shared Function WorkOrderDetail() As DataTable
        Dim db As New DataLinqToSql.dlqWorkOrderDataContext
        Return (From k In db.WorkOrderDetails Select OrderNumber = k.OrderNumber, ID = k.ID).ToDataTable
    End Function


    KSG

    • Marked as answer by R. Salehi Monday, September 10, 2012 10:41 AM
    Thursday, September 06, 2012 9:15 PM