Parameterized queries on iteration

Question

I am inserting a web form into the database and so using parameterized queries. I have a CheckBoxList. How can I iterate over CheckBoxList, create an insert statement for every checked thing (many-to-many), and keep this parameterized and executed in one swoop?

I have this right now:

string query = "INSERT INTO resources (url, submitted_by, author_name) VALUES (@url, @submitted_by, @author_name);";
foreach (ListItem li in CheckBoxList1.Items)
    {
        if (li.Selected = true)
        {
            query += "; INSERT INTO ";
        }
    }
    SqlCommand cmd = new SqlCommand(query, conn);
    cmd.Parameters.AddWithValue("@url", TextBox1.Text);
    cmd.Parameters.AddWithValue("@submitted_by", TextBox2.Text);
    cmd.Parameters.AddWithValue("@author_name", TextBox3.Text);

    try
    {
        conn.Open();
        cmd.ExecuteNonQuery();
        Label1.Text = "Added to database.";
    }

As you can see it's unfinished. Any suggestions?

Answer 1

You could use LINQ to generate unique named parameters for each item in your collection, then add the associated values in later:

var builder = new StringBuilder();
var listParams = CheckBoxList1.Items
                     .Where(li => li.Selected)
                     .Select(li, idx => new 
                     {
                         PhoneString = String.Format("@phone_id{0}", idx),
                         PhoneValue = GetPhoneId(li),
                         ResourceString = String.Format("@resource_id{0}", idx),
                         ResourceValue = GetResourceId(li)
                     };
foreach (var param in listParams)
{
    builder.AppendFormat("INSERT INTO phones_resources (phone_id, resource_id) 
                          VALUES ({0}, {1});", 
                          param.PhoneString, param.ResourceString);
}
SqlCommand cmd = new SqlCommand(builder.ToString(), conn);
foreach (var param in listParams)
{
    cmd.Parameters.AddWithValue(param.PhoneString, param.PhoneValue);
    cmd.Parameters.AddWithValue(param.ResourceString, param.ResourceValue);
}

I'm assuming you have some way of getting associated phone_id, resource_id from any given ListItem - you can just plug that in where I've put the placeholder Get___ functions.

Note: Switched to a StringBuilder - it's much better than building up a string with repeated +=.