Running SQL against DataTable

[jfrost10]

Hey guys,

Anyone know off hand if its possible, and how to do it if it is, to run a sql query against a datatable?

I want to delete a row from an in-memory datatable, but not sure how.

thanks

Jack

 

[Zarcom]

I don't think you can run a sql query. Instead loop through the records and convert the logic in your sql statment into vb or whatever language that your using. That'l do donkey, that'l do

 

[link9]

Specifically SQL... no.

The datatable does, however, have a .select() method which takes a filterExpression as an argument, and returns an array of datarows.

You could then take that array, new up the datatable that you .select()ed from, and plug the rows back into that table to get your desired effect.

Either that or plug them into an entirely new table, if you like.

Here's the doc address on my hard drive for that method:
ms-help://MS.VSCC/MS.MSDNVS/cpref/html/frlrfsystemdatadatatableclassselecttopic.htm

Should just be able to plug it into your help thing, and get more info that I'd be able to spit out right here.

That or search msdn, of course.

hth
paul

 

[jfrost10]

Thanks for the tips guys.

I found some extra info on why you can't fire a sql statement against a datatable, if you're interested.

Apparantly, since really datasets/datatables are really just XML in a pretty interface, there's no way to access them like you could old school recordsets that acted more like distributed, mini-databases.

This was from another user post, so although I'm not sure how bang on it is, it did make sense as I thought it through. Another option I thought of was to actually get the xml data, use an xslt transformation, and do it that way.

But that seemed too much like work too, so I have a code solution similar to Zar's idea of looping. Not what I would want as the ideal, but thats a limitation given more by microsoft.

one day, ONE DAY, users will stop buying 15 %^!@$@$ inch monitors and running 800x600 so I don't have to create these damn multi panel pages!


Jack

 

[link9]

Upon a bit more thought, I think the best way to approach this would be to:

Extract the .defaultView from the table, which is really just a handle to the actual rows
Apply a .rowFilter to that view to get at the rows that you want to delete
Iterate through those rows, deleting them as you go
Remove the .rowFilter

Voila. The rows are deleted from the dataTable.

 

[Zarcom]

stupid 15 inch That'l do donkey, that'l do