将值插入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.