CODEWITHSUNDEEP
vb.netlinq.net-4.0
mmj89
mmj89

Reputation: 33

LINQ with DataSet in vb.net

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.

Upvotes: 2

Views: 10266

Answers (3)

mmj89
mmj89

Reputation: 33

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

Upvotes: 0

Captain Fantastic
Captain Fantastic

Reputation: 1

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

Upvotes: 0

tinstaafl
tinstaafl

Reputation: 6948

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

Upvotes: 2

Related Questions