LINQ with DataSet in vb.net

1
votes

I want to select all columns in dataset.Dataset is retrived from database table.

Here is my code :

  lstvCustomers.Items.Clear()
    Dim result = (From cust In dsCust.Tables(0).AsEnumerable).ToList
    'When i set where clause condition (Where cust.Field(Of String)("Ccd").Contains(txtCustID.Text))
    ' error occured in bellow line Error : The source contains not datarow
    Dim custTable As DataTable = result.CopyToDataTable
    lstvCustomers.Columns.Clear()
    For cls As Integer = 1 To custTable.Columns.Count - 1
        lstvCustomers.Columns.Add("COl - " & cls)
    Next

    Dim i As Integer = 0
    For Each row In custTable.Rows
        Dim lst As ListViewItem = lstvCustomers.Items.Add(row(0))
        For cls As Integer = 1 To custTable.Columns.Count - 1
            lst.SubItems.Add(row(cls))
        Next
        i = i + 1
    Next

OUTPUT
Col1 COl2 COl3 COL4 Col5 COl6 COL7
Cust101 Cust101 True Cust101 Cust101 Cust101 232323
Cust102 Cust102 True Cust102 Cust102 Cust102 234324

I want to select all columns from Dataset. Help me.

3
vb.netlinq.net-4.0
Why don't you select the entire DataRow instead? Apart from that, what means "not retrievable"? – Tim Schmelter
I did with DataRow but could not work not rertrievable means it gets only "Ccd" columns' value – mmj89
What means not work? This works: From cust In custTable.AsEnumerable Where cust.Field(Of String)("Ccd").Contains(txtCustID.Text) and returns all datarows(cust) where the Ccd field contains the text in the textbox. – Tim Schmelter
Its working but i want to get all columns values(Ccd and Cnm). But it gives only first columns(Ccd) value. Second Column(Cnm) also gives values of First Colunm(Ccd) – mmj89
It's still not clear, if you filter the rows you get an IEnumerable(Of DataRow) that you can enumerate in a For Each or which you can use to create another collection(f.e. with CopyToDataTable a DataTable or with ToList a List(Of DataRow)). Then you can access each column via columnname or ordinal index. So actually you have all columns. If you need all columns in a single property, that's a different question. Maybe you want to join all columns to a single String. Please clarify your requirement. – Tim Schmelter

3 Answers

1
votes

Try:

    Dim qry = (From cust In custTable.AsEnumerable
                Where cust.Field(Of String)("Ccd").Contains(txtResults.Text)
                Select cust).ToList

This will return each datarow that matches the condition as a list of datarow where you can access each field as needed.

To add the rows to a listview try this:

For Each row In qry
    lstvCustomers.Items.Add(row.Field(Of String)("Ccd")).SubItems.Add(row.Field(Of String)("Cnm"))
Next
0
votes

I searched far and wide (well, more than four Google searches) for a solution to this and in the end I eventually gleaned (above) that LINQ doesn't do deletes - so you use LINQ to select the rows you want to get rid of and delete them through the 'usual' mechanism of the database technology in use. Seems to make all that database-agnostic propaganda about LINQ pretty silly? This one does at least work although I will play around with the two 'For Each' loops and see if I can reduce that to one. I had a table of Properties where I wanted to delete all the entries with a chosen property name (third keyfield) for an object type (first keyfield) and I came up with this. FYI the REPLY object is a bit like the Error object - I just package error messages, a binary Pass/fail flag and a few other things so I can pass back loads of stuff from a query and (eventually) show the error to the user.

  ' <summary>
  ' The user has decided they don't want a particular property type so we delete all
  ' properties of that type in the table - belonging to any object of that ObjectType
  ' </summary>
  ' <param name="sObjectType"></param>
  ' <param name="sName"></param>
  ' <returns></returns>
  ' <remarks></remarks>
  Public Function DeleteAllPropsByName(sObjectType As String, sName As String) As Reply

    DeleteAllPropsByName = New Reply(True)

    Dim T As DataTable = mDB.DataTableImage(msTableName)
    Dim q = From rw In T.AsEnumerable
            Where rw.Field(Of String)("ObjectType") = sObjectType _
            And rw.Field(Of String)("PropName") = sName

    ' Somewhere to remember our list of target rows to delete
    Dim rows As New List(Of DataRow)

    For Each row In q
      '
      ' LINQ doesn't delete so we need to delete from the Datatable directly.
      ' If we delete here we get the collection modified error so we have to save 
      ' the datarows to ANOTHER list and then delete them from there.
      rows.Add(row)

    Next

    For Each rw As DataRow In rows
      '
      ' Call the Delete routine in my table class passing it the 
      ' primary key of the row to delete
      '
      DeleteAllPropsByName = gDB.Table(msTableName).Delete( _
                                  rw.Item("ObjectType").ToString, _
                                  CInt(rw.Item("ObjectID")), _
                                  rw.Item("PropName").ToString)

      If Not DeleteAllPropsByName.OK Then
        ' The reply object (in DeleteAllPropsByName) has all the error info so we can just
        ' exit and return it if the delete failed.
        Exit Function
      End If

    Next

  End Function
0
votes
lstvCustomers.Items.Clear()  
Dim result = (From cust In dsCust.Tables(0).Select("Ccd LIKE '%" & txtCustID.Text & "%'")).ToList  
Dim clm As Integer = 0  
For Each row As DataRow In result  
    lstvCustomers.Items.Add(row(0))  
    lstvCustomers.Items(clm).SubItems.Add(row(1))  
    clm = clm + 1  
Next