本文介绍了将值插入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();推荐答案
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中的多个表的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！