从动态文本框中检索动态sql参数值的循环中出现问题

Question

all。我是VB.NET的新手。我面临的问题是如何从动态文本框中传递动态SQL参数值来搜索数据。我添加了对动态文本框和标签的控制，并希望根据用户输入的动态文本框值搜索数据库表上的数据。目前，我只能从1个动态文本框值中搜索数据。

我希望可以检索用户输入的动态文本框中的所有值，并根据用户输入的SQL参数变量名称和值搜索数据。

有人能给我一些解决这个问题的办法吗？我在这个问题上已经坚持了好几天了。谢谢你的帮助！

我尝试过的：

Private Sub FilterData()
Dim count As Integer = 0
'filterdata for radiobutton
Try
For Each TextBox As TextBox In grp2.Controls.OfType(Of TextBox)()
For Each Label As Label In grp2.Controls.OfType(Of Label)()
Using connection As New SqlConnection("connectionString")
'user key in the SQL statement
sql = TextBox1.Text
Dim sql2 As String
sql2 = sql
Dim sp1 As String() = sql.Split(New String() {"where"}, StringSplitOptions.None)
sql = sp1(0) & " where " & Label.Text & " = @parameter or " & Label.Text & " =@parameter"
If (TextBox.Text <> "") Then
count += 1
For j As Integer = 0 To count - 1
Using cmd As New SqlCommand(sql, connection)
cmd.Parameters.AddWithValue("@parameter", TextBox.Text)
'cmd.Parameters.Add("@parameter", SqlDbType.NVarChar, 20).Value = TextBox.Text
connection.Open()
Dim dt As New DataTable()
Dim reader As SqlDataReader
reader = cmd.ExecuteReader()
dt.Load(reader)
DataGridView1.DataSource = dt
End Using
Next
Else
GetData()
End If
'cmd.Dispose()
connection.Close()
End Using
Next
Next
Catch ex As Exception
'MsgBox(ex.Message)
End Try
End Sub

Answer 1

首先，如果Labels和TextBoxes之间有1:1的对应关系，那么就不应该使用两个嵌套的For Each循环，因为这将使每个Label和每个TextBox配对。您应该做的是创建数组，然后使用单个For循环访问控件对：

Dim labels = grp2.Controls.OfType(Of Label)().ToArray()
Dim textBoxes = grp2.Controls.OfType(Of TextBox)().ToArray()
For i = 0 To labels.getUpperBound(0)
Dim label = labels(i)
Dim textBox = textBoxes(i)
'...
Next

至于构建SQL和添加参数，我倾向于这样做：

Dim labels = grp2.Controls.OfType(Of Label)().ToArray()
Dim textBoxes = grp2.Controls.OfType(Of TextBox)().ToArray()
Dim criteria As New List(Of String)
Dim command As New SqlCommand
For i = 0 To labels.getUpperBound(0)
Dim label = labels(i)
Dim textBox = textBoxes(i)
Dim parameterName = "@" & label.Text.Replace(" ", "_")
criteria.Add($"[{label.Text}] = {parameterName}")
command.Parameters.AddWithValue(parameterName, textBox.Text)
Next
Dim sql = "SELECT * FROM MyTable"
If criteria.Any() Then
sql &= " WHERE " & String.Join(" OR ", criteria)
End If
command.CommandText = sql

Answer 2

我认为应该开始分离UI和数据逻辑，这里是一个实现示例：

首先在数据库中有一个表：

CREATE TABLE Customer (
Id              INT IDENTITY (1, 1) PRIMARY KEY,
FirstName       VARCHAR (255) NOT NULL,
LastName        VARCHAR (255) NOT NULL,
Phone           VARCHAR (25),
Email           VARCHAR (255) NOT NULL,
Street          VARCHAR (255),
City            VARCHAR (50),
State           VARCHAR (25),
ZipCode         VARCHAR (5)
);

然后在VB.Net中创建底层实体：

Public Class Customer
Public Property Id As Integer
Public Property FirstName As String
Public Property LastName As String
Public Property Phone As String
Public Property Email As String
Public Property Street As String
Public Property City As String
Public Property State As String
Public Property ZipCode As String
End Class

数据加载器

现在您需要一个数据访问组件，它可以将记录加载到上面实体的列表中——这里是一个很好的实现：

Imports System.Data.SqlClient
Public Class CustomerDataAccess
Public Property ConStr As String
Public Sub New(ByVal constr As String)
constr = constr
End Sub
Public Function GetCustomersByCriterias(constraints As Object) As List(Of Customer)
Dim query As String = "SELECT Id, FirstName, LastName, Phone, Email, Street, City, State, ZipCode
FROM   [dbo].[Customer] "
Dim result = New List(Of Customer)()
Using con = New SqlConnection(ConStr)
Using cmd = con.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = query
'' here the magic to add dynamic criteria coming from constraints
cmd.ApplyConstraints(Of Customer)(constraints)
con.Open()
LoadCustomerData(cmd, result)
End Using
End Using
Return result
End Function
Private Sub LoadCustomerData(ByVal cmd As SqlCommand, ByVal result As List(Of Customer))
Using rdr = cmd.ExecuteReader()
Dim idIdx As Integer = rdr.GetOrdinal("Id")
Dim firstNameIdx As Integer = rdr.GetOrdinal("FirstName")
Dim lastNameIdx As Integer = rdr.GetOrdinal("LastName")
Dim phoneIdx As Integer = rdr.GetOrdinal("Phone")
Dim emailIdx As Integer = rdr.GetOrdinal("Email")
Dim streetIdx As Integer = rdr.GetOrdinal("Street")
Dim cityIdx As Integer = rdr.GetOrdinal("City")
Dim stateIdx As Integer = rdr.GetOrdinal("State")
Dim zipCodeIdx As Integer = rdr.GetOrdinal("ZipCode")
While rdr.Read()
Dim item = New Customer()
item.Id = rdr.GetValueOrDefault(Of Integer)(idIdx)
item.FirstName = rdr.GetValueOrDefault(Of String)(firstNameIdx)
item.LastName = rdr.GetValueOrDefault(Of String)(lastNameIdx)
item.Phone = rdr.GetValueOrDefault(Of String)(phoneIdx)
item.Email = rdr.GetValueOrDefault(Of String)(emailIdx)
item.Street = rdr.GetValueOrDefault(Of String)(streetIdx)
item.City = rdr.GetValueOrDefault(Of String)(cityIdx)
item.State = rdr.GetValueOrDefault(Of String)(stateIdx)
item.ZipCode = rdr.GetValueOrDefault(Of String)(zipCodeIdx)
result.Add(item)
End While
End Using
End Sub
End Class

扩展方法

以下是上面引用的扩展方法，它们可以发挥你正在寻找的魔力：

DataReader扩展，使用Dbnull exfeptial大小写和强制转换可以轻松地从SalDataReader读取值

Module DataReaderExtenions
<Extension()>
Function GetValueOrDefault(Of T)(row As IDataRecord, fieldName As String) As T
Dim ordinal = row.GetOrdinal(fieldName)
Return row.GetValueOrDefault(Of T)(ordinal)
End Function
<Extension()>
Function GetValueOrDefault(Of T)(row As IDataRecord, ordinal As Integer) As T
Return (If(row.IsDBNull(ordinal), Nothing, row.GetValue(ordinal)))
End Function
<Extension()>
Function GetValueOrDefaultSqlite(Of T)(row As IDataRecord, fieldName As String) As T
Dim ordinal = row.GetOrdinal(fieldName)
Return row.GetValueOrDefault(Of T)(ordinal)
End Function
<Extension()>
Function GetValueOrDefaultSqlite(Of T)(row As IDataRecord, ordinal As Integer) As T
Return (If(row.IsDBNull(ordinal), Nothing, Convert.ChangeType(row.GetValue(ordinal), GetType(T))))
End Function
End Module

允许您从匿名对象值中提取条件的命令扩展：

Imports System.Reflection
Imports System.Runtime.CompilerServices

Module CommandExtensions
<Extension()>
Function AddParameter(command As IDbCommand, name As String, value As Object) As IDataParameter
If command Is Nothing Then Throw New ArgumentNullException("command")
If name Is Nothing Then Throw New ArgumentNullException("name")
Dim p = command.CreateParameter()
p.ParameterName = name
p.Value = If(value, DBNull.Value)
command.Parameters.Add(p)
Return p
End Function
<Extension()>
Function ToDictionary(data As Object) As Dictionary(Of String, Object)
If TypeOf data Is String OrElse data.[GetType]().IsPrimitive Then Return New Dictionary(Of String, Object)()
Return (From [property] In data.[GetType]().GetProperties(BindingFlags.[Public] Or BindingFlags.Instance)
Where [property].CanRead
Select [property]).ToDictionary(Function([property]) [property].Name, Function([property]) [property].GetValue(data, Nothing))
End Function
<Extension()>
Sub ApplyConstraints(Of TEntity)(cmd As IDbCommand, constraints As Object)
If constraints Is Nothing Then Return
Dim dictionary = constraints.ToDictionary()
Dim whereClause = " WHERE "
For Each kvp In dictionary
Dim columnName = kvp.Key
Dim propertyName = kvp.Key
Dim prefix = "@"c
Dim value = kvp.Value
whereClause += $"{columnName} **like** {prefix}{propertyName} AND "
cmd.AddParameter(propertyName, value)
Next
If String.IsNullOrEmpty(whereClause) Then Return
cmd.CommandText += whereClause.Remove(whereClause.Length - 5, 5)
End Sub
End Module

示例：

在编码了所有这些东西之后，现在你可以做以下操作：

Dim DataGridView1 As DataGridView = New DataGridView()
Dim ConStr As String = ConfigurationManager.ConnectionStrings("MyApp").ConnectionString
Dim dal As CustomerDataAccess = New CustomerDataAccess(ConStr)
Dim criterias = New With {.FirstName = "%James%", .LastName = "%Nadin%"}
DataGridView1.DataSource = dal.GetCustomersByCriterias(criterias)

尽管有这些代码，你仍然需要将你的文本框(在正确命名后(绑定到一个SearchEntity，并使用这个实体来提供标准

我希望这些材料能帮助你解决你的问题，并促使你改进你的架构和；开发技能