What is the best way to insert values into several tables in SQL server ?

Question

0.00/5 (No votes)

See more:

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;

C#

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 :

C#

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

Posted 21-Jun-16 12:47pm

ramy nemer

Updated 21-Jun-16 12:57pm

PIEBALDconsult

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PIEBALDconsult · Accepted Answer · 2016-06-21T12:57:00

Solution 1

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

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.

Posted 21-Jun-16 12:57pm

PIEBALDconsult

Updated 21-Jun-16 13:06pm

v4

Comments

ramy nemer 21-Jun-16 19:02pm

you mean liike this :
string StrQuery = "INSERT INTO [dbo].[collaborationUnit] (incidentNo, PuserNickname, tokenId, PoliceUserRole) VALUES (@incidentNo, @PuserNickname, @tokenId, @PoliceUserRole)";

SqlCommand cmd10 = cnn.CreateCommand();
cmd10.CommandText = StrQuery;

for (int i = 0; i < dataGridView5.Rows.Count; i++)
{
cmd10.Parameters.AddWithValue("@incidentNo", newLastIncidentNo);
cmd10.Parameters.AddWithValue("@PuserNickname", dataGridView5.Rows[i].Cells["Column26"].Value);
cmd10.Parameters.AddWithValue("@tokenId", dataGridView5.Rows[i].Cells["Column31"].Value);
cmd10.Parameters.AddWithValue("@PoliceUserRole", dataGridView5.Rows[i].Cells["Column32"].Value);
cmd10.Connection = cnn;
cmd10.ExecuteNonQuery();
}

PIEBALDconsult 21-Jun-16 19:05pm

No. Create and add the parameters _outside_ the loop ; set the _values_ of the parameters inside the loop.

ramy nemer 21-Jun-16 19:06pm

its better to use one sqlcommand for all queries ? am a little bit confused of this cz am having multiple tables to insert to and at last i need to use if all controls are !null it can save changes,so its more easy to use one sqlcommand for all queries to deal with this condition ??

PIEBALDconsult 21-Jun-16 19:07pm

That's fine.