How to save multiple dynamic textboxes value into SQL Server database

Question

I want to add some panel dynamically into a single panel on a button click. And each dynamic panel consist of multiple text box in horizontally. And then I want to save those text box value into the database.

I have completed to add dynamic panel and horizontal multiple text box into that panel. But couldn't know how to save them into database.

Here is the code I have written:

    int v = 0;

    TextBox txt1;
    TextBox txt2;
    TextBox txt3;
    TextBox txt4;
    TextBox txt5;

    ComboBox cmb4;

    public void tett()
    {
        v = 0;

        Panel whitePanel = new Panel();
        whitePanel.Name = "wt";

        // Quantity
        txt1 = new TextBox();
        txt1.Location = new Point(192, 38);
        txt1.Size = new Size(120, 24);
        txt1.Name = "text" + v ;
        txt1.Text = txt1.Name;
        v = v + 1;

        // Total Price
        txt2 = new TextBox();
        txt2.Location = new Point(566, 38);
        txt2.Size = new Size(120, 24);
        txt2.Name = "text" + v;
        txt2.Text = txt2.Name;

        txt2.TextChanged += Txt2_TextChanged;
        v = v + 1;

        // Unit Price
        txt3 = new TextBox();
        txt3.Location = new Point(753, 38);
        txt3.Size = new Size(120, 24);
        txt3.Name = "text" + v;
        txt3.Text = txt3.Name;
        v = v + 1;

        // Sell Price
        txt4 = new TextBox();
        txt4.Location = new Point(903, 38);
        txt4.Size = new Size(120, 24);
        txt4.Name = "text" + v;
        txt4.Text = txt4.Name;
        v = v + 1;

        // Product
        txt5 = new TextBox();
        txt5.Location = new Point(5, 38);
        txt5.Size = new Size(120, 24);
        txt5.Name = "text" + v;
        txt5.Text = txt5.Name;

        txt5.AutoCompleteSource = AutoCompleteSource.CustomSource;
        txt5.AutoCompleteMode = AutoCompleteMode.SuggestAppend;

        txt5.MouseClick += textBox5_MouseClick;

        Label lbl3 = new Label();
        Label lbl4 = new Label();
        Label lbl5 = new Label();
        Label lbl6 = new Label();
        Label lbl7 = new Label();
        Label lbl8 = new Label();

        lbl3.Location = new Point(5, 15);
        lbl3.Text = "Product";
        lbl4.Location = new Point(192, 15);
        lbl4.Text = "Quantity";
        lbl5.Location = new Point(379, 15);
        lbl5.Text = "Unit";
        lbl6.Location = new Point(566, 15);
        lbl6.Text = "Total Price";
        lbl7.Location = new Point(753, 15);
        lbl7.Text = "Unit Purchase Price";
        lbl8.Location = new Point(903, 15);
        lbl8.Text = "Unit Sell Price";

        cmb4 = new ComboBox();

        // Unit
        cmb4.Location = new Point(379, 38);
        cmb4.Size = new Size(120, 24);

        whitePanel.BackColor = ColorTranslator.FromHtml("#ECF0F5");
        whitePanel.Location = new Point(1, a * 10);
        whitePanel.Size = new Size(1330, 60);

        var _button = new Button();
        _button.Text = "Dispose";
        _button.Name = "DisposeButton";
        _button.Location = new Point(1053, 38);
        _button.MouseClick += _button_MouseClick;

        whitePanel.Controls.Add(_button);

        a = a + 5;
        v = v + 1;

        whitePanel.Controls.Add(lbl3);
        whitePanel.Controls.Add(lbl4);
        whitePanel.Controls.Add(lbl5);
        whitePanel.Controls.Add(lbl6);
        whitePanel.Controls.Add(lbl7);
        whitePanel.Controls.Add(lbl8);

        whitePanel.Controls.Add(txt1);
        whitePanel.Controls.Add(txt2);
        whitePanel.Controls.Add(txt3);
        whitePanel.Controls.Add(txt4);
        whitePanel.Controls.Add(txt5);

        whitePanel.Controls.Add(cmb4);

        panel1.Controls.Add(whitePanel);
}

In this way the output is like this..... By click on the New Purchase this multiple panel with text box will appear:

Multiple dynamic panel with multiple text box

Here the way of setting text box name is not seems better way to me. I want to use an array for set the name of text boxes.

And after all I want to save those values into db by click on the save button using an array or for loop... But I don't know how to define it..

Can anyone please help me?

And thanks in advance

Answer 1

With this approach, a gridview is usefull. But within your question, the approach is;

int rowNum = 1;
    public void tett() {

        Control[] controlsToAdd = { new TextBox(), new TextBox(), new ComboBox(), new TextBox(), new TextBox(), new TextBox(), new Button() };
        string[] labels = { "Product", "Quantity", "Unit", "Total Price", "Unit Purchase", "Unit Sell Price" };
        Panel whitePanel = new Panel() {
            Name = "wt",
            Dock = DockStyle.Top,
            AutoSizeMode = AutoSizeMode.GrowAndShrink,
            AutoSize = true,
            Padding = new Padding(0, 5, 0, 5),
            BackColor = ColorTranslator.FromHtml("#ECF0F5")
        };

        int controlX = 5, controlY = 15, controlDistance = 15;

        for (int i = 0; i < controlsToAdd.Length; i++) {
            if (labels.Length > i) {
                var label = new Label() {
                    Text = labels[i],
                    Location = new Point(controlX, controlY)
                };
                whitePanel.Controls.Add(label);
            }

            var control = controlsToAdd[i];
            control.Location = new Point(controlX, controlY + 23);
            control.Size = new Size(120, 24);
            if (control is Button) {
                control.Name = "disposeButton" + i;
                control.Text = "Dispose";
                control.Click += _button_MouseClick;
            } else {
                if(i == 0) { //if it is the Product textbox
                    ((TextBox)control).AutoCompleteSource = AutoCompleteSource.CustomSource;
                    ((TextBox)control).AutoCompleteMode = AutoCompleteMode.SuggestAppend;
                    //control.MouseClick += textBox5_MouseClick;
                }
                control.Name = "Text" + i;
                control.Text = "Row" + rowNum + " Text" + i;
            }
            whitePanel.Controls.Add(control);
            controlX += 120 + controlDistance;
        }
        panel1.Controls.Add(whitePanel);
        whitePanel.BringToFront();
        rowNum++;
    }

    private void _button_MouseClick(object sender, EventArgs e) {
        //button does its job here
        ((Button)sender).Parent.Parent.Controls.Remove(((Button)sender).Parent);
    }
    public SqlConnection Conn { get; }
    void save() {
        foreach(Panel whitePanel in panel1.Controls) {
            var sqlString = "INSERT INTO products(productField, quantityField, unitField, priceField, purchaseField, sellPriceField) VALUES (";
            foreach (Control control in whitePanel.Controls) {
                if(!(control is Label) && !(control is Button)) { //So, textbox or combobox remained
                    sqlString += "'"+ control.Text +"', ";
                }
            }
            sqlString = sqlString.Substring(0, sqlString.Length - 2) + ")";

            //Assume you have previous construction of SqlConnection as name "Conn"
            if (Conn != null) {
                if (Conn.State != ConnectionState.Open) Conn.Open();
                using (var cmd = new SqlCommand(sqlString, Conn)) {
                    cmd.ExecuteNonQuery();
                }
                Conn.Close();
            }
            Console.WriteLine(sqlString);
        }

    }

I have tested and works as expected. If it is not suit your needs, make me know.