none
DataTable result from VB.net Linq enquiry on SQLServer RRS feed

  • Question

  • Using Visual Basic in Visual Studio Community 2015 I have successfully built a LINQ enquiry over several tables within an SQLServer database that results in the production of a List(of …) object.

    However, what I really want to do is to produce a DataTable result instead (because it can later be filtered as a DataView for presentation purposes).

    I know that I could "convert" the contents of my List(of …) object into a DataTable object row-by-row, field-by-field - but I would rather produce the DataTable directly if possible.

    As usual - any advice you may have to offer will be gratefully received.

    PaulJ

    • Moved by 宝宝徐 Wednesday, November 8, 2017 5:26 AM
    Tuesday, November 7, 2017 9:40 AM

Answers

  • Hi Cherry,

    Thank you for your contribution to my question.

    I have been to the page you described a couple of times (when previously attempting to Google an answer).  Unfortunately I don't speak C# so I can't quite follow the code examples.  Is their a VB version of this page?

    Thanks and rgds,

    PaulJ

    For the code Cherry presented you could take the code and convert it here.

    C#

    // Bind the System.Windows.Forms.DataGridView object
    // to the System.Windows.Forms.BindingSource object.
    dataGridView.DataSource = bindingSource;
    
    // Fill the DataSet.
    DataSet ds = new DataSet();
    ds.Locale = CultureInfo.InvariantCulture;
    FillDataSet(ds);
    
    DataTable orders = ds.Tables["SalesOrderHeader"];
    
    // Query the SalesOrderHeader table for orders placed 
    // after August 8, 2001.
    IEnumerable<DataRow> query =
        from order in orders.AsEnumerable()
        where order.Field<DateTime>("OrderDate") > new DateTime(2001, 8, 1)
        select order;
    
    // Create a table from the query.
    DataTable boundTable = query.CopyToDataTable<DataRow>();
    
    // Bind the table to a System.Windows.Forms.BindingSource object, 
    // which acts as a proxy for a System.Windows.Forms.DataGridView object.
    bindingSource.DataSource = boundTable;
    

    Converted to VB.NET

    ' Bind the System.Windows.Forms.DataGridView object
    ' to the System.Windows.Forms.BindingSource object.
    dataGridView.DataSource = bindingSource
    
    ' Fill the DataSet.
    Dim ds As New DataSet()
    ds.Locale = CultureInfo.InvariantCulture
    FillDataSet(ds)
    
    Dim orders As DataTable = ds.Tables("SalesOrderHeader")
    
    ' Query the SalesOrderHeader table for orders placed 
    ' after August 8, 2001.
    Dim query As IEnumerable(Of DataRow) = From order In orders.AsEnumerable() Where order.Field(Of DateTime)("OrderDate") > New DateTime(2001, 8, 1)order
    
    ' Create a table from the query.
    Dim boundTable As DataTable = query.CopyToDataTable(Of DataRow)()
    
    ' Bind the table to a System.Windows.Forms.BindingSource object, 
    ' which acts as a proxy for a System.Windows.Forms.DataGridView object.
    bindingSource.DataSource = boundTable
    
    '=======================================================
    'Service provided by Telerik (www.telerik.com)
    'Conversion powered by NRefactory.
    'Twitter: @telerik
    'Facebook: facebook.com/telerik
    '=======================================================


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, November 8, 2017 2:43 PM
    Moderator

All replies

  • Hi,

    I will move your thread to VB forum for professional support, if you have problem about visual studio IDE, please feel free to let me know.

    Best regards,

    Joyce


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 8, 2017 5:24 AM
  • Hi PJ,

    You can take a look the following article to use CopyToDataTable() method to do this.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/creating-a-datatable-from-a-query-linq-to-dataset 

    Best regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 8, 2017 7:59 AM
    Moderator
  • Hi Cherry,

    Thank you for your contribution to my question.

    I have been to the page you described a couple of times (when previously attempting to Google an answer).  Unfortunately I don't speak C# so I can't quite follow the code examples.  Is their a VB version of this page?

    Thanks and rgds,

    PaulJ

    Wednesday, November 8, 2017 11:02 AM
  • VB and C# have not so many differences. Likewise Dutch and English. Most Dutch persons can read English but English persons seldom Dutch. Those languages as official languages are the most alike to each other. (Siblings) 

    The same is with C# and VB. If you know a little bit C# as a VB developer, you can completely read it. 

    Meanwhile you can use this freeware converter for snippets to do the job with that page. 

    https://www.tangiblesoftwaresolutions.com/free_editions.html


    Success
    Cor


    • Edited by Cor Ligthert Wednesday, November 8, 2017 12:01 PM
    Wednesday, November 8, 2017 12:00 PM
  • LINQ query results do not transform directly to a DataTable, but the below code may work for you as an extension method. It looks like the second answer contains a function that will do what you are asking:

    https://stackoverflow.com/questions/3291599/convert-a-linq-query-resultset-to-a-datatable


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, November 8, 2017 1:03 PM
  • However, what I really want to do is to produce a DataTable result instead (because it can later be filtered as a DataView for presentation purposes).

     know that I could "convert" the contents of my List(of …) object into a DataTable object row-by-row, field-by-field - but I would rather produce the DataTable directly if possible.

    I am trying to figure out why, since a List(of objects) can be bound to the control and filtered too just like the datatable.

    Wednesday, November 8, 2017 1:44 PM
  • Hi Cherry,

    Thank you for your contribution to my question.

    I have been to the page you described a couple of times (when previously attempting to Google an answer).  Unfortunately I don't speak C# so I can't quite follow the code examples.  Is their a VB version of this page?

    Thanks and rgds,

    PaulJ

    For the code Cherry presented you could take the code and convert it here.

    C#

    // Bind the System.Windows.Forms.DataGridView object
    // to the System.Windows.Forms.BindingSource object.
    dataGridView.DataSource = bindingSource;
    
    // Fill the DataSet.
    DataSet ds = new DataSet();
    ds.Locale = CultureInfo.InvariantCulture;
    FillDataSet(ds);
    
    DataTable orders = ds.Tables["SalesOrderHeader"];
    
    // Query the SalesOrderHeader table for orders placed 
    // after August 8, 2001.
    IEnumerable<DataRow> query =
        from order in orders.AsEnumerable()
        where order.Field<DateTime>("OrderDate") > new DateTime(2001, 8, 1)
        select order;
    
    // Create a table from the query.
    DataTable boundTable = query.CopyToDataTable<DataRow>();
    
    // Bind the table to a System.Windows.Forms.BindingSource object, 
    // which acts as a proxy for a System.Windows.Forms.DataGridView object.
    bindingSource.DataSource = boundTable;
    

    Converted to VB.NET

    ' Bind the System.Windows.Forms.DataGridView object
    ' to the System.Windows.Forms.BindingSource object.
    dataGridView.DataSource = bindingSource
    
    ' Fill the DataSet.
    Dim ds As New DataSet()
    ds.Locale = CultureInfo.InvariantCulture
    FillDataSet(ds)
    
    Dim orders As DataTable = ds.Tables("SalesOrderHeader")
    
    ' Query the SalesOrderHeader table for orders placed 
    ' after August 8, 2001.
    Dim query As IEnumerable(Of DataRow) = From order In orders.AsEnumerable() Where order.Field(Of DateTime)("OrderDate") > New DateTime(2001, 8, 1)order
    
    ' Create a table from the query.
    Dim boundTable As DataTable = query.CopyToDataTable(Of DataRow)()
    
    ' Bind the table to a System.Windows.Forms.BindingSource object, 
    ' which acts as a proxy for a System.Windows.Forms.DataGridView object.
    bindingSource.DataSource = boundTable
    
    '=======================================================
    'Service provided by Telerik (www.telerik.com)
    'Conversion powered by NRefactory.
    'Twitter: @telerik
    'Facebook: facebook.com/telerik
    '=======================================================


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, November 8, 2017 2:43 PM
    Moderator
  • Ladies, gentlemen and DA924x,

    Thank you all for your contributions which have not only answered my original question but given me much more to think about.

    The more i learn about VB.net the more I realise how little I know and how valuable these community pages are.

    Rgds,

    Paul J

    Thursday, November 9, 2017 4:26 PM
  • Ladies, gentlemen and DA924x,

    Thank you all for your contributions which have not only answered my original question but given me much more to think about.

    The more i learn about VB.net the more I realise how little I know and how valuable these community pages are.

    Rgds,

    Paul J

    The advantage of using a collection of objects as opposed to a datatable.

    https://dzone.com/articles/reasons-move-datatables

    http://lauteikkehn.blogspot.com/2012/03/datatable-vs-list.html

    Friday, November 10, 2017 3:31 PM