How can I insert multiple queries using loop into database tables using c#?

Question

How can I put these queries in a loop (from student_fee_record_2 to student_fee_record_10) There is only difference between student_fee_record_1 and others, from student_fee_record_2 to 10 have no insertion for fee_txt. I need them to insert in a loop separately from student_fee_record_1, So that my code can be shorter, these are too much lines of code, Please help me to short this code using loop. And please note that I’m using varchar() for all fields in my database. Here is my code.

private void btn_add_Click(object sender, EventArgs e)
        {
            string constring = "Data Source=Niazi;Initial Catalog=IIHS;Integrated Security=True";
            SqlConnection conDataBase = new SqlConnection(constring);
            conDataBase.Open();
            string Query = "insert into student_fee_record_1 (student_id, student_name, student_f_name," +
                "program, address, email_address, date, fee_submit)" +
                "values('" + std_id_txt.Text + "','" + std_name_txt.Text + "','" + f_name_txt.Text + "'," +
                "'" + program_txt.Text + "', '" + address_txt.Text + "', '" + email_txt.Text + "'," +
                "'" + date_txt.Text + "', '" + fee_txt.Text + "');"+

                "insert into student_fee_record_2 (student_id, student_name, student_f_name," +
                "program, address, email_address, date)"+
                "values('" + std_id_txt.Text + "', '" + std_name_txt.Text + "', '" + f_name_txt.Text + "', " +
                "'" + program_txt.Text + "', '" + address_txt.Text + "', '" + email_txt.Text + "'," +
                "'" + date_txt.Text + "')" +
                "insert into student_fee_record_3 (student_id, student_name, student_f_name," +
                "program, address, email_address, date)" +
                "values('" + std_id_txt.Text + "', '" + std_name_txt.Text + "', '" + f_name_txt.Text + "', " +
                "'" + program_txt.Text + "', '" + address_txt.Text + "', '" + email_txt.Text + "'," +
                "'" + date_txt.Text + "')" +
                "insert into student_fee_record_4 (student_id, student_name, student_f_name," +
                "program, address, email_address, date)" +
                "values('" + std_id_txt.Text + "', '" + std_name_txt.Text + "', '" + f_name_txt.Text + "', " +
                "'" + program_txt.Text + "', '" + address_txt.Text + "', '" + email_txt.Text + "'," +
                "'" + date_txt.Text + "')" +
                "insert into student_fee_record_5 (student_id, student_name, student_f_name," +
                "program, address, email_address, date)" +
                "values('" + std_id_txt.Text + "', '" + std_name_txt.Text + "', '" + f_name_txt.Text + "', " +
                "'" + program_txt.Text + "', '" + address_txt.Text + "', '" + email_txt.Text + "'," +
                "'" + date_txt.Text + "')" +
                "insert into student_fee_record_6 (student_id, student_name, student_f_name," +
                "program, address, email_address, date)" +
                "values('" + std_id_txt.Text + "', '" + std_name_txt.Text + "', '" + f_name_txt.Text + "', " +
                "'" + program_txt.Text + "', '" + address_txt.Text + "', '" + email_txt.Text + "'," +
                "'" + date_txt.Text + "')" +
                "insert into student_fee_record_7 (student_id, student_name, student_f_name," +
                "program, address, email_address, date)" +
                "values('" + std_id_txt.Text + "', '" + std_name_txt.Text + "', '" + f_name_txt.Text + "', " +
                "'" + program_txt.Text + "', '" + address_txt.Text + "', '" + email_txt.Text + "'," +
                "'" + date_txt.Text + "')" +
                "insert into student_fee_record_8 (student_id, student_name, student_f_name," +
                "program, address, email_address, date)" +
                "values('" + std_id_txt.Text + "', '" + std_name_txt.Text + "', '" + f_name_txt.Text + "', " +
                "'" + program_txt.Text + "', '" + address_txt.Text + "', '" + email_txt.Text + "'," +
                "'" + date_txt.Text + "')" +
                "insert into student_fee_record_9 (student_id, student_name, student_f_name," +
                "program, address, email_address, date)" +
                "values('" + std_id_txt.Text + "', '" + std_name_txt.Text + "', '" + f_name_txt.Text + "', " +
                "'" + program_txt.Text + "', '" + address_txt.Text + "', '" + email_txt.Text + "'," +
                "'" + date_txt.Text + "')" +
                "insert into student_fee_record_10 (student_id, student_name, student_f_name," +
                "program, address, email_address, date)" +
                "values('" + std_id_txt.Text + "', '" + std_name_txt.Text + "', '" + f_name_txt.Text + "', " +
                "'" + program_txt.Text + "', '" + address_txt.Text + "', '" + email_txt.Text + "'," +
                "'" + date_txt.Text + "')";

                //SqlConnection conDataBase = new SqlConnection(constring);
                SqlCommand cmdDataBase = new SqlCommand(Query, conDataBase);
                SqlDataReader myReader;
                try
                {
                    //conDataBase.Open();
                    myReader = cmdDataBase.ExecuteReader();
                    MessageBox.Show("Record added successfully.");
                    ClearAll(this);
                    load_table();
                    while (myReader.Read())
                    {

                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
        }

You've got some of the basics of databases wrong here. You should not have one table per student record - you should have one table only, `student_fee_records`, with an `id` column taking the place of the number on the end of the table name. Also: your code is vulnerable to SQL injection. You should look up how to use parameterized queries. — Blorgbeard, Jun 12 '17 at 16:48
We *could* tell you how to achieve what you ask but this would mean sending you off with a solution wrong on so many levels. Reconsider your database design and look into using SqlParameter. — Filburt, Jun 12 '17 at 16:53
Holy [sql injection Batman!](https://stackoverflow.com/questions/601300/what-is-sql-injection) — , Jun 12 '17 at 17:06
If you want to shorten your code, you can always turn those queries into stored procedures. — Kyle Rone, Jun 12 '17 at 17:19

jdweng · Answer 1 · 2017-06-12T20:26:31.133

Easy

        private void btn_add_Click(object sender, EventArgs e)
        {
            string constring = "Data Source=Niazi;Initial Catalog=IIHS;Integrated Security=True";
            SqlConnection conDataBase = new SqlConnection(constring);
            conDataBase.Open();

            string Query = "";

            for (int recordNum = 1; recordNum <= 10; recordNum++)
            {
                if (recordNum == 1)
                {
                    Query = string.Format(
                        "insert into student_fee_record_{0} (student_id, student_name, student_f_name," +
                        "program, address, email_address, date, fee_submit)" +
                        "values('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
                        recordNum.ToString(), std_id_txt.Text, std_name_txt, f_name_txt.Text, program_txt.Text, address_txt.Text, date_txt.Text, email_txt.Text, fee_txt.Text);
                }
                else
                {
                    Query = string.Format(
                        "insert into student_fee_record_{0} (student_id, student_name, student_f_name," +
                        "program, address, email_address, date)" +
                        "values('{1}','{2}','{3}','{4}','{5}','{6}','{7}')",
                        recordNum.ToString(), std_id_txt.Text, std_name_txt, f_name_txt.Text, program_txt.Text, address_txt.Text, date_txt.Text, email_txt.Text);
                }

                //SqlConnection conDataBase = new SqlConnection(constring);
                SqlCommand cmdDataBase = new SqlCommand(Query, conDataBase);
                SqlDataReader myReader;
                try
                {
                    //conDataBase.Open();
                    myReader = cmdDataBase.ExecuteReader();
                    MessageBox.Show("Record added successfully.");
                    ClearAll(this);
                    load_table();
                    while (myReader.Read())
                    {

                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

Make sure you use my update. I forgot a closing parenthesis. — jdweng, Jun 12 '17 at 17:15
Won't compile. `Query` is accessed outside its scope. And if it did compile, it would be executing all the `MessageBox`, `load_table()` stuff inside the loop. Also, still full of SQL injection. — Blorgbeard, Jun 12 '17 at 20:12
I saw the error earlier but was having issues with my network connection and couldn't update. "string Query" was defined twice. I fixed. Will check for more errors. — jdweng, Jun 12 '17 at 20:27

How can I insert multiple queries using loop into database tables using c#?

1 Answers1