Stored Procedure for inserting text field values that is created dynamically to the same id using asp.net C#

Question

Im new to ASP.net webforms.Im having a event page,in which i have a field to add sales channel heads mail id.when i click on the plus button i will be able to add more than one sales channels head.

For inserting the form values into the database im using Stored procedure.and its inserting the records with one sales channel head email id.

I want to know how i can write a stored procedure for inserting dynamic textbox values into sql server for the same record(That is the id and event name should be same).

This is my stored procedure

CREATE PROCEDURE SPInsertEvent
   @eventName varchar(200),
   @eventDate date,
   @costPerHead varchar(200),
   @totalCost varchar(200),
   @salesChannelHeads varchar(200),
   @salesManagers varchar(200),
   @eventCreationTime datetime
AS
BEGIN
   SET NOCOUNT ON
-- Insert statements for procedure here
   INSERT INTO dbo.hp_event
   (event_name, event_date, cost_per_head, total_cost, sales_channel_head,    sales_manager,    event_creation_time)
   VALUES
   (@eventName, @eventDate, @costPerHead, @totalCost, @salesChannelHeads, @salesManagers, @eventCreationTime)
END

This is my ASP.net function

SqlCommand cmd = new SqlCommand("SPInsertEvent", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("EventName", txtEventName.Text);
cmd.Parameters.AddWithValue("EventDate", Convert.ToDateTime(txtEventDate.Text));
cmd.Parameters.AddWithValue("CostPerHead", txtTotCostPerHead.Text);
cmd.Parameters.AddWithValue("TotalCost", txtTotalCostEvent.Text);
cmd.Parameters.AddWithValue("SalesChannelHead", txtSalesChannelHead.Text);
cmd.Parameters.AddWithValue("SalesManager", txtSalesManagers.Text);
cmd.Parameters.AddWithValue("EventCreationTime", DateTime.Now);
conn.Open();
int k = cmd.ExecuteNonQuery();
if (k != 0)
{
  string message = "Event added successfully.";
  string script = "window.onload = function(){ alert('";
  script += message;
  script += "')};";
  ClientScript.RegisterStartupScript(this.GetType(), "SuccessMessage", script, true);
}
conn.Close();

Answer 1

You said in the comments "What i need is a stored procedure for inserting saleschannel heads email id(txtSalesChannelHead,txtSalesChannelHead1,txtSalesChannelHead2) into the sql server table with same id,that is there will be duplicate rows in the table". Handling a dynamic number of inputs like that is not best done in a stored procedure, from what i can see of your scenario. The easier way is to run the insert procedure from your .NET code once for each textbox. Now I don't know how your textboxes are being added, so I can't tell you how to get the set of textbox values, but once you have it, a simple foreach loop will let you run the stored procedure once for each of them.

Answer 2

Instead of storing all the list of email ids for the given event in one table, I would suggest you to store them in separate table and you can reference them from the hp_event table whenever you need. So your database design should be some thing like below where eventid of hp_eventSalesManagers references eventId of hp_event -

To make this design work you can make use of Table Valued Parameters in ADO.NET and follow the below steps:

1. Create a User Defined Type -

     CREATE TYPE [dbo].[ChannelHeads] As Table
     (    
       EmailIds VARCHAR(50)
     )
   

2. Whenever you click button populate a new Data Table(I am using Session to keep track of the previous data), below is the sample code:

      protected void btnAdd_Click(object sender, EventArgs e)
       {
           if (Session["DataTable"] == null)
           {
               dataTable = new DataTable();
               dataTable.Columns.Add("EmailIds", typeof(string));
               Session.Add("DataTable", dataTable);
           }
           else
           {
               //If yes then get it from current session
               dataTable = (DataTable)Session["DataTable"];
           }
   
           DataRow dt_row;
           dt_row = dataTable.NewRow();
           dt_row["EmailIds"] = name.Text;
           dataTable.Rows.Add(dt_row);
       }
   

3. When submitting to data base add the below parameter(See the way I am passing the data table to DB):

      SqlParameter parameterSalesChannelHeads = new SqlParameter();
      parameterSalesChannelHeads.ParameterName = "@salesChannelHeads";
      parameterSalesChannelHeads.SqlDbType = System.Data.SqlDbType.Structured;
      parameterSalesChannelHeads.Value = (DataTable)Session["DataTable"];
      parameterSalesChannelHeads.TypeName = "dbo.ChannelHeads";
      cmd.Parameters.Add(parameterSalesChannelHeads);
   

   Change all your parameters in above format just to make sure you are using Parameters.Add instead of Parameters.AddWithValue as mentioned here

4. Finally change the procedure as below to populate the tables, below is one of the way, you can enable error handling and improve the code:

     ALTER PROCEDURE SPInsertEvent
       @eventName varchar(200),
       @eventDate datetime,
       @costPerHead varchar(200),
       @totalCost varchar(200),
       @salesChannelHeads As [dbo].[ChannelHeads] Readonly,
       @salesManagers varchar(200),
       @eventCreationTime datetime
    AS
     BEGIN 
     SET NOCOUNT ON
     DECLARE @eventID INT
   
     -- Insert statements for procedure here
     INSERT INTO dbo.hp_event
     (event_name, eventDate, costPerHead, totalCost, eventCreationTime, 
     salesManagers)
     VALUES
     (@eventName, @eventDate, @costPerHead, @totalCost,@eventCreationTime, 
     @salesManagers)
   
     SET @eventID = SCOPE_IDENTITY()
   
     INSERT INTO dbo.hp_eventSalesManagers
      (eventid,event_name,salesChannelHeads)
     SELECT @eventID, @eventName, EmailIds
      FROM 
      @salesChannelHeads 
   
   END
   

Finally change the data types of the fields accordingly as mentioned in the comment section for better clarity and usages.