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

Answer 2

while not the cleanest solution in this case, you could easily do this with a normal for loop ;

var textboxes = panelBE.Controls.OfType<TextBox>();
for(int i=0; i < textboxes.length; i+=2)
{
   var articleCodeTextbox = textboxes[i];
   var designationCodeTextbox = textboxes[i+1];
   //build your query
}

the danger of this of course is that there might be an uneven number of textboxes in the panelBE control, leading you to an IndexOutOfBounds exception. Another danger is that there is no direct guarantee that your two textboxes in the loop are indeed related to each other.

If you want to be certain that your textboxes all match, and reduce the risk of an IndexOutOfBounds exception, you could do something like this :

Dictionary<TextBox, TextBox> Textboxes = new Dictionary<TextBox, TextBox>();

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);
    Textboxes.Add(textBoxArticleCodeBE, textBoxDesignationBE);
    panelBE.Show();
}

You could then simply loop over all textboxes that are an ArticleCodeBE textbox, and you could pull the accompanying DesignationCodeBE textbox from the dictionary like this :

foreach (TextBox articleCodeTB in panelBE.Controls.OfType<TextBox>().Where(tb => tb.Name.Contains("ArticleCodeBE")))
{
     var designationCodeTB = TextBoxes[articleCodeTB];
     //build your query  
}

An even better solution in this case would be to do something in the direction of a proper viewmodel with a dedicated control. you wouldn't have to worry about matching up textboxes anymore.

Answer 3

How about that:

foreach (TextBox tb in this.Controls.OfType<TextBox>().Where(textbox => textbox.Name.StartsWith("DesignationBE")))
{
    //do stuff
}