Thanks Cor and Larry for any help you can give me. Here is the code I
am using:
_____________________________________
Here are the declarations:
Private TableList As New ArrayList
Private TablePrimaryKeys As New Hashtable
Private TableFields As New Hashtable
Private Structure FieldInfo
Dim FieldName As String
Dim OrdinalPosition As Integer
Dim DataType As Type
Dim Size As Integer
End Structure
____________________________________
Here is how I fill them:
Dim dt As DataTable
Dim dRow As DataRow
Dim dColumn As DataColumn
Dim aNull As DBNull
Dim aField As New FieldInfo
Dim fieldList As New ArrayList
Dim strPKList As String
Dim strTableName As String
Dim i As Integer
ConfigOpt.Initialize(Application.StartupPath & "\" &
Application.ProductName & ".cfg")
MarymonteDALConnectString = ConfigOpt.GetOption("Connect String")
MarymonteDALDataProvider = ConfigOpt.GetOption("DB Type")
TableList.Clear()
TablePrimaryKeys.Clear()
TableFields.Clear()
Try
If MarymonteDALDataProvider = "OleDb" Then
Dim cn As New OleDbConnection(MarymonteDALConnectString)
Dim cmd As New OleDbCommand
Dim myReader As OleDbDataReader
' test the connection string and read table info
cn.Open()
' get list of tables
Dim myNull() As Object = {aNull, aNull, aNull, "TABLE"}
dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, myNull)
Dim dr As DataRow
For Each dr In dt.Rows
TableList.Add(dr("TABLE_NAME"))
Next
' read table data
cmd.Connection = cn
For i = 0 To TableList.Count - 1
strTableName = TableList(i)
cmd.CommandText = "SELECT * FROM " & strTableName
myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
dt = myReader.GetSchemaTable()
strPKList = ""
fieldList.Clear()
For Each dRow In dt.Rows
aField.FieldName = dRow("ColumnName")
aField.OrdinalPosition = dRow("ColumnOrdinal")
aField.DataType = dRow("DataType")
aField.Size = dRow("ColumnSize")
fieldList.Add(aField)
If dRow("IsKey") Then
strPKList += dRow("ColumnName") & ";"
End If
Next
TableFields.Add(strTableName, fieldList)
TablePrimaryKeys.Add(strTableName, strPKList)
myReader.Close()
Next
________________________________________________
And here is my test code to access them:
Private Sub DisplayTableInfoTest()
Dim i As Integer
Dim tstField As New FieldInfo
Dim tstFieldList As New ArrayList
Dim strTableName As String
Dim key As New Object
For Each key In TableFields
tstFieldList.Clear()
tstFieldList = CType(TableFields.Item(key), ArrayList)
Dim n As Integer
For n = 0 To tstFieldList.Count - 1
tstField = CType(tstFieldList(n), FieldInfo)
MessageBox.Show(tstField.FieldName)
Next
Next
For i = 0 To TableList.Count - 1
strTableName = TableList(i)
tstFieldList.Clear()
tstFieldList = TableFields.Item(strTableName)
MessageBox.Show("Primary keys for " & strTableName & ": " &
TablePrimaryKeys(strTableName), strTableName)
Dim strTest As String
Dim m As Integer
For m = 0 To tstFieldList.Count - 1
tstField = CType(tstFieldList(m), FieldInfo)
strTest = ""
strTest += tstField.FieldName & vbCrLf & _
tstField.OrdinalPosition.ToString & vbCrLf & _
tstField.DataType.ToString & vbCrLf & _
tstField.Size.ToString
MessageBox.Show(strTest, TableList(i))
Next
Next
End Sub
__________________________________________________ __
There are only two tables in the database (OpDetails and Operators).
OpDetails has 3 fields and Operators has 6. I see it fill the
ArrayList (fieldList) with the correct number of fields for each
table. And it saves that field list to the HashTable (TableFields).
Also to note, the othe HashTable (TablePrimaryKeys) is filled
correctly and displays correctly.
In my test code this is what I see:
1. Displays the correct table names.
2. Displays the correct table primary keys (from the other HashTable I
filled).
3. Shows the 6 fields from Operators when accessing OpDetails (this is
first time through the display loop)
4. Shows no fields for Operators (second time through the loop)
5. I never see the 3 fields from OpDetails.
Seems like the fields for the second table processed (Operators) are
saved for the first table (OpDetails) and no fields saved for the
first table processed (OpDetails).
Hope this is not too confusing and somone can see my mistake.
TIA,
John
On 1 Apr 2005 01:48:31 -0800, "Larry Lard" <la*******@hotmail.com>
wrote:
J L wrote:
[...] I then add this arraylist to a HashTable whose key is the name of the
Access table and value is the arraylist of FieldInfo objects. The
hashtable is named TableFields
I am having trouble retrieving the information. Here is the code I am
using :
dim testField as FieldInfo
dim testFieldList as ArrayList
dim strTableName as String
for i = 0 to TableList.Count - 1
strTableName = TableList(i)
testFieldList.Clear()
testFieldList = TableFields.Item(strTableName)
[...] The problem is that this shows the field information for the second
table as belonging to the first and has no field information for the
second one.
Am I using the correct syntax to access an ArrayList of FieldInfo
data stored in a HashTable?
This looks fine; what we need to see also is the code where you load up
TableFields in the first place.
Is there a better way to go about it? The bottom line is that I want
to have a list of field name, ordinal position, data type and size
for each field in each data table so I can do some validation in my DAL
in a generic way once I know a table name.
A HashTable is a perfectly good way to store (key, value) information.