Most Efficient way to access schema with ODBC and OleDB

Question

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

Answer 1

Use the SQL Server Management Objects:

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.aspx

for example:

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).

Answer 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.

Answer 3

Hi Rally25ra,

Can we use SMO objec to access schema sql via ODBC?

Thanks!