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)
{
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();