Hi Experts,
This question:
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_22627604.html...has inspired me to try to come up with a way to quickly apply update criteria to a single column in a DataTable without having to iterate the column's rows. So that the result would be as if you could run an update statement against the table itself. Something like:
UPDATE myTable SET column1 = 'updated' WHERE column1 = 'update_me'
Here's the solution I came up with, which doesn't work (yet):
Create a temporary column in the DataTable
Set the temp column's Expression property to an IIF statement applying the update crieria
Remove the Expression from the temp column but make the data persist (this is the part that doesn't work)
Set the Expression property of the column to be updated to the temp column
Remove the Expression from the column to be updated but make the data persist (again, won't work)
Remove the temp column
Here's a snippet I used to test this idea:
' Create the table
Dim dt As New DataTable
dt.Columns.Add("column1")
dt.Rows.Add("updated")
dt.Rows.Add("update_me")
DataGridView1.DataSource = dt
' Try to update using column Expression
dt.Columns.Add("temp")
dt.Columns("temp").Express
ion = "IIF(column1='update_me', 'updated', column1)
dt.Columns("temp").Express
ion = Nothing ' <--- this doesn't work; makes the data in temp go away
First, is this idea viable at all? In order to make this work I need to remove an Expression from the temporary column, but make the data that are already in that column persist. Is there a way to do that?
Second, my impression is that this method would be more efficient than iterating rows and updating values using a simple If block when the column has very many rows. I wonder what the row count threshold would be to determine which method would be more efficient. (I suppose I could answer this myself using some performance testing if I could just get it to work. :) )
Any input is appreciated.
Start Free Trial
