Runtime: Are there plans to support GetSchema in System.Data.Odbc?

[jbb352]

I'm currently using the System.Data.Odbc 4.5.0-preview1-25914-04. Works great for connecting to various sources both on Windows and Linux. I do however have a requirement to get the schema list for the source and when trying to use GetSchema I get a NotImplemented error.
Are there plans to implement this feature?

[danmosemsft]

@jbb352 the code should have the same level of functionality as in regular .NET Framework (plus Unix support with unixODBC). I am not aware of plans to add to it, but that doesn't mean we can't.

@divega are we likely to add features to S.D.Odbc? If not, would you consider community contributions?

[danmosemsft]

cc @saurabh500

[divega]

System.Data triage: moving to the System.Data.SqlClient for the managed providers team to ultimately handle the issue.

[divega]

@jbb352 I assume you are asking about GetSchema() on OdbcConnection because I believe the one on OdbcDataReader is already implemented.

As far as I remember, the version of the method on the connection is a hard problem because there is very little standardization across ADO.NET providers on what the returned DataTable contains. I also think this was never implemented in the ODBC provider in .NET Framework.

So far we haven't implemented any new functionality in this code base (i.e. the original code form .NET Framework was ported with minimal changes to .NET Core), but I don't discard that we could do so in the future.

[saurabh500]

I looked into the OdbcConnection.GetSchema() and if it works on .Net Framework. @jbb352 Do the .Net Framework capabilities work for you?

``` C#
static void Main(string[] args)
{
String cStr = (new OdbcConnectionStringBuilder()
{
Dsn = "sqlodbc"
}).ToString();

        using (OdbcConnection oConnection = new OdbcConnection(cStr)) { 
            oConnection.Open();
            DataTable schema = oConnection.GetSchema();

            foreach (DataColumn col in schema.Columns)
            {
                Console.Write($"{col.ColumnName} ");
            }
            Console.WriteLine();

            foreach(DataRow row in schema.Rows)
            {
                foreach(DataColumn col in schema.Columns)
                {
                    Console.Write($"{row[col]} " );
                }
                Console.WriteLine();
            }

            DataTable viewSchema = oConnection.GetSchema("Tables");
            foreach (DataColumn col in viewSchema.Columns)
            {
                Console.Write($"{col.ColumnName} ");
            }
            Console.WriteLine();

            foreach (DataRow row in viewSchema.Rows)
            {
                foreach (DataColumn col in viewSchema.Columns)
                {
                    Console.Write($"{row[col]} ");
                }
                Console.WriteLine();
            }
        }
    }

The output 

CollectionName NumberOfRestrictions NumberOfIdentifierParts
MetaDataCollections 0 0
DataSourceInformation 0 0
DataTypes 0 0
Restrictions 0 0
ReservedWords 0 0
Columns 4 4
Indexes 4 4
Procedures 4 3
ProcedureColumns 4 4
ProcedureParameters 4 4
Tables 3 3
Views 3 3
TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
master dbo MSreplication_options TABLE
master dbo spt_fallback_db TABLE
master dbo spt_fallback_dev TABLE
master dbo spt_fallback_usg TABLE
master dbo spt_monitor TABLE
master dbo TableName TABLE
master dbo tpce_build_timer TABLE
master sys trace_xe_action_map TABLE
master sys trace_xe_event_map TABLE
Press any key to continue . . .

```

[saurabh500]

DevNote: Since the classes in internal namespace System.Data.ProviderBase are duplicated over System.Data.Odbc and System.Data.SqlClient, Odbc hasn't received some of the improvements done to SqlClient in terms of implementing APIs in .Net Core.
The lack of GetSchema() is a result of the this discrepancy.

To tackle this problem an issue related to DbConnectionPool code consolidation was opened at https://github.com/dotnet/corefx/issues/26607

The code which lights up the GetSchema() for Odbc to bring it at par with .Net framework already exists in Corefx repo and should be non-duplicated by moving it to the Common folder and making both the providers consume the same code.

[jbb352]

Hi @saurabh500 just to confirm, yes I have it working with .Net Framework 4.6 just not with dotnet core/standard

[divega]

I looked into the OdbcConnection.GetSchema() and if it works on .Net Framework...

Good to hear @saurabh500. I guess I was looking at the wrong code :blush:

[iamatinkerer]

Can I work on the fix for this issue?
Since the work was done for SqlClient in https://github.com/dotnet/corefx/pull/19937/files, I will have a good idea of what to do. I think a bunch of code for this particular issue can be moved to the Common/src/System/Data/ProviderBase folder and can be reused in Odbc and SqlClient.

However, I don't know where to get the System.Data.Odbc.OdbcMetadata.xml used in OdbcConnectionFactory.cs at https://github.com/Microsoft/referencesource/blob/90b323fe52bec428fe4bd5f007e9ead6b265d553/System.Data/System/Data/Odbc/OdbcConnectionFactory.cs#L108
If the XML is provided, then I could send a PR for this issue.

[iamatinkerer]

Unless of course someone is actively working on this.

[saurabh500]

@iamatinkerer Thanks for showing interest. We haven't started working on this issue actively. I will upload the System.Data.Odbc.OdbcMetadata.xml on Github and provide the link. You can take this issue.

[saurabh500]

@iamatinkerer Here you go https://github.com/saurabh500/corefx/blob/67468a0b81f46702471716965f01817ec2adb9a2/src/System.Data.Odbc/src/Resources/System.Data.Odbc.OdbcMetaData.xml

[karelz]

@iamatinkerer thanks for your help!
I sent you collaborator invite (that way we can assign the issue to you - GH limitation). Please let me know when you accept and I will assign it to you (temporarily assigning it to myself).
Note: Collaborators are automatically subscribed to all notifications per repo (500+ per day). We recommend to switch the repo to "Not Watching" - that will send you notifications only where you are mentioned, assigned or explicitly subscribed.

[iamatinkerer]

@karelz Thanks for the email subscription tip. I have accepted the collaborator invitation.