Why is DataTable/View/Set absent? #1039

Closed
FransBouma opened this Issue Feb 27, 2015 · 153 comments
@FransBouma

I looked here: https://github.com/dotnet/corefx-progress and saw DataTable/View/Set isn't ported to .NET Core. IMHO this is a mistake, as DataTable and friends are a convenient way to handle untyped data at runtime, and is e.g. often used when fetching data through stored procedures as it's easy to e.g. fetch a dataset with the result of a proc which returns 3 cursors on e.g. Oracle.

I fail to see why this cornerstone of many people's data-access code is absent in a framework that targets servers.

@terrajobst terrajobst was assigned by ellismg Feb 27, 2015
@ellismg
Member
ellismg commented Feb 27, 2015

@terrajobst When you have some free time perhaps you could give an overview about how we came up with what we think the surface area for .NET Core v1.0 is?

@FransBouma

Whatever may be the reason, having no real story for how to fetch tabular data using stored procs, which is today mostly done using DbDataAdapters storing data in DataSet/Tables, is likely going to stop a lot of people migrating their code to .NET Core for the simple reason that a lot of people have to work with stored procs and the tabular data they return every day. While it might be that a datareader opened on a proc call's resultset and materializing the rows into objects is 'easy', fact is that on databases which return cursors, this isn't that easy, while DbDataAdapters solve that problem today with a few lines of code.

I do understand DataTable/View/Set and DbDataAdapter come with a fair set of interfaces you likely don't want to port now (not sure if they are, haven't checked), e.g. IBindingList, IListSource and the like, but IMHO it's a cornerstone for Data access on .NET, despite what some hip people think.

@tthiery
tthiery commented Mar 2, 2015

I want to extend the question: I understand that .Net Core is not the full .Net Framework and some features are cut out. Nevertheless, many of us rely on some features and want to migrate to .Net Core. But there are two groups: The impossible ones (e.g. WebForms / WPF) and the possible and even reasonable packages like e.g. DataTable/View, XmlSchema, SerialPort, System.Activities, etc. Some may have technical constraints, not be hip, or legally restrained but to know the status and strategy of them would be a great help for understanding this new .Net.

For these cut-out features will there be

  • separate NuGet packages outside (or in a later version) of the .Net Core?
  • and if yes, will the be created/maintained by Microsoft or should we expect that we need to go to the reference source and do it on our own? (not that this is wrong ... it is just something many employers will not pay for).
  • is there an overview of these gaps? I was surprised by XmlSchema (imagine the thousands of existing services), SerialPort (imagine all the inexpensive USB chips which create a virtual COM port) and now by DataTable. The analyzer only helps me with existing code but does not help me in first place when I have to decide on paper which framework to use.

I have not heard of a general strategy for these gaps, maybe I lost it somewhere.

@RickStrahl

I ran into a use case today where I needed DataTable in order to return dynamic data to a service client. JSON.NET can easily serialize data tables for example. There are no easy solutions for materializing DYNAMIC data in .NET and DataTables make it easy when you don't know what the heck you're going to get back and you need to iterate over it. It's very useful for a number of things...

I can see why DataSet/DataTable is falling out of favor. Personally I rarely use them, but for dynamic data scenarios they are often the best choice available in .NET. So I hope we get this at some point or else an alternative that makes it easier to get dynamic data that can actually be serialized.

@FransBouma

There's another problem: DbConnection.GetSchema(). It's a way to obtain meta-data from the connected database and all ADO.NET providers have support for it, often with detailed schemas about metadata which can be used by data-access code to e.g. generate code on the fly for types or parameters. A lot of work has been put into this method by ADO.NET providers to make this method work on the database the provider is for: often they use detailed metadata queries so the user of the method doesn't have to.

GetSchema() returns a DataTable, as it's unknown what the types are of the rows returned as that depends on the schema requested and the ado.net provider used.

This method can't be used in .NET core if DataTable isn't there.

@terrajobst
Member

Why is DataTable/View/Set absent?

We generally consider DataSet and friends (DataTable, DataView, TableAdapter) legacy. However, that doesn't necessarily mean that those APIs will never be available on .NET Core as we generally do support legacy APIs if they important for porting scenarios (for example, non-generic collections are also available for .NET Core).

So why are these types missing then? Simply because we're focusing on non-legacy components first and so far DataTable hasn't popped up yet.

@tthiery
tthiery commented Mar 6, 2015

@terrajobst Answered also my question. A list of "legacy" .Net Framework technologies would be awesome (maybe including a state like "never", "maybe", "later"). I need to understand, in which technologies I can trust.

@FransBouma

@terrajobst and what about the scenarios I brought up? You can state something is 'legacy' in your eyes but that doesn't necessarily mean use cases a lot of devs are faced with every day are simply 'gone' or have valuable alternatives (as there are no alternatives at this point).

Sure, someone can port the code from referencesource and that might look OK, but it's not, as those types aren't the same types exposed and used in the ADO.NET providers.

I don't have the feeling Microsoft really understands the seriousness of this problem.

@kazuk
kazuk commented Mar 6, 2015

I wish an alternative to SqlDbReader GetScema before makes DataTable and DataSet etc to be legacy and discontinued

My point DataTable is needed for metadata access and some of development work in now
can not be obsolete and needs maintain works fine

@robertmclaws

@FransBouma It is a serious problem for you, because you maintain database products that depend on DataTable functionality. I bet the number of people like you vs the rest of .NET developers is like a fraction of 1%. And a small one, at that.

When rewriting a framework, some stuff gets left behind, especially anything in the edge cases. All of the cases you made are edge cases. You may spend most of your life in them, and many others might because they use your products. But they are not Microsoft's focus moving forward. DataTables are a 15 year old technology, and there has to be a better way to do some of the things you described.

There are reasons EF7 is only usable for simple next-gen ASP.NET apps, and some of the scenarios you mentioned are among those reasons.

For anyone building things that are beyond simple apps, ASP.NET v6 on .NET 4.6 is going to be the way to go. DotNet Core will not be usable for many people in v1, and that is OK. Not every single feature can come along for the ride, otherwise if everything is legacy than nothing is.

Frans, You can't tell me you couldn't come up with a better way to write DbConnection.GetSchema() that could work in your own apps for .NET Core if you really thought about it. Write it as an extension method on DbConnection and make it work with older versions of the framework by using your own objects. Can't be that hard.

@FransBouma

@advancedrei

It is a serious problem for you, because you maintain database products that depend on DataTable functionality.

My tools don't depend on datatable in their core, they use datatables for meta-data retrieval in some situations (as metadata is only exposed through datatables in these situations)

I bet the number of people like you vs the rest of .NET developers is like a fraction of 1%. And a small one, at that.

Please don't make up numbers and pass them on as real. Almost every application calling stored procedures uses them, and of those applications there are many.

DataTables are a 15 year old technology, and there has to be a better way to do some of the things you described.

There's always a better way, sadly there's not one available at the moment.

There are reasons EF7 is only usable for simple next-gen ASP.NET apps, and some of the scenarios you mentioned are among those reasons.

I think you're mistaken. Plus it's besides the point.

You can't tell me you couldn't come up with a better way to write DbConnection.GetSchema() that could work in your own apps for .NET Core if you really thought about it. Write it as an extension method on DbConnection and make it work with older versions of the framework by using your own objects. Can't be that hard.

Sometimes GetSchema() is necessary, but it's just an example. You also have GetSchemaTable on a datareader, which is used in a lot more cases (as not everyone reads metadata from a schema), as it tells you what the layout of the resultset is. Essential information for creating datastructures to store resultsets of unknown layout. And yes, that's not in less than 1% of the applications out there. Try most enterprise applications which have to talk to databases with many stored procedures for example. Good luck with writing solid server side code without datatable.

Sure, like I said there's always a way, but the thing is: people will want to port code they already have to .NET core and e.g. run it on Linux. Now they can't do that.

@nvivo
nvivo commented Mar 7, 2015

@terrajobst,

Out of curiosity, is there any public information you are using to decide what is important to port or not? Mono used to use Moma to scan and publish a database of "what people really need" to help drive the implementation.

Is there any metric like that being used to help decisions, or is it based more on the direction the team want things to go?

@tthiery
tthiery commented Mar 7, 2015

@FransBouma +1 for the answer. The 1% is totally unrealistic considering that EF showed up in 2011 (2/3 of .Net lifetime are before) and the consideration that DataTable was the dominant technology for data retrieval in all earlier examples. I would more go for 20% to 30% of all enterprise platforms will have dependencies to DataTable (numbers are random as well).
Nevertheless, @advancedrei is not completely wrong. He is very right that it is a wrong assumption that the .Net Core will be anytime close a reasonable migration or extension path for existing code. Primary focus are and will be for a while ASP.Net (where the EF is quite dominant in new designs) and .Net Native (which do not rely on local databases but data centers using services) as far as I understand.

@nvivo +1. I totally agree. It is a bit ... in-transparent currently (especially for a Open Source project which expects contributions). @terrajobst Like stated above ... having a forecast what might (DataTable, XmlSchema, etc) and what definitely will be never ever be in (remember the endless .. WPF/WinForms/WebForms discussions) would really be helpful.

@robertmclaws

Entity Framework came out of Object Spaces which puts it in the 2008-2009 timeframe. And the 1% I referred to was forward-looking development. How many of those 30% Enterprise projects aren't even on .NET 4.0 yet? I'd venture to say a lot.

NETCORE is not concerned with people who will never migrate anyways. It is concerned with building the next generation of apps. Not maintaining the last one. The Frameworks that have already shipped can handle that just fine.

@FransBouma

@advancedrei .NET core on Linux could spark new interest in .NET from dev teams who now won't look at .NET as it's windows only but do have lots of databases with stored procs. There are a tremendous amount of database-driven applications out there which have to use stored procs (I use stored procs as an example, as it's one of the use cases for a datatable as the resultsets are often of unknown layout: as you can't determine the layout .... without a datatable ;)) and they can't pick .NET if there's not a solid scenario available for their situation.

There's little known about the ADO.NET story for .NET core, no code in that area is opened yet (to my knowledge) and how the provider model will look like is completely unknown. For server-side software that's an important point, and it now looks like v1.0 of .NET core is completely useless which IMHO wastes a tremendous opportunity to win people back to .NET.

But alas... I'm starting to repeat myself. :)

@nvivo
nvivo commented Mar 7, 2015

@tthiery, @terrajobst

Is there any metric like that being used to help decisions?

I'm sorry. I know it sounded like I was questioning the decision, but I was not. =) I was genuinely curious how the process of selecting the API was/is done.

I have been using pure ADO.NET for years without EF, and I have not used DataTable for anything new in ages. I think this can be provided later by a package, but don't need to be in the core right now. We need to remember .NET 4.6 will still be released and all this stuff will be there.

@RickStrahl

I'm with others here who point out that we should be looking forward not backward, but... I think we need to be careful here that we don't kill functionality that may be crucial to building applications.

The two things that have jumped out at me are lack of a good story for creating new providers so that third parties have an easy time to create new providers. This (or some other mechanism like standardized DI) is a MUST and currently not supported/documented. Right now you're stuck with SQL Server and third parties can build their own providers but have no effective way to swap providers at runtime (maybe there's something there that we can't see but this was what the provider model was for previously).

Personally I don't see DataTable/DataSet as needed tech, but the features it provides - an easy way to retrieve list based dynamic query DB data is a solid use case for building solutions on top of raw ADO.NET and for third parties that build on top of it. I think something is needed to provide this functionality out of box. It could be as simple as a dictionary, but it should be there natively IMHO because it is a common scenario if you use ADO.NET raw.

So to me the story isn't about backwards compatibility but making sure the use cases that are common are addressed. If that means new interfaces that's fine, as long as some fundamental scenarios that third party tools and frameworks can build on are addressed in some way.

Honestly I think this is all of utmost importance. After all most applications use data. And the story for data in vNext as presented by Microsoft so far looks like a bungling mess. I'm not sure if it actually is or not, but the message has been muddled and confused. From the outside it looks like EF with SQL Server is the only viable solution that's in box and that really sucks if that's how it ends up. Especially in light of .NET Core running on other platforms where SQL Server usage is not very likely. If this is just a messaging thing - then that needs to be corrected with some official commentary. If it's a feature thing then I think there should be discussion on what needs to be there.

@nvivo
nvivo commented Mar 7, 2015

I agree with @RickStrahl that EF + SqlServer is bad, and agree with @FransBouma that there is no way to do some things like just selecting untyped data without dealing with DataReader. But I don't see anything else filling this gap DataTables left.

What is really to be missed? All the select + order by is done much better by linq directly on objects. For other things, there are alternatives to EF. SimpleData is a famous one, Dapper from StackExchange is also very handy, and even I maintain a pretty useful one =).

I bet new alternatives will appear even more with .NET Core, and eventually a winner will come up. But we need to give it some time.

@FransBouma

I agree with @RickStrahl here. It's the concept / featureset provided by Datatable and friends that's essential, and if I may add, essential for v1.0 to be there. Datatable is an obvious choice to provide that featureset as a truckload of code is already written out there supporting it, but it's not essential to have it as the type to provide that featureset, however why write something else to replace something you already have?

@nvivo
nvivo commented Mar 7, 2015

@FransBouma,

Which part exactly do you miss from DataTable? It seems the only thing that you miss is a type that represents "an array of object arrays" for untyped data, but that is provided out of the box.

Is that it or am I missing something?

@nvivo
nvivo commented Mar 7, 2015

As a note, I saw the comments about the GetSchema. The point is that those things can be provided as separate modules, they are not exactly required by anything other than supporting the IDE around DataTable/Set/View stuff. And even this could be today exposed as real objects instead of datatables.

If all is needed is a way to retrieve untyped tabular data, we could strip away 90% of DataTable and have a single new type that have no change detection, no select capabilities, etc. It should only hold an array of "rows", so "an array of object arrays".

@robertmclaws

I agree with @nvivo, a DataTable is just an array of object arrays. Not sure why the functionality can't be replicated with some custom code and without the "sky is falling" mentality.

@FransBouma

@nvivo

Which part exactly do you miss from DataTable? It seems the only thing that you miss is a type that represents "an array of object arrays" for untyped data, but that is provided out of the box. Is that it or am I missing something?

DataTable isn't a construct with arrays of object arrays, it's a construct with typed columns (data isn't stored internally in object arrays, but vertically in typed columns), which has the ability to define custom views on top of it you can sort/filter without touching the original datatable, which can be filled with a DbDataAdapter which can create the typed columns for you during fetch based on metadata obtained from the resultset, which is also stored in a datatable.

Like I said, if something is replacing it with equal functionality, fine, but that's not in the cards. This means that ado.net providers can't provide the same functionality they do today. GetSchema is one thing, but as I said, DbDataReader.GetSchemaTable is another, and that one IS used a lot by user code (directly or indirectly through data-access code or dbdataadapters).

@advancedrei please stay on point with this discussion. I understand you don't give a shit about datatable and friends and that's fine, but just because it's irrelevant to you doesn't mean it's irrelevant to many thousands of developers out there.

@nvivo
nvivo commented Mar 8, 2015

@FransBouma,

DataTable isn't a construct with arrays of object arrays

I'm obviously not saying how DataTable is implemented, I'm saying this is the only part that is missing.

if something is replacing it with equal functionality, fine, but that's not in the cards

Don't get me wrong here. I have a lot of code that depends on GetSchemaTable and DataTables in general out there. I have data migration tools that automatically compare and move data between different providers using this functionality.

But that doesn't matter because we are not discussing removing this functionality from .NET. We are discussing if this functionality should be added to a new framework that has a different target.

You say that you want "equal" functionality, but most of what DataTable/View/Set provides can be achieved with POCO and LINQ in a much cleaner way. You can create views, group by, count, select different rows and columns, etc. And in most cases, that will be achieved with LINQ using less memory.

I believe the only way DataTable should be added to CoreFx would be with a simpler design that integrates better with LINQ and removes things like .Select, OrderBy, Views, etc. It should be just a container for tabular data, nothing more.

As someone that works a lot with DbProviderFactory, I liked how the design went. Providers are clean and do only what they need to allow database access using SQL. Everything else can be build on top of it later, and that's how it should be.

Version 1.0 is not even out yet, and @advancedrei said already, this version won't have a lot of stuff for a lot of people. But it's better to start clean and add pieces as it goes than start bloated.

@FransBouma

@nvivo

You say that you want "equal" functionality, but most of what DataTable/View/Set provides can be achieved with POCO and LINQ in a much cleaner way. You can create views, group by, count, select different rows and columns, etc. And in most cases, that will be achieved with LINQ using less memory.

You can't create POCOs if you don't have meta-data of the fields of the resultset. You can't determine the schema of a resultset of a stored procedure unless GetSchemaTable() is implemented. Again, if that's implemented but it gives me a typed object, no problem. However if it returns an array of object arrays, what am I going to do with that? What element is what and of what type?

I'm not talking about the POCO/typed element route which is created from metadata obtained in some way, I'm talking about calling a stored procedure which returns a resultset and you now have to do something with that resultset.

Anyway I have given enough examples, @RickStrahl has given additional examples and background of why this feature is very important. I simply don't get why one would want to be limited by excluding functionality which is already implemented and has worked for over a decade.

@nvivo
nvivo commented Mar 8, 2015

@FransBouma,

Everything you said is perfectly achievable if you change your mind model.

You can't determine the schema of a resultset of a stored procedure unless GetSchemaTable() is implemented.

Why is that? Any database nowadays implements INFORMATION_SCHEMA to provide this kind of information in a standard way. If you need something very specific, you can just run any query against the database.

I'm not talking about the POCO/typed element route which is created from metadata obtained in some way

So, you are saying that if you ignore all the other ways to do it, there is no way to do it?

DataTable is a huge implementation that is intended to do a lot of things that are not that common anymore. I have been building commercial apps for ages with plain ADO.NET and didn't use DataTables for a long time, I see them as legacy as well. Most frameworks and controls today focus on objects with observables and other ways to track changes.

I do believe it has some features that are still valuable and have no replacement, but we need find what those features are and try to come up with an alternative for those only. Trying to bring this huge implementation full of duplicated stuff that is easily achievable with LINQ is not a good option.

@FransBouma

Everything you said is perfectly achievable if you change your mind model.

It's not for me, it's for all those devs out there who can't proceed to .NET core because someone thought 'Datatable' is legacy while not considering the consequences of that decision.

You can't determine the schema of a resultset of a stored procedure unless GetSchemaTable() is implemented.

Why is that? Any database nowadays implements INFORMATION_SCHEMA to provide this kind of information in a standard way. If you need something very specific, you can just run any query against the database.

  1. Not every database implements all schemas of information_schema
  2. Not every database is 100% bugfree in those schemas (e.g. sql server)
  3. For a resultset from a stored procedure, e.g. the direct resultset like with sql server or the resultset of a cursor like with Oracle or PostgreSql, the schema isn't in one of those schemas, but is determined on the fly, as the resultset is dynamic. If you don't know what you're talking about, please don't derail this discussion with nonsense as it's important for people who have to work with DataTable and friends.
  4. I've written meta-data retrieval drivers for a lot of databases and for a lot of different meta-data elements. Trust me on this that INFORMATION_SCHEMA is not that helpful, if helpful at all.

The DbConnection.GetSchema() call is an example, not the use case. There are many more examples to give where DataTables are used today and are very useful. I have given a couple, others have given other examples.

DataTable is a huge implementation that is intended to do a lot of things that are not that common anymore. I have been building commercial apps for ages with plain ADO.NET and didn't use DataTables for a long time, I see them as legacy as well. Most frameworks and controls today focus on objects with observables and other ways to track changes.

Common in your world != common in everyone's world. I write ORMs for a living, I know DataTables are not the only way to do data-access. I also know that everything that even looks like a stored procedure is automatically useless if datatables are not in the framework anymore.

I do believe it has some features that are still valuable and have no replacement, but we need find what those features are and try to come up with an alternative for those only.

Why come up with alternatives for code that has already been proven to work for over a decade and which is used by a lot of people, which is supported today in ADO.NET providers (so when these are ported to .NET core, this code will work automatically).

Trying to bring this huge implementation full of duplicated stuff that is easily achievable with LINQ is not a good option.

You're not focusing on the real problem. I'm not talking about creating a projection of a resultset to a typed object, I'm talking about being able to store untyped resultsets in a datastructure which makes it possible to use that untyped resultset without knowing what the layout is (as e.g. that might differ based in input, some stored procedures are written that way).

Anyway, I've done my best. If MS wants to shoot themselves in the foot by limiting the Data-access story of their .NET core framework to 'whatever EF7 can do', so be it.

@HaloFour
HaloFour commented Mar 8, 2015

After reading through this thread I tried to find out what of ADO.NET is being brought over to CoreFX (or at least would be supported on top of it) and I couldn't really find anything. Are connections, commands and readers also getting the axe (or at least deferred until after the first release)? Doesn't EF7 depend on these things to function?

A server core framework for web applications that doesn't support DB access doesn't sound very useful to me.

I can't honestly say that I've used DataTables and their kin recently but I do use data readers frequently when I need both firehose performance and/or need to avoid the inherent limitations of ORMs. I also have a metric ton of extension methods around DbConnection, DbCommand and DbDataReader to vastly simplify their use which I'd love to propose for potential inclusion/discussion.

Update: My apologies, apparently I can't read. I do see the progress page which includes the types in System.Data.Common.

@nvivo
nvivo commented Mar 8, 2015

@HaloFour, check for System.Data.Common under https://github.com/dotnet/corefx-progress/tree/master/src-diff/README.md.

Specifically, check the definition of DbProviderFactory that defines what a Data Provider is.

Basically it was reduced to connection, command, transaction and datareader.

It seems all that is needed to me.

@nvivo
nvivo commented Mar 9, 2015

@FransBouma,

It's not for me, it's for all those devs out there who can't proceed to .NET core

Who are you? Superman? Let's be honest here, any issue we open is because some personal need. But please bring some of those oppressed developers to the discussion, so they can participate, maybe having more points of view on this would help.

Not every database implements all schemas of information_schema

Most schemas provided by .NET are just queries to these tables. I have also worked a lot with those, and there are huge differences between different providers to the point that unless you just want a list of tables, you have to write different code to get what you want anyways. Why should .NET add another level of indirection? If the database can't provide this information in a sane way, what guarantees you have that the driver will?

You may argue this is useful in some cases, but is not required to have a data provider, and it's not required to use procedures in any way, even though you insist it's impossible to execute a store procedure without it, which I honestly don't get it as I have used them without that since forever.

You're not focusing on the real problem. I'm not talking about creating a projection of a resultset to a typed object, I'm talking about being able to store untyped resultsets in a datastructure which makes it possible to use that untyped resultset without knowing what the layout

Back to my point, we need to understand exactly which part of the DataTable stuff is missing and address this specifically. The things you are talking about don't need to include change tracking, select, orderby, table relationships, views, etc. This should be addressable with a simple container for tabular data, and it shouldn't be that hard to build that and even include in .NET later.

@FransBouma

@nvivo

It's not for me, it's for all those devs out there who can't proceed to .NET core

Who are you? Superman? Let's be honest here, any issue we open is because some personal need. But please bring some of those oppressed developers to the discussion, so they can participate, maybe having more points of view on this would help.

What does this add to answering my question, which wasn't directed to you at all btw. Is it necessary here to explain if I know what I'm talking about? It seems so. As if I don't know the field I'm specialized in and spend full time in.

Not every database implements all schemas of information_schema

Most schemas provided by .NET are just queries to these tables. I have also worked a lot with those, and there are huge differences between different providers to the point that unless you just want a list of tables, you have to write different code to get what you want anyways. Why should .NET add another level of indirection? If the database can't provide this information in a sane way, what guarantees you have that the driver will?

connection.GetSchema() gives also schemas about the type mappings provided by the ado.net provider, and an easy to use interface to otherwise complicated queries. But it's an example of where datatable's are used. There are many more.

You may argue this is useful in some cases, but is not required to have a data provider, and it's not required to use procedures in any way, even though you insist it's impossible to execute a store procedure without it, which I honestly don't get it as I have used them without that since forever.

sigh. of course you can execute a stored procedure, consuming the resultset returned by it in any reasonable way isn't possible, as the only interface you'll get in .NET core is DbDataReader.GetValues(). Good luck with that. Before you say 'but I know the schema', you don't: you can't determine the resultset from the proc. You might be able to determine the field names but no types. If a field in the object array returned from GetValues() is DBNull.Value, you can't determine the type of it. No metadata schema in the DB will help you there.

But of course, you did that many times over without problems.

Back to my point, we need to understand exactly which part of the DataTable stuff is missing and address this specifically.

'we'? Who are 'we' here? I don't see how you should understand anything. I asked a question to Microsoft and I got the dreaded 'it's legacy' excuse I've heard too many times before already. Other people have also asked questions in this thread without any answer at all.

I've described which parts are missing and others have too. That you don't get that, that's fine but irrelevant: you weren't the person I addressed in my initial question.

The things you are talking about don't need to include change tracking, select, orderby, table relationships, views, etc. This should be addressable with a simple container for tabular data, and it shouldn't be that hard to build that and even include in .NET later.

Yes, let's rewrite something from scratch to avoid porting perfectly fine code over we already have laying around. That's time well spent! :/ Especially because your 'simple container for tabular data' will very likely look like a datatable, which is already highly optimized. But again, whatever is added is fine as long as it addresses what's missing, and as there are millions of lines of code out there already working with datatable which can't be ported to .NET core without it (but of course you won't see the need of that at all, as you have done things differently many times over) I don't see why on earth something else has to be written from scratch while there's already a class which does this.

If this is the future of Microsoft Open Source, where asking a simple important question results in a serious lack of answers from MS coupled to defending the question to people who come in and think they have to show their lack of knowledge, it'll be a fun place to be, for sure.

@nvivo
nvivo commented Mar 9, 2015

'we'? Who are 'we' here? I don't see how you should understand anything. I asked a question to Microsoft

This is an open source channel to discuss features of an open source project with interested people. If you wanna talk to Microsoft only, you should call their support.

@FransBouma

To add: it's already frustratingly difficult enough to convince MS to include something or to change something. It's even harder if people who have no stake in the matter come in and try to defend what MS has apparently decided. If this gets included, nothing will change for you. You won't lose any time, code, hair whatever. Nothing. However people who do rely on this feature are helped. Yes, I asked this question on their behalf because I spotted the absence of DataTable, and most people likely won't look at .NET core till it's v1.0 Adding it then is much harder than when v1.0 hasn't been released.

I must say this whole debate has been very frustrating for me. I asked a serious question and got 1) a standard excuse from MS without any reasoning behind it and 2) have to defend why I even dared to ask the question to people without any stake in the matter.

From the lack of response from the .NET team I conclude that unless IBM or other big corp calls MS and asks them wtf they were thinking by not including datatable, we won't see it in v1. This is a shame, but at the end of the day, it's MS' responsibility to provide value for everyone.

This is an open source channel to discuss features of an open source project with interested people. If you wanna talk to Microsoft only, you should call their support.

Yes, but you're defending their decision while you have no stake in the matter: if you are or aren't convinced datatable should be added is irrelevant. All your posts do is make this whole debate a matter of whether you are convinced or not, but it should be whether Microsoft is convinced or not. This derails the discussion as it centers around what you think, which is irrelevant: you don't decide what's included in .NET core, MS does.

So again, unless you work for MS and have a stake in the matter, please stay out of the debate; that you aren't convinced it's useful because you don't use it is not an argument. I don't write websites, it doesn't make ASPNET not needed.

@nvivo
nvivo commented Mar 9, 2015

Oh, for crying out loud! Stop complaining already. You didn't provide a single point on why any of these things can't be done in any other way. All I see is that you are losing your favorite tool and you don't want to learn anything new.

Again, this is not a microsoft support channel. This is an open source project and anyone can discuss here anything related to the future of the platform. Get over it.

@FransBouma

Oh, for crying out loud! Stop complaining already. You didn't provide a single point on why any of these things can't be done in any other way. All I see is that you are losing your favorite tool and you don't want to learn anything new.

Sorry? My framework doesn't even rely on datatable. (only in the pipeline which projects to datatable, which is an additional feature). It's an O/R mapper, remember?

And I don't want to learn anything new? Where is that the case? I'm a software dev for over 21 years now. If I don't want to learn anything new I'd still be writing C on a Sun workstation. But thanks again for posting an offtopic post.

Again, this is not a microsoft support channel. This is an open source project and anyone can discuss here anything related to the future of the platform. Get over it.

I don't think you grasp the concept of why I asked this question. Here's a hint: it's not for my own code, I don't write LoB apps, it's not for you, it's not for the other 'I don't use it so it's garbage' intellectuals here, it's for the people who don't read this thread and don't know DataTable isn't supported and will be faced with a problem when v1.0 is released. That is of course irrelevant to you as you couldn't care less about these people. Duly noted.

@Tragetaschen

I'm a bit confused: .NET Core is about factoring the .NET Framework into individual, small and focused Nuget packages. So if anything, there will be such a separate package for DataTable and friends. A v1 Release of .NET core means the underlying bits (corlib, JIT, etc.) are done and some Nuget packages are released as well. These packages are currently driven by the ASP.NET 5 efforts.

I don't get why it would be a problem when (as well as many other parts of the framework) DataTable wouldn't be available yet when v1 is pushed out. This is something that can be easily added on a completely independent time base.

@nvivo
nvivo commented Mar 9, 2015

@Tragetaschen,

The issue is that removing DataTable causes some other parts of the framework to be removed, like GetSchemaTable and a type that can hold untyped data offline with no need to deal with datareader.

Those things can be added later as you said, and may impact some people but there are alternatives today.

As @FransBouma repeatedly said, he doesn't use these features neither it affects his projects. But he is worried that some other people will have their lives devastated by not having this specific support in v 1.0 for some reason.

I think it would be much more useful to discuss things that really affect someone so we can discuss alternatives and even create new proposals to add those things back in a saner way, instead of trying to bring the entire DataTable bundle to corefx.

@Tragetaschen

Still, this thread now focuses way too much on a "removal" while it's actually about "not yet ported over". This is what @terrajobst made perfectly clear in his response.

Of course, a package for DataTable will either have a dependency on a package including GetSchemaTable, or (if it's only required in that one scenario) will include and provide this and other types.

@FransBouma

A package can't be supplied later without also altering e.g. DbDataReader's interface, as it is currently exposed there. That is, if the package wants to supply the functionality that's there today. (Another affected element is DbProviderFactory, which currently supplies DbDataAdapter, a class which is also not ported over but which is often used in combination with DataTable/Set, e.g. when fetching resultsets from procs with e.g. cursors.)

If it's just a standalone class, sure, it can be added later. However adding this later will break the interfaces of several classes available at v1, meaning there's a versioning problem on the interface if this is added later on, which is avoidable if the type is included in v1 (and which also avoids a lot of people having to wait for the addition of the type to move to .net core).

@nvivo
nvivo commented Mar 9, 2015

@FransBouma,

Why this needs to break the DbDataReader interface just to get the schema? You insist on that, but I don't see why this is the case.

Your assumption is that the only way to do this is by returning a datatable from reader.GetSchemaTable, but there are other ways to do that.

I'd support a way to do that with extension methods based on some interface implementation that is optional and returns some new type other than DataTable. Most types in .NET are following this convention, and it works.

@FransBouma

Extension methods don't work here as the method has to be implemented by the ADO.NET provider being used, which means it has to be implemented by the datareader, hence the design of the code as it is today. An extension method can be used of course, but code using that extension method then is tied to the ado.net provider, throwing dbproviderfactory out of the window.

So yes, it will break the interface.

But for the gazillionth time, it was an example, not the use case. E.g. it totally doesn't address Rick's concerns at all or how to fetch a dynamic resultset from a query so you can at least do something with the data other than 'oh, field X has value NULL, no idea what type'.

@nvivo
nvivo commented Mar 9, 2015

Extension methods don't work here as the method has to be implemented by the ADO.NET provider being used, which means it has to be implemented by the datareader.

Why? I don't see any reason for that to be true. There are so many ways to implement it that doesn't require a single interface to be exposed.

But for the gazillionth time, it was an example, not the use case

Yes, you didn't provide a single use case yet. I get that. It would be more productive if you did.

@FransBouma

Why? I don't see any reason for that to be true. There are so many ways to implement it that doesn't require a single interface to be exposed.

What will implement the extension method? The only way it can report on the layout of the resultset if it can look into the raw data of the resultset inside the datareader. Anything outside the datareader and the ado.net provider thus can't do that.

Yes, you didn't provide a single use case yet. I get that. It would be more productive if you did.

wat... πŸ‘Ž

(edit) Anyway, have fun. I'm out of here. MS apparently doesn't give a shit, and I have no time for debating this issue with people who just want to troll along.

@nvivo
nvivo commented Mar 9, 2015

You don't need to expose that with the DbDataReader interface. That's my point.

This an additional feature, it can be exposed with an additional interface.

@ilmax
ilmax commented Mar 9, 2015

Disclaimer : I don't build ORM, but I do use them on a daily basis.

I think that DataTable & friends are an obsolete technology, but the lack of alternatives to dynamically extract data from a DbDataReader feels to me like ADO.NET is losing power.

To be clear, I understand @FransBouma request, but I also understand @nvivo motivation. As @FransBouma pointed out, (at lest IMHO) is not the absence of DataTable the point, but the absence of any valid alternatives, and given that not in every data retrieval scenario the data shape is known in advance, sometimes POCO are simply not enough/convenient.

As adding functionality via extension methods, this could be achieved of course, but since every DbDataReader implementation should have it's own say GetSchema extension method, you should cast the DbDataReader to it's underlying type to call the correct method, and this makes working with DbProviderFactory and different ADO.NET implementation at the same time a little bit more difficult.

Note either that @terrajobst response

So why are these types missing then? Simply because we're focusing on non-legacy components first and so far DataTable hasn't popped up yet.
does not states that DataTable & friends will never be part of netfx.

@william-gross

@FransBouma, thank you for your posts here. I agree with you.

I maintain an open-source framework (https://www.nuget.org/packages/Ewl/) that uses GetSchemaTable (for SQL Server, MySQL, and Oracle) to determine the shape of query results so it can generate code. There are about twenty web applications built on top of this framework, and none of them will be able to move to .NET core if it does not include GetSchemaTable or something else with the same functionality.

@nvivo
nvivo commented Apr 28, 2015

There is no doubt some people will miss this feature. But for every developer that will miss GetSchemaTable, there are 50 missing WinForms or WPF or something else. I'm missing some features that are not ported as well, and I'll need to find alternatives. But if .NET Core is driven by that, the entire framework must be brought together.

I believe the team didn't remove a feature just for the sake of removing it. There is a reason behind this, and we should either ask them if there is anything planned to provide this support or come up with an alternative design that don't require all the legacy stuff implemented in a new framework.

@FransBouma

There is no doubt some people will miss this feature. But for every developer that will miss
GetSchemaTable, there are 50 missing WinForms or WPF or something else. I'm missing some features that
are not ported as well, and I'll need to find alternatives. But if .NET Core is driven by that, the
entire framework must be brought together.

Frankly I don't care. I have functionality to support and if there's no alternative because MS doesn't want to port it, I have a serious problem with porting my system to .NET Core. I understand things are pretty small at the start of .NET core, and things will be increasing over time. However with this particular issue it doesn't look like it will be making it to .NET core _at all_, ever. And that's the problem I have with it. 

I believe the team didn't remove a feature just for the sake of removing it. There is a reason behind
this, and we should either ask them if there is anything planned to provide this support or come up
with an alternative design that don't require all the legacy stuff implemented in a new framework.

I think I did that ;) (ask what is going to happen). So far no reason has been brought forward other than the usual 'not our problem' bullshit. Microsoft should take a note from Oracle which goes beyond everything to keep Java backwards compatible so stuff keeps working. 

It's of course that by supporting datatable a lot of other stuff has to be brought over as well. That's a lot of work, but hey, they started this mess. 

I don't mind if it's not in .NET core v1.0 as it's very limited anyway. I do mind if because Microsoft thinks it's legacy they can ignore features completely and shrug when the users of these features suddenly have a big problem. 

If I have to port the stuff myself, so be it. The problem however is that GetSchemaTable is in the ADO.NET interfaces so if I port the .NET reference source over, I still lack the essential method in the interface. So MS holds a key where no-one can get access to and in the future no-one will be able to add this feature themselves to the code as well. 

So in short: this whole mess sucks big time. The way it is handled, the way the silence bugs on and the way there's no clear vision how this is going to be solved in the future (if ever). 

Yeah, cheerio! .NET core! let's all be happy.... :/

    FB
@nvivo
nvivo commented Apr 28, 2015

@terrajobst I agree on the silence issue.

This is not about porting DataTable/DataView, it's about how do we access metadata about queries in this new api?.

It would be nice to hear anything about your plans on this.

@shanselman

Ping

@tthiery
tthiery commented May 4, 2015

@shanselman we are still here and curiously waiting ;)

@davkean
Member
davkean commented May 5, 2015

Sorry for the late reply, I thought I already replied to this issue.

First of all I should clear things up, just because something hasn't been ported yet, does not mean it won't be ported. These things take time, design[1] and resources, and we're right now focusing on getting .NET Core working for two different app-models: Universal Windows Apps and modern MVC/WebAPI web app across Mac, Linux and Windows. Right now we don't even have a networking stack across these three OSs and we haven't yet open sourced the data stack.. :) After that we'll start looking at other scenarios that we're missing with the new stack.

To summarize some conversations we've already had internally about this:

  • We want DataSet/DataTable, et al, in some form going forward (but separate to the base policy-free abstractions)
  • 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).

[1] For example, as you've noticed GetSchemaTable pulls in DataTable, which itself pulls in DataSet, a large policy-heavy type that we do want to bring back in some form but does not belong in the same assembly as our policy-free data abstractions.

@FransBouma

Thanks for the reply!

Sorry for the late reply, I thought I already replied to this issue.

First of all I should clear things up, just because something hasn't been ported yet, does not mean it
won't be ported. These things take time, design[1] and resources, and we're right now focusing on
getting .NET Core working for two different app-models: Universal Windows Apps and modern MVC/WebAPI
web app across Mac, Linux and Windows. Right now we don't even have a networking stack across these
three OSs and we haven't yet open sourced the data stack.. :) After that we'll start looking at other
scenarios that we're missing with the new stack.

:)

To summarize some conversations we've already had internally about this:

  • We want DataSet/DataTable, et al, in some form going forward (but separate to the base policy-
    free abstractions)
Is this something the community could help with? As in, if someone surgically cuts out the DataTable and friends from reference source into a repo, would that be something MS would be interested in if the community would come up with, say, an assembly which contains those classes? Or is it better not to spend time on that at the moment because you guys/girls don't have a plan set in stone yet with what to do with these classes?
  • 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.
I think if there's a GetSchemaTable which returns something we can use at runtime, whatever the form, (as well as GetSchema()), it is fine by all of us. DataTable for fetching is then a separate issue, as that can be added later on, the GetSchema/GetSchemaTable methods on the other hand are important to have from the get-go as they're in the interface for connection/reader, so adding them later is a serious breaking change and a dependency hell, so if that's avoidable, all the better. 

[1] For example, as you've noticed GetSchemaTable pulls in DataTable, which itself pulls in DataSet, a
large policy-heavy type that we do want to bring back in some form but does not belong in the same
assembly as our policy-free data abstractions.

Yeah, I can imagine you'd want to avoid pulling in that behemoth if you can avoid it. :)

Cheers,

    Frans
@william-gross

Thank you @davkean for the information. I agree with you and @FransBouma that GetSchemaTable's functionality could be brought back in a different form that doesn't rely on DataSet/DataTable.

@dmitry-a-morozov

To me the most important advantage of DataTable is ability to push data back to a database.
It comes in 2 flavors:

  • Updates via SqlDataAdater with optimistic concurrency, batching and merging support.
  • Bulk copy via SqlBulkCopy which also relies heavily on DataTable. This is very useful for analytical workloads (big data story). In a company I work for we use it heavily in data cleansing processes.

Being exclusively F# developer I don't use DataTable very often but sometime it's really handy. It will tedious to roll out similar functionality on my own. Also, I must say DataTable is fairly straightforward type in it's core as oppose to obscure objects models used by EF .

@Dean-NC
Dean-NC commented May 9, 2015

I use ASP.Net MVC 5, but still prefer ADO.Net and Stored-procs. over Entity Framework (I use a personal library that abstracts ADO.Net so ADO.Net code is not scattered in my apps).

With that said, I use DataTable when calling procs with Table-valued parameters. I just looked, and there's a very limited amount of ways to use TVP's, with DataTable being the only way for me. DBDataReader can also be used, but the data I'm sending to the proc is never something I've queried...it's data I have in a IList<> or an array.

Without DataTable, is there any reasonable way to use TVP's ?? I can understand not putting DataSets in the new Core, but DataTable is a lighter-weight object and has many uses...there are some good examples posted here. PLEASE put DataTable in Core.

@Dean-NC
Dean-NC commented Jun 4, 2015

ping

@terrajobst
Member

@saurabh500 might have some thoughts here.

@Dean-NC
Dean-NC commented Jun 22, 2015

@terrajobst
I'm fairly new to github, is there a way I can contact @saurabh500 ?

@YoungGah
Member

DataSet/DataTable: We are evaluating to see if this is something we want to bring in a future release or whether it will be in the same form or not. Please let us know if you have opinions on whether it will be acceptable to see the feature in different form. It will be also good to get examples of what scenarios people are using DataSet/DataTable for from community.

GetSchema: We are going add the support in the future although its return value might be in DataTable form.

@Dean-NC
Dean-NC commented Jun 24, 2015

@YoungGah,
Thanks for the reply, but you can get plenty of examples of why it's needed by looking at the comments in this thread. The one I posted about was passing a table-valued parameter to stored-procs. I wouldn't care if you changed the "form", just as long as it still worked for sending table-valued-parameters to stored procs.

Please strongly consider getting this added. Table-valued parameters are very beneficial in several scenarios.

I just did some testing, and it's possible to use a List (of SqlDataRecord) instead of DataTable for using the table-parameters, but it's not as clean since each SqlDataRecord takes a little more to setup, and with each record you have to define the MetaData (column info), whereas the DataTable is defined once and you just add rows to it. Of course I could abstract that into a helper method.

@FransBouma

DataSet/DataTable: We are evaluating to see if this is something we want to bring in a future release
or whether it will be in the same form or not. Please let us know if you have opinions on whether it
will be acceptable to see the feature in different form. It will be also good to get examples of what
scenarios people are using DataSet/DataTable for from community.

Just look at this thread, there are plenty of examples given: dynamic, untyped resultsets and e.g. getschema results. 

GetSchema: We are going add the support in the future although its return value might be in DataTable
form.

'the future', as in post RTM? how are people going to obtain resultset information at RTM then? You do know that it is a bit hard (read: impossible) to determine a column's type without getschema on the datareader, when e.g. the value is NULL. 

Without getschema on the datareader at release your data-access API will be unusable unless the resultset is known, e.g. the query is generated from meta-data obtained through other means. This means that any stored proc call issued as-is which fetches data is dead in the water. I truly don't understand why you'd cripple your API like that. 

    FB 
@GiorgioG

+1 what FransBouma said.

@anpete
Contributor
anpete commented Jun 24, 2015

@FransBouma Can you use GetFieldType, GetName etc?

@FransBouma

@FransBouma https://github.com/FransBouma Can you use GetFieldType, GetName etc?

For the .NET type alone it might work, but GetSchemaTable returns other information as well like (max)length and e.g. whether the field is a PK value. This is info which is usable for the object the data is used in e.g. when it is persisted again. the problem with the lack of this kind of information at runtime is that you need to obtain it through other means, and people using stored procedures will then need to use special tooling. I of course won't mind that, as I build these tools for a living, but not everyone wants/can use my tooling or tooling from my competitors. 

Another problem is when one of the columns is a BLOB or equivalent and it has to be read by a stream reader. You can't determine that from the interfaces provided by the dbdatareader, it's available in the schema table though. 

This all might sound like edge cases but for people using stored procedures these are the building blocks they work with and therefore aren't edge cases at all. Again, it's not required to obtain this information through the datatable type, if there's an equivalent, just offer it. But you'll see that e.g. IDataReader.GetSchemaTable offers different schemas depending on the underlying ado.net provider. So it's not that easy: the datatable offered the dynamic nature needed for this method. 

I understand it's a bit of a problem as choosing to keep the existing api requires the datatable/view/set family jump on board which is a lot of work and choosing to use an alternative means it has to be designed well and flexible enough for all ado.net providers and that's not simple either and costs time and that's likely not there. However simply saying 'we'll add it in the future' has no real value: not having this API in whatever form that works is equivalent to making your new and shiny API stumble and not really an alternative to the people who left .NET for other platforms and who you all so eagerly want to come back. 

    FB
@NickCraver

Have there been any new thoughts or progress on this? It took me a while to find this issue, but I've also hit a blocker here on getting column sizes from a DataReader here due to DataTable and as a result GetSchemaTable() missing. I can get nvarchar from the DataReader via .GetDataTypeName(), but (as far as I can tell), there's no way to get the column width. Since I want to take the data and create tables elsewhere with it, this is particularly problematic. The best I can do is guess at column widths based on ym widest data from the reader, and that has down-the-road issues of future inserts handling resizes, etc. It's not a good position to be in.

This is a problem specific to core. Is there another way to get this data I'm unaware of? If not, then +1 for adding these types back in, they are needed.

@NickCraver

@terrajobst Can you please comment if any way to get a result set schema is coming to .Net Core? If it's not then I need to abandon .Net core for features and entire projects on my end. Not knowing what is or isn't on the roadmap is extremely frustrating from the user end of things. There are a large number of use cases here and there's been no Microsoft response on these pretty fundamental features needed in many apps for over 3 months. If they're not something you guys have any interest in adding back for whatever reason, that's fine. But it'd be very helpful to let the community know so we can avoid .Net Core. Earlier in this issue these features were called "legacy," but they are not. They are the only way to do things. They have not been superseded because no other method to get this information currently exists.

To be clear: I don't care what method Microsoft wants to provide to get this schema information, but there needs to be a method.

A simple concrete use case: I want to select data via a query and create a table with that data elsewhere, I currently can't. There's no way to tell how wide any field is or if it's nullable.

@Petermarcu
Member

@YoungGah to help provide more clarity on the roadmap for this component.

@GiorgioG

It's short-sighted on Microsoft's part to brush aside the serious concerns of some pretty smart people in the .Net community. If Microsoft is serious about cross-platform .Net, then it can't leave out vital functionality. I've been considering leaving .Net altogether for some time and Microsoft's mishandling of issues like this only serve to alienate people like me further. Sad, given that I've been using it since .Net 1.0 beta became available.

@benaadams
Collaborator

@NickCraver there is a SqlMetaData class, not sure where/how it fits in though.

@FransBouma

@benaadams Anything in the SqlClient namespace is not usable to solve this issue. It has to be a Data.Common class so it is usable by all ADO.NET providers that will be running on .net core. That's what makes this issue so important: DbDataReader, the base class, has to have an API for this (as it does now in full .net, but it's using DataTable at the moment, hence the issue! :)) so ADO.NET providers can fill in the blanks in their provider to provide the actual data at runtime.

The situation currently is that one can execute ExecuteReader on a DbDataReader but there's no way to know what the resultset looks like (column specifics), as DbDataReader misses an essential part of the puzzle. Why there's no-one within Microsoft concerned about this is beyond me. It looks like there's internal chaos/turmoil around .NET core as there's no real leadership expressed in their actions. Making things OSS is nice, but if there's no vision implemented it's just marketing.

@schotime

I use List<Dictionary<string, object>> for all dynamic queries.
Done!

@FransBouma

@Petermarcu There's no need for a roadmap, it has to be in the API at day RTM as all ADO.NET providers have to provide the same API to produce this essential piece of information. It's useless to have some SqlClient class which can perhaps do it, as users working on e.g. PostgreSql with Npgsql can't use that.

Lessons from ADO.NET in .NET 1.0/1.1 clearly dictate if you don't put it in the API on day one you'll regret it later on, a clear example of that is the way errors were reported in ADO.NET in .net 1.x or how the lack of DbProviderFactory was making using multiple databases in a .NET application a nightmare.

Also, has anyone within Microsoft ever thought of the reason why there is DbProviderFactory in .NET core, but not a universal way to obtain metadata like in the full .net api? I.o.w.: Why have a DB agnostic way to create and use ADO.NET provider classes, yet to do anything with them you have to fall back to ADO.NET provider specific API classes, making the whole DB agnostic factory system totally useless?

So is there anyone designing this API, or in charge of a vision of it? Or are you people simply looking at the sky and thinking "Today is a good day to port that random class over to .NET core"?

@schotime don't pollute this discussion with random bollocks.

@schotime

@FransBouma Good point. You've done a pretty good job of that yourself ;)

@FransBouma

@FransBouma Good point. You've done a pretty good job of that yourself ;)

Of course. https://github.com/schotime/NPoco/blob/8334a03ff06d2bbe8dcd4caf0c342cd4730202c7/src/NPoco/SqlBulkCopyHelper.cs#L28

Have fun porting that to .NET core, Adam. Or closer to home (which I now maintain Massive), I'd like to port Massive to .NET core some day but that's difficult too, as a lack of schema info retrieval (the core issue) is a bit of a hurdle. If there's no dynamic resultset retrieval done then everything is fine, but the world isn't that. I'm happy for you that you can get away with it, others don't.

@schotime

So it won't be in the first version of .NET Core. That's why its the first version. Neither is SignalR and other staple libraries.

The .NET core version of NPoco will be pretty easy. It just won't have that method in it. You can only support what the platform libraries support when they support it.

@FransBouma

@schotime So you're not affected by it, that's great. Others unfortunately are. Your post suggested those people are talking out of their ass. I can assure you, we're not. It's not that we want that method, but a method which allows developers to obtain meta-data of a resultset. If it's not in v1.0, every ADO.NET provider out there will provide their own solution which is meaningless as it's not db agnostic, and which forces developers to make a choice: either not use the method (which will be hard in some cases, not in your world, but in other people's worlds) or take a dependency on the ADO.NET provider assembly (which is a pain, as we all (not you, but the people who were around in the .NET 1.x era writing ORMs) have learned the hard way).

Having DB agnostic code in non-DB agnostic libraries is a pain, and this is a core example of it. DataTable is the vehicle the meta-data is currently provided in .NET full. If DataTable is too big to port, I can understand that (as I've said months ago already) but provide an alternative for the missing API methods that aren't ported because of DataTable not being ported.

That you don't understand what the fuss is all about and are fine with it how it is now: great. Not everyone lives in your world, some people have code to write which is affected by this. And before you think you're clever: yes we have looked at alternatives, but there aren't any, because, how odd, the only way to obtain the information today is through the API that's not ported!

(to elaborate: even if I use my meta-data retrieval queries in my drivers, I can't deal with the information, as I don't know the resultset layout per column of the resultset that's returned by the database! So I have a datareader, and I have to guess that column "DataType" in my resultset is an int and not nullable. Perhaps on this version of Oracle it's an Int64, because ODP.NET thought it would be funny to return a NUMBER(10,0) as Int64, and on another version of Oracle it's an int32. Oops... I guess your micro ORM simply assumes what it gets back from the DB fits in the shapes it has to project to, which is fine, but some people like to have more robustness so they can deal with a variety of situations which might not match exactly or require extra code to check whether they're dealing with the right type of data.)

@nvivo
nvivo commented Sep 22, 2015

So you're not affected by it, that's great. Others unfortunately are. Your post suggested those people are talking out of their ass.

@FransBouma This is definitely not what I got from his post. To me @schotime was simply being reasonable. You said you have a product that depends on these features and you make a living out of it, so you have a lot at stake, we get it. But this can't be the only side the team sees to make a decision.

That you don't understand what the fuss is all about and are fine with it how it is now: great.
Not everyone lives in your world, some people have code to write which is affected by this.

How you're not the one being a jerk here? You're the one who thinks people with a different view are here just talking out of their ass.

The reason why this has not been added yet seems pretty clear to me:

  1. Schema discovery is a hard problem, the current abstraction was not goog enough to be universal (as you said)
  2. It depends on DataTables which are considered legacy and most people don't use them anymore
  3. Schema discovery is useful, but is not required. 99% of the cases you can do just fine without it, and that's what people have been doing.
  4. Most new projects (which is the focus of CoreFx) are moving to nosql/schemaless solutions
  5. Most old projects never had the need for schema discovery

I am not talking out of my ass here. As someone who uses ADO.NET a lot too, I'd really like the provider model to be simplified.

Schema discovery can be done by looking at the INFORMATION_SCHEMA and if that's not standard enough, it won't get better by creating another abstraction and moving the problem to the provider level. It didn't work universally before, it won't work now.

I say let's keep the provider dead simple as it is, and see if it really impacts people. DataTables/DataViews can be added as a separate packages. Don't make DbDataReader depend on it.

I say let the team focus on more important stuff and let the community figure out better ways to do it. If there is a winner model, I'm sure it can be included later.

@ryanbnl
ryanbnl commented Sep 22, 2015

@FransBouma don't you use INFORMATION_SCHEMA? It's cross platform, cross database and cross language. It's THE API for database metadata.

I use it for all of my database-related code generation needs, from generating types to automated migrations.

Can't you replace your existing metadata subsystem with an implementation based on INFORMATION_SCHEMA? Or do you have architectural issues blocking you from making the changes?

@NickCraver

@nvivo I think there are several problems and bad assumptions with the view here. The mere fact that someone was labeled Legacy which has not been superseded immediately indicates to me that the .Net team isn't aware of many use cases altogether, not that they have been consciously recognized and not prioritized.

As far as specific points:

Schema discovery is a hard problem, the current abstraction was not good enough to be universal (as you said)

Saying that the only method wasn't good isn't justification for removing the only method available.

It depends on DataTables which are considered legacy and most people don't use them anymore
Schema discovery is useful, but is not required. 99% of the cases you can do just fine without it, and that's what people have been doing.

The current method does, yes. No one here is advocating for restoring that method exactly, just having a method. We're after the functionality without specifying implementation.

Most new projects (which is the focus of CoreFx) are moving to nosql/schemaless solutions

Citation needed. If that was true, why even bother to include SqlClient?

Most old projects never had the need for schema discovery

Sure, that's a fair statement. But some do. It's mostly libraries underneath that transparently used this before, which is mostly taken for granted.

I say let's keep the provider dead simple as it is, and see if it really impacts people.

It is impacting people, and we're letting the .Net Core team know. Right here, right now.

Schema discovery can be done by looking at the INFORMATION_SCHEMA

No, it can't. You're assuming the only schema someone needs is user tables. I'm working with DMVs currently. INFORMATION_SCHEMA doesn't include any of the system views. It also doesn't cover someone going SELECT * From MYTABLE - how do you propose I translate that to a known schema given the result set? That's a extremely non-trivial task. If you have a more complex query it becomes an exercise in re-implementing each platforms query parser to figure out which column came from where.

If there's an alternative way to get schema from a result set, please: I am all ears. As far as I know, it doesn't exist in .Net core. We need this functionality; it doesn't exist. These points can't be debated, only the priority (or not) or bringing functionality back is an open question. We really need to hear from Microsoft on that front.

@FransBouma

@ryanbnl Not all databases implement all INFORMATION_SCHEMA schemas, and not all of them do it correctly (I've written meta-data retrieval systems for many RDBMSs and there isn't a way to obtain deep meta-data (like which FK fields are used in which FK constraint) for all DBs). But even if they all do, the problem is still that the information inside the schemas isn't of the same type in each DB, so you still need to know which type is which in which DB. But even that is perhaps retrievable up front and storable in a big mapping blob that's used every time.

The problem is of a different kind: you execute a query Q on database D and you get a resultset back. What are the types and characteristics of each column in that resultset? As the query is perhaps a select on a complex join with aggregates and derived tables, you can't simply say which type is what and how big the string typed column is and whether it's nullable or not.

So in that situation you need the information of the resultset. This information is available in the datareader, it's exposed in a generic API in .net full, and essential for some operations. this is missing currently in .net core and MS isn't willing to add it soon, if at all. That's the issue. :)

@ryanbnl
ryanbnl commented Sep 22, 2015

Ahh, that makes a lot of sense. I've not (yet) hit issues of incomplete INFORMATION_SCHEMA metadata. That is indeed an massive advantage to using the .net metadata.

Do you have to offer 100% support or .Net Core targets? People targeting .Net Core next year will all be the early adopter types, they (we) can accept pragmatic limitations..

@nvivo
nvivo commented Sep 22, 2015

@NickCraver,

It seems we have 3 different discussions here:

  1. having datatable/dataview available in CoreFX somehow
  2. having some way to extract schema information for objects and dynamic queries
  3. keeping the old api for returning a DataTable with a schema from DataReader

I'm all in favor of the first two. (1) can be done in separate packages and it probably will
and (2) can be added easily, but probably won't make it into 1.0, which is no big deal. I'm really against (3).

Saying that the only method wasn't good isn't justification for removing the only method available.

It is not the only method available. You can query INFORMATION_SCHEMA or any other system tables as you always could. That is what the provider does under the hood. The point that some small number of people want the provider to abstract it for them just to avoid some queries is something else.

I know INFORMATION_SCHEMA doesn't have all the data. The point is that this the most standard we will get. If the people creating databases couldn't come up with a standard way to represent this information across all of them, a .NET api won't either. Also the schema tables provided by .NET were never standard, they have some standard columns and a lot of custom ones depending on the provider. If you're doing something more advanced, you already need to decide per provider what to do.

How is that different from just calling select * from sys.something on SQL Server or select * from mysql.something on mysql if you need? This discussion sounds like there is no way to do it, or that it's too hard to get this information while it's very easy. Libraries will have to do it instead of the provider, but that's it. As someone who implements these kinds of libraries, I think we can do a better job abstracting this for the specific usage of the library than trying to make .NET come up with a general abstraction for the entire universe of all databases as it has tried before.

As for @FransBouma point on dynamic queries, I agree. But this can be done in a new API without dependency on DataTables:

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

IReadOnlyList<IColumnSchema> schema = reader.GetResultSchema();

We could also have a very simple interface to get the mappings for datatypes:

Dictionary<string, Type> mappings = providerFactory.GetTypeMappings();
// bigint = Int64,
// varchar = String
// etc

It's not the end of the world people, and it's not hard to come up with a good enough api for this. We could focus on discussing what we actually need instead of complaining that the old .net api is not available.

@NickCraver

It is not the only method available. You can query INFORMATION_SCHEMA or any other system tables as you always could. That is what the provider does under the hood.

No...it doesn't. That is, to be blunt, a gross misunderstanding of what it does. It shows you the shape and schema of the result set. Let's take a very simple example:

Select 'bob' as Name

Show me how to get the column type and length for Name. INFORMATION_SCHEMA is related but not at all a viable solution for what we're talking about. Nor is it readily queryable since we're talking about the result of queries, not raw tables. That's assuming it even had the data in the first place, which it doesn't for DMVs, etc. And let's not forget stored procedures with dynamic output depending on the code paths and options passed.

Look back in this thread, no one is complaining directly about the API. We're pointing out missing, essential functionality. If it's restored via a new API that's fine, no one's debating that. But before going down the forking-from-CLR road, it'd be extremely nice for Microsoft to chime in with better reasoning. The reasoning provided thus far ("legacy") doesn't show awareness of the current use cases or the lack of any alternatives. That's fine, I'm not blaming anyone for that - but we've now pointed these things out and it's time for a more informed decision and prioritization I think.

@nvivo
nvivo commented Sep 22, 2015

I just proposed a solution for that, it seems you didn't read my post. I'm here since the fisrt post, I'm aware of what is being discussed.

@FransBouma

@ryanbnl

Do you have to offer 100% support or .Net Core targets? People targeting .Net Core next year will all be the early adopter types, they (we) can accept pragmatic limitations..

The thing is that they do need some sort of API to obtain meta-data of a resultset. And the only way to obtain the information in the first place is the ADO.NET provider itself, so they'll provide the code, and that means, as there's no generic interface, that developers have to hard-reference the ADO.NET provider, which is a pain, as .NET 1.x has learned us. So that might evolve in some sort of generic interface down the road but that will mean that code which utilizes that (ORMs, micro-orms etc.) will have to deal with the legacy crap that's left behind before that new API, i.e. the APIs of the ADO.NET providers offered themselves. We already have that with error reporting in ADO.NET today and it's stupid design and above all a burden (I know, I have to maintain code on that). This is a new framework, MS should invest in defining the generic API first, and then implement it in ADO.NET providers like SqlClient. But they're doing it the other way around: first SqlClient and if someone yells hard enough they'll perhaps implement it in a generic way.

@nvivo

I know INFORMATION_SCHEMA doesn't have all the data. The point is that this the most standard we will get. If the people creating databases couldn't come up with a standard way to represent this information across all of them, a .NET api won't either.

INFORMATION_SCHEMA's are standarized. SQL is too. Both are not implemented in full by all databases and by all versions of these databases.

Resultsets have nothing to do with these schemas, but with the actual query executed. Inside ADO.NET providers (the datareader implementations which produce data per row) this information is already available (as they have to know e.g. the type aspects of a column so they have to know how to convert a set of bytes into a .NET typed object).

Anyway, I've spent enough time debating your derailing attempts.

@terrajobst It's been long enough. Who inside Microsoft do I have to contact to get support for this added to the ADO.NET api? As the people from MS involved in this thread are incapable of getting anything going. Mind you, I started this issue on February 27th. That's almost 7 months ago (!).

@nvivo
nvivo commented Sep 22, 2015

@FransBouma You obviously didn't read anything, so there is no point in replying.

Also, MS won't do anything with complaints. They didn't forget, they decided to remove the API. The only way anyone will get any results is to start a new issue and propose a solution that doesn't involve undoing exactly what they decided to do.

@dmitry-a-morozov

@nvivo

It depends on DataTables which are considered legacy and most people don't use them anymore

This is slight overstatement.
Look at what I've done in F#.
http://fsprojects.github.io/FSharp.Data.SqlClient/data%20modification.html

You can of course hand-write all those INSERT, UPDATE and DELETE statements but it's a lot of boilerplate code.
Also optimistic concurrency, in-memory merge and bulk insert support will be missed too.

@terrajobst
Member

@nvivo

They didn't forget, they decided to remove the API.

That's a bit of an overstatement. We simply decided not to port it yet.

@nvivo
nvivo commented Sep 24, 2015

@terrajobst

We simply decided not to port it yet.

I was talking specifically about DataReader.GetSchemaTable(). It seems this was intentionally removed from the API, wasn't it?

@YoungGah
Member

Thanks for all the input and feedbacks. We will consider and evaluate bring in DataSet (might be in the same form as .NET Framework or in modified form) in the next version of .NET Core. For now, we are fully focused in delivering SqlClient including open sourcing and making it available on Linux and Mac. Once we achieve the right level of quality for SqlClient, we will start planning and evaluation for the next set of components/features to brining in. When we have more concrete plan, we will share out to the community. Regarding the feedback on SqlDataReader.GetSchemaTable, we are planning to add the functionality back to SqlClient in V1 although most likely it won’t in the form of DataTable. Please stay tuned.

Meantime, if you prefer not to wait for us to evaluate DataSet in the future version, the source for the .NET Framework implementation of DataTable and DataSet is licensed under MIT and is available on Reference Source. We are happy to provide guidance to anyone who is willing to port the implementation as a standalone library for .NET Core. The right way to start this would be to file an issue so we can lay down how a port could look like and what this means for libraries that want to target both, .NET Framework as well as .NET Core.

@FransBouma

For now, we are fully focused in delivering SqlClient including open sourcing and making it available on Linux and Mac. Once we achieve the right level of quality for SqlClient, we will start planning and evaluation for the next set of components/features to brining in. When we have more concrete plan, we will share out to the community.

Does this mean you're focused on System.Data.Common and as a specialization implementation SqlClient, or are you just focused on SqlClient and is System.Data.Common the ugly stepchild which gets code only after SqlClient has proven that it works? Because you state:

Regarding the feedback on SqlDataReader.GetSchemaTable, we are planning to add the functionality back to SqlClient in V1 although most likely it won’t in the form of DataTable.

The functionality should be in DbDataReader, with provider specific customization in SqlClient. Only then we can be sure other ADO.NET providers will have the same interface and not, as I described earlier, each their own specific method which is only usable through either reflection or a hard reference to the ADO.NET provider (which is a STUPID thing to have in generic database code.) Sorry for the caps, but I'm starting to get a little fed up after 7 months of waiting for an answer and then reading about Microsoft only focusing on SqlClient.

Asking people to do your job by asking them to port the code is only going to work if Microsoft stops focusing on their own products first: as it then never will become a community effort but will stay a Microsoft product.

@YoungGah
Member

@FransBouma

System.Data.Common is an important investment to us as well especially as it will provide the common interfaces for all types of providers. We will continue to invest in System.Data.Common and extend them as we add features just as we have done on .NET Framework. We just believe that System.Data.Common is in better shape than System.Data.SqlClient at this point although System.Data.Common has bugs we need fix. And, yes, you are correct that we will have to add GetSchemaTable on DataReader as well. I didn't mean to imply that we will only add it to SqlDataReader.

@FransBouma

@YoungGah Thanks for the clarification! then I have no further questions and hope you and your team can pull this off to everyone's satisfaction :)

@nvivo
nvivo commented Sep 25, 2015

@YoungGah. This is great information, pretty much what I was expecting. Will there be an issue to track the design of the data provider interface? It would be great if the community could provide some feedback before v1 on this core component.

I had issues trying to mock the original providers, as they do a lot of internal calls. Would be very useful to have real interfaces formalized and updated instead of removed, so we can write code these interfaces and mock them using standard mocking frameworks.

@YoungGah
Member

@nvivo
Absolutely! We are tossing around ideas of the API returning schema in JSON format. Any opinions? The issue is track at #3423.

@andrew-vandenbrink

@youngGah I need DataTable as it is part of my data structure, just like anyone use list or stack. You can put WriteXml or WriteJson as extension method right?

@joshfree joshfree assigned YoungGah and unassigned terrajobst Oct 12, 2015
@joshfree joshfree added this to the Future milestone Oct 12, 2015
@lstratman

@YoungGah mentioned previously that the best way to get DataTable/Set/View into CoreCLR in the immediate future would be to implement it in a standalone library. I may be able to take that on, but is there an idea on when those classes will be officially implemented, even if it's extremely vague, like fairly soon after the initial release, or the end of next year? The project that I'm going to be converting will be depending on DataTable and I just want to make sure that it's going to be worth my time to do the implementation.

@FransBouma

A separate library won't help, there's an essential aspect missing: the meta-data api in DbConnection and DbDataReader which you need to create datatables prior to filling them with data. No meta-data api: no untyped resultsets and no datatable usage as it is used today.

What's so mindboggling is the feet dragging over at Redmond about designing this meta-data API. As if it's something so new and so weird, it takes ages to come up with something.

@sanjoych
sanjoych commented Nov 3, 2015

So System.Data.Common is feature complete now for V1 of CoreCLR?

@jonat3n1

One important note about Dataset/DataTable/DataView is that these classes have been available in Mono for several years now and are also available in Xamarin's products for IOS and Android. I believe this has made it easier for many .Net developers to create apps for Linux, Mac and IOS products while actually avoiding other key Microsoft technologies such as Silverlight, Windows Phone, RT, etc. I don't know if Microsoft realizes how many .net applications have Datasets/DataTables embedded in their core libraries that don't need to change, would be too expensive to change and would add unnecessary risk. On top of all of this EF 7 is still much slower than core ado.net. I think the adoption rate for universal windows apps or asp.net 5,6,7 etc using .net core would take off if these classes were added to .net core.

@chadbr
chadbr commented Jan 18, 2016

I'll do a +1 on DataSet (/Table/View) -

I've never understood the distaste for these objects by the general population. I get the feeling we have many many more developers now that can't think at that level of abstraction?
I don't see how anyone builds a general data framework (i.e. non code-based entities / models / whatever) without them. A sad step back IMHO.

I'd be happy to join any effort to port them as an external library on top of .net core...

@mgravell
Contributor

Well, "general data framework" isn't something that most people need.
Historically, such frameworks have been abused and misused when model-based
/ DTO-based frameworks would have been far preferable. There are a few
limited cases where genuine "I don't know the layout of the data at all"
applies, but that tends to be for things like reporting frameworks via some
DSL and query generation. And in those cases too: DataTable can be
unnecessarily expensive (with original/current value tracking, etc).

The scenarios where they are genuinely good fits do exist, but are much
rarer in practice than the books, IDE tooling, and common patterns of 10
years ago might suggest. For 95%+ of the scenarios that they have been used
historically: the world has moved on. Dismissing people who are using appropriate tools for their needs as being unable to "think at that level of abstraction" is disingenuous.

@atifaziz

Well, "general data framework" isn't something that most people need.
For 95%+ of the scenarios that they have been used historically: the world has moved on.

@mgravell In your very own words, isn't it β€œdisingenuous” to assume that those people using appropriate tools (like DataTable) for their needs are some niche & shrinking group that hasn't moved on?

DataTable can be unnecessarily expensive (with original/current value tracking, etc).

Agree so then let's make that a feature request, that change tracking can be disabled for the many times it's not needed (even have it off way by default).

good fits do exist, but are much rarer in practice than the books, IDE tooling, and common patterns of 10 years ago might suggest

That's a re-education problem that's (hopefully) not going to need addressing. But yes, we definitely don't need as much IDE tooling as we had in the past, especially around DataSet & company being components you can interact with and connect on a design surface. That's where their weight needs to be shed.

@NickCraver

@YoungGah @saurabh500 I'm seeing the new APIs popup, which is great. For everyone to get on the same page though, can you please update us on the plans here? What's the current plan for classes coming to .Net Core? What's coming? What's not? What's in RC2 & RTM? What needs design or implementation help? We're more than happy to help.

@FransBouma

I'm closing this issue, as it's continued in other threads 10 times over.

@FransBouma FransBouma closed this Jan 27, 2016
@terrajobst
Member

@FransBouma

Thanks, that seems like a good idea. This thread has become a bit unmanageable due to sheer size.

@MelbourneDeveloper

I noticed today that the DataTable class exists in System.Data in .NET Core, but it also seems that there is no SqlDataAdapter, so there is no way to read the data in to the
DataTable

@atifaziz

I noticed today that the DataTable class exists in System.Data in .NET Core

@MelbourneDeveloper Except it is empty.

@zhanzushun

Frustrated and astonished finding DataTable/View/Set is missing from NET CORE.
Have to stop migration...
:-(

@MelbourneDeveloper

It's not hard to implement them. I implemented them in Silverlight. The annoying one is going to be reading the data with a data adapter.

How do I submit code?

@MihaMarkic

AFAIK ADO.NET (and much of the assemblies we know in "classic" .net) will be added in next releases.

@MelbourneDeveloper

Can I take it that we will be able to load a table of data in to a datatable using an sqldataadapter?

@MelbourneDeveloper

Is very nice.

@andrew-vandenbrink

@MelbourneDeveloper Unless @Eilon or @terrajobst state that DataTable will be ported as is in current .Net Framework, don't get your hopes up

@VitaliyMF

For everyone who is looking for old DataTable/DataRow/DbDataAdapter/DbCommandBuilder in .net core: take a look to my NReco.Data library, maybe it is exactly what you need. It provides similar API (RecordSet model) and offers great flexibility over database agnostic SQL generation.

@redowl3
redowl3 commented Aug 29, 2016

As Azure now only supports Asp.Net Core RTM how can bits of the framework still be missing and waiting to be ported across? In our case we use an external library to load up Excel files which produces a DataSet, not having the ability to easily manipulate this data is an absolute show stopper for us.

@benaadams
Collaborator

@redowl3 you can run Asp.Net Core on full framework which will be present on the Windows VMs

@redowl3
redowl3 commented Aug 29, 2016

Thanks Ben - do you know what references are needed though to access DataSet/DataRows etc...?

@FransBouma

@redowl3 Just reference System.Data, from the full framework, as you run on full framework anyway :)

@redowl3
redowl3 commented Aug 29, 2016

We've tried adding net45 framework and adding System.Data to the supported framework assemblies but that caused an issue with other dependencies that don't support that framework?

@benaadams
Collaborator
benaadams commented Aug 29, 2016 edited

Try net451 its the earliest framework aspnet core supports

@redowl3
redowl3 commented Aug 29, 2016

Thanks

@MelbourneDeveloper

We shouldn't have to do this, but instead of us all howling to get this done. Why doesn't someone who has the time, export the code from System.Data using ILSpy, and then bring the necessary code for SqlDataAdapter to compile? The code is all there. You can export the whole lot with ILSpy. It wouldn't take much to get it compiling in .NET Core. Just exclude all the unnecessary code for now.

Maybe someone wants to set up a repo on GitHub somewhere and others could contribute until it's done?

@MelbourneDeveloper

Well, actually, DataTable is even simpler. It would take only a couple of hours to pull in to Core and then you could write your own adapters...

@benaadams
Collaborator
benaadams commented Aug 30, 2016 edited

Why doesn't someone who has the time, export the code from System.Data using ILSpy

Well you might want to use the MIT licenced reference source instead; just saying...

Also its already on github https://github.com/Microsoft/referencesource/blob/master/System.Data/System/Data/DataTable.cs

@MelbourneDeveloper

In that case, what are we even worried about?

@FransBouma

@MelbourneDeveloper It might be a good idea to read this whole thread to know why it's not just the datatable class itself, but more importantly the ADO.NET api which exposes DataTable. As these methods are absent on .NET Core, you can port the datatable class all you want, but it won't get you anywhere. :) (and porting it is more time consuming than you might think as it comes with dataview, which contains a lot of databinding code, all of which won't work on .net core)

@MelbourneDeveloper

?

The code is all there. If it's not in the repo mentioned above, you can pull it out with ILSpy. That includes DataViews and database specific adapters like SqlDataAdapter.

It's not a solution in and of itself. Someone will need to go through the process of getting the code to compile for .NET Core, and then test it. But, it is possible.

There's no reason legal or otherwise, as far as I can tell why someone can't just start a repo and go for it. Of course, I'm not a lawyer and am not offering legal advice.

@chadbr
chadbr commented Aug 31, 2016

Mono? Or does it have the binding bits also?

@MelbourneDeveloper

Mono? Binding Bits?

Look! Here is the code for DataView.
https://github.com/Microsoft/referencesource/blob/master/System.Data/System/Data/DataView.cs

It's all there.

@FransBouma

The code is all there. If it's not in the repo mentioned above, you can pull it out with ILSpy. That
includes DataViews and database specific adapters like SqlDataAdapter.

Yeah, but that's against the EULA.

It's not a solution in and of itself. Someone will need to go through the process of getting the code
to compile for .NET Core, and then test it. But, it is possible.

The question isn't whether it's theoretically possible, but whether it's doable. 'Someone will need' yeah, that's always the case, isn't it? 

There's no reason legal or otherwise, as far as I can tell why someone can't just start a repo and go
for it. Of course, I'm not a lawyer and am not offering legal advice.

Heh, yeah that's clear ;)

Mono? Binding Bits?

Look! Here is the code for DataView.
https://github.com/Microsoft/referencesource/blob/master/System.Data/System/Data/DataView.cs

It's all there.

yeah, but it's not compilable on .net core because it depends on apis which aren't in .NET core at the moment. 

And even if you spent all that effort to port these too, you'll run into the issue where DbDataReader doesn't expose a datatable with GetSchemaTable(). 

If you want to port all that, fine by me, but it will take more than just a couple of days. And I doubt MS will merge the PR. 

    FB
@redowl3
redowl3 commented Aug 31, 2016

Which (I think?) brings us back to my question of how can MSFT only support Core RTM apps on Azure when not everything has been ported?

@MelbourneDeveloper

"The question isn't whether it's theoretically possible, but whether it's doable. 'Someone will need' yeah, that's always the case, isn't it?"

Of course it's doable. Most people here only need certain bits and pieces to get their code compiling. Why doesn't someone who's got some time get their bit compiling and then stick it up in a repo somewhere? Then, others can contribute as they get their bits compiling. This is better than sitting around an complaining.

yeah, but it's not compilable on .net core because it depends on apis which aren't in .NET core at the moment.

Some will compile, some won't. In the majority of cases it will be a matter of finding the equivalent API call in .NET Core.

And even if you spent all that effort to port these too, you'll run into the issue where DbDataReader doesn't expose a datatable with GetSchemaTable().

It's exposed. The method is there in the public repo:

virtual public DataTable GetSchemaTable()
{
throw new NotSupportedException();
}

That method needs to be overridden by the inheriting class.

@FransBouma

@MelbourneDeveloper I'd suggest you read this whole thread, you're trying to rehash things which have already been discussed at length here and in other threads. The TL;DR: it's not as simple as you seem to think, not by far. Don't you think we would otherwise already have implemented a workaround?

@VitaliyMF

@MelbourneDeveloper , @redowl3 DataSet/DataTable/DataRow implementations are over-complicated and it is good that they are not ported to .NET Core "as is". The structure is too heavy; it implements too many functions and in overall this "monolith" component is not the best choice in every concrete case.

Instead of just asking for old DataTable in .NET Core, could anyone list the functionality/usage scenarios that still needed and not covered by existing .NET Core-compatible libraries?

@MihaMarkic

Instead of just asking for old DataTable in .NET Core, could anyone list the functionality/usage scenarios that still needed and not covered by existing .NET Core-compatible libraries?

Oh, no, please, not again.

@mahara
mahara commented Sep 1, 2016

Of course it's doable. Most people here only need certain bits and pieces to get their code compiling. Why doesn't someone who's got some time get their bit compiling and then stick it up in a repo somewhere? Then, others can contribute as they get their bits compiling. This is better than sitting around an complaining.

@MelbourneDeveloper
You talk like it is something easy to be done. Why don't you start doing what you said, and let others benefit from the work you'd done? Piece of cake, isn't it?

@roji
Contributor
roji commented Sep 1, 2016 edited

@VitaliyMF please read this entire thread (as painful as that might be).

Note that since this was debated quite a bit of time has gone by, so here's a quick summary of the current state of affairs as I see it. .NET Core RTM has been released and it does include an alternate resultset metadata API which does not rely on DataTable/DataSet (#5915), addressing one of the main concerns originally raised. A database/table metadata API (as opposed to a resultset metadata API) is still missing (#5024), but it could be argued that users that really need this can query schema information manually (e.g. INFORMATION_SCHEMA or some database-specific structures), even if that's not database-independent and generally sucks.

IMHO this means that at this point there doesn't seem to be any missing functionality depending on DataTable/DataSet - you can do (almost?) anything without DataTable/DataSet (e.g. access dynamic results via DbDataReader APIs). So the remaining reason for DataTable/DataSet is for porting across code that already uses them, or a need for an untyped data access API that also includes the DataTable/DataSet extras (e.g. optimistic concurrency). These are of course valid requests/requirements.

Note that Microsoft seems to have also shifted their general strategy, and intend to make .NET Core more backwards-compatible with .NET Framework, to help porting (see this article). This means that DataTable/DataSet may come back, or they may not. Either way, IMHO it's great to have them as a totally separate package without any core aspects of ADO.NET depending on them (i.e. metadata).

@VitaliyMF

@roji Thank you for the detailed explanation. As I know that IDbColumnSchemaGenerator was specially introduced to cover schema aspect (not very helpful in practice - for example, Microsoft.Data.Sqlite.SqliteDataReader doesn't implement this interface).

Am I understand correctly, that only reason to have DataTable in .NET Core is backward compatibility?.. Well, not everything can be easily ported to .NET Core 1.0 and a lot of other incompatibilities will prevent old .NET projects from migration anyway (you don't say "System.Web" and WebForms should come back, right?). But new .NET Core projects will not use/depend on old DataTable!

Regarding untyped data access API: when needed, it may be provided by 3rd party libraries (I already mentioned my NReco.Data lib that implements command builder/data adapter and RecordSet with API similar to DataTable/DataRow). For me, it is still unclear, why old DataTable should come back into NETStandard ADO.NET API.

@chadbr
chadbr commented Sep 1, 2016

I thought this thread was closed :)

Could someone from Microsoft simply clarify if it's on the roadmap or not?

@roji
Contributor
roji commented Sep 1, 2016

As I know that IDbColumnSchemaGenerator was specially introduced to cover schema aspect (not very helpful in practice - for example, Microsoft.Data.Sqlite.SqliteDataReader doesn't implement this interface).

Are you sure about that? It's documented on SqliteDataReader. The Npgsql provider also implements it.

Am I understand correctly, that only reason to have DataTable in .NET Core is backward compatibility?.. Well, not everything can be easily ported to .NET Core 1.0 and a lot of other incompatibilities will prevent old .NET projects from migration anyway (you don't say "System.Web" and WebForms should come back, right?). But new .NET Core projects will not use/depend on old DataTable!

Well, it's true that originally Microsoft seemed to want to remove legacy/unwanted APIs in .NET Core. But at some point (again, see this blog) the decision seems to have made to really prioritize making .NET Core as compatible as possible with .NET Framework, to help people port. Whatever we think of DataTable/DataSet, there are definitely tons of programs out there relying on it.

Especially with .NET Core being broken down into nugets, there doesn't seem to be any harm in making DataTable/DataSet available in .NET Core, as a separate package (as long as it isn't needed for any core operation, e.g. metadata). I'm guessing it might be somewhat low on the internal priority list though, with everything else going on.

@MelbourneDeveloper

@MelbourneDeveloper
You talk like it is something easy to be done. Why don't you start doing what you said, and let others benefit from the work you'd done? Piece of cake, isn't it?

I already did some work around this in Silverlight by simply looking at the metadata of the System.Data namespace in Visual Studio (ILDasm like functionality) - not with any use of ILSpy, or with any code from the reference source. It's not as hard as people make out.

Here's an example of production code Fill method for a DataAdapter:

            for (int i = 0; i < reader.FieldCount; i++)
            {
                var columnName = reader.GetName(i);
                if (!dataTable.Columns.Contains(columnName))
                {
                    dataTable.Columns.Add(new DataColumn(columnName));
                }
            }

            while (reader.Read())
            {
                var dataRow = new DataRow(dataTable);

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var columnName = reader.GetName(i);
                    dataRow[columnName] = reader.GetValue(i);
                }

                dataTable.Rows.Add(dataRow);
            }

Originally, @benaadams mentioned that this code was using the MIT license

Well you might want to use the MIT licenced reference source instead; just saying...
Also its already on github https://github.com/Microsoft/referencesource/blob/master/System.Data/System/Data/DataTable.cs

But, after looking at the license agreement, it only allows us to use the code as a "reference":
http://referencesource.microsoft.com/license.html
http://referencesource.microsoft.com

The source is actually released under the MS-RSL license. Which according to Wikipedia "is the most restrictive of the Microsoft Shared Source licenses" (https://en.wikipedia.org/wiki/Shared_source#Microsoft_Reference_Source_License_.28Ms-RSL.29).

Are we able to get clarification on this? Are we allowed to take the System.Data code, slightly modify it for the purpose of compilation and use on .NET Core, and then stick that code up in a GitHub repo? The code may, or may not be used by Microsoft at a later point as a basis for building more functionality in to the System.Data namespace in .NET Core.

Just to clarify, I have no intention of breaking the license agreements put forward by Microsoft, or breaking the spirit of the agreements. I am merely raising this as a point that if Microsoft were to allow us to do it, we could recreate this library ourselves. If 10 programmers spent a small amount of time working on just the area of the functionality they need, and those areas were different, we'd be able to knock this over in a very short time span. But, if Microsoft doesn't want us to do this, then I withdraw any comments about attempting this, and will be happy to wait for the team to go ahead with this.

@MelbourneDeveloper

it could be argued that users that really need this can query schema information manually (e.g. INFORMATION_SCHEMA or some database-specific structures)

This is what I have done in the past, but it's not ideal. You will need to write a layer over the top if you are implementing for multiple database platforms.

with .NET Core being broken down into nugets, there doesn't seem to be any harm in making DataTable/DataSet available in .NET Core, as a separate package (as long as it isn't needed for any core operation, e.g. metadata).

Exactly. And, why not let the community build it?

@MelbourneDeveloper

@VitaliyMF

@MelbourneDeveloper , @redowl3 DataSet/DataTable/DataRow implementations are over-complicated and it is good that they are not ported to .NET Core "as is". The structure is too heavy; it implements too many functions and in overall this "monolith" component is not the best choice in every concrete case.

Instead of just asking for old DataTable in .NET Core, could anyone list the functionality/usage scenarios that still needed and not covered by existing .NET Core-compatible libraries?

On a basic level you are right. These classes are bloated and unnecessary. People should rewrite their code so as to avoid them.

That doesn't change the fact that legacy code does exist, and in order to embrace the new .NET Core technology, legacy code must be made to compile and work with the new technology. With the flick of a switch, large swathes of legacy code could be made to compile, and since this library does not need to be deployed with every instance of the .NET Core architecture, it's a mute point to say that it's too heavy. Any library could be too heavy. But, just taking some existing code and compiling it for .NET Core won't slow .NET Core down. Every developer still needs to make conscious decisions about performance no matter what.

@svick
Contributor
svick commented Sep 2, 2016
@MelbourneDeveloper
MelbourneDeveloper commented Sep 3, 2016 edited

Boom!

Off we go!

@bharatmangal04

Can anyone suggest what would be the recommended approach to pass data ( User Defined Table type or paramenters) to stored procedure in ASp.NET Core if using ADO.NET

@saurabh500
Member
saurabh500 commented Nov 14, 2016 edited

@bharatmangal04 Are you referring to SQL CLR UDTs on Sql Server?
Can you open a new issue for this question?

@sureba73

Tracking.... need DataTable/DataSet badly :(

@chadbr
chadbr commented Nov 23, 2016 edited

@sureba73 it's in... #8622

@roozbehid
roozbehid commented Nov 30, 2016 edited

So it is in daily build. How can I use it?
I am so confused on how these work
I've installed the newset sdk and so in my global.json I have

"sdk": { "version": "1.0.0-preview4-004130" }

So does it mean I can use DataTable/DataSet?
on my csproj I have

`

  <Version>1.0.0-alpha-20161104-2</Version>
  <PrivateAssets>All</PrivateAssets>
</PackageReference>
<PackageReference Include="NETStandard.Library">
  <Version>1.6.1-preview1-24530-04</Version>
</PackageReference>

`

<TargetFramework>netcoreapp1.0</TargetFramework>

So should I be able to just use them?!

@karelz karelz modified the milestone: Future, 1.0.0-rtm Dec 3, 2016
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment