使用 Vb.net 更新 SQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21246159/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update SQL table using Vb.net
提问by SZman
I have a VB.net script that needs to put information into a table in the database. The catch is that it is based off of an item Id, so when I am updating I cannot just insert the row would like to update the row if it is already there. For this I need to use the Update command like so:
我有一个 VB.net 脚本，需要将信息放入数据库中的表中。问题是它基于项目 ID，所以当我更新时，我不能只插入想要更新的行（如果它已经存在）。为此，我需要像这样使用 Update 命令：
mysqlCommand = New SQLCommand(UPDATE tableName Colum2=Newvalue ... Where Column1=ItemId)
However I am wondering if there is anyway to tell wether or not a line was updated when I run
但是我想知道是否有任何方式可以告诉我运行时是否更新了一行
mysqlcommand.ExecuteNonQuery()
so that I do not need to check before I run this command and switch between this update command and a INSERT INTO command
这样我就不需要在运行此命令之前检查并在此更新命令和 INSERT INTO 命令之间切换
回答by Neolisk
From documentation of SqlCommand.ExecuteNonQuery @ MSDN:
来自SqlCommand.ExecuteNonQuery @ MSDN 的文档：
Executes a Transact-SQL statement against the connection and returns the number of rows affected.
对连接执行 Transact-SQL 语句并返回受影响的行数。
So you can write like this:
所以你可以这样写：
Dim recordsAffected As Integer = mysqlcommand.ExecuteNonQuery()
If recordsAffected = 1 Then 'it worked
EDIT:Regarding @macoms01 answer, my original intention was to have something like this:
编辑：关于@macoms01 的回答，我的初衷是这样的：
Select Case recordsAffected
Case 0: 'it did not work
Case 1: 'it worked
Case Else: Throw New BadDataException
End Select
You can never be sure that unique constraints are set on the DB - expect bad data.
您永远无法确定在数据库上设置了唯一约束 - 预计会出现错误数据。
But then I thought it would probably be too complicated for its purpose.
但后来我认为它的目的可能太复杂了。
回答by macoms01
I agree with @Neolisk, but a slight improvement could be to use the following:
我同意@Neolisk，但使用以下内容可能会略有改进：
Dim recordsAffected As Integer = mysqlcommand.ExecuteNonQuery()
If recordsAffected > 0 Then 'it worked
or this:
或这个：
Dim recordsAffected As Integer = mysqlcommand.ExecuteNonQuery()
If recordsAffected >= 1 Then 'it worked
@Neolisk's code will work great as long as Column1 is a unique identifier. Otherwise you may be updating more than one row. The "greater than"/"greater than or equal to" operator will catch the scenario where you are updating more than one row.
只要 Column1 是唯一标识符，@Neolisk 的代码就会很好用。否则，您可能会更新不止一行。“大于”/“大于或等于”运算符将捕获您更新多于一行的情况。
