CODEWITHSUNDEEP
sqlvb.net
JH99
JH99

Reputation: 3

Querying datasets vb.net

I'm working with an external access database (.accdb) in VB.NET and I'm trying to limit the amount of times that my [Winforms] program pings the data since it slows it down pretty considerably. I figured I could do this by querying my in-memory dataset instead of continuously going back to the database itself. Though, unfortunately, I cannot seem to figure out how to query my in-memory dataset. I tried looking up LINQ but couldn't find any instructions on how to set it up.

Am I missing something? Is there a better way to do this?

Thanks so much! My starting code below...

 locsql = "SELECT * FROM TABLE1)
 Dim objAdapter As New OleDb.OleDbDataAdapter(locsql, objconn)
 objAdapter.Fill(locdata, "BASE")

So I can easily do some basic things I need with locdata("BASE").rows.item("Item") but I have to do some stuff like SELECT thing FROM TABLE1 WHERE this = this and that = that and I would just rather not keep going back to the database if possible.

Is it possible?

Upvotes: 0

Views: 6677

Answers (3)

JH99
JH99

Reputation: 3

What I ended up doing was breaking down my data filters and creating new tables based on a series of loops and if/else statements. all of these responses were very helpful, just didn't seem to work for what I needed. Thanks everyone!

Upvotes: 0

Steve
Steve

Reputation: 216291

If you want to use Linq against a DataTable you need to apply the AsEnumerable method then apply the usual Linq methods.

For example.

If you need to find all the customers having the Field Country equals to 'Canada' and then Sum the total of their Amount column you could write

Dim listOfCanadians = locdata.Tables(0).AsEnumerable() _ 
                      .Where(Function(row) row.Field(Of String)("Country") = "Canada") _ 
                      .ToList()
Dim totalCanadaAmount = listOfCanadians.Sum(Function(row) row.Field(Of Decimal)("Amount"))

Upvotes: 1

Steve
Steve

Reputation: 5545

You need to use the Select method on each datatable you want to query. The syntax is very similar to the part after the WHERE clause in you normal SQL query. IE

locdata.tables(0).Select("ID = 47")

This returns an array of datarows.

Upvotes: 2

Related Questions