Retrieving table fields from access DB

[Chris]

Hi,

I'm using the following code

DataTable tables = ((OleDbConnection)oleconn).GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null,
"TABLE" });
foreach (DataRow r in tables.Rows)
{
string tableName = r["TABLE_NAME"] as string; //
Table-Name ....
//richTextBox1.Text += tableName + "\n";
TreeNode node = treeView1.Nodes.Add(tableName);
node.Nodes.Add("Level two node");
}

To get all the tables from a specific access database. If anyone
knows how to get the field/column names for these tables, I'd
appreciate it.

[RobinS]

"Chris" <co*****@gmail.comwrote in message
news:11*********************@e65g2000hsc.googlegro ups.com...

Hi,

I'm using the following code

DataTable tables = ((OleDbConnection)oleconn).GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null,
"TABLE" });
foreach (DataRow r in tables.Rows)
{
string tableName = r["TABLE_NAME"] as string; //
Table-Name ....
//richTextBox1.Text += tableName + "\n";
TreeNode node = treeView1.Nodes.Add(tableName);
node.Nodes.Add("Level two node");
}

To get all the tables from a specific access database. If anyone
knows how to get the field/column names for these tables, I'd
appreciate it.

I don't know if this will work against Access, but I use this against
SQLServer. Try changing all the SQL stuff to OleDB. Sorry; my example is
in VB. If you can't figure it out, post back and I will translate it for
you.

---------------------------
Here are all of the properties (and their types) you can retrieve for the
data columns using a DataReader.

col name = ColumnName, type = System.String
col name = ColumnOrdinal, type = System.Int32
col name = ColumnSize, type = System.Int32
col name = NumericPrecision, type = System.Int16
col name = NumericScale, type = System.Int16
col name = IsUnique, type = System.Boolean
col name = IsKey, type = System.Boolean
col name = BaseServerName, type = System.String
col name = BaseCatalogName, type = System.String
col name = BaseColumnName, type = System.String
col name = BaseSchemaName, type = System.String
col name = BaseTableName, type = System.String
col name = DataType, type = System.Type
col name = AllowDBNull, type = System.Boolean
col name = ProviderType, type = System.Int32
col name = IsAliased, type = System.Boolean
col name = IsExpression, type = System.Boolean
col name = IsIdentity, type = System.Boolean
col name = IsAutoIncrement, type = System.Boolean
col name = IsRowVersion, type = System.Boolean
col name = IsHidden, type = System.Boolean
col name = IsLong, type = System.Boolean
col name = IsReadOnly, type = System.Boolean
col name = ProviderSpecificDataType, type = System.Type
col name = DataTypeName, type = System.String
col name = XmlSchemaCollectionDatabase, type = System.String
col name = XmlSchemaCollectionOwningSchema, type = System.String
col name = XmlSchemaCollectionName, type = System.String
col name = UdtAssemblyQualifiedName, type = System.String
col name = NonVersionedProviderType, type = System.Int32

Here's how I got this list; tableName is passed in as a String. This shows
the columns you can get, and then shows selected values for each column
defined in the table.

**Note: This list will probably be different for Access.
Dim cn As New SqlConnection(My.Settings.DBConnString)
'put the table name in brackets in case it has spaces in it
Dim SQLString As String = "SELECT * FROM [" & tableName & "]"
Try
cn.Open()
Dim cmd As New SqlCommand(SQLString, cn)
Dim rdr As SqlDataReader =
cmd.ExecuteReader(CommandBehavior.KeyInfo)
Dim tbl As DataTable = rdr.GetSchemaTable
'This shows all of the information you can access about each
column.
For Each col As DataColumn In tbl.Columns
Debug.Print("col name = " & col.ColumnName & _
", type = " & col.DataType.ToString)
Next
For Each row As DataRow In tbl.Rows
'DataTypeName actually gives the same
' data type name as is displayed in SQLServer
Debug.Print("{0}, ColumnSize = {1}, DataType = {2},
DataTypeName = {3}, IsExpression = {4} ", _
row("ColumnName"), row("ColumnSize"), row("DataType"), _
row("DataTypeName"), row("IsExpression"))
Next
rdr.Close()
Catch
MessageBox.Show("Error opening the connection to the database.")
Finally
cn.Close()
End Try
Here's another way to get a list of tables.

Public Sub New(ByVal connectionString As String)
Dim cn As New SqlConnection(connectionString)
Try
cn.Open()
'The Restrictions are: table_catalog, table_schema,
' table_name, table_type.
'For my case, the table_catalog is the database name.
'table_Schema is the owner.
'Table_name is nothing because I want all tables.
'table_type is "BASE TABLE".
Dim restrictions() As String = New String() _
{My.Settings.DatabaseName, "dbo", Nothing, "BASE TABLE"}
Dim dt As DataTable = cn.GetSchema("Tables", restrictions)

'Uncomment this if you want to see the columns you can
' access from the GetSchema command.
'I'm leaving it in here for future reference.
'For Each col As DataColumn In dt.Columns
' Debug.Print(col.ColumnName.ToString)
'Next

For Each rw As DataRow In dt.Rows

'Uncomment this if you want to see the values
' for each of these columns.
'I'm leaving it in here for future reference.
'Debug.Print("Table_Catalog = {0}, Table_Schema = {1},
Table_Name = {2}, Table_Type = {3}", _
' rw.Item("TABLE_CATALOG"), rw.Item("TABLE_SCHEMA"), _
' rw.Item("TABLE_NAME"), rw.Item("TABLE_TYPE"))

'sysdiagrams shows up if you have a database diagram;
exclude it here
If rw.Item("TABLE_NAME").ToString.ToUpper <"SYSDIAGRAMS"
Then
Me.Add(rw.Item("TABLE_NAME").ToString)
End If
Next

'sort the list of tables
Me.Sort()

Catch
MessageBox.Show("Error opening connection to database.")
Finally
cn.Close()
End Try
End Sub

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------