[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 721
  • Last Modified:

Linq to DataTable

Hi There,

I am using linq to populate DataGridView

Private DS As DataTable = New DataTable("O")
// I have some values in DS.....

        Dim query = _
    From o In DS.AsEnumerable() _
    Where (o.Field(Of String)("column1") = TextBox1.Text Or
         (o.Field(Of String)("column2") = TextBox2.Text)
       
    Select o Order By "column1" Descending

            ' Create a table from the query.
        Dim boundTable As DataTable = query.CopyToDataTable()

 With Me.dataGridView
             
            .DataSource = boundTable
            .AutoGenerateColumns = True

End with

When I try to search with empty values in textBox1 and TextBox2I am getting "The source contains no DataRows."

To get the results I have to have some data in textBox1 and TextBox2. If either of Box value is empty I got the above exception.

Please advice.

Thanks.
0
theartha
Asked:
theartha
  • 5
  • 4
  • 3
  • +1
2 Solutions
 
käµfm³d 👽Commented:
Would this suffice?

Dim boundTable As DataTable = DS.Clone()

query.CopyToDataTable(boundTable, LoadOption.OverwriteChanges)

Open in new window

0
 
vbighamCommented:
One of the awesome things about Linq is that you can define you query dynamically, in this case you might (or might not) have a conditional filter for the results:
 
' Start out with the basic order by query (true in all cases) '
Dim query = _
    From o In DS.AsEnumerable() _
    Select o Order By "column1" Descending

' Now filter by the others if we have values provided '
If Not String.IsNullOrWhiteSpace(TextBox1.Text) AndAlso Not String.IsNullOrWhiteSpace(TextBox2.Text) Then
    ' We have values for both, so filter the query with both '
    query = _
    From o In query _
    Where (o.Field(Of String)("column1") = TextBox1.Text _
    OrElse (o.Field(Of String)("column2") = TextBox2.Text)        
    Select o 

ElseIf Not String.IsNullOrWhiteSpace(TextBox1.Text)
    ' We have a value for column1, so filter the query with only that '
    query = _
    From o In query _
    Where (o.Field(Of String)("column1") = TextBox1.Text _
    Select o 

ElseIf Not String.IsNullOrWhiteSpace(TextBox2.Text)
    ' We have a value for column2, so filter the query with only that '
    query = _
    From o In query _
    Where (o.Field(Of String)("column2") = TextBox2.Text) _
    Select o 
End If

' Use the query as it was filtered (or was not) '
Dim boundTable As DataTable = query.CopyToDataTable()

With Me.dataGridView              
    .DataSource = boundTable
    .AutoGenerateColumns = True
End with

Open in new window

This code assumes .Net 4.0, if you aren't using that you might have to come up with your own implementation for String.IsNullOrWhiteSpace() or String.IsNullOrEmpty()
0
 
käµfm³d 👽Commented:
@vbigham
This code assumes .Net 4.0, if you aren't using that you might have to come up with your own implementation for String.IsNullOrWhiteSpace() or String.IsNullOrEmpty()
IsNullOrEmpty exist in 2.0+; IsNullOrWhiteSpace appears to be new  = )
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
vbighamCommented:
Ah, good to know.  Thanks, kaufmed!
0
 
thearthaAuthor Commented:
@kaufmed: Its not working. I mean filter is not working
@vbigham: This code might be good if we have few textBoxes, but in my case I have 5 to 6 textboxes.

Thanks.
0
 
käµfm³d 👽Commented:
Interesting. Here is my test:

Screenshot
And this is the code for each:

Top
Module Module1

    Sub Main()
        Dim DS As New DataTable("O")

        DS.Columns.Add("column1", GetType(String))
        DS.Columns.Add("column2", GetType(String))

        DS.Rows.Add("one", "y")

        Dim query = From o In DS.AsEnumerable() _
                    Where (o.Field(Of String)("column1") = "x" Or o.Field(Of String)("column2") = "y") _
                    Select o Order By "column1" Descending

        Dim boundTable As DataTable = DS.Clone()

        query.CopyToDataTable(boundTable, LoadOption.OverwriteChanges)

        Console.WriteLine(boundTable.Rows.Count)

        Console.ReadKey()
    End Sub

End Module

Open in new window


Bottom
Module Module1

    Sub Main()
        Dim DS As New DataTable("O")

        DS.Columns.Add("column1", GetType(String))
        DS.Columns.Add("column2", GetType(String))

        DS.Rows.Add("one", "two")

        Dim query = From o In DS.AsEnumerable() _
                    Where (o.Field(Of String)("column1") = "x" Or o.Field(Of String)("column2") = "y") _
                    Select o Order By "column1" Descending

        Dim boundTable As DataTable = DS.Clone()

        query.CopyToDataTable(boundTable, LoadOption.OverwriteChanges)

        Console.WriteLine(boundTable.Rows.Count)

        Console.ReadKey()
    End Sub

End Module

Open in new window

0
 
käµfm³d 👽Commented:
I do think you need to change your Order By condition though:

Select o Order By o.Field(Of String)("column1") Descending

Open in new window

0
 
thearthaAuthor Commented:
@kaufmed:

What if one column is empty and other has some value.

i.e in the below condition
Where (o.Field(Of String)("column1") = "x" Or o.Field(Of String)("column2") = "y") _

IF  "column1" = TextBox1.Text and if user enters nothing in it BUT enters  some value for textBox2.

"column1" = TextBox1.Text
"column2" = TextBox2.Text

.
.
.
.
"column1" = ""
"column2" = TextBox2.Text

How to construct a query in this scenario....

Thanks.




0
 
käµfm³d 👽Commented:
I'm probably missing something, but this is what I get:

Screenshot
using code:

Module Module1

    Sub Main()
        Dim DS As New DataTable("O")

        DS.Columns.Add("column1", GetType(String))
        DS.Columns.Add("column2", GetType(String))

        DS.Rows.Add("", "y")   ' "column1" is empty

        Dim query = From o In DS.AsEnumerable() _
                    Where (o.Field(Of String)("column1") = "x" Or o.Field(Of String)("column2") = "y") _
                    Select o Order By o.Field(Of String)("column1") Descending

        Dim boundTable As DataTable = DS.Clone()

        query.CopyToDataTable(boundTable, LoadOption.OverwriteChanges)

        Console.WriteLine(boundTable.Rows.Count)

        Console.ReadKey()
    End Sub

End Module

Open in new window

0
 
vbighamCommented:
You can try using ScottGu's Dynamic linq (haven't tried it myself):
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Or you could try building up a selector predicate function (warning this code creates multiple nested closures):
 
Friend Sub Test()
        ' TODO: Uncomment different combinations for testing '
        Dim textBox1 As New TextBox ' With {.Text = "9"} '
        Dim textBox2 As New TextBox ' With {.Text = "b"} '
        Dim textBox3 As New TextBox ' With {.Text = "three"} '
        Dim textBox4 As New TextBox ' With {.Text = "four"} '
        Dim textBox5 As New TextBox ' With {.Text = "e"} '

        Dim predicate As Func(Of DataRow, Boolean)

        Dim DS As New DataTable("O")
        DS.Columns.Add("column1", GetType(String))
        DS.Columns.Add("column2", GetType(String))
        DS.Columns.Add("column3", GetType(String))
        DS.Columns.Add("column4", GetType(String))
        DS.Columns.Add("column5", GetType(String))

        DS.Rows.Add("one", "two", "three", "four", "five")
        DS.Rows.Add("a", "b", "c", "d", "e")
        DS.Rows.Add("9", "8", "7", "6", "5")

        ' Note the change in the Order By based on kaufmeds suggestion '
        Dim query As EnumerableRowCollection(Of DataRow) = From o In DS.AsEnumerable() _
                                                           Select o Order By o.Field(Of String)("column1") Descending

        predicate = GetPredicateSelectorFunction(query, {"column1", "column2", "column3", "column4", "column5"}, textBox1, textBox2, textBox3, textBox4, textBox5)

        query = From o In query Where predicate(o) Select o

        Dim boundTable As DataTable = query.CopyToDataTable()

        Console.WriteLine(boundTable.Rows.Count)
    End Sub

    Private Function GetPredicateSelectorFunction(query As EnumerableRowCollection(Of DataRow), columnNames() As String, ParamArray textBoxes() As TextBox) As System.Func(Of DataRow, Boolean)
        Dim pred As Func(Of DataRow, Boolean) = New Func(Of DataRow, Boolean)(Function(o) False)
        Dim emptyCount As Integer

        If columnNames Is Nothing OrElse textBoxes Is Nothing Then
            ' Assume null parameters on purpose, and always select all '
            Return New Func(Of DataRow, Boolean)(Function(o As DataRow) True)
        End If

        If columnNames.Count <> textBoxes.Count Then
            Throw New Exception("column names and textboxes count must match.")
        End If

        For i As Integer = 0 To textBoxes.Count - 1
            Dim tb As TextBox = textBoxes(i)
            Dim colName As String = columnNames(i)

            If tb IsNot Nothing AndAlso Not String.IsNullOrWhiteSpace(tb.Text) Then
                Dim temp As Func(Of DataRow, Boolean) = pred
                pred = New Func(Of DataRow, Boolean)(Function(o As DataRow) temp(o) Or o.Field(Of String)(colName) = tb.Text)
            Else
                emptyCount += 1
            End If
        Next

        If emptyCount = textBoxes.Count Then
            Return New Func(Of DataRow, Boolean)(Function(o As DataRow) True)
        End If

        Return pred
    End Function

Open in new window

0
 
vbighamCommented:
Oops, I meant to remove the "query As EnumerableRowCollection(Of DataRow),"  parameter from GetPredicateSelectorFunction..  That turned out not to be necessary after all.
0
 
CodeCruiserCommented:
Do you have to use LINQ?
0
 
thearthaAuthor Commented:
@CodeCruiser:

No necessary, I am java developer and newbie in VB so I am trying to learn and implement.
0
 
CodeCruiserCommented:
There are other approaches as well. For example, you can try

dTable.DefaultView.RowFilter = "columnname='" & txt1.text & "' AND columnname='" & txt2.text & "'"

You will then get the result of filtering in dTable.DefaultView which is a DataView object.

You can also sort using the dTable.DefaultView.Sort property.


Another approach would be to use a stored procedure to do the filtering because you can use something like below to ignore empty textboxes

Select *
From table
Where (@textbox1='' OR columnname=@textbox1)

@textbox1 is a parameter to stored procedure.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now