Multiple Insert Statements in One Connection

Multiple Insert statements in one connection

You should parameterized your query - ALWAYS, but for now you can concatenate those queries with ; and then execute them once like:

string allQueries = string.join(';', query2, query3, query4, query5);
command.CommandText = allQueries; 
int c = command.ExecuteNonQuery();

Currently you are just executing one query. Semicolon ; marks end of statement in SQL, so combining these statements with ; will make them separate statements but they will be executed under one execution.

kcray - This is what worked for me.

 string[] arr = { query2, query3 };
 string allQueries = string.Join(";", arr);
 command.CommandText = allQueries;
 int c = command.ExecuteNonQuery();

How to execute Multiple Insert statements in a single query?

As @Caius Jard said, you can't do this with an ad-hoc query.

So what is an option to do so?

Step 1: Create a Stored Procedure in the Database:

CREATE PROCEDURE usp_InsertData
@Name NVARCHAR(200),
@Phone NVARCHAR(100),
@DateCreated Date,
@PickUpDate Date
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Customers(Name, Phone) VALUES (@Name,@Phone)

    INSERT INTO PriceHold(DateCreated, PickUpDate) VALUES (@DateCreated,@PickUpDate) 
END

Step 2: Call above Stored procedure in C# Code:

private void AddPhBttn_Click(object sender, RoutedEventArgs e)
{
     var furniture = new SqlConnection("Data Source=LAPTOP-F4QFMPFD\\MSSQLSERVER1;Initial Catalog=Furniture;Integrated Security=True");

     SqlCommand add = new SqlCommand("usp_InsertData", furniture);
     add.CommandType = System.Data.CommandType.StoredProcedure;

     add.Parameters.AddWithValue("@Name", nameTxtBox.Text);
     add.Parameters.AddWithValue("@Phone", phoneTxtbox.Text);
     add.Parameters.AddWithValue("@DateCreated", dateTxtBox.Text);
     add.Parameters.AddWithValue("@PickUpDate", puDateTxtBox.Text);
     furniture.Open();

     int i = add.ExecuteNonQuery();

     if (i != 0)
     {
          MessageBox.Show("saved");
     }
     else
     {
         MessageBox.Show("error");
     }
     furniture.Dispose();

}

Which is faster: multiple single INSERTs or one multiple-row INSERT?

https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html

The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

• Connecting: (3)
• Sending query to server: (2)
• Parsing query: (2)
• Inserting row: (1 × size of row)
• Inserting indexes: (1 × number of indexes)
• Closing: (1)

From this it should be obvious, that sending one large statement will save you an overhead of 7 per insert statement, which in further reading the text also says:

If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements.

How do you execute multiple sql insert statements in one php page

Your code is close to correct. I tweaked it a bit.

// establish DB connection (this includes creates $conn variable)

$conn = mysqli_connect("127.0.0.1", "app", "aaaa", "sss");
if ( ! $conn ) {
    throw new Exception('Connection failed');
}

$userId = 123;
$supplierId = 200;
$order_items = [
    ['prodId' => 2000, 'qty' => 5, 'costPrice' => 1.25],
    ['prodId' => 2001, 'qty' => 7, 'costPrice' => 2.25],
    ['prodId' => 2002, 'qty' => 9, 'costPrice' => 1.20],
    ['prodId' => 2003, 'qty' => 15, 'costPrice' => 3.25],
    ['prodId' => 2004, 'qty' => 25, 'costPrice' => 5.22],
    ['prodId' => 2005, 'qty' => 35, 'costPrice' => 5.25],
    ['prodId' => 2006, 'qty' => 45, 'costPrice' => 11.51],
];

// create PO record
$sql = "INSERT INTO purchaseorders (PODate, supplierId, userId) VALUES (?,?,?);";
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql)) {
    // header("Location: ../place.php?error=sqlerror");
    throw new Exception("SQL error! " . mysqli_error($conn));
}
else {
    $date = date('Y-m-d H:i:s');
    mysqli_stmt_bind_param($stmt, "sss", $date, $supplierId, $userId);
    mysqli_stmt_execute($stmt);        
}

// // get last PO record id
$POId = mysqli_insert_id($conn); 
echo "POId = " . print_r($POId, true) . "\n";

$sql1 = "INSERT INTO podetails (PONumber, productId, poquantity, 
            pocostPrice, delivered, paidStatus) VALUES 
            (?,?,?,?,?,?);";
$stmt1 = mysqli_stmt_init($conn);
if (!$stmt1) {
    throw new Exception("SQl Connect error! " . mysqli_error($conn));
}

if (!mysqli_stmt_prepare($stmt1, $sql1)) {
    throw new Exception("SQl Connect error! " . mysqli_error($conn));
}
// for each record in grid - create PO details record
foreach ($order_items as $item) {
    $delivered = "false";
    $paidStatus = "false";
    if ( ! mysqli_stmt_bind_param($stmt1, "ssssss", $POId, 
    $item['prodId'], $item['qty'], $item['costPrice'], $delivered, $paidStatus) ) {
        throw new Exception("SQl error! " . mysqli_error($conn));
    }
    if( ! mysqli_stmt_execute($stmt1) ) {
        throw new Exception("SQl error! " . mysqli_error($conn));
    }
}                        

echo "PO Placed: PO Number = ".$POId . "\n";

I made these tables to run it locally.

CREATE TABLE `purchaseorders` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `PODate` date DEFAULT NULL,
  `supplierId` int(10) unsigned DEFAULT NULL,
  `userId` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

CREATE TABLE `podetails` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `PONumber` int(10) unsigned DEFAULT NULL,
    `productId` int(10) unsigned DEFAULT NULL,
    `poquantity` int(11) DEFAULT NULL,
    `pocostPrice` decimal(9,2) DEFAULT NULL,
    `delivered` varchar(20) NOT NULL DEFAULT 'false',
    `paidStatus` varchar(20) NOT NULL DEFAULT 'false',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

How to combine multiple INSERT INTO's with prepared statements?

One of the many advantages of Dapper over straight ADO.NET is the simplicity of inserting a list of data to a table.

You just need a list of a class <T> where the properties of the class have the same name of your database fields, so...

private class ChatRoomUser
{
    public int chatroomId {get;set;}
    public int userId {get;set;}
}

public static void LinkUsersToChatroom(int chatroomId, int[] userIds)
{
    //Transform the array in an IEnumerable of ChatRoomUser
    var users = userIds.Select(x => new ChatRoomUser
    { 
        chatroomId = chatroomId, 
        userIds = x
    };
    using SqlConnection connection = new(connectionString);
    connection.Execute(@"INSERT INTO chatroom_users (chatroomId, userId)  
                         VALUES (@chatroomId, @userId)", 
                         users);

}

You can use this approach if you want to maintain the actual interface of your current method, but, of course, nothing prevents you to build the list directly where you build the array and pass the list instead of the two parameters or even create an overload of this method that accepts the list as its input.

---

Related Topics