LINQ 与 vb.net 中的数据集

LINQ with DataSet in vb.net

提问by mmj89

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

输出
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.

我想从数据集中选择所有列。帮我。

采纳答案by tinstaafl

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

回答by mmj89

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

回答by Captain Fantastic

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.

我广泛搜索（嗯，超过四个谷歌搜索）来解决这个问题，最后我最终收集到（上面）LINQ 不会删除 - 所以你使用 LINQ 来选择你想要删除的行通过正在使用的数据库技术的“通常”机制删除它们。似乎使所有关于 LINQ 的与数据库无关的宣传变得非常愚蠢？尽管我会玩弄两个“For Each”循环，看看我是否可以将其减少到一个，但这个至少可以工作。我有一个属性表，我想在其中删除具有对象类型（第一个键域）的选定属性名称（第三个键域）的所有条目，然后我想出了这个。仅供参考 REPLY 对象有点像 Error 对象 - 我只是打包错误消息，

  ' <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