CODEWITHSUNDEEP
c#sql-serverdatabaset-sqlinsert
Creator
Creator

Reputation: 1512

how to insert data into multiple tables at once

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

        }
    }

Upvotes: 4

Views: 21144

Answers (4)

Tapfuma Gahadzikwa
Tapfuma Gahadzikwa

Reputation: 7

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`  
   }    
}

Upvotes: 0

Ramgy Borja
Ramgy Borja

Reputation: 2458

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

Upvotes: 0

Neel
Neel

Reputation: 11721

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

Upvotes: 1

martavoi
martavoi

Reputation: 7082

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

Upvotes: 4

Related Questions