How to execute a SQL query against a System.Data.DataTable Object?

Question

I have a DataTable object loaded using below code.

DataSet dataset = new DataSet();
adapter.Fill(dataset);
DataTable myDataTable = dataset.Tables[0];

I want to execute this SQL query against that DataTable object:

UPDATE myDataTable 
SET Enabled = 'YES' 
WHERE ID = '123';

I was able to do this using LINQ, but the requirement is to execute SQL queries against the DataTable object.

How can we achieve this?

Thanks huMpty.. I need to dynamically create the query from string input, and there can be multiple columns to update. Any suggestions? — Shalvin Abraham
– Shalvin Abraham, Commented Oct 5, 2015 at 15:05
so... you need to dynamically generate a SQL statement which will never be seen or used by a SQL database? "No matter how far you have gone on a wrong road, turn back." — STW
– STW, Commented Oct 5, 2015 at 15:16
It is not like that. An example would be, at run time, I need to change the value of the field Enabled to YES for ID 123 in the table. I am storing data in an in-memory datatable object for the time being, and hence wants to see if there is a way to execute SQL queries to update it. Only valid queries will be there. — Shalvin Abraham
– Shalvin Abraham, Commented Oct 5, 2015 at 15:19

Russ Clarke · Accepted Answer · 2015-10-05 14:52:25Z

2

The problem is that a DataTable isn't an implementation of SQL, although it understands very limited subset of SQL.

You can use the SELECT filter and then update the rows programmatically:

var myRows = myDataTable.Select("ID = '123'");
foreach (var row in myRows)
{
    row["Enabled"] = "YES";
}

answered Oct 5, 2015 at 14:52

Russ Clarke

17.9k4 gold badges44 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shalvin Abraham Over a year ago

Thanks Russ for your suggestion. The requirement is to execute update queries against the datatable at runtime. The column names are unknown at design time. So I am trying to figure out a way to perform updation at runtime. There can be multiple columns as well to update, and hence cannot use a parameterized version of your solution as well.

Russ Clarke Over a year ago

I would strongly suggest looking at executing directly against the database, using SqlConnection, SqlCommand and so on. If you don't know the schema at design time, then a DataTable isn't going to be the best option; Never mind that they're almost universally despised!

Collectives™ on Stack Overflow

How to execute a SQL query against a System.Data.DataTable Object?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related