Is it possible to determine the tables and its column in MS Access in a particular database using c#?
Can any one guide me in this issue?
Add a comment
|
2 Answers
Depending on the way you are connecting to the ms-access fiele, you can read tables and column/field names in different ways:
- With an OLEDB connecion: you can use the ADOX Objects collection and read tables, columns, indexes, etc available in the database
With an ODBC connection, you can list the MS-ACCESS system tables, open a recordset on each table, and then read all field names. Tables can be listed with the following SQL instruction:
SELECT name FROM MSysObjects WHERE type = 1
In all case, once a table is opened through a recordset, you can read its field names by looping on the fields collection of the recordset object
Comments
Apologies, SQL Server biased and posted that answer without actually reading your question properly!
Here is a solution for reading the Table names from an MS Access database. It is the same idea i.e. querying the Db schema.
Snippet
String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
OleDbConnection con = new OleDbConnection(connect);
con.Open();
Console.WriteLine("Made the connection to the database");
Console.WriteLine("Information for each table contains:");
DataTable tables = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});
foreach(DataRow tableRow in tables.Rows)
{
Console.WriteLine("Table Name: {0}", tableRow[0]);
DataTable cols = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[]{null,null, tableRow[0], null});
foreach (DataRow colRow in cols.Rows)
{
Console.WriteLine("Column Name: {0}", colRow[0]);
}
}
con.Close();
