用于插入/更新 MySql DB 的函数返回错误的“受影响的行"；数字 [英] Function for inserting/updating MySql DB returns wrong "affected rows" number

问题描述

我正在使用下面的代码使用 DataTable

I'm using the code below to insert or update a MySql table using values from a DataTable

Function DB_Multi_Ins_Or_Upd_From_DT(ByVal dt As DataTable, ByVal DestTbl$) As Integer
    Using SQLConnection As New MySqlConnection(CnStr) 
        Using sqlCommand As New MySqlCommand()
            Dim CmdTxt$ = "INSERT INTO " & DestTbl & " ("
            For Each Col As DataColumn In dt.Columns
                CmdTxt &= Col.ColumnName & ", "
            Next
            CmdTxt = CmdTxt.Substring(0, CmdTxt.Length - 2) & ") VALUES ("
            For x As Integer = 0 To dt.Rows.Count - 1
                For xx As Integer = 0 To dt.Columns.Count - 1
                    CmdTxt &= "@" & "R" & x.ToString & "C" & xx.ToString & ", "
                    sqlCommand.Parameters.AddWithValue _
                        ("@" & "R" & x.ToString & "C" & xx.ToString, dt.Rows(x)(xx))
                Next xx
                CmdTxt = CmdTxt.Substring(0, CmdTxt.Length - 2) & "), ("
            Next x
            CmdTxt = CmdTxt.Substring(0, CmdTxt.Length - 3)
            CmdTxt &= " ON DUPLICATE KEY UPDATE "
            For Each Col As DataColumn In dt.Columns
                CmdTxt &= Col.ColumnName & "=VALUES(" & Col.ColumnName & "), "
            Next
            CmdTxt = CmdTxt.Substring(0, CmdTxt.Length - 2)
            With sqlCommand
                .CommandText = CmdTxt
                .Connection = SQLConnection
                .CommandType = CommandType.Text
            End With
            Try
                SQLConnection.Open()
                Dim AffRows% = sqlCommand.ExecuteNonQuery()
                Return AffRows
            Catch ex As MySqlException
                Return -1
            Finally
                SQLConnection.Close()
            End Try
        End Using
    End Using
End Function

碰巧代码更新了 1 行但返回了 2.

It happens that the code updates 1 row but returns 2.

似乎仅在 update 上返回错误结果，而在 insert 上返回正确数量的受影响行

It seems that the wrong result is returned only on update while on insert is returned the right number of affected rows

怎么了?如何修复并获取受影响行的正确数量?

What's wrong? How can I fix and get rigth number of affected rows?

要测试我的代码，它需要:

To test my code it needs:

1) 导入 MySql.Data.MySqlClient

1) Imports MySql.Data.MySqlClient

2) 给 cnstr 赋值，比如"datasource=" + Server_Name + ";username=" + UserDB + ";password="+ 密码 + ";database=" + Database_Name + ""

2) give a value to cnstr like "datasource=" + Server_Name + ";username= " + UserDB + ";password=" + Password + ";database=" + Database_Name + ""

3) 创建一个 DataTable，其列名类似于数据库表的字段

3) create a DataTable who has column names like fields of a DB table

4) 调用传递DataTable和表名的函数在数据库上

4) call the function passing the DataTable and the name of the table on the DB

推荐答案

使用 ON DUPLICATE KEY UPDATE，每行的受影响行值为 1，如果该行作为新行插入，如果更新现有行，则为 2，并且0 如果现有行设置为其当前值

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values

dev.mysql.com

这篇关于用于插入/更新 MySql DB 的函数返回错误的“受影响的行"；数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！