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.

What rdbms are you working with?

Zohar Peled
– Zohar Peled

2018-02-22 09:20:22 +00:00
Commented Feb 22, 2018 at 9:20 — Zohar Peled
– Zohar Peled, Commented Feb 22, 2018 at 9:20

Steve · Accepted Answer · 2018-02-22 13:18:36Z

4

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

edited Feb 22, 2018 at 13:18

answered Feb 22, 2018 at 8:42

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Timothy Groote Over a year ago

kudos for parameterised queries. you're writing half a DAL here just to explain the concept.

Zohar Peled Over a year ago

Initially I thought that your answer is correct, but then I noticed that it will generate an SQL that looks like this:

INSERT INTO myTable (ArticleCode, Designation) VALUES (@ArticleCodeBE1, @DesignationBE1, @ArticleCodeBE2, @DesignationBE2, @ArticleCodeBEn, @DesignationBEn)

... You need a slightly different code to create VALUES(@p1, p2),(@p3, @p4)...

A. Petit Over a year ago

@Steve I tried to run your code but it returns errors on the use of Append() and Add() methods. Error 5 No overload for method 'Add' takes 2 arguments and

Error 2 'System.Collections.Generic.List<string>' does not contain a definition for 'Append' and no extension method 'Append' accepting a first argument of type 'System.Collections.Generic.List<string>' could be found (are you missing a using directive or an assembly reference?)

Aluan Haddad Over a year ago

I think params SqlParameter[] params would be way nicer than List<SqlParameter> prms = null for both caller and callee.

Steve Over a year ago

@A.Petit Let me rewrite the whole thing in Visual Studio. You never appreciate Intellisense enough if you don't write this code by hand

|

Timothy Groote · Accepted Answer · 2018-02-22 08:43:33Z

0

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.

edited Feb 22, 2018 at 8:43

answered Feb 22, 2018 at 8:33

Timothy Groote

8,69229 silver badges53 bronze badges

2 Comments

Flater Over a year ago

The increment needs to be i+=2, not i++. This also hinges on the textboxes list being in the exact same order as the textboxes were added. Which is likely the case, but cannot be guaranteed to be the same.

Timothy Groote Over a year ago

@Flater regarding the edit about the order of the textboxes ; i was already addressing that :)

Kiwimanshare · Accepted Answer · 2018-02-22 08:48:18Z

0

How about that:

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

answered Feb 22, 2018 at 8:48

Kiwimanshare

1601 silver badge9 bronze badges

Collectives™ on Stack Overflow

Dynamically generate SQL queries

3 Answers 3

12 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

12 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related