1 insert statement with multiple values VB net with parameters

Question

0.00/5 (No votes)

See more:

I need to input 1 GB data every 15 minutes
if looping my insert statement it will take around 20 minutes
its take to long

I need to insert multiple Rows in 1 insert statement
looping the values but not the insert statement

code

Sub bandingkan_data_tblpibconr()
        Dim Bs_access As New DataTable
        Bs_access = query.LoadAcces_tblpibconr
        Dim dt3 As New DataTable
        dt3 = Bs_access
        Cmd.Connection = connNpgsql.OpenConnection()



        Dim kueri As String

        kueri = "insert into tblpibconr values"
        For i = 0 To dt3.Rows.Count - 1


            kueri = kueri + "('" + dt3.Rows(i)("car").ToString + "','" + dt3.Rows(i)("reskd").ToString + "','" + dt3.Rows(i)("contno").ToString + "','" + dt3.Rows(i)("contukur").ToString.Trim() + "','" + dt3.Rows(i)("conttipe").ToString + "')"
            kueri = kueri + ","

    
        Next
        kueri = kueri.Remove(kueri.Length - 1, 1)

        Cmd.CommandText = kueri
        Cmd.ExecuteNonQuery()


        connNpgsql.CloseConexion()
    End Sub

<pre lang="vb">

this is my code and its works
but now I need to add parameters on it

Cmd.Parameters.Add("@car", NpgsqlTypes.NpgsqlDbType.Text).Value = dt3.Rows(i)("car").ToString
    Cmd.Parameters.Add("@reskd", NpgsqlTypes.NpgsqlDbType.Text).Value = dt3.Rows(i)("reskd").ToString
    Cmd.Parameters.Add("@contno", NpgsqlTypes.NpgsqlDbType.Text).Value = dt3.Rows(i)("contno").ToString
    Cmd.Parameters.Add("@contukur", NpgsqlTypes.NpgsqlDbType.Text).Value = dt3.Rows(i)("contukur").ToString.Trim()
    Cmd.Parameters.Add("@conttipe", NpgsqlTypes.NpgsqlDbType.Text).Value = dt3.Rows(i)("conttipe").ToString

how can i modify my kueri and input my parameter inside it

What I have tried:

I need to input data from the data table into PostgreSQL
if u guys know how to input it faster like use bulk insert please let me now

Sub bandingkan_data_tblpibconr()
    Dim Bs_access As New DataTable
    Bs_access = query.LoadAcces_tblpibconr
    Dim dt3 As New DataTable
    dt3 = Bs_access
    Cmd.Connection = connNpgsql.OpenConnection()

    Try
        Dim insProd As String = "insert into tblpibconr(car, reskd, contno, contukur,conttipe) values (@car, @reskd, @contno, @contukur,@conttipe)"
        Dim cmdSql As New NpgsqlCommand(insProd, connNpgsql.OpenConnection)
        connNpgsql.OpenConnection()
        Dim i As Integer
        For i = 0 To (dt3.Rows.Count - 1)
            Cmd.Parameters.Clear()
            ' ------------ WORKING CODE -------------------
            With Cmd

                .Parameters.Add("@car", NpgsqlTypes.NpgsqlDbType.Text).Value = dt3.Rows(i)("car").ToString
                .Parameters.Add("@reskd", NpgsqlTypes.NpgsqlDbType.Text).Value = dt3.Rows(i)("reskd").ToString
                .Parameters.Add("@contno", NpgsqlTypes.NpgsqlDbType.Text).Value = dt3.Rows(i)("contno").ToString
                .Parameters.Add("@contukur", NpgsqlTypes.NpgsqlDbType.Text).Value = dt3.Rows(i)("contukur").ToString.Trim()
                .Parameters.Add("@conttipe", NpgsqlTypes.NpgsqlDbType.Text).Value = dt3.Rows(i)("conttipe").ToString

            End With
            ' ------------ WORKING CODE -------------------
            Cmd.CommandText = insProd
            Cmd.ExecuteNonQuery()
        Next
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        connNpgsql.CloseConexion()
    End Try
End Sub

I have tried this code but it still looping the insert statement

Posted 22-Sep-20 6:29am

Lix Felix

Updated 22-Sep-20 22:30pm

v2

Add a Solution

2 solutions

Solution 1

VB

        kueri = "insert into tblpibconr values"
...
            kueri = kueri + "('" + dt3.Rows(i)("car").ToString + "','" + dt3.Rows(i)("reskd").ToString + "','" + dt3.Rows(i)("contno").ToString + "','" + dt3.Rows(i)("contukur").ToString.Trim() + "','" + dt3.Rows(i)("conttipe").ToString + "')"
            kueri = kueri + ","

Not necessary a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

Quote:
I need to insert multiple Rows in 1 insert statement

What is wrong in doing 1 insert per record ?
[Update]

Quote:
I need to input 1 GB data every 15 minutes
if looping my insert statement it will take around 20 minutes

Have you looked at bulk insert ?
Bulk Insert Data into SQL Server[^]
SQL Server INSERT Multiple Rows Into a Table Using One Statement[^]

Posted 22-Sep-20 7:07am

Patrice T

Updated 22-Sep-20 19:03pm

v3

Comments

Lix Felix 22-Sep-20 21:53pm

that's why I need to use parameters sir to convert the data type
I need to input 1 GB data every 15 minutes
if looping my insert statement it will take around 20 minutes
its take to long

Patrice T 23-Sep-20 1:04am

Use Improve question to update your question.
So that everyone can pay attention to this information.

Lix Felix 23-Sep-20 1:32am

I don't think I can use a bulk insert with PostgreSQL sir

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-09-22T22:30:00

Try something like this:

VB.NET

Using conn As NpgsqlConnection = connNpgsql.OpenConnection()
    Using cmd As New NpgsqlCommand("", conn)
        Dim sb As New StringBuilder("insert into tblpibconr(car, reskd, contno, contukur,conttipe) values ");
        For i As Integer = 0 To dt3.Rows.Count - 1
            If i <> 0 Then sb.Append(",")
            sb.AppendFormat("(@car{0}, @reskd{0}, @contno{0}, @contukur{0}, @conttipe{0})", i)
            
            Dim row As DataRow = dt3.Rows(i)
            cmd.Parameters.Add("@car" & i, NpgsqlTypes.NpgsqlDbType.Text).Value = row("car")
            cmd.Parameters.Add("@reskd" & i, NpgsqlTypes.NpgsqlDbType.Text).Value = row("reskd")
            cmd.Parameters.Add("@contno" & i, NpgsqlTypes.NpgsqlDbType.Text).Value = row("contno")
            cmd.Parameters.Add("@contukur" & i, NpgsqlTypes.NpgsqlDbType.Text).Value = row("contukur")
            cmd.Parameters.Add("@conttipe" & i, NpgsqlTypes.NpgsqlDbType.Text).Value = row("conttipe")
        Next
        
        cmdSql.CommandText = sb.ToString()
        cmdSql.ExecuteNonQuery()
    End Using
End Using

To limit the number of rows per batch when you get the "too many parameters" error:

VB.NET

Const RowsPerBatch As Integer = 1000

Using conn As NpgsqlConnection = connNpgsql.OpenConnection()
    For batchStart As Integer = 0 to dt3.Rows.Count - 1 Step RowsPerBatch
        Using cmd As New NpgsqlCommand("", conn)
            Dim sb As New StringBuilder("insert into tblpibconr(car, reskd, contno, contukur,conttipe) values ");
            For i As Integer = 0 To Math.Min(dt3.Rows.Count - batchStart - 1, RowsPerBatch - 1)
                If i <> 0 Then sb.Append(",")
                sb.AppendFormat("(@car{0}, @reskd{0}, @contno{0}, @contukur{0}, @conttipe{0})", i)
                
                Dim row As DataRow = dt3.Rows(batchStart + i)
                cmd.Parameters.Add("@car" & i, NpgsqlTypes.NpgsqlDbType.Text).Value = row("car")
                cmd.Parameters.Add("@reskd" & i, NpgsqlTypes.NpgsqlDbType.Text).Value = row("reskd")
                cmd.Parameters.Add("@contno" & i, NpgsqlTypes.NpgsqlDbType.Text).Value = row("contno")
                cmd.Parameters.Add("@contukur" & i, NpgsqlTypes.NpgsqlDbType.Text).Value = row("contukur")
                cmd.Parameters.Add("@conttipe" & i, NpgsqlTypes.NpgsqlDbType.Text).Value = row("conttipe")
            Next
            
            cmdSql.CommandText = sb.ToString()
            cmdSql.ExecuteNonQuery()
        End Using
    Next
End Using