How to Delete Parent and Child Records in Database using Stored Procedure and C#

Introduction

In this article i will discuss How to Delete Parent and Child Records in Database using Stored Procedure and C#.This is very beneficial while deleting multiple records from DataGrid or Gridview.

Implementation

I have made one simple Store procedure, which can be used to Multiple Delete operations from Different Tables. The store procedure provides you the facility to check the foreign constraints also. If any particular record is in use in the given table, then the record cannot be deleted. And if not then it will be deleted.

Actually, I have made one column called Status which contains values like, "Active", "InActive" And "Delete".When I am call stored procedure not deleting the record from the table, but just modifying its status to 'Delete'.

In this procedure is accept 6 parameters.

@Id :is for single record and @MultiUserID is for deleting multiple records to be deleted.
@Mode:describes the mode to be passed to the Stored Procedure whether it is a single delete or multiple delete.
@MainTable:is the table from which the records are to be deleted.
@MainField:is the field of the MainTable (mostly the Primary Key column).
@Tables:are the table name the value of @MainField from @MainTable is used.
@Fields:are the fields which are value of @Mainfield(Ie. IDs) in the other tables.

The @Fields IDs of the Maintable used in other table as references. (which I used)

Stored Procedure

CREATE PROCEDURE [dbo].[Proc_Multiple_Delete]
(
@MODE varchar(50)='',
@ID int = 0,
@MultiUserId varchar(255)='',
@MainTable varchar(100)='',
@MainField varchar(100)='',
@Tables varchar(1000)='',
@Fields varchar(1000)=''
)
AS
BEGIN
DECLARE @INDEX_TABLE INT
DECLARE @INDEX_FIELD INT
DECLARE @INDEX_ID INT
DECLARE @TABLENAME VARCHAR(100)
DECLARE @FIELDNAME VARCHAR(100)
DECLARE @QUERY1 VARCHAR(1000)
DECLARE @QUERY2 VARCHAR(1000)
DECLARE @MULTIID VARCHAR(500)
DECLARE @STR VARCHAR(1000)
DECLARE @ID_DEL VARCHAR(10)

SET @INDEX_TABLE = 1
SET @INDEX_FIELD = 1
SET @INDEX_ID = 1

IF (@Tables IS NULL OR @Fields IS NULL) RETURN

WHILE (@INDEX_TABLE != 0 AND @INDEX_FIELD != 0)
BEGIN
SELECT @INDEX_TABLE = CHARINDEX(',', @Tables)
SELECT @INDEX_FIELD = CHARINDEX(',', @Fields)
IF (@INDEX_TABLE != 0 AND @INDEX_FIELD != 0)
BEGIN
SELECT @TABLENAME = LEFT(@Tables, @INDEX_TABLE-1)
SELECT @FIELDNAME = LEFT(@Fields, @INDEX_FIELD-1)
END
ELSE
BEGIN
SELECT @TABLENAME = @Tables
SELECT @FIELDNAME = @Fields
END

--PRINT(@TABLENAME + ' ' + @FIELDNAME)

SET @QUERY1 = 'SELECT ' + @MAINFIELD + ' FROM ' + @MAINTABLE + ' WHERE ' + @MAINFIELD + ' = ' + Convert(Varchar(20),@ID)
SET @QUERY2 = 'SELECT ' + @FIELDNAME + ' FROM ' + @TABLENAME + ' WHERE Status <> ''Delete'' '
--PRINT(@QUERY1)
--PRINT(@QUERY2)

CREATE TABLE #ID(SINGLE_ID INT)
INSERT #ID EXEC(@QUERY1)

CREATE TABLE #MULTIPLEID(MULTI_ID INT)
INSERT #MULTIPLEID EXEC(@QUERY2)

--SELECT SINGLE_ID FROM #ID

IF @MODE='DELETE'
BEGIN
IF EXISTS(SELECT SINGLE_ID FROM #ID)
BEGIN
IF @ID NOT IN (SELECT MULTI_ID FROM #MULTIPLEID)
BEGIN
EXEC('UPDATE '+ @MAINTABLE + ' SET Status=''Delete'' WHERE ' + @MAINFIELD + ' = ' + @ID)
SELECT @ID AS RETURNVAL
END
ELSE
BEGIN
SELECT 0 AS RETURNVAL
END
END
ELSE
BEGIN
SELECT 0 AS RETURNVAL
END
END

IF @MODE='MULTIDELETE'
BEGIN
IF @MultiUserID <> ''
BEGIN
WHILE(@INDEX_ID != 0)
BEGIN
SELECT @INDEX_ID = CHARINDEX(',',@MULTIUSERID)
IF @INDEX_ID != 0
SELECT @ID_DEL = LEFT(@MULTIUSERID, @INDEX_ID-1)
ELSE
SELECT @ID_DEL = @MULTIUSERID

IF @ID_DEL NOT IN (SELECT MULTI_ID FROM #MULTIPLEID)
BEGIN
EXEC('UPDATE ' + @MAINTABLE + ' SET Status = ''Delete'' WHERE ' + @MAINFIELD + ' = ' + @ID_DEL)
SELECT 1 AS RETURNVAL
END
ELSE
SELECT 0 AS RETURNVAL
SET @MULTIUSERID = RIGHT(@MULTIUSERID, LEN(@MULTIUSERID)-@INDEX_ID)
IF LEN(@MULTIUSERID) = 0 BREAK
END
END
ELSE
SELECT 0 AS RETURNVAL
END

-- Upto Here
SELECT @Tables = RIGHT(@Tables, LEN(@Tables)- @INDEX_TABLE)
SELECT @Fields = RIGHT(@Fields, LEN(@Fields)- @INDEX_FIELD)

IF(LEN(@Tables)=0 or LEN(@Fields)=0) BREAK
END
END

How to use?

Example 

EXEC Proc_Multi_Delete 'MULTIDELETE',0,'1,9','BookMaster','BookID','BookAllocation','BookID'

Here we are passing two IDs for books to be deleted from BookMaster table which has main field (i.e. Primary Key) as BookId.The another table is BookAllocation table, in which the BookID of BookMaster is used as reference as the same name ie. BookID. But in some cases the reference may not have the same name of the column, that why we have to pass that field name in @Fields.

So the Procedure will check the BookID 1 and 9 whether they are present in BookAllocation table or not. If they are present, it will return 0 value and if they are not used in BookAllocation table, the Stored Procedure will set their status as 'Delete'.

That's all.i hope this is help and save time.thank you for reading.