如果不使用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 ... 

如果命令返回零计数记得清除参数集合随后插入查询

Answer 2

你可以在东西一个查询做像这样：

.CommandText = "INSERT INTO bookrecords (Title, Author, Edition, Publisher, ISBN) " & _ 
"SELECT * FROM (SELECT @title, @author, @edition, @publisher, @isbn AS ISBN) t " & _ 
"WHERE NOT EXISTS(SELECT ISBN FROM bookrecords b WHERE t.ISBN = b.ISBN) "