Click here to Skip to main content
11,482,402 members (72,164 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET
how to insert,update,delete,select record from multiple table having primary key and foriegn key constrain?
Posted 24-Dec-12 8:18am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

For Insert use SCOPE_IDENTITY()[^] to get value of the primary key and then insert that into the 2nd table which has a foreign key reference.
BEGIN TRY
  BEGIN TRANSACTION
 
     INSERT INTO Table1 (Col1, Col2) VALUES (Val1, Val2)
     --Declare a variable to store the auto generated Primary key
     DECLARE @Table1_PK INT
     SET @Table1_PK = SCOPE_IDENTITY()
 
     --Insert data into the second table
     INSERT INTO Table2 (Table1_PK, Col3) VALUES (@Table1_PK, Val3)
 
 
 
  COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
  ROLLBACK TRANSACTION
  --Exception handling
 END CATCH
For Update use the unique Primary Key value to update the records
BEGIN TRY
  BEGIN TRANSACTION
 
     UPDATE Table1
     SET Col1 = @SomeValue1,
         Col2 = @SomeValue2
     WHERE Id = @Id --Here Id is the Primary key column of table1 and has a 
                    --foreign key reference in table2

    UPDATE Table2
    SET Col3 = @SomeValue3
    WHERE Table1_PK = @Id
 

  COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
  ROLLBACK TRANSACTION
  --Exception handling
 END CATCH

Like update we use the unique Primary key value to delete the records. First delete all the records from the table that has a foreign key reference and then delete the data from the main table
BEGIN TRY
  BEGIN TRANSACTION
 
   DELETE FROM Table2 WHERE Table1_PK = @Id
   DELETE FROM Table1 WHERE Id = @Id 
 
  COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
  ROLLBACK TRANSACTION
  --Exception handling
 END CATCH

For selecting the records you do a INNER JOIN[^] or a LEFT JOIN[^] and retrieve the data

SELECT * FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.ID = T2.Table1_PK 
--Where clause goes here
You might also find the below articles helpful
CRUD Operation in ASP.NET Web Applications Using HTTP Handler and jQuery[^]
Insert, Update, Search and Delete (CRUD operation) using ASP.Net and MySQL[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.150520.1 | Last Updated 24 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100