Before starting, I would like to thank Sudipta Sanyal Sir for making this easy for me.
Sometimes, it becomes tough for beginners or intermediate users to insert data into a database in parent-child relationships (in a Tree-structured format), especially when multiple rows are going to be affected at once in multiple tables.
Let's understand it with an example.
Here, I have 4 tables -
- tblClient: Where client information will be inserted.
- tblProduct: All products of the above-inserted client. (Say Client 1 can have more than two products to insert).
- tblParameterTransact: Here, we'll insert the parameter against a particular client and a particular product.
- tblParameter: the parameters that the client took while inserting the product.
Note - ID column in each and every table is of IDENTITY Type. I am attaching an image for exact understanding of the flow.
This is the sample form that I have created.
Those checkboxes are fetched from table tblParameter.
First, we'll fill in the client information, and then we'll insert products and their parameters. I am holding the product information in Viewstate (datatable) so that the client can insert multiple products.
It will look like the below image.
Now, by looking at the image, we know that a total of 7 rows are going to be affected by the table transaction. Here, I am looping through GridView and inserting the data to the tables.
Here is the code for reference.
- public void Insert(object sender, EventArgs e)
- {
- int a = 0;
- cms.clientname = name.Text;
- cms.mobile = mobile.Text;
- cms.Email = email.Text;
- cms.productname = prdname.Text;
- foreach (GridViewRow row in GridView1.Rows)
- {
- cms.productname = row.Cells[0].Text;
- cms.productname = row.Cells[1].Text;
- cms.strings = row.Cells[2].Text;
- a=objDA.AddClientProduct(cms);
- }
- if (a > 0)
- {
- ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Insert is successfull')", true);
- }
- }
The stored procedure that I have created is mentioned below.
- CREATE PROCEDURE [dbo].[Usp_FinalInsert]
- (
- @Clientname NVARCHAR(100),
- @Email NVARCHAR(100),
- @Mobile NVARCHAR(100),
- @Prodname NVARCHAR(100),
- @Input NVARCHAR(500)
- )
- AS
- BEGIN
-
- DECLARE @lClientID INT
- DECLARE @lProductID INT
- DECLARE @lCharacter CHAR(1) = ','
-
- DECLARE @Output TABLE (value NVARCHAR(1000))
- DECLARE @StartIndex INT,
- @EndIndex INT
- SET @StartIndex = 1
- IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @lCharacter
- BEGIN
- SET @Input = @Input + @lCharacter
- END
- WHILE CHARINDEX(@lCharacter, @Input) > 0
- BEGIN
- SET @EndIndex = CHARINDEX(@lCharacter, @Input)
-
- INSERT INTO @Output
- (
- value
- )
- SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
- SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
- END
-
- IF NOT EXISTS( SELECT 1 FROM tblClient WHERE clientname = @Clientname
- AND Email = @Email)
- BEGIN
- INSERT INTO tblClient ( clientname,Email)
- VALUES (@Clientname, @Email)
- SELECT @lClientID = @@IDENTITY
- END
- ELSE
- BEGIN
- SELECT @lClientID = clientID FROM tblClient
- WHERE clientname = @Clientname AND Email = @Email
- END
-
- IF NOT EXISTS( SELECT 1 FROM tblProduct
- WHERE clientID = @lClientID AND prodname = @Prodname)
- BEGIN
- INSERT INTO tblProduct ( prodname, ClientID)
- VALUES (@Prodname, @lClientID) SELECT @lProductID = @@IDENTITY
- END
- ELSE
- BEGIN
- SELECT @lProductID = prodID FROM tblProduct
- WHERE clientID = @lClientID AND prodname = @Prodname
- END
-
- SELECT @lProductID, @lClientID, value
- FROM @Output o
- INSERT INTO tblParamTransact ( ProdID, ClientID,paramID)
- SELECT @lProductID as lProductID, @lClientID as lClientID, value
- FROM @Output o
- END
After insertion, the tables look like this.
Here, I am sending the parameter values separated by commas. So, for every value before the comma, a new row will be created in the Transaction Table (as mentioned in the Stored Procedure).
Another alternative to this is to send the values in XML format to the database in a parent-child relationship (Tree-structured format).