我不想使用Loop [英] I do not want to use the Loop

问题描述

我不想使用循环

这是一个缓慢的过程

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

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

推荐答案

请勿使用连接以形成SQL语句。 EVER！使用参数化声明。



在这种情况下，这将极大地帮助你：



0）循环之前：

0.1）使用参数标记创建INSERT命令。

0.2）添加参数。

1）循环内部：

1.1）设置参数值。

1.2）执行命令。



也可能使用一个事务。

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.

循环本身并不会导致你的方法变慢。事实上，你为数据网格中的每个条目单独执行数据库INSERT。



1）将数据库插入 - 东西移出你的循环（使用Cm） ....结束使用）



2）在你的循环中，将所有Insert-Statements连接成一个Batch-Statement，最好使用StringBuilder *



3）当你的循环完成后，你的数据库插件会一次执行那个大插入语句。



编辑：您还可以进行一些其他改进，首先是使用Sql-Parameters。请参考我以前的答案之一：

如何使用VB 2010在sql server 2008中插入日期时间？ [ ^ ]



* 编辑：我在这里谈论将完整插入语句连接成一个所谓的批处理语句。不是从多个字符串文字和变量连接像你当前那样的单个Insert-Statement。这应该用Sql-Parameters来完成，它允许在不使用Batch-Statement时将Insert-Statement编写为单个字符串文字，或者使用格式化字符串文字来构建具有不同Sql参数的Batch-Statement每个Insert-Statement的-Names。

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.

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?[^]

* 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.

这篇关于我不想使用Loop的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！