将值插入SQL Server中的多个表的最佳方法是什么？ [英] What is the best way to insert values into several tables in SQL server ?
本文介绍了将值插入SQL Server中的多个表的最佳方法是什么？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我一直在搜索几天，我已经看到我可以一起加入几个查询插入db，但我有大约13个表，这意味着13个命令。这就是我使用的东西，这样在按钮保存上使用它是否安全？它对我有用;
i have been searching for several days, i have seen that i can join several query together to insert into db, but i have about 13 table,this means 13 command. this is what am i using, is it safe to use it this way on button save?? it is working for me;
try
{
using (SqlConnection cnn = new SqlConnection(ConnString))
{
cnn.Open();
SqlCommand cmd1 = cnn.CreateCommand();
cmd1.Connection = cnn;
cmd1.CommandText = "Insert into incident( incidentNo, incStartDate, incStartTime, incEndDate, incEndTime, eventStartDate, eventEndDate, eventStartTime, eventEndTime, incidentDescription, eventTypeId, resultactionBoolean, resultActionTaken, eventEvidenceSave) Values (@incidentNo, @incStartDate, @incStartTime, @incEndDate, @incEndTime, @eventStartDate, @eventEndDate, @eventStartTime, @eventEndTime, @incidentDescription, @eventTypeId, @resultactionBoolean, @resultActionTaken, @eventEvidenceSave)";
cmd1.CommandType = CommandType.Text;
cmd1.Connection = cnn;
cmd1.Parameters.AddWithValue("@incidentNo", newLastIncidentNo);
cmd1.Parameters.AddWithValue("@incStartDate", dateTimePicker13.Value.Date);
cmd1.Parameters.AddWithValue("@incEndDate", dateTimePicker1.Value.Date);
cmd1.Parameters.AddWithValue("@incStartTime", dateTimePicker14.Value.ToString("HH:mm:ss"));
cmd1.Parameters.AddWithValue("@incEndTime", dateTimePicker4.Value.ToString("HH:mm:ss"));
cmd1.Parameters.AddWithValue("@eventStartDate", dateTimePicker2.Value.Date);
cmd1.Parameters.AddWithValue("@eventEndDate", dateTimePicker5.Value.Date);
cmd1.Parameters.AddWithValue("@eventStartTime", dateTimePicker3.Value.ToString("HH:mm:ss"));
cmd1.Parameters.AddWithValue("@eventEndTime", dateTimePicker6.Value.ToString("HH:mm:ss"));
cmd1.Parameters.AddWithValue("@incidentDescription", textBox1.Text);
cmd1.Parameters.AddWithValue("@eventTypeId", comboBox2.SelectedIndex + 1);
if (radioButton4.Checked == true)
{
cmd1.Parameters.AddWithValue("@resultactionBoolean", "True");
cmd1.Parameters.AddWithValue("@resultActionTaken", textBox3.Text);
}
else
{
cmd1.Parameters.AddWithValue("@resultactionBoolean", "False");
cmd1.Parameters.AddWithValue("@resultActionTaken", textBox3.Text);
}
if (radioButton6.Checked == true)
{
cmd1.Parameters.AddWithValue("@eventEvidenceSave", textBox5.Text);
}
else
{
string eventEvidenceSaveDefaultvalue = "لم يتم تخزين الحدث كدليل";
cmd1.Parameters.AddWithValue("@eventEvidenceSave", eventEvidenceSaveDefaultvalue);
}
cmd1.ExecuteNonQuery();
if (radioButton2.Enabled == true)
{
// callRedirect is completed
for (int i = 0; i < dataGridView2.Rows.Count; i++)
{
string StrQuery = "INSERT INTO [dbo].[callRedirect] (ISFsectionId, callRedirectDate, incidentNo, callRedirectTime, callRedirectGrade, callRedirectFName, callRedirectLName, callRedirectSerialNo, callRedirectRemark) VALUES (@ISFsectionId, @callRedirectDate, @incidentNo, @callRedirectTime, @callRedirectGrade, @callRedirectFName, @callRedirectLName, @callRedirectSerialNo, @callRedirectRemark)";
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandText = StrQuery;
cmd.Parameters.AddWithValue("@incidentNo", newLastIncidentNo);
cmd.Parameters.AddWithValue("@callRedirectDate", dataGridView2.Rows[i].Cells[1].Value.ToString());
cmd.Parameters.AddWithValue("@callRedirectTime", dataGridView2.Rows[i].Cells[2].Value.ToString());
cmd.Parameters.Add("@ISFsectionId", SqlDbType.VarChar).Value = dataGridView2.Rows[i].Cells["Column11"].Value;
cmd.Parameters.AddWithValue("@callRedirectGrade", dataGridView2.Rows[i].Cells["Column12"].Value);
cmd.Parameters.AddWithValue("@callRedirectFName", dataGridView2.Rows[i].Cells["Column13"].Value);
cmd.Parameters.AddWithValue("@callRedirectLName", dataGridView2.Rows[i].Cells["Column14"].Value);
cmd.Parameters.AddWithValue("@callRedirectSerialNo", dataGridView2.Rows[i].Cells["Column20"].Value);
cmd.Parameters.AddWithValue("@callRedirectRemark", dataGridView2.Rows[i].Cells["Column19"].Value);
cmd.Connection = cnn;
cmd.ExecuteNonQuery();
}
}
else { return; }
for (int i = 0; i < dataGridView6.Rows.Count; i++)
{
SqlCommand cmd4 = cnn.CreateCommand();
cmd4.CommandText = " INSERT into dbo.incidentManyClassify (incidentNo, IncClassifyId, incClassifyDesc) values (@incidentNo, @IncClassifyId, @incClassifyDesc)";
cmd4.Connection = cnn;
cmd4.Parameters.AddWithValue("@incidentNo", newLastIncidentNo);
cmd4.Parameters.AddWithValue("@IncClassifyId", dataGridView6.Rows[i].Cells["classifycombocolumn"].Value);
cmd4.Parameters.AddWithValue("@incClassifyDesc", dataGridView6.Rows[i].Cells["Column16"].Value);
cmd4.ExecuteNonQuery();
}
我的尝试：
我所使用的每个命令：
What I have tried:
for every command i have used :
SqlCommand cmd4 = cnn.CreateCommand();
cmd4.CommandText = " INSERT into table (column1,column2) values (@value1, @value2, )";
cmd4.Connection = cnn;
cmd4.Parameters.AddWithValue("@value1", textbox1.text);
cmd4.Parameters.AddWithValue("@value2", textbox2.text);
cmd4.ExecuteNonQuery();
推荐答案
这可能有用，但请不要创建命令循环中的参数和参数。
That might work, but please don't create the commands and parameters inside the loops.
Create command
Set command text
Create and add parameters
Loop
Set parameter values
Execute command
您还可以执行如果你愿意，可以在一次执行中使用多个SQL语句。
另外，因为你使用了con.CreateCommand，所以你不需要再次设置连接。
You can also perform multiple SQL statements in one execution if you like.
Also, because you used con.CreateCommand, you don't need to set the connection again.
这篇关于将值插入SQL Server中的多个表的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
