One frequent problem in database application is displaying values from a lookup table, for example the description of a customer type instead of the code itself.
One easy way to do this is through a DataColumn Expression and a relation between two DataTables.
Module Module1
Sub Main()
Dim ds AsNew DataSet
Dim dtCustomers AsNew DataTable
Dim dtLookup AsNew DataTable
' Create a lookup table
dtLookup.Columns.Add("Type", GetType(Integer))
dtLookup.Columns.Add("Description", GetType(String))
dtLookup.Rows.Add(NewObject() {1, "Een"})
dtLookup.Rows.Add(NewObject() {2, "Twee"})
' Create a customers tab;e
dtCustomers.Columns.Add("Name", GetType(String))
dtCustomers.Columns.Add("Type", GetType(Integer))
dtCustomers.Rows.Add(NewObject() {"Customer 1 A", 1})
dtCustomers.Rows.Add(NewObject() {"Customer 1 B", 1})
dtCustomers.Rows.Add(NewObject() {"Customer 2 A", 2})
dtCustomers.Rows.Add(NewObject() {"Customer 2 B", 2})
dtCustomers.Rows.Add(NewObject() {"Customer 1 C", 1})
ds.Tables.Add(dtCustomers)
ds.Tables.Add(dtLookup)
' And create the lookup column
ds.Relations.Add("Rel1", dtLookup.Columns("Type"), dtCustomers.Columns("Type"))
dtCustomers.Columns.Add("Description", GetType(String), "Parent(Rel1).Description")
ForEach row As DataRow In dtCustomers.Rows
Console.WriteLine("Naam: {0}, Soort: {1} = {2}", row("Name"), row("Type"), row("Description"))
Next
Console.ReadKey()
EndSub
EndModule
The two lines that matter are:
ds.Relations.Add("Rel1", dtLookup.Columns("Type"), dtCustomers.Columns("Type"))
dtCustomers.Columns.Add("Description", GetType(String), "Parent(Rel1).Description")
The first relates the two tables and the second creates a calculated column using the expression.
Enjoy!