Most Efficient way to access schema with ODBC and OleDB

I am making a DAL template with C# and I am wondering what is the most efficient way to access schema information with ODBC and OleDB. I need the columns, column types, and primary key information. thanks

asked Jan 16, 2009 at 2:44 4,848 9 9 gold badges 39 39 silver badges 52 52 bronze badges

2 Answers 2

For OleDb, there is an OleDbConnection.GetoleDbSchemaTable() method. I've used it with Access.

I have an example in this code on GitHub: SchemaValidator.cs

There should be a .Schema() method on OdbcConnection too IIRC.

I have noticed that the actual returned values may vary by database, so you'll want to do a fair amount of checking and debugging to see what he returned values may be.

answered Jan 16, 2009 at 3:26 CodingWithSpike CodingWithSpike 43.5k 18 18 gold badges 104 104 silver badges 139 139 bronze badges

Use the SQL Server Management Objects:

Microsoft.SqlServer.Management.Smo.Server s = new Microsoft.SqlServer.Management.Smo.Server( @"DUFF\SQLEXPRESS" ); foreach ( Database db in s.Databases ) < if ( ! db.IsSystemObject ) < listboxDatabase.Items.Add( db.Name ); >>

from here, you can get the tables in the database (and other objects).