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

        }
    }

回答1:

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

回答2:

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

回答3:

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