Hello,
I was wondering if it's possible to use SQL to select a subset of data from
a DataTable? I'm wondering because I have to fix a large form that does a
large amount of data updating that is currently not using transactions - so
when it fails the database is left in a mess. I'd like to load the underlying
database tables into DataTables and then perform the processing on them.
Since all of the business logic and processing was built into the SQL update
and insert statements, it would be easiest if I could just run them against
the DataTables I make, then call DataAdapter.Update when done.
Is there a way to use SQL to update/insert on a .net DataTable?
Thanks!
George Heinrich

Yes you can. think of the DataTable as an in memory representation of a sql
server table with i.e. joins, relations and dml.

Look up the Select() method on the DataTable object. It returns an array of
DataRows.

As an aside you really want to "correct" your entire situation by adding
transacitons to you SP, removing business logic from the database to a data
layer, etc.

Your current path sounds like it is wasting tons of bandwidth and resources
retrieving unnecessary data that you want to later filter in code. Leave db
stuff to the db and logic to the code.

hope that helps.

--
kevin...


"George H" wrote:

Kevin, Thanks for the response. Unfortunately you are dead-on. However, I've
been thrown into this situation and am trying to triage the issues as tiem
allows. My main concern right now is that there is no transactions use din
thsi code, so when this 3000 line processing fails it leaves the database in
a non-atomic state. It's causing all sorts of problems. Also, the logic that
has been written into the SQL is so convoluted that it will take months to
untangle and fix. Currently, the code selects data into a datatable, then
updates it using a DB update call, then selects more data into another table,
then updates it using a DB call...it's extremely wasteful programming. I want
to wrap the whole thing in a transaction so I can roll it back when it
fails...but I don't have time to rewrite all of the SQL. So I can use a
transaction and just let teh code call the db 30-40 times or i can load all
the data into datatatbles and performi the sql against them and then just
update each datatable back to teh db once at the end...a rock and a hard
place!

"kevin" wrote:

George

Been there and done that. The positive side = "job security".

Also you might want to post in another group as this one looks dead. I
accidentally posted a question here yesteday, as opposed to the General
group, and just happened to see your post.
--
kevin...


"George H" wrote: