SQL Query/Command against In-Memory DataTable?

[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?

 

[Chris]

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?

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

 

[David Browne]

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?

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

 

[Cor Ligthert [MVP]]

Terry,

What is an out-memory DataTable.

A DataTable is not a database. However, you can get in many ways a datarow
back.

In my opinion is for your purpose the mose easy one the

DataTable.Select
http://msdn.microsoft.com/library/d.../frlrfsystemdatadatatableclassselecttopic.asp

If it is the primary key to select, than is better the

DataRowCollection.Find
http://msdn.microsoft.com/library/d...systemdatadatarowcollectionclassfindtopic.asp

I hope this helps,

Cor

 

[Terry Olsen]

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]"

 

[Terry Olsen]

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".