如何使用循环将多个查询插入到数据库表中？
问题描述：
如何，我可以把这些查询在环（从student_fee_record_2到student_fee_record_10）有10 student_fee_record_1和其他之间仅相差从student_fee_record_2没有插入的fee_txt。我需要他们插入一个循环单独从student_fee_record_1，这样我的代码可以更短，这些是太多的代码行，请帮我使用循环缩短此代码。请注意，我正在为我的数据库中的所有字段使用varchar（）。 这是我的代码。如何使用循环将多个查询插入到数据库表中？
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);
}
}
答
易
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);
}
}
}
+0
请确保您使用我的更新。我忘了一个右括号。 – jdweng
+0
不会编译。 “Query”在其范围之外访问。如果它编译完成，它将执行循环内的所有'MessageBox'，'load_table（）'东西。此外，仍然充满了SQL注入。 – Blorgbeard
+0
我之前看到错误，但遇到了网络连接问题，无法更新。 “字符串查询”被定义了两次。我解决了。将检查更多的错误。 – jdweng
你有一些数据库的基础知识的错在这里。每个学生记录不应该有一张表 - 你只应该有一张表，'student_fee_records'，'id'栏取代表名后面的数字。另外：您的代码易受SQL注入攻击。您应该查看如何使用参数化查询。 – Blorgbeard
我们可以*告诉你如何实现你所要求的，但这意味着你要在这么多的层面上解决错误。重新考虑你的数据库设计，并使用SqlParameter进行研究。 – Filburt
Holy [sql注入蝙蝠侠！]（https://stackoverflow.com/questions/601300/what-is-sql-injection） – Amy