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 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.
  1. public void Insert(object sender, EventArgs e)  
  2. {  
  3.    int a = 0;  
  4.    cms.clientname = name.Text;  
  5.    cms.mobile = mobile.Text;  
  6.    cms.Email = email.Text;  
  7.    cms.productname = prdname.Text;  
  8. foreach (GridViewRow row in GridView1.Rows)  
  9. {  
  10.    cms.productname = row.Cells[0].Text;  
  11.    cms.productname = row.Cells[1].Text;  
  12.    cms.strings = row.Cells[2].Text;  
  13.    a=objDA.AddClientProduct(cms);  
  14.    }  
  15.    if (a > 0)  
  16.     {  
  17.       ClientScript.RegisterStartupScript(this.GetType(), "alert""alert('Insert is successfull')"true);  
  18.    }  
  19. }  
The stored procedure that I have created is mentioned below.
  1. CREATE PROCEDURE [dbo].[Usp_FinalInsert]  
  2. (  
  3.    @Clientname NVARCHAR(100),  
  4.    @Email NVARCHAR(100),  
  5.    @Mobile NVARCHAR(100),  
  6.    @Prodname NVARCHAR(100),  
  7.    @Input NVARCHAR(500)  
  8. )  
  9. AS  
  10. BEGIN  
  11. -------------------------------------------------------------------  
  12. DECLARE @lClientID INT  
  13. DECLARE @lProductID INT  
  14. DECLARE @lCharacter CHAR(1) = ','  
  15. ---------------------------  
  16. DECLARE @Output TABLE (value NVARCHAR(1000))  
  17. DECLARE @StartIndex INT,  
  18. @EndIndex INT  
  19. SET @StartIndex = 1  
  20. IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @lCharacter  
  21. BEGIN  
  22. SET @Input = @Input + @lCharacter  
  23. END  
  24. WHILE CHARINDEX(@lCharacter, @Input) > 0  
  25. BEGIN  
  26. SET @EndIndex = CHARINDEX(@lCharacter, @Input)  
  27. --------------------------------  
  28. INSERT INTO @Output  
  29. (  
  30.    value  
  31. )  
  32. SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)  
  33. SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))  
  34. END  
  35. -----------------------------------------------  
  36. IF NOT EXISTS( SELECT 1 FROM tblClient WHERE clientname = @Clientname  
  37. AND Email = @Email)  
  38. BEGIN  
  39. INSERT INTO tblClient ( clientname,Email)  
  40. VALUES (@Clientname, @Email)  
  41. SELECT @lClientID = @@IDENTITY  
  42. END  
  43. ELSE  
  44. BEGIN  
  45. SELECT @lClientID = clientID FROM tblClient  
  46. WHERE clientname = @Clientname AND Email = @Email  
  47. END  
  48. ----------------------------------------------------  
  49. IF NOT EXISTS( SELECT 1 FROM tblProduct  
  50. WHERE clientID = @lClientID AND prodname = @Prodname)  
  51. BEGIN  
  52. INSERT INTO tblProduct ( prodname, ClientID)  
  53. VALUES (@Prodname, @lClientID) SELECT @lProductID = @@IDENTITY  
  54. END  
  55. ELSE  
  56. BEGIN  
  57. SELECT @lProductID = prodID FROM tblProduct  
  58. WHERE clientID = @lClientID AND prodname = @Prodname  
  59. END  
  60. -----------------------------------------------------  
  61. SELECT @lProductID, @lClientID, value  
  62. FROM @Output o  
  63. INSERT INTO tblParamTransact ( ProdID, ClientID,paramID)  
  64. SELECT @lProductID as lProductID, @lClientID as lClientID, value  
  65. FROM @Output o  
  66. 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).