INSERT More than 50 Query Using same opened connection

Question

So what i basically want is to insert more than 1 query to my database without needing of conn.close than conn.open too many times and also doing try more than 50 times ..

Here is exp for the code :

    Dim COMMAND As MySqlCommand
    Dim reader As MySqlDataReader
    Dim conn As MySqlConnection
    Dim Item1 As String = "INSERT INTO item_template (entry, name, display) VALUES ('1234', 'Testing', '654';"
    Dim Item3 As String = "UPDATE Item_template SET entry = '123' where name like 'test32111';"
    Dim Item2 As String = "INSERT INTO item_template (entry, name, display) VALUES ('123467', 'Testing332', '65478';"
    Try
        conn.Open()
        COMMAND = New MySqlCommand(Item1, conn)
        reader = COMMAND.ExecuteReader
        conn.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
    Try
        conn.Open()
        COMMAND = New MySqlCommand(Item3, conn)
        reader = COMMAND.ExecuteReader
        conn.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
    Try
        conn.Open()
        COMMAND = New MySqlCommand(Item2, conn)
        reader = COMMAND.ExecuteReader
        conn.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

insert multiple rows: https://stackoverflow.com/a/452934/832052 — djv, Sep 17 '18 at 19:20
Have you tried stacking the sql commands in the same string and execute the contatenated string? Alternativaly, save everything into a .sql file and run the file. — Pedro, Sep 17 '18 at 19:20
@pedro I know about that yeah, i already have something like that but this is different the SQL also have update and other insert and replace and update etc.... so i want to run them all together instead of doing the method i posted.. — John Evenetcha, Sep 17 '18 at 19:22
@djv i already know that method i will fix my post to fit the requirements.. — John Evenetcha, Sep 17 '18 at 19:22
Just open the connection once, run all the commands you wish to run, then close the connection at the end? — JNevill, Sep 17 '18 at 19:27
You don't need to close and reopen the connection between queries; open the connection at the start, do your queries, then close. _You can even reuse the command object by using its CommandText property to set the current query; but make sure the clear the parameters if/when you start using parameterized queries._ — Uueerdo, Sep 17 '18 at 19:27
If you have triggers on those tables, you would want to do a single command with multiple insert. If not then it may not matter. — djv, Sep 17 '18 at 19:34
@JNevill well that doesn't work, it will just insert first query and ignore others ... — John Evenetcha, Sep 17 '18 at 19:34
@Uueerdo Yeah like i said it will just insert only first one and ignore others also my queries are pretty long that's why i made just simple queries so i can explain my issue :) — John Evenetcha, Sep 17 '18 at 19:35
@JohnEvenetcha I'm pretty certain it will not. Unless you have a setting ticked where you need to commit each transaction after submitting ([more info here](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqltransaction.commit?view=netframework-4.7.2)) — JNevill, Sep 17 '18 at 19:35
@JNevill I'm not saying :D xD it says already :p i means i tried it please can you show me exp on how it could be as answer ? and i'll see maybe i was doing something wrong.. — John Evenetcha, Sep 17 '18 at 19:37
Ah, I think I see your (original) problem; you need to close readers... or just use command's ExecuteNonQuery method. — Uueerdo, Sep 17 '18 at 19:37
Exactly :D haha so can you please post on how i could make it clearly ? and thank you — John Evenetcha, Sep 17 '18 at 19:38
Oh @uueerdo that's a good catch. Instead of sending setting up your `reader` you just do [COMMAND.ExecuteNonQuery();](https://dev.mysql.com/doc/dev/connector-net/8.0/html/M_MySql_Data_MySqlClient_MySqlCommand_ExecuteNonQuery.htm) Or close your reader between commands (but why open them at all? There is nothing to "read" from an INSERT or UPDATE). — JNevill, Sep 17 '18 at 19:41

Uueerdo · Answer 1 · 2018-09-17T19:47:29.323

Without addressing other matters like parameterized/prepared queries, the simplest fix should be...

Dim COMMAND As MySqlCommand
Dim conn As MySqlConnection
Dim Item1 As String = "INSERT INTO item_template (entry, name, display) VALUES ('1234', 'Testing', '654');"
Dim Item3 As String = "UPDATE Item_template SET entry = '123' where name like 'test32111';"
Dim Item2 As String = "INSERT INTO item_template (entry, name, display) VALUES ('123467', 'Testing332', '65478');"
Try
    conn.Open()
    COMMAND = New MySqlCommand("", conn)
    COMMAND.CommandText = Item1
    COMMAND.ExecuteNonQuery
    COMMAND.CommandText = Item2
    COMMAND.ExecuteNonQuery
    COMMAND.CommandText = Item3
    COMMAND.ExecuteNonQuery
    conn.Close()
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

My VB is a little rusty though, so beware of typo-level errors (I tried to add ; more than once)

Thanks actually just remove the dim reader as mysqldatareader :D as it's not needed :) — John Evenetcha, Sep 17 '18 at 19:44

Olivier Jacot-Descombes · Accepted Answer · 2018-09-18T09:42:44.237

There are some optimizations you can make. You should embed the disposable resources in a Using-Statement. The Using statement automatically closes and disposes the resources at the end. Especially the connection will be closed automatically. It does so, even if the Using-block is left prematurely because of an exception or because a Return statement terminates the method.

Keep the connection open while processing the batch.

DRY: Don't Repeat Yourself. Try to avoid code repetition make with copy paste. In the following example I create an array of SQL texts that can be processed in a loop.

SQL commands like INSERT, UPDATE, DELETE do not return a result set. Use ExecuteNonQuery instead of ExecuteReader for them.

The command can be resued by assigning it a new command text.

Dim sqlTexts = {
    "INSERT INTO item_template (entry, name, display) VALUES ('1234', 'Testing', '654';",
    "UPDATE Item_template SET entry = '123' where name like 'test32111';",
    "INSERT INTO item_template (entry, name, display) VALUES ('12367', 'Testing332', '65478';"
}

Using _
    conn As New MySqlConnection(connectionString),
    command As New MySqlCommand With {
        .CommandType = CommandType.Text,
        .Connection = conn
    }

    conn.Open()
    For Each sql As String In sqlTexts
        Try
            command.CommandText = sql
            command.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    Next
End Using

Note that the Using-statement includes the command as well as we are using VB's Implicit Line Continuation feature as for the initialization of the sqlTexts array.

Thanks very much ! i just can't accept both answers or it's possible as your is great and the other is nice too ! thanks ;) — John Evenetcha, Sep 17 '18 at 19:57
i will pick urs just because there is more than 600 SQL line :D different hehe doing other method is more easy to understand but yours is really great :) — John Evenetcha, Sep 17 '18 at 20:10
Is it possible to add progress bar to this so people know that it will take long :D — John Evenetcha, Sep 17 '18 at 22:06
See: [Make Progress Bar Running From Public Class](https://stackoverflow.com/q/17031667/880990) — Olivier Jacot-Descombes, Sep 18 '18 at 09:30
It's fine i got better method which works great :) thanks again — John Evenetcha, Sep 18 '18 at 11:05

INSERT More than 50 Query Using same opened connection

2 Answers2