API for retrieving Columns Schema from DbDataReader

[NickCraver]

This is to unwrap the specific issue of resultset schemas from the many involved in #14302.
Back on May 5th @davkean noted:

We want GetSchemaTable in some form (we should get a separate bug filed on that, however, as you could imagine bringing that back with a different design that doesn't pull in DataSet/DataTable).

While it doesn't seem that anyone cares what method exists for of getting the schema of a resultset, we seem to agree on needing a method of doing it in .Net Core. For example, if a user runs a simple query such as:

SELECT 'bob' Name, 1 Id

Then there's no way to see what the types are. While getting the .Net type (via .GetFieldType(), e.g. DateTime) is possible and getting part the SQL type (via .GetDataTypeName(), e.g. NVARCHAR) is possible, other vital attributes of the schema shape currently aren't possible. For example there's no way to distinguish a varchar(20) from a varchar(max). There's also no way to tell if the column was nullable. Previously, this functionality was provided by .GetSchemaTable() which returned a DataTable class.

Currently I have a very simple use case: query data here, create a table with that data elsewhere. The query can be something like: SELECT * FROM sys.database_files. I point this out to illustrate that INFORMATION_SCHEMA (as proposed by some in #14302 as an alternative) is not a viable option. The same issue presents on any stored procedure which can also return any result set shape depending on the code paths. We need a way to see the scheme of a result set, these database-query alternatives are both expensive and non-covering.

This is a hard blocker for projects of myself and others. I have uses in both one-off projects as well as Opserver (which monitors SQL) and Dapper.Net. I know from #14302 that I'm not alone on this block. The portability of .Net core is extremely appealing for certain projects, especially those where we need to monitor the innards of SQL without depending on a .Net install (many if not most existing SQL servers only have .Net 3.5). Unfortunately though, this one missing piece of the puzzle may prevent using it altogether, forcing full CLR installs.

While SqlClient specifically serves my blocking use case, it's important to point out as @FransBouma notes in the other issue: this needs to be in the base API, not specifically up in SqlClient. For example, I also need this for Dapper features and it works across many providers - it is not at all SQL Server specific.

So how about it, can we please get an API for result set schemas added back? I'm happy to contribute to designing and provide feedback on a new API here if that's the best route. I know many others are as well.

[FransBouma]

Additionally: DbConnection.GetSchema(). This too relies currently on DataTable and should get a proper alternative in .NET core going forward. There are no alternatives for some schemas retrievable through this method, for some providers. If there's no alternative in .NET core, there's no way to write generic code to obtain metadata about a schema aspect. E.g. on Firebird, some metadata is very hard to retrieve from the schema (read: very complex metadata queries) and the schemas provided by this method are very helpful. I think the solution chosen for DbDataReader.GetSchemaTable should also be applied to DbConnection.GetSchema as it too simply has to return a datatype with tabular data which is only known at runtime.

[william-gross]

This is a blocker for me as well, as I commented in #14302.

[NickCraver]

Responding to @YoungGah's note above:

Absolutely! We are tossing around ideas of the API returning schema in JSON format. Any opinions?

Given that JSON serialization is not included in core, wouldn't it be a bit odd to have libraries implementing DataReader taking dependencies to support it? I wouldn't need JSON serialization in several apps aside from this. I'd love to hear what others hear, but from the SQL side, this option isn't very appealing. That being said, I certainly see why it's being discussed for other providers.

With tabular data it can usually be expressed by positional or named arguments (e.g. a dictionary, list of an interface, etc.). I think that's a better approach for SQL, but I also recognize how that doesn't really work for a document type data store.

[roji]

Am the maintainer of Npgsql, this is definitely a very important discussion, and it seems important to have this for .NET Core RTM.

Here are some thoughts:

• It definitely seems that dropping DataTable/View/Set is a good approach as long as an alternative schema-fetching mechanism is provided.
• Re resultset metadata, as has been mentioned before a limited subset is available via GetFieldType, GetDataTypeName. Although this API approach could be extended (e.g. IsNullable), this doesn't seems like an optimal API for getting the results. It would also probably be different from whatever new API will replace DbConnection.GetSchema. If so, you may consider removing GetFieldType, GetDataTypeName from .NET Core as well as these will be a subset of whatever full new metadata API will be introduced.
• Re JSON, I agree with @NickCraver that it seems a bit out place in the ADO.NET API:
  • It would force a particular JSON API (Newtonsoft?) on all ADO.NET consumers (and producers).
  • It would be a weakly-typed API
  • It is somewhat cumbersome for provider writers, since relational databases typically don't communicate schema information in JSON.
• Why not introduce a simple .NET object-based model, e.g. DbColumn, DbTable? Providers could extend these as usual in ADO.NET for provider-specific metadata attributes (e.g. SqlDbColumn)...

[FransBouma]

JSON is a silly format in this context, it's an untyped text blob that needs deserializing using an external library. So -1 for JSON. The main issue is besides the external library needed that it's weakly typed: a piece of code calling GetDbSchema has to be sure the data it receives back is of the same type and the elements embedded inside it is stored in the same types. Always. With JSON that's up in the air, hence: bad choice.

[nvivo]

@YoungGah

We are tossing around ideas of the API returning schema in JSON format. Any opinions?

I'm not sure about the idea of json for this. What would be the advantage over a simple object that can be easily serialized if needed? This is supposed to be used in the context of .NET calls, there is hardly a need for this information to be that interoperable to require a json blob.

On https://github.com/dotnet/corefx/issues/1039#issuecomment-142271673 I proposed a very a simple interface that has the basic information that have been proven to be needed, and a dictionary for additional data.

interface IColumnSchema {
    int ColumnOrder;
    string ColumnName;
    string ProviderDataType;
    Type ClrMappedType;
    int MaxLength;
    bool CanBeNull;
    IReadOnlyDictionary<string, object> CustomProviderData;
}

interface IReaderSchema()
{
     IReadOnlyList<IColumnSchema> Columns { get; }
}

This is supposed to be used as:

var command = CreateDbCommand("select 1 from bob");
var reader = command.ExecuteReader();

var schema = reader.GetResultSchema(); // returns IReaderSchema

foreach (var c in schema.Columns) {
    ....
}

I'm not proposing this exact api, but something along the lines of this idea, with interfaces describing this data instead of the datatable.

@YoungGah and others, what do you think?

[YoungGah]

Thanks for all your feedbacks and JSON is out, then. We will get back to you with another proposal.

[bricelam]

In case it's useful, I did an EF feature spec on a new Schema Information API a few years ago. We never got around to implementing it, and it was definitely skewed toward EF6, but it may be interesting to look at in the context of this issue.

[YoungGah]

@bricelam Thanks for the spec! We will look into it.

[roji]

@bricelam, @YoungGah, this Schema Information definitely seems like a step in the right direction! Some immediate points that come to mind:

• The proposal handles schema table/view metadata, but not resultset metadata (i.e. from a DbDataReader). Of course it would be pretty trivial to extend the proposal for that.
• It's worth thinking about allowing providers to extend these classes to provide provider-specific information
• At least in PostgreSQL there are function features not covered (e.g. variadic)
• Unique constraint is missing

But I'm guessing this is just a starting point, I'll be glad to provide more feedback on an up-to-date proposal.

[roji]

Note the current effort in EF7 to define a relational model produced by a reverse engineering provider. Ideally this could be the same model returned by an ADO.NET provider.

[saurabh500]

cc @jcyang75