[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3893
  • Last Modified:

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
0
emmee
Asked:
emmee
  • 7
  • 4
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now