Here, I will explain how to insert data into master and detail table in C# using single store procedure.

Generally people create a separate store procedure for each table like master and detail table.
But say you execute a store procedure from C# code for master table (salemaster) and it executes successfully, and again you execute store procedure from C# code for the second table (saledetail) table, but what if the second execution fails for any reason like data mistmatch, run time error occured, sql server connection timeout etc.?
Your master table(salemaster) executed successfully but detail table (saledetail) did not. So there is a problem of relationship because your master table has rows (data) but detail table does not. So how to control this? That's why I am demonstrating this solution for said problem.

First, you need to create a sample database with the two tables, Salemaster and Saledetail, with the following fields and store procedure:

SaleMaster
  1. Create Table SaleMaster
  2. (ID Int NOT NULL identity,
  3. SaleDate Datetime,
  4. CustomerName varchar (50)
  5. )
SaleDetail
  1. Create Table SaleDetail
  2. (ID Int NOT NULL identity,
  3. SalesMasterID Int NOT NULL,
  4. ProdName varchar (50),
  5. Qty decimal (14,2),
  6. Rate decimal (14,2)
  7. )
  10. Create Procedure ADD_SALE_MASTER_DETAIL_TABLE
  11. @SaleDate date,
  12. @Customername varchar (50),
  13. @StrProduct TEXT,
  14. @StrQty TEXT,
  15. @StrRate TEXT,
  16. @Gridcount smallint
  17. AS
  18. Declare
  19. @Product varchar (50),
  20. @Qty decimal(14,2),
  21. @Rate decimal(14,2),
  22. @C1 int,
  23. @C2 int,
  24. @C3 int,
  26. @COUNT INT,
  27. @MAXSRNO INT
  30. SET NOCOUNT ON;
  31. BEGIN TRAN
  33. SET @MAXSRNO=(SELECT ISNULL(MAX(ID),0)+1 FROM SaleMaster)
  35. Insert into SaleMaster(Saledate,customerName)
  36. Values(@Saledate,@Customername)
  38. -------------------Prdouct grid-------------------
  39. SET @C1 = 1
  40. SET @C2 = 1
  41. SET @C3 = 1
  42. SET @COUNT=1
  44. While @Count<=@Gridcount
  45. Begin
  46. SET @Product=CONVERT(CHAR(50),SUBSTRING(@StrProduct ,@C1,20))
  47. SET @Qty =Convert(DECIMAL(14,2),Substring(@StrQty,@C2,14))
  48. SET @Rate =Convert(DECIMAL(14,2),Substring(@StrRate,@C3,14))
  49. Insert Into SaleDetail(SalesMasterID,ProdName,Qty,Rate)
  50. Values(@MaxSrNo,@Product,@Qty,@Rate)
  51. SET @Count=@Count+1
  52. SET @C1=@C1+20
  53. SET @C2=@C2+14
  54. SET @C3=@C3+14
  55. End
  56. -------------------End grid-------------------
  58. COMMIT TRAN
  59. RETURN 0
Now, create the simple Windows Application in Visual Studio.

OUTPUT

Set the controls name as follows:

  1. Datetimepicker
  2. txtCustomer
  3. txtProduct
  4. txtQty
  5. txtRate
  6. dataGridView
  7. btnAdd
Source code
  1. private void btnAdd_Click(object sender, EventArgs e)
  2. {
  3. int add = dataGridView.Rows.Add();
  4. dataGridView.Rows[add].Cells["Product"].Value = txtProduct.Text;
  5. dataGridView.Rows[add].Cells["Qty"].Value = txtQuantity.Text;
  6. dataGridView.Rows[add].Cells["Rate"].Value = txtRate.Text;
  8. txtProduct.Text = "";
  9. txtQuantity.Text = "";
  10. txtRate.Text = "";
  12. if (MessageBox.Show("Add more details", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question)==DialogResult.Yes )
  13. {
  14. txtProduct.Focus ();
  15. }
  16. else
  17. btnSaveData.Focus ();
  18. }
  21. private void btnSaveData_Click(object sender, EventArgs e)
  22. {
  23. //product grid
  24. string product = "";
  25. string qty = "";
  26. string rate = "";
  27. int prodcount = 0;
  29. for (int i = 0; i <= dataGridView.Rows.Count - 1; i++)
  30. {
  31. product = product + Convert.ToString(dataGridView.Rows[i].Cells["Product"].Value).PadRight(20);
  32. qty = qty + Convert.ToString(dataGridView.Rows[i].Cells["Qty"].Value).PadRight(14);
  33. rate = rate + Convert.ToString(dataGridView.Rows[i].Cells["Rate"].Value).PadRight(14);
  34. prodcount += 1;
  35. }
  37. SaveData(dateTimePicker.Value, txtCustomer.Text, product, qty, rate, prodcount);
  38. }
  41. private void SaveData(DateTime _saledate, string _customer, string _product, string _qty, string _rate, int _productcount)
  42. {
  43. var connection = new SqlConnection("data source=(local); initial catalog=SampleData; integrated security=SSPI");
  44. var command = new SqlCommand("ADD_SALE_MASTER_DETAIL_TABLE", connection);
  45. command.Parameters .Add (new SqlParameter("@Saledate",_saledate ));
  46. command.Parameters .Add (new SqlParameter("@customername",_customer ));
  47. command.Parameters .Add (new SqlParameter("@StrProduct",_product ));
  48. command.Parameters .Add (new SqlParameter("@StrQty",_qty));
  49. command.Parameters .Add (new SqlParameter("@StrRate",_rate));
  50. command.Parameters.Add(new SqlParameter ("@Gridcount",_productcount));
  51. command.CommandType = CommandType.StoredProcedure;
  52. connection.Open ();
  53. try
  54. {
  55. int result = command.ExecuteNonQuery();
  57. if (Convert.ToBoolean (result))
  58. {
  59. MessageBox.Show("Record has been successfully saved..");
  60. }
  62. }
  63. catch (Exception ex)
  64. {
  65. MessageBox.Show (ex.Message ,"Message",MessageBoxButtons.OK,MessageBoxIcon.Error );
  66. }
  67. finally
  68. {
  69. connection.Close ();
  70. }
  71. }