|
|
|
| Title | Make a lookup cache using ADO.NET in Visual Basic .NET |
|---|
| Description | This example shows how to make a lookup cache in Visual Basic .NET. |
|---|
| Keywords | ADO.NET, cache, lookup, lookup table, table |
|---|
| Categories | Database, VB.NET |
|---|
|
|
|
The LookupCache class does all the work. It's constructor takes as parameters an open database connection, the name of the lookup table, the name of the field to use in looking up values, and the name of the field that contains the value to return. It selects all of thue records from the table and saves their values in a collection, using the lookup names as keys.
The class's GetValue method simply looks up a key in the collection and returns the value.
|
|
Imports System.Data.OleDb
Public Class LookupCache
Private m_Values As New Collection
' Load the lookup values.
Public Sub New(ByVal conn As OleDbConnection, ByVal _
table_name As String, ByVal name_field As String, _
ByVal value_field As String)
' Open a DataReader to get the data.
Dim db_command As New OleDbCommand( _
"SELECT " & name_field & ", " & value_field & _
" FROM " & table_name, _
conn)
Dim data_reader As OleDbDataReader = _
db_command.ExecuteReader()
' Save the values.
Do While data_reader.Read()
m_Values.Add(data_reader.GetValue(1), _
data_reader.GetValue(0).ToString)
Loop
data_reader.Close()
db_command.Dispose()
End Sub
' Return a value.
Public Function GetValue(ByVal name_value As String) As _
String
Return m_Values(name_value).ToString
End Function
End Class
|
|
|
The main program uses the LookupCache class like this:
|
|
Private Sub Form1_Load(ByVal sender As System.Object, ByVal _
e As System.EventArgs) Handles MyBase.Load
' Compose the database file name.
' Modify this if the database is somewhere else.
Dim database_name As String = Application.StartupPath()
database_name = database_name.Substring(0, _
database_name.LastIndexOf("\"))
database_name = database_name & "\test.mdb"
' Compose the connect string.
Dim connect_string As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & database_name
' Open a database connection.
Dim conn As New OleDbConnection(connect_string)
conn.Open()
' Load the LookupCaches.
Dim name_to_abbrev As New LookupCache(conn, "States", _
"StateName", "Abbrev")
Dim abbrev_to_name As New LookupCache(conn, "States", _
"Abbrev", "StateName")
' Close the connection.
conn.Close()
conn.Dispose()
' Display some values.
Dim txt As String = ""
txt &= "CA <--> " & abbrev_to_name.GetValue("CA") & _
vbCrLf
txt &= "CO <--> " & abbrev_to_name.GetValue("CO") & _
vbCrLf
txt &= "NV <--> " & abbrev_to_name.GetValue("NV") & _
vbCrLf
txt &= "MA <--> " & abbrev_to_name.GetValue("MA") & _
vbCrLf
txt &= "UT <--> " & abbrev_to_name.GetValue("UT") & _
vbCrLf
txt &= vbCrLf
txt &= "California <--> " & _
name_to_abbrev.GetValue("California") & vbCrLf
txt &= "Colorado <--> " & _
name_to_abbrev.GetValue("Colorado") & vbCrLf
txt &= "Nevada <--> " & _
name_to_abbrev.GetValue("Nevada") & vbCrLf
txt &= "Massachusetts <--> " & _
name_to_abbrev.GetValue("Massachusetts") & vbCrLf
txt &= "Utah <--> " & name_to_abbrev.GetValue("Utah") & _
vbCrLf
txtResults.Text = txt
txtResults.Select(0, 0)
End Sub
|
|
|
You could easily modify the GetValue method to use default values in case a specific key is missing in the collection.
For more information on ADO.NET programming, see my book Visual Basic .NET Database Programming.
|
| |
|
| |
|
|
