I do not want to use the Loop

Question

1.00/5 (1 vote)

See more:

I do not want to use the Loop
It's a slow process

VB

Dim StrNumber, StrBarcode, StrProductName, StrQty, StrUnitPrice, StrUnit, StrTotal As String
        Dim insertcommenttext As String = ""
        Dim Cm As New MySqlCommand
        Try
            For i As Integer = 0 To Me.DataGridList.Rows.Count - 1
                AUTO_NUMBER()

                StrNumber = Me.DataGridList.Rows(i).Cells(0).Value.ToString() 
                StrBarcode = Me.DataGridList.Rows(i).Cells(1).Value.ToString()
                StrProductName = Me.DataGridList.Rows(i).Cells(2).Value.ToString() 
                StrQty = Me.DataGridList.Rows(i).Cells(3).Value.ToString() 
                StrUnitPrice = Me.DataGridList.Rows(i).Cells(4).Value.ToString()
                StrUnit = Me.DataGridList.Rows(i).Cells(5).Value.ToString() 
                StrTotal = Me.DataGridList.Rows(i).Cells(6).Value.ToString() 

                insertcommenttext = "INSERT INTO Temporarybill (AutoNumber,TemID,Number,Barcode,ProductName,Qty,UnitPrice,Unit,Total,CustomerID) VALUES('" _
                    & IntAutoNumber & "','" & "TEM-" & IntTem_ID & "','" & StrNumber & "','" & StrBarcode & "','" & StrProductName & _
                    "','" & StrQty & "','" & StrUnitPrice & "','" & StrUnit & "','" & StrTotal & "','" & TbxBoxCustomersID.Text & "')"

                With Cm
                    .CommandType = CommandType.Text
                    .CommandText = insertcommenttext
                    .Connection = StrSettingConn
                    .ExecuteNonQuery()
                End With
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Posted 22-Mar-15 10:52am

SN Mono

Updated 22-Mar-15 12:18pm

PIEBALDconsult

v3

Add a Solution

Comments

Maciej Los 22-Mar-15 17:27pm

Does DataGridView has datasource?

2 solutions

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)

PIEBALDconsult · Answer 1 · 2015-03-22T12:23:00

DO NOT use concatenation to form SQL statments. EVER! Use a parameterized statement.

This will help you immensely in this case:

0) Before the loop:
0.1) Create the INSERT command, with parameter markers.
0.2) Add the parameters.
1) Inside the loop:
1.1) Set the parameter Values.
1.2) Execute the command.

Possibly use a transaction as well.

manchanx · Answer 2 · 2015-03-22T10:57:00

It's not the loop itself that causes your method to be slow. It's the fact that you execute a database-INSERT for each entry in your datagrid individually.

1) Move the database-Insert-Stuff out of your loop (With Cm .... End With)

2) In your loop, concatenate all the Insert-Statements into one Batch-Statement, preferably using a StringBuilder *

3) When your loop is done, your database-Insert executes that one "big" Insert-Statement in one go.

Edit: There are also some other improvements you can make, first and foremost to use Sql-Parameters. Please refer to one of my previous answers:
How to insert datetime in sql server 2008 using VB 2010?[^]

* Edit: I'm talking here about concatenating the "full" Insert-Statements into a so-called Batch-Statement. Not about concatenating a single Insert-Statement like you currently do, from multiple string-literals and variables. That should be done instead with Sql-Parameters which allows to write the Insert-Statement either as one single string-literal when not using a Batch-Statement, or with a formatted string-literal for building a Batch-Statement with different Sql-Parameter-Names for each Insert-Statement.

I do not want to use the Loop

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0