how to insert data into multiple tables at once

Question

I am wondering if the code I have written is the best way to insert data into multiple tables at ones.

For me this is the first time i'am using code to insert data into the database.
Before i was always using the tools from visual studio to do it.

So I have 1 textbox and when I enter a product name and press the save button I save the product into 3 tables.

My code works but is this a good way to do it ??
Is there a better way to do it??

private void SaveButton_Click(object sender, EventArgs e)
    {
        if (AddProductTables.Text != "")
        { 


        try
        {
            String ConnectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\DataBase\MyStock.mdf;Integrated Security=True;Connect Timeout=30";
            SqlConnection myconnection = new SqlConnection(ConnectionString);
            myconnection.Open();

            SqlCommand StockCommand = myconnection.CreateCommand();
            StockCommand.CommandText = "insert into Stock([Product]) values (@Product)";
            StockCommand.Parameters.AddWithValue("@Product", AddProductTables.Text);

            SqlCommand LandhuisMisjeCommand = myconnection.CreateCommand();
            LandhuisMisjeCommand.CommandText = "insert into LandhuisMisje([Product]) values (@Product)";
            LandhuisMisjeCommand.Parameters.AddWithValue("@Product", AddProductTables.Text);

            SqlCommand TheWineCellarCommand = myconnection.CreateCommand();
            TheWineCellarCommand.CommandText = "insert into TheWineCellar([Product]) values (@Product)";
            TheWineCellarCommand.Parameters.AddWithValue("@Product", AddProductTables.Text);

            StockCommand.ExecuteNonQuery();
            LandhuisMisjeCommand.ExecuteNonQuery();
            TheWineCellarCommand.ExecuteNonQuery();

            myconnection.Close();
            MessageBox.Show("Saved");
        }


        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }


        }
        else
        {
            MessageBox.Show("Insert Product name");

        }
    }

I think it's better to use a stored procedure and write all query in it and simply call it from c# , Or else you can use first table entry as a stored procedure and all other changes you can handled by a trigger — Arunprasanth K V
– Arunprasanth K V, Commented Jan 30, 2015 at 6:17
Use stored procedure to handle required logic gracefully, data can be passed to stored procedure as an Xml. — Hari Prasad
– Hari Prasad, Commented Jan 30, 2015 at 6:24
you can also separate different query by using semicolons like Dmitry Martovoi's answer — Arunprasanth K V
– Arunprasanth K V, Commented Jan 30, 2015 at 6:27

martavoi · Accepted Answer · 2015-01-30 06:25:00Z

4

Simply put all the statements into one command separated by semicolon:

using (var connection = new SqlConnection(ConnectionString))
using (var command = connection.CreateCommand())
{
    connection.Open();
    command.CommandText = @"insert into Stock([Product]) values (@Product);
                        insert into LandhuisMisje([Product]) values (@Product);
                        insert into TheWineCellar([Product]) values (@Product);"
    command.Parameters.AddWithValue("@Product", AddProductTables.Text);
    command.ExecuteNonQuery()
}

answered Jan 30, 2015 at 6:25

martavoi

7,1027 gold badges43 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hari Prasad Over a year ago

Probably we might need to modify your code not to use AddWithValue() ? Refer (blogs.msmvps.com/jcoehoorn/blog/2014/05/12/…)

martavoi Over a year ago

@HariPrasad: sure, you are right! But this thread is not about ado.net parameters :)

Neel · Accepted Answer · 2015-01-30 06:29:57Z

I would suggest you to do multiple separate INSERT in a TRANSACTION

BEGIN TRANSACTION
INSERT [...]
INSERT [...]
COMMIT TRANSACTION

and you will have to first finish procedure for first table then another as shown below :-

sqlcmd.CommandText = "INSERT INTO Stock([Product]) values (@Product);
  sqlcmd.Parameters.AddWithValue("@Product", AddProductTables.Text);
  sqlCmd.ExecuteNonQuery();
  sqlCmd.Parameters.Clear();
  sqlcmd.CommandText = "INSERT INTO LandhuisMisje([Product]) values (@Product);
  sqlcmd.Parameters.AddWithValue("@Product", AddProductTables.Text);
  sqlCmd.ExecuteNonQuery();

By this way you can achieve by single command variable instead of taking multiple as in your code

Ramgy Borja · Accepted Answer · 2017-07-29 05:00:52Z

write all commandtext, in one statement and used SET instead of using VALUES.

SqlCommand command = myconnection.CreateCommand();    
StockCommand.CommandText = "insert into Stock SET [Product]=@Product;
                            insert into LandhuisMisje SET [Product]=@Product;
                            insert into TheWineCellar SET [Product]=@Product";
command.Parameters.AddWithValue("@Product", AddProductTables.Text);
command.ExecuteNonQuery();

Tapfuma Gahadzikwa · Accepted Answer · 2021-08-01 08:47:58Z

string strConnString = "myconnectionstring"; // get it from Web.config file    
SqlTransaction objTrans = null;    
   
using (SqlConnection objConn = new SqlConnection(strConnString))    
{    
   objConn.Open();    
   objTrans = objConn.BeginTransaction();    
   SqlCommand objCmd1 = new SqlCommand("insert into Stock SET [Product]=@Product;)", objConn);    
   SqlCommand objCmd2 = new SqlCommand("insert into LandhuisMisje SET [Product]=@Product", objConn); 
SqlCommand objCmd3 = new SqlCommand("insert into TheWineCellar SET [Product]=@Product", objConn);   
   try    
   {    
      objCmd1.ExecuteNonQuery();    
      objCmd2.ExecuteNonQuery(); // Throws exception due to foreign key constraint  
      objCmd3.ExecuteNonQuery(); 
      objTrans.Commit();    
   }    
   catch (Exception)    
   {    
      objTrans.Rollback();    
   }    
   finally    
   {    
      objConn.Close();  `enter code here`  
   }    
}

Collectives™ on Stack Overflow

how to insert data into multiple tables at once

4 Answers 4

2 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Linked

Related