SQL Query/Command against In-Memory DataTable?

  • Thread starter Thread starter Terry Olsen
  • Start date Start date
T

Terry Olsen

I have an in-memory DataTable with about 35,000 rows. I have an IP
address column that is populated and a hostname column that is not. I
want to be able to quickly update the hostname column so that it shows
the hostname for the IP. Rather than iterate through all the rows, I'm
wondering if there's a way to update it quickly with an SQL update
command? Something like...

UPDATE MyDT SET hostname='localhost' WHERE IP='127.0.0.1'

Can this be done against an in-memory DataTable?
 
Terry said:
I have an in-memory DataTable with about 35,000 rows. I have an IP
address column that is populated and a hostname column that is not. I
want to be able to quickly update the hostname column so that it shows
the hostname for the IP. Rather than iterate through all the rows, I'm
wondering if there's a way to update it quickly with an SQL update
command? Something like...

UPDATE MyDT SET hostname='localhost' WHERE IP='127.0.0.1'

Can this be done against an in-memory DataTable?
Click to expand...

AFAIK, and someone I hope will correct me if I am wrong, but since the
local datatable doesn't have an index built into it, the table is going
to have to run through each row itself at runtime.

You could build your own index using a hash collection. Then the lookup
would give you the row, but you'd have to handle adds/deletes in your
datatable and collection together.

Chris
 
Terry Olsen said:
I have an in-memory DataTable with about 35,000 rows. I have an IP
address column that is populated and a hostname column that is not. I
want to be able to quickly update the hostname column so that it shows
the hostname for the IP. Rather than iterate through all the rows, I'm
wondering if there's a way to update it quickly with an SQL update
command? Something like...

UPDATE MyDT SET hostname='localhost' WHERE IP='127.0.0.1'

Can this be done against an in-memory DataTable?
Click to expand...


You can filter based on SQL-like predicates,

For Each r As DataRow In MyDT.Select("IP = '127.0.0.1'")
r("hostname") = "localhost"
Next

You can even speed this up with a key


MyDT.PrimaryKey = New DataColumn() {MyDT.Columns("IP")}

David
 
Thanks David. That worked great. I had to change the name of the
column because it contained a minus sign (c-ip) and the select method
threw "cannot find column [c]"
 
An In-Memory DataTable is one I created completely in memory, as opposed
to one that I loaded from an SQL server. Which means there is no
"database" to write back to. So this DataTable is not a representation
of a database as some tutorials specify, but it IS the database I'm
working with.

I suppose using the term In-Memory DataTable is akin to saying "Hot
Water Heater".
 
Back
Top