How to compare 2 different tables using c# code, get differents and do update/insert

Hiya

I'm having trouble writing code in c# as i'm a newbie.  This is what I need to do, I've got the psuedo/bsuiness logic in my head but I can't seem to translate this into c# code:

Compare TABLE A with TABLE B, does the ID already exist on table B? If it doesn't, then do an insert (get data from table A and it's related tables) into TABLE C and other tables related to TABLE C. Otherwise, if the Id already exists I have to do the above but do an update instead

NB It's not an option for me just replicate full table data from one destination to another, as I have to get only updated data this is based on some criteria (no dates though!) Also database table comparison tools are not an option.

Please can someone provide me with some code for doing this
Thanks
emmeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JimBrandleyCommented:
It's not all that difficult to do, but I need the details for your table definitions for all tables involved.

Jim
0
emmeeAuthor Commented:
Jim

--------------------------------------------------------
TABLE A and TABLES related to A
I forgot that Table A and those related to A are not tables but views.....

OpActivitiesV (Viewname)
   AcID
  SubAcID
  AcName
  SubActName
  StartDate
  EndDate
  Status

OpTasksV (View name)
  AcID
  SubAcID  
  TaskID  
  TaskName
  TaskPercent
  MainTask

OpTeamInV(View name)
  AcID
  SubAcID  
  TeamRole
  TeamName    
--------------------------------------------------------
TABLE B
ProcessingTable (Name of table)
  ActivityID (nvarchar 50)
  SubActivityID (Primary Key) (nvarchar 50)
  Sent (nvarchar 50)
  SentDate (DateTime)
  Approval (nvarchar 50)
  Approval date (DateTime)

--------------------------------------------------------

TABLE C and TABLES related to C
ActivitiesTable (Name of table)
  ActivityID (nvarchar 50)
  SubActivityID (Primary Key) (nvarchar 50)
  ActivityName (nvarchar MAX)
  SubActivityName  (nvarchar MAX)
  DateStarted (DateTime)
  DateEnded (DateTime)
  Status  (nvarchar MAX)

TasksTable  (Name of table)
  ActivityID (compund key) (nvarchar 50)
  SubActivityID  (compund key) (nvarchar 50)
  TaskID  (compund key) (nvarchar 50)
  TaskName (nvarchar MAX)
  PercentageOfTask (nvarchar 50)
  MainTask (nvarchar MAX)

TeamsInvolved
   ActivityID (nvarchar 50)
  SubActivityID  (primary key) (nvarchar 50)
  TeamRole (nvarchar 50)
  TeamName (nvarchar MAX)
   




0
emmeeAuthor Commented:
Hi Jim

Does the above table definitions make sense?

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

JimBrandleyCommented:
The most efficient way to accomplish this is to use a stored procedure, which you can then invoke from your C# code. I'll try to get this cross-posted to the correct DB zone if you can tell me which kind of DB you are using.

Jim
0
emmeeAuthor Commented:
I'm using SQL Server. Makes sense i.e. using a stored procedure.  Just out of curiosity how could I acomplish the above in C# using code comparison
0
emmeeAuthor Commented:
I'm using SQL Server. Makes sense i.e. using a stored procedure.  Just out of curiosity how could I acomplish the above in C# using code comparison as I would be interested in finding out out how to do that in C# as well
0
JimBrandleyCommented:
You would need to select the rows from OpActivitiesV that match and save them in a DataTable in a DataSet.  Then two more selects, one for OpTasksV and one for OpTeamInV to add two more DataTables to the DataSet. Then add relations to the tables in the DataSet. Then, you would need to insert the data from those three tables into the recipients.

Jim
0
emmeeAuthor Commented:
i had started doing the following earlier today, so is that wrong, opening up three datasets as opposed to one dataset?

I 'm stuck on trying to do a loop through to compare table A row with table B and so on
SqlConnection myConnection = new SqlConnection(@"server=servername; uid=myid; pwd=mypassword; Database=mydb");
 
myConnection.Open();
String sSQL = "SELECT * FROM dbo.ProcessingTable";
String sSQL2 = "select * from dbo.ActivitiesTable";
String sSQL3 = "Select * from dbo.OpActivitiesV";
           
SqlDataAdapter adapter = new SqlDataAdapter(sSQL, myConnection);
SqlDataAdapter adapter1 = new SqlDataAdapter(sSQL2, myConnection);
SqlDataAdapter adapter2 = new SqlDataAdapter(sSQL3, myConnection);
 
DataSet ds = new DataSet();
DataSet ds1 = new DataSet();
DataSet ds2 = new DataSet();
           
adapter.Fill(ds, "ProcessingTable");
adapter1.Fill(ds1, "ActivitiesTable");
adapter2.Fill(ds2, "OpActivitiesV");
 
custgrid.DataSource=ds.Tables["ProcessingTable"].DefaultView;
custgrid.DataBind();
pp.Controls.Add(custgrid);
 
custgrid2.DataSource=ds1.Tables["ActivitiesTable"].DefaultView;
custgrid2.DataBind();
pp2.Controls.Add(custgrid2);
 
custgrid3.DataSource=ds2.Tables["OpActivitiesV"].DefaultView;
custgrid3.DataBind();  
pp3.Controls.Add(custgrid3);

Open in new window

0
JimBrandleyCommented:
The comparison should be done with the select, Then you only get the rows back that need to be inserted. Say:
SELECT * FROM dbo.OpActivitiesV  WHERE SubAcID NOT IN(SELECT  SubActivityID FROM dbo.ProcessingTable

Jim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
emmeeAuthor Commented:
To admin

I'd like to award points, I'm sure I posted this elsewhere concerning this question. But, I'd like to award less as the question has not been fullly answered. However, I'd like to recognise that fact that some help was given

0
emmeeAuthor Commented:
Jim

--------------------------------------------------------
TABLE A and TABLES related to A
I forgot that Table A and those related to A are not tables but views.....

OpActivitiesV (Viewname)
   AcID
  SubAcID
  AcName
  SubActName
  StartDate
  EndDate
  Status

The one with this (Jim):  and award 100 points

OpTasksV (View name)
  AcID
  SubAcID  
  TaskID  
  TaskName
  TaskPercent
  MainTask
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.