Inserting into Many-to-Many database?

Question

Guys I am trying to learn many-to-many relationship databases but I am having trouble in inserting data in the "link/junction" table. This is what my database look like :

Now as my application is windows desktop application in C#, I have a simple Form in which I have 5 textboxes & a button, this is what it looks like :

Note : Image 2 - red represents the textbox name with .Text and blue represents the type of data I will be entering. EngineerID 1, 2 and 3 already exists in table tblEngineer.

Now on pressing add, I want to enter the value of Date & Phone Number in table tblTicketDetail and value of EngineerID 1, EngineerID 2 and EngineerID 3 in table tblTicket_Engineer. Table tblEngineer already has some rows in it.

Now if I was to enter only one EngineerID in tblTicket_Engineer, I would do this in click event of "Add" button :

        string statement = "INSERT INTO tblTicketDetail(IssueDate,Number) VALUES(@text1,@text2);SELECT SCOPE_IDENTITY()";
        using (SqlCommand command = new SqlCommand(statement))
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ST"].ConnectionString.ToString()))
        {

                command.Parameters.AddWithValue("@text1", textBox1.Text);
                command.Parameters.AddWithValue("@text2", textBox2.Text);
                connection.Open();
                command.Connection = connection;
                ID = command.ExecuteScalar().ToString();
                connection.Close();
        }

        string statement1 = "INSERT INTO tblTicket_Engineer(ID,EID) VALUES(@text1,@text3)";
        using (SqlCommand command1 = new SqlCommand(statement1))
        using (SqlConnection connection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ST"].ConnectionString.ToString()))
        {

            command1.Parameters.AddWithValue("@text1", ID);
            command1.Parameters.AddWithValue("@text3", textBox3.Text);
            connection1.Open();
            command1.Connection = connection1;
            command1.ExecuteNonQuery();
            connection1.Close();
        }

Look at the foreign keys in table tblTicket_Engineer and tell me if my foreign keys are correct.

What should I do to enter 3 rows at once in table tblTicket_Engineer? This is what I want to happen in tblTicket_Engineer when I press add button (For this image, values were entered manually)

Answer 1

You could use UNION ALL. Try something like this :

        string statement1 = @"INSERT INTO tblTicket_Engineer(TicketID,EngineerID) 
                        SELECT @ID,@EID1 UNION ALL
                        SELECT @ID,@EID2 UNION ALL
                        SELECT @ID,@EID3";
        using (SqlCommand command1 = new SqlCommand(statement1))
        using (SqlConnection connection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ST"].ConnectionString.ToString()))
        {

            command1.Parameters.AddWithValue("@ID", PID);
            command1.Parameters.AddWithValue("@EID1", comboBox1.SelectedValue);
            command1.Parameters.AddWithValue("@EID2", comboBox2.SelectedValue);
            command1.Parameters.AddWithValue("@EID3", comboBox3.SelectedValue);
            connection1.Open();
            command1.Connection = connection1;
            command1.ExecuteNonQuery();
            connection1.Close();