Click here to Skip to main content
14,735,093 members
Please Sign up or sign in to vote.
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
Updated 22-Sep-20 23:30pm
v2

Try something like this:
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:
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
   
v5
Comments
Lix Felix 23-Sep-20 5:28am
   
this code gives me an error sir "Append is not a member of npgsqlConnectionStringBuilder"
Richard Deeming 23-Sep-20 5:35am
   
Read the answer again.
Dim sb As New StringBuilder(...)

That's StringBuilder, NOT npgsqlConnectionStringBuilder.

You may need to add Imports System.Text at the top of your file.
Lix Felix 30-Oct-20 5:01am
   
Hello sir, after I check and use the code that u give to me, that code gives me an error sir, data inserted is not correct it's just looping the first 1000 data
Richard Deeming 30-Oct-20 6:02am
   
My code:
Dim row As DataRow = dt3.Rows(batchStart + i)

Your code:
Dim row As DataRow = dt3.Rows(i)

Spot the difference.
Lix Felix 23-Sep-20 5:49am
   
Using conn As New NpgsqlConnection = connNpgsql.OpenConnection() (=) this symbol give me an error "End of statement "
Richard Deeming 23-Sep-20 5:50am
   
Remove the New from that line:
Using conn As NpgsqlConnection = connNpgsql.OpenConnection()
Lix Felix 23-Sep-20 7:03am
   
'A statement cannot have more than 65535 parameters' sir what can I do with this error ?
Lix Felix 23-Sep-20 7:04am
   
its work fine for the previous table
Richard Deeming 23-Sep-20 7:05am
   
Split the source data up into smaller batches. You're inserting 5 parameters per row, so you can insert a maximum of 13107 rows per batch.
Lix Felix 23-Sep-20 7:07am
   
so I need to limit the looping?
Richard Deeming 23-Sep-20 7:08am
   
Yes.
Lix Felix 23-Sep-20 7:12am
   
sir, I have 54.000 rows data if the max is 2000, how can I insert all the data, sir?
Richard Deeming 23-Sep-20 7:14am
   
See my updated answer.
Lix Felix 23-Sep-20 7:25am
   
Thank you so much sir its work perfectly
Lix Felix 23-Sep-20 8:22am
   
sir, I have another problem when I tried to run 2 subs at the same time
I got this error "System.ObjectDisposedException: 'Cannot access a disposed object.
Object name: 'NpgsqlConnection'.'"
Richard Deeming 23-Sep-20 8:33am
   
Looks like you're reusing the same connection object across multiple database calls. Don't do that. Create a new connection object on each call to OpenConnection, and wrap it in a Using block to make sure it's disposed of properly when you've finished with it.
Lix Felix 23-Sep-20 8:46am
   
oke sir, I add this on my code and its work fine
Dim connPOSTGRES As New ConnNpsql
Richard Deeming 23-Sep-20 7:06am
   
For this one, you're inserting 28 parameters per row, so you can insert 2340 rows per batch.
        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[^]
   
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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900