我正在努力將我的搜索代碼鏈接到一個文本框，以便實際搜索和輸入可能的搜索。如何將我的文本框連接到我的搜索代碼以使其作業？
（一點背景關系，我正在嘗試在 Visual Basic（vb.net）中連接一個 SQL 資料庫并擁有一個可搜索的資料庫或電子表格）
我嘗試將代碼放在下面的文本框子中。有什么幫助！
Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
Try
Using connection = New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=vbconnectionfinal;Integrated Security=True")
' create a new instance of the command object
Using Command = New SqlCommand("Select * From TrueTrack WHERE UserName = @name AND DeviceType = @type", connection)
' parameterize the query
Command.Parameters.Add("@name", SqlDbType.VarChar, 100).Value = "my name search"
Command.Parameters.Add("@type", SqlDbType.VarChar, 100).Value = "my device type search"
' create a new instance of the data adapter object
Using adapter = New SqlDataAdapter(Command)
' fill a DataTable with the data from the adapter
Dim table = New DataTable()
adapter.Fill(table)
DataGridView1.DataSource = Table
End Using
End Using
End Using
Catch ex As Exception
' display the error
MessageBox.Show(ex.Message)
End Try
End Sub
uj5u.com熱心網友回復：
基于 TextBox 值進行查詢的一種方法是使用計時器，該計時器僅在最終按鍵后的一段時間后運行。此示例將在最后一次更改 TextBox 后 1 秒后進行查詢。它還將查詢從 UI 執行緒卸載到計時器執行緒，這是一個后臺執行緒。所以你的用戶界面不會在運行查詢時凍結
Private ReadOnly queryTimer As New System.Threading.Timer(AddressOf runQuery, Nothing, -1, -1)
Private queryString As String
Private textChangeQueryDelay As Integer = 1000
Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
queryString = TextBox1.Text
queryTimer.Change(textChangeQueryDelay, -1)
End Sub
Private Sub runQuery(state As Object)
Dim table = New DataTable()
Using connection = New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=vbconnectionfinal;Integrated Security=True")
Using Command = New SqlCommand("Select * From TrueTrack WHERE UserName = @name AND DeviceType = @type", connection)
Command.Parameters.Add("@name", SqlDbType.VarChar, 100).Value = queryString
Command.Parameters.Add("@type", SqlDbType.VarChar, 100).Value = "my device type search"
Using adapter = New SqlDataAdapter(Command)
adapter.Fill(table)
End Using
End Using
End Using
DataGridView1.Invoke(Sub() DataGridView1.DataSource = table)
End Sub
但它沒有明確地方"my name search"和"my device type search"來源。我假設名稱來自 TextBox1 所以這就是代碼的作用。
另一件事是您正在檢查查詢中的相等性，但更合適的事情（因為您正在對 TextChanged 執行操作）可能是檢查類似即
Using Command = New SqlCommand("Select * From TrueTrack WHERE UserName like @name AND DeviceType = @type")
...
Command.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = "%" & querystring & "%"
這樣您就可以回傳所有名稱，如您的 TextBox 文本。這感覺更“正確”，因為您在用戶鍵入時進行查詢。但是對于完全匹配，特別是對于多個 where 子句（我仍然沒有在這個答案中調和），在評論中提到的按鈕單擊上執行一次可能是你最好的選擇。
根據評論更新
您可以通過在每個 TextBox_TextChanged 事件處理程式中呼叫查詢計時器來查詢名稱和型別（或讓一個處理程式同時處理兩者）
Private ReadOnly queryTimer As New System.Threading.Timer(AddressOf runQuery, Nothing, -1, -1)
Private searchName As String
Private searchType As String
Private textChangeQueryDelay As Integer = 1000
Private Sub TextBoxes_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged, TextBox2.TextChanged
searchName = TextBox1.Text
searchType = TextBox2.Text
queryTimer.Change(textChangeQueryDelay, -1)
End Sub
并且在查詢中您可以有條件地添加或不添加引數
Private Sub runQuery(state As Object)
Dim table = New DataTable()
Using connection = New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=vbconnectionfinal;Integrated Security=True")
Using command = New SqlCommand()
command.Connection = connection
Dim commandText = "SELECT * FROM TrueTrack WHERE 1=1 "
If Not String.IsNullOrEmpty(searchName) Then
commandText &= " AND UserName like @name "
command.Parameters.Add("@name", SqlDbType.VarChar, 100).Value = "%" & searchName & "%"
End If
If Not String.IsNullOrEmpty(searchType) Then
commandText &= " AND DeviceType like @type "
command.Parameters.Add("@type", SqlDbType.VarChar, 100).Value = "%" & searchType & "%"
End If
command.CommandText = commandText
Using adapter = New SqlDataAdapter(command)
adapter.Fill(table)
End Using
End Using
End Using
DataGridView1.Invoke(Sub() DataGridView1.DataSource = table)
End Sub
uj5u.com熱心網友回復：
每次用戶在文本框中鍵入字母時，我都不會運行所有資料庫代碼。用戶在準備好搜索時單擊按鈕怎么樣。
所述Command和connection可以在一個單一的組合Using塊通過在第一行的末尾添加一個逗號。
命令引數的值是您傳遞給函式的引數。
您不需要 a 的額外開銷DataAdapter。只是Load一個DataTable和一個DataReader。
Private Function SearchDatabase(Name As String, Type As String) As DataTable
Dim dt As New DataTable
Using connection = New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=vbconnectionfinal;Integrated Security=True"),
Command = New SqlCommand("Select * From TrueTrack WHERE UserName = @name AND DeviceType = @type", connection)
Command.Parameters.Add("@name", SqlDbType.VarChar, 100).Value = Name
Command.Parameters.Add("@type", SqlDbType.VarChar, 100).Value = Type
connection.Open()
Using reader = Command.ExecuteReader
dt.Load(reader)
End Using
End Using
Return dt
End Function
Private Sub Button1_Click() Handles Button1.Click
Dim dt As DataTable
Try
dt = SearchDatabase(txtName.Text, txtType.Text)
Catch ex As Exception
MessageBox.Show(ex.Message)
Exit Sub
End Try
'Use the values in the DataTable as needed
End Sub
uj5u.com熱心網友回復：
您可以使用為您處理引數化的通用資料庫管理器類
- 它會將此資料庫代碼從您的表單中洗掉
- 更容易維護您的資料庫代碼，因為它只會在一個地方
- 您所要做的就是實體化資料庫管理器并將 sql 和引數傳遞給它，這樣您就不必像這樣重復您的代碼
以下是資料庫管理器的外觀：
Imports System.Data.SqlClient
Public Class DBManager
Public Function GetDataSet(sql As String, Optional parameters As Dictionary(Of String, Object) = Nothing) As DataSet
Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=vbconnectionfinal;Integrated Security=True")
Try
' Attempt to establish connection
If connection.State <> ConnectionState.Open Then
connection.Open()
End If
Using cmd As New SqlCommand(sql, connection)
' Add parameters
If parameters IsNot Nothing Then
For Each key As String in parameters
cmd.Parameters.Add(new SqlParameter(key, parameters(key)))
Next
End If
' Fill dataset
Dim ds As New DataSet
Dim adapter As New SqlDataAdapter(cmd)
adapter.Fill(ds)
' Return dataset
Return ds
End Using
Catch ex As Exception
' Log to some central manager or file or throw to caller
' Throw ex
End Try
End Using
Return Nothing
End Function
Public Function ExecuteCommand(sql As String, Optional parameters As Dictionary(Of String, Object) = Nothing) As Integer
Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=vbconnectionfinal;Integrated Security=True")
Try
' Attempt to establish connection
If connection.State <> ConnectionState.Open Then
connection.Open()
End If
Using cmd As New SqlCommand(sql, connection)
' Add parameters
If parameters IsNot Nothing Then
For Each key As String in parameters
cmd.Parameters.Add(new SqlParameter(key, parameters(key)))
Next
End If
Return cmd.ExecuteNonQuery() ' Count of affected rows
End Using
Catch ex As Exception
' Log to some central manager or file or throw to caller
' Throw ex
End Try
End Using
Return -1
End Function
End Class
然后，您可以在整個代碼的各個地方使用此管理器
Module Module1
Sub Main()
' Sql Only
Dim conn As New DBManager
Dim sql As String = "SELECT * FROM TestData;"
Try
' Remember parameters are optional
Dim ds As DataSet = conn.GetDataSet(sql)
If ds IsNot Nothing Then
' Do work on retrieved dataset
End If
Catch ex As Exception
' Catch here if db manager is throwing
End Try
' Parameterized sql
sql = "INSERT INTO TestData (Value)
VALUES (@value);"
Dim parameters As New Dictionary(Of String, Object) From {
{ "@value", "x" }
}
Try
Dim affectedRows As Integer = conn.ExecuteCommand(sql, parameters)
If affectedRows = -1 Then
' Something went wrong
End If
Catch ex As Exception
' Catch here if db manager is throwing
End Try
End Sub
End Module
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/405633.html
標籤：