mysql - 如何使用vb.net检查记录是否存在，如果不存在则插入？

Question

我一直在检查数据库中是否存在记录。这在 php 中很容易，但我找不到如何在 vb.net 中执行此操作的好教程。如果数据库中不存在，我想从文本框中插入值。

这是我的代码:

Using SQLConnection As New MySqlConnection(connString)


        Using sqlCommand As New MySqlCommand()
            With sqlCommand
            'check if record exist 

            'if not execute these
                .CommandText = "INSERT INTO bookrecords (Title, Author, Edition, Publisher, ISBN) values (@title,@author,@edition,@publisher,@isbn)"
                .Connection = SQLConnection
                .CommandType = CommandType.Text
                .Parameters.AddWithValue("@title", txtTitle.Text)
                .Parameters.AddWithValue("@author", txtAuthor.Text)
                .Parameters.AddWithValue("@edition", txtEdition.Text)
                .Parameters.AddWithValue("@publisher", txtPublisher.Text)
                .Parameters.AddWithValue("@isbn", txtISBN.Text)

            End With


            Try
                SQLConnection.ConnectionString = "Server=localhost;Database=booksdb;Uid=root;Pwd=;"
                SQLConnection.Open()
                sqlCommand.ExecuteNonQuery()
                iReturn = True
                'MessageBox.Show("Connection Opened")

            Catch ex As MySqlException
                MessageBox.Show("Error: " & ex.ToString())
                iReturn = False
            Finally
                SQLConnection.Close()
                'MessageBox.Show("Connection Closed")
            End Try

        End Using
    End Using

我只是希望 @isbn 成为确定记录是否已存在的关键。

Answer 1

显而易见的事情(不触及数据库架构)是向数据库发送一个计数查询以获取所需的 ISBN。
这可以通过 ExecuteScalar 适当的查询来完成

Using con = new MySqlConnection(connectionString)
    Using sqlCommand As New MySqlCommand()
        With sqlCommand
            .Connection = con
            .Parameters.AddWithValue("@isbn", txtISBN.Text)
            .CommandText = "SELECT COUNT(*) FROM  bookrecords WHERE ISBN = @isbn"
        End With
        con.Open()
        Dim result = sqlCommand.ExecuteScalar()
        if Convert.ToInt32(result) = 0 Then
            ' ISBN doesn't exist ....
            sqlCommand.Parameters.Clear()
            ' rest of your insert code ...

如果命令返回计数为零，请记住清除后续插入查询的参数集合