Dynamically generate SQL queries

Question

I dynamically create Textboxes according to user input like so :

int nbrTextBoxBE = int.Parse(textBoxNbrBE.Text);
panelBE.Controls.Clear();
panelBE.Focus();
for (int i = 0; i < nbrTextBoxBE; i++)
{
    TextBox textBoxArticleCodeBE = new TextBox();
    TextBox textBoxDesignationBE = new TextBox();
    textBoxCArticleCodeBE.Name = "ArticleCodeBE" + (i + 1);
    textBoxDesignationBE.Name = "DesignationBE" + (i + 1);
    textBoxArticleCodeBE.Text = "";
    textBoxDesignationBE.Text = "";
    panelBE.Controls.Add(textBoxArticleCodeBE);
    panelBE.Controls.Add(textBoxDesignationBE);
    panelBE.Show();
}

And with a button I'd like to save these to my database in this form :

INSERT INTO myTable (ArticleCode, Designation) VALUES (ArticleCodeBEi.Text, DesignationBEi.Text)

I tried to do so with a foreach loop :

foreach (TextBox tb in panelBE.Controls.OfType<TextBox>())
{
    // do stuff 
}

but of course it generates double the number of queries I need.

Answer 1

The most correct way to do your task is using a parameter collection

// This is assumed to be even
TextBox[] txtArray = panelBE.Controls.OfType<TextBox>().ToArray();

for(int x = 0; x < txtArray.Length; x+=2)
{
    TextBox tArticle = txtArray[x];
    TextBox tDesignation = txtArray[x+1];

    // Where you build the query text
    StringBuilder sb = new StringBuilder();

    // The placeholders for the parameters to be used
    List<string> prmNames = new List<string>();

    // The parameter collection 
    List<SqlParameter> prms = new List<SqlParameter>();

    // Initial text for the query
    sb.Append("INSERT INTO myTable (ArticleCode, Designation) VALUES (");

    prmNames.Add("@p" + tArticle.Name);
    prms.Add(new SqlParameter() 
    {
        ParameterName = "@p" + tArticle.Name,
        SqlDbType = SqlDbType.NVarChar,
        Value = tArticle.Text
    });
    prmNames.Add("@p" + tDesignation.Name);
    prms.Add(new SqlParameter()
    {
         ParameterName = "@p" + tDesignation.Name,
         SqlDbType = SqlDbType.NVarChar,
         Value = tDesignation.Text
    });

    // Concat the parameters placeholders and close the query text
    sb.Append(string.Join(",", prmNames) + ")");

    // Pass everything to an utility method
    // This could be part of a bigger utility database class
    ExecuteInsertInto(sb.ToString(), prms);
}

Where ExecuteInsertInto is

private int ExecuteInsertInto(string query, List<SqlParameter> prms = null)
{
   using(SqlConnection cnn = new SqlConnection(..connectionstring goes here..))
   using(SqlCommand cmd = new SqlCommand(query))
   {
        cnn.Open();
        if(prms != null)
            cmd.Parameters.AddRange(prms.ToArray());
        return cmd.ExecuteNonQuery();
   }
}

Notice that this assumes that the controls are retrieved in the same order in which the fields are written in the INSERT INTO string. If this is not the case, then you need to take the control names, substring the control name to take only the text before the "BE" text and add that string to the StringBuilder instance.

  string fieldName = tb.Text.Substring(0, tb.Text.IndexOf("BE"));

There is also a better way to pass every command with a single call to the ExecuteInsertInto but the exact syntax depends on the database type. In short you could build a single query text with many inserts. For Sql Server see Multiple Insert Statements vs Single Inserts with multiple values