How do I use a C# Foreach loop to insert records into a database?

Question

I'm currently working on a project where I've been asked to work on a WPF with the following requirements:

1. Allow the user to be able to add more textboxes in a stack panel
2. Save the user input (as string/nvarchar) in each textbox as a new record in the database

The idea behind this is that if we receive multiple packages, each shipment is typically split up into multiple locations and we'd need to located where each component is.

I've included a sample of my code below, its barebones and will probably have more than just the one 'Records' field things will be inserted into. I've set the RecordID field to being an identity field so I'm not worried about declaring the ID field. It's not included in the sample but each record is linked to a ShipmentID.

I'd appreciate any help as I've been having trouble implementing it (and have hit a wall with my research) as my code runs into a:

'System.InvalidOperationException' occurred in System.Data.dll' with the CommandText Property not initialized error, when I try I try to save the record. I've unfortunately found only vague tidbits how to solve my issue (or maybe I'm just terrible at research).

    private void StackAddTB(object sender, RoutedEventArgs e)
    {
        TextBox NewBox = new TextBox() { Margin = new Thickness(0, 10, 0, 0), Width = 100, Height = 20 };
        StackBoxes.Children.Add(NewBox);
    }

    private void SaveMulti(object sender, RoutedEventArgs e)
    {
        string CStr = Manifesting.Properties.Settings.Default.PSIOpsSurfaceCS;
        SqlConnection Connection = new SqlConnection(CStr);
        string Query = "INSERT INTO TestLoop (Record), Values (Record)";
        SqlCommand Command = new SqlCommand(Query, Connection)

        foreach (TextBox TestTB in StackBoxes.Children.OfType<TextBox>())
        {
            try
            {
                Connection.Open();
                Command = Connection.CreateCommand();
                Command.Parameters.AddWithValue("@Record", TestTB.Text );
                Command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                Connection.Close();
            }
        }

Edit: I've checked through the Looping Through Textbox question (as per mr. reds comment), and while I've found some things useful from the other post it does not go into detail in how those values are saved in a db.

Answer 1

To start with, this is how I would structure your code.

    private void SaveMulti(object sender, RoutedEventArgs e)
    {
        string CStr = Manifesting.Properties.Settings.Default.PSIOpsSurfaceCS;
        string Query = "INSERT INTO TestLoop (Record) Values (@Record)";
        string Record = StackBoxes.Children.OfType<TextBox>().ToString();

        using(var conn = new SqlConnection(CStr))
        {
            foreach (TextBox TestTB in StackBoxes.Children.OfType<TextBox>())
            {
                using(var cmd = new SqlCommand(Query, conn))
                {
                    try
                    {
                        //Removed as pointed out by EJoshuaS
                        //cmd = conn.CreateCommand();
                        cmd.Parameters.AddWithValue("@Record", TestTB.Text);
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        MessageBox.Show(ex.ToString());
                    }
                }
            }
        }
    }

Next, what is the purpose of this line?

string Record = StackBoxes.Children.OfType<TextBox>().ToString();

Answer 2

When you do this:

 Command = Connection.CreateCommand();
  Command.Parameters.AddWithValue("@Record", TestTB.Text );
  Command.ExecuteNonQuery();

You "clobber" whatever was in Command before, so you never specify anywhere what the actual query is. That's why it gives you the exception - you literally give it no query to run.

Note that this line:

 SqlCommand Command = new SqlCommand(Query, Connection);

currently makes no difference whatsoever.