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 @Table1_PK INT
SET @Table1_PK = SCOPE_IDENTITY()
INSERT INTO Table2 (Table1_PK, Col3) VALUES (@Table1_PK, Val3)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
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
UPDATE Table2
SET Col3 = @SomeValue3
WHERE Table1_PK = @Id
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
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
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
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[
^]