Inserting Data Into Multiple Tables In MSSQL At Once In .NET (Affecting Multiple Rows)

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 an 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 (data table) 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).