在不遍历每一行的情况下获取SQL记录

Question

上下文：我在数据库中保存了大约50条带有指纹的姓名记录，这些记录将用于使用指纹扫描仪进行日常登录/超时。

代码：

Dim conn As New MySqlConnection("...database_details")
Dim cmd As New MySqlCommand
Private Sub FetchRecords(sender As Object, e As IZKFPEngXEvents_OnCaptureEvent) Handles ZkFprint.OnCapture
'fingerprint sample from the scanner, returns a BASE64 format
Dim fp = e.aTemplate 

Using conn 
conn.Open()
cmd = conn.CreateCommand()
With cmd
.CommandType = CommandType.Text
'fprint stores varchar data type
.CommandText = "SELECT id,name,reg_fprint FROM tbl_emp"
End With
Dim dr as MySqlDataReader = cmd.ExecuteReader()
While dr.Read
'converts the fprint into BASE64
Dim fpFromDB = ZkFprint.DecodeTemplate1(dr.GetValue(1)) 

'compares fp to fpFromDB; returns true if match otherwise false
Dim result As = ZkFprint.VerFinger(fpFromDB, fp)
If result = True Then
lbName.Text = dr.GetValue(1)
Else
MessageBox.Show("Fingerprint not found")
End If
End While
End Using
End Sub

问题：当我扫描手指以计时输入/输出时，MessageBox。Show("Fingerprint not found"(行将在我的lbName之前触发X-1次。文本更改为匹配记录。这是因为dr.Read((读取第一行，直到我找到记录。

我想要什么：有没有一种方法可以让我在不遍历每一行的情况下获得记录？

我尝试了什么：我删除了Else语句，它解决了我的问题，但又导致了另一个问题：如果手指没有注册，它不会提供任何反馈。

If result = True Then
lbName.Text = dr.GetValue(1)
End If

编辑：感谢您的输入，我很感激。多亏了命令下面的@Shadow，我在玩了几次代码后也设法想出了一个解决方案。我用For循环替换了While循环，并将数据放在DataTable中，这样我就可以轻松地遍历它们。如果记录不存在，我还使用recordCount作为引用。

Dim fp = e.aTemplate 
Dim dt As New DataTable
Dim recordCount As Integer
Using conn 
conn.Open()
cmd = conn.CreateCommand()
With cmd
.CommandType = CommandType.Text
'fprint stores varchar data type
.CommandText = "SELECT id,name,reg_fprint FROM tbl_emp"
End With
Dim da As New MySqlDataAdapter(cmd)
da.Fill(dt)
For recordCount = 0 To dt.Rows.Count - 1
Dim result As = ZkFprint.VerFinger(dt.Rows(recordCount).Item(2).ToString, fp)
If result = True Then
lbName.Text = dt.Rows(recordCount).Item(1).ToString
Exit For
End If
Next
If recordCount = dt.Rows.Count Then
MessageBox.Show("Fingerprint not found")
End If
End Using

Answer 1

您可以考虑在转换为base64 时比较列filt

Dim conn As New MySqlConnection("...database_details")
Dim cmd As New MySqlCommand
Private Sub FetchRecords(sender As Object, e As IZKFPEngXEvents_OnCaptureEvent) Handles ZkFprint.OnCapture
'fingerprint sample from the scanner returns a BASE64 format
Dim fp = e.aTemplate 

Using conn 
conn.Open()
cmd = conn.CreateCommand()
With cmd
.CommandType = CommandType.Text
'fprint stores varchar data type
.CommandText = "SELECT id,name,reg_fprint FROM tbl_emp WHERE TO_BASE64(reg_fprint)=@reg_fprint"
.Parameters.Add(New MySqlClient.MySqlParameter("@reg_fprint", fp))
End With
Dim dr as MySqlDataReader = cmd.ExecuteReader()
If dr.Read             
lbName.Text = dr.GetValue(1)
lbName.refresh() 'To update label text
Else
MessageBox.Show("Fingerprint not found")
End If
End Using
End Sub

但你可以再次改变这个块

If result = True Then
lbName.Text = dr.GetValue(1)
End If

至

If result = True Then
lbName.Text = dr.GetValue(1)
lbName.Refresh() 'Update the label
End If