clamum
Lifer
My experience with database interaction and DataTables in .NET has been limited to rather simple SELECT queries so I'm wondering if anyone can point me in the right direction with this.
I'm querying LDAP and getting a bunch of results, which get put into a DataTable if they do not exist in the corresponding SQL table (based on a UserID, an int primary key).
Then with this DataTable I do an insert into the table; this "insert from LDAP if not exists in database table" works fine, but then I need to run an UPDATE operation. What happens is I grab all results and create a subset DataTable for those that do not exist in the DB and need to be inserted. For those that do exist, I need to overwrite the DB table's data with the resulting data with an UPDATE query.
Problem is that I'm not sure how to go about doing this with what the project has setup as far as classes: SqlDataAdapter, SqlConnection, etc (System.Data.SqlClient classes). I've been using LINQ for so long and like I said anytime I've used these older classes it was simple select queries.
What I need to do is do something like this (in SQL):
UPDATE d
SET d.col1 = r.col1,
....d.col2 = r.col2,
....etc
FROM dbTable d
INNER JOIN result r ON d.col10 = r.col10
The join would be done on the primary key of the database table (the UserID column I mentioned above) with the corresponding unique column from the DataTable result set. I've tried doing a SqlDataAdapter.Update() call but I get a primary key violation.
Just wondering if anyone has any hints. Thanks for any help.
I'm querying LDAP and getting a bunch of results, which get put into a DataTable if they do not exist in the corresponding SQL table (based on a UserID, an int primary key).
Then with this DataTable I do an insert into the table; this "insert from LDAP if not exists in database table" works fine, but then I need to run an UPDATE operation. What happens is I grab all results and create a subset DataTable for those that do not exist in the DB and need to be inserted. For those that do exist, I need to overwrite the DB table's data with the resulting data with an UPDATE query.
Problem is that I'm not sure how to go about doing this with what the project has setup as far as classes: SqlDataAdapter, SqlConnection, etc (System.Data.SqlClient classes). I've been using LINQ for so long and like I said anytime I've used these older classes it was simple select queries.
What I need to do is do something like this (in SQL):
UPDATE d
SET d.col1 = r.col1,
....d.col2 = r.col2,
....etc
FROM dbTable d
INNER JOIN result r ON d.col10 = r.col10
The join would be done on the primary key of the database table (the UserID column I mentioned above) with the corresponding unique column from the DataTable result set. I've tried doing a SqlDataAdapter.Update() call but I get a primary key violation.
Just wondering if anyone has any hints. Thanks for any help.
