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?
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?
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();
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 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