I think that you should begin to separate UI and data logic here is an example of implementation:
First you have a table in database:
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)
);
Then you create the underlying entity in 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
Data loader
Now you need a data access component that loads records to a list of this above entity here a nice implementation:
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
Extensions methods
Below are extensions methods referenced above that do the magic you are looking for:
DataReader extensions to make it easy to read values from SalDataReader with Dbnull exfeptional case and casting
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
Command extensions that lets you extract criteria from an anonymous object values:
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
Example:
After coded all these stuff now you can do the following:
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)
Despite all this code you are still need to bind your textbox (after naming them correctly) to a SearchEntity and use this entity to provide criterias
I hope this material can help you tackle your issue and incite you to improve your architecture & dev skills
