Table are empty using ODBC Provider

Oct 24, 2016 at 9:41 AM
I need to use an ODBC provider to connect to my MSSQLServer but fetched table structures are empty: no columns and no keys.

I have also tried to use test code using SqlServerNorthwind.ReadNorthwindUsingOdbc() TestMethod without success.

Adding the last assert, the test fails.
namespace DatabaseSchemaReaderTest.IntegrationTests
{
    [TestClass]
    public class SqlServerNorthwind
    {
        [TestMethod, TestCategory("SqlServer.Odbc")]
        public void ReadNorthwindUsingOdbc()
        {
            //you'll get much more information from System.Data.SqlClient
            const string providername = "System.Data.Odbc";
            const string connectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=Northwind;Trusted_Connection=Yes;";
            ProviderChecker.Check(providername, connectionString);

            var dbReader = new DatabaseReader(connectionString, providername) { Owner = "dbo" };
            //this is slow because it pulls in sp_ stored procedures and system views.
            dbReader.Exclusions.StoredProcedureFilter = new PrefixFilter("sp_", "fn_", "dm_", "xp_");
            var schema = dbReader.ReadAll();

            Assert.IsTrue(schema.Tables.Count > 0);
            Assert.IsTrue(schema.Tables.Any(x => x.Columns.Any()), "Fails here!");
        }
    }
}
Am I doing something wrong ?
Coordinator
Oct 24, 2016 at 1:45 PM
ODBC connections give limited metadata back, unfortunately. By it's nature ("open database connect") we can't "upgrade" to the database-specific code we need because we can't tell if it's oracle or mssql or something else. We could try to parse the connection string, but that won't work if it's a DSN, which is common if it's ODBC.

There is a bug in the DSR when reading columns (didn't read schema). I will push the a small fix, so you will pass the test with columns.

But it won't read the primary keys and foreign keys, which is pretty limiting. It's just not available from the ADO ODBC connection. I can't see a technical way to solve that, without using the database specific provider like System.Data.SqlClient. :(