Runtime: Consider porting System.Data.OleDb to .NET Core and include it in Windows Compatibility Pack

[milenstack]

how do using oledb?

https://docs.microsoft.com/zh-cn/dotnet/api/system.data.oledb?view=netcore-2.0

[WernerMairl]

Basically OleDB is a windows (only) technology, so i can imagine (assumption) that (currently) there is no plan to migrate to Core (X-Plat).

just my 2 cent...but waiting for comments from the team...

regards
Werner

[ryanelian]

Will OleDB be provided under the Windows Compatibility Pack?

[divega]

System.Data Triage: We could consider porting System.Data.OleDb, but we would need to understand better:

1. What value would it bring to the table?

   • We believe OLE DB support is not as widespread as ODBC across databases and operating systems
   • How many customers want to port their applications or libraries to .NET Core but are blocked on not having OLE DB support? Are there reasons they cannot move to ADO.NET or ODBC?
   • Are there really any performance or functional benefits in specific OLE DB providers over existing ADO.NET or ODBC providers?

2. Are there any issues blocking or making the cost of porting high?

   • It is possible that the managed providers team knows this cc @saurabh500 @geleems

Moving this to Futures for the time being. We may consider prioritizing it or closing it if we gather additional feedback.

cc @ajcvickers

[ryanelian]

How many customers want to port their applications or libraries to .NET Core but are blocked on not having OLE DB support? Are there reasons they cannot move to ADO.NET or ODBC?

I was blocked from porting a feature from a legacy customer server because (AFAIK, CMIIW) ASP.NET Core MVC does not have a way of creating / reading / writing an MS Access database.

I'm under assumption that MS Access requires OLE DB.

This feature was required because the web server needs to write data to an MS Access database file, which then will be shipped to a government server via FTP file drop. 😢

If you somehow know how to query an MS Access database without OLE DB, please let me know. That knowledge is very important to me. Thanks for reading.

P.S. Thank you for porting System.Data.Odbc, it was very useful for accessing the data in a legacy DB2 server. 👍

[orellabac]

@ryanelian I know that https://github.com/brianb/mdbtools can be used to use Access on *nix platforms.

I also have libraries that have general db access code that can be used for SqlServer, odbc, or oledb.
Obviously there are portions of code that will not run unless they are on windows but it will be great to have System.Data.oledb to port the library

[feco93]

In our project we would like to support all .NET Framework compatible providers on windows, but on Linux, Mac just the .NET Standard compatible providers.

Is there any other ways to accomplish this behaviour without the Microsoft.Windows.Compatibility nuget package? Or we have to wait for this feature?

Thanks in advance,
Ferenc

[xdvarpunen]

👍 .NET Core support ODBC, but ODBC driver needs to be installed possibly (OBDC Data Sources shows options).
👍 OLEDB is not supported even through Microsoft.Windows.Compatibility NuGet.
👍 The Office engines might need to be installed separately.
👍 .NET Core can call Automation like VBA through .NET. through Activator.

@feco93 @ryanelian @divega I got ODBC working by setting database file path through System DSN. Other options did not work yet.

[mguinness]

The ability to query Windows Search is one gap for .NET Core currently. Right now Querying the Index Programmatically uses the Search.CollatorDSO provider. Windows Search doesn't appear to get much love, but it's a very useful technology and I would love to see a IQueryable provider for it if Ole DB is no longer an option.

[danmosemsft]

As a datapoint, if you would consume this library please up-thumb the top comment..

[voltcode]

OleDb used to be declared by MS by the best way to query MS SQL database (better than ODBC) at some, rather long, timeframe before .net core. There are many enterprise apps that do this. It would ease the pain of migrating such apps to .net core if this API was supported as a Platform Extension on Windows.

[divega]

OleDb used to be declared by MS by the best way to query MS SQL database (better than ODBC) at some, rather long, timeframe before .net core. There are many enterprise apps that do this.

@voltcode can you provide examples of this? In .NET, from the very beginning, the recommended and simplest way to connect to SQL Server has always been SqlClient.

In other words, any customer that used System.Data.OleDb to connect to SQL Server should probably switch to SqlClient as part of the transition to .NET Core 3.0. Unless I am mistaken (and in that case I would love to know!), there shouldn't be any loss of functionality.

On the other hand, there are specific databases like Microsoft Access (aka Jet) for which the only way to connect is System.Data.OleDb. When/if we decide to port OleDb, it is most likely going to be driven by those.

[divega]

A couple of questions to any application developers that has subscribed to this issue:

1. What types of databases do you connect to using System.Data.OleDb?
2. If alternatives are available (for example, a database-specific ADO.NET provider, or System.Data.Odbc), is there any reason you preferred to use System.Data.OleDb?

Thanks a lot.

[voltcode]

@divega I found some of the history referenced on https://stackoverflow.com/questions/103167/what-is-the-difference-between-ole-db-and-odbc-data-sources

OLE DB can be used with non-relational data sources, and works pretty well with Excel with a db-query like API, instead of dedicated Excel interop that requires Excel to be installed. We have some code for example that uses OleDb for both SQL Server and Excel because it allowed us to use one API instead of two.

There are many .NET applications in the world that use OLE DB in some form today. Moving them to Core is very expensive, if you have to rewrite the db layer. For the new application, yes - ODBC should be used, period. Please be aware that System.Data.Odbc has been declared stable only 6 months ago for .net core/net standard.

I don't know the current status of .net core db drivers, but allowing users to re-use existing drivers should help .net core adoption.

There's also a big hole in .net core regarding SQL Server Analysis Services connectivity - AdomdClient is not really supported. I'm not sure if it is built on top of OLE DB for OLAP extension or not. If yes, then perhaps OLEDB support could improve things.

[divega]

We have some code for example that uses OleDb for both SQL Server and Excel because it allowed us to use one API instead of two.

This is a really good data point @voltcode, thank you. Indeed, accessing data from Microsoft Office applications (mostly Microsoft Access, but also Excel) is one primary use case we have identified for System.Data.OleDb.

I think it is worth mentioning that the ADO.NET provider model already enables accessing the majority of the functionality in a provider-agnostic way (using the base classes in System.Data.Common or the interfaces in System.Data, and a provider factory), so you shouldn't really need to use System.Data.OleDb to access SQL Server because you wanted to use the same API you use to access data in Excel files (although that is orthogonal in your case if you still need System.Data.OleDb anyway to access the data in Excel files).

There are many .NET applications in the world that use OLE DB in some form today.

But again, can you provide specific examples besides that one application you own? Also, do you have concrete plans to port this application to .NET Core 3.0? Would you consider doing it if System.Data.OleDb was made available on .NET Core.

Sorry I insist on trying to get as real as possible here, but we are trying to leverage a finite amount of resources to get the best possible experience on .NET Core 3.0, and if we spend time porting System.Data.OleDb, very likely some else's favorite feature will have to wait :smile:

For the new application, yes - ODBC should be used, period.

Actually, if there is a database-specific ADO.NET provider you can use, that should in general be your first choice.

There's also a big hole in .net core regarding SQL Server Analysis Services connectivity - AdomdClient is not really supported. I'm not sure if it is built on top of OLE DB for OLAP extension or not. If yes, then perhaps OLEDB support could improve things.

That is a good point, but I think System.Data.OleDb won't help. If you are using the clients linked to from https://docs.microsoft.com/azure/analysis-services/analysis-services-data-providers and need the functionality to be supported on .NET Core, please create a separate issue for those.

[mguinness]

@divega There's no way to use Windows Search in .NET Core currently as the Search.CollatorDSO provider is the only way to query the index. I assume this is also the case with PowerShell Core,

[FroggieFrog]

We use OLEDB to connect to a MS Access database (*.accdb via ACE) and read data from EXCEL files.
We really plan to migrate to .NET Core 3 because of performance reasons.
Our main application is a mix of WinForms and WPF. We suppose that because of the open sourcing of them (for .NET core but not for full framework), that future development will only happen for .NET Core.
So OLEDB is the main blocker for us.
We also use the Windows Search, but that feature is not vital to us. Another benefit for us would be the availability to use a more modern web browser control.
All other stuff (APIs) that we use (e.g. DirectShow) is already supported.

[voltcode]

@divega yes I have plans for migration to .net core 3, but this issue is a blocker. We have a set of applications with similar dependencies, so it's not just a single app.

Throughout ca. 10years, at customers sites I saw and maintained many apps that used OLEDB, primary use was for reading Excel files. Most of those apps work today, and cannot be moved to core because it requires too much redevelopment.
It seems that this issue is mostly a child of Excel's success,the largest the data accessing capabilities, the more successful the framework is. If you ever plan to ditch .net fx, then this is one of the blockers.

[rido-min]

@FroggieFrog thanks for your feedback. FYI you can use modern web view in WinForms/WPF running on .NET Framework using https://www.nuget.org/packages/Microsoft.Toolkit.Win32.UI.Controls/

[Grauenwolf]

Now that WPF runs on .NET Core, I'm going to need OleDB/Access as well. (Not that I like using Access, but there are too many legacy databases to ignore it.)

And while I currently don't need Excel in my WPF projects, that was a common requirement in the past so it wouldn't surprise me at all if I see it again in the future.

[gulbanana]

Does Access not already work through ODBC?

[orellabac]

Yes it does. I made it work on windows and on mac using odbc. See https://mrojas.ghost.io/msaccess-in-dotnetcore/

[divega]

Thank you @orellabac for posting this. Very helpful! Have you had the experience of porting an existing application using this approach? Did you find any limitations or clear advantages when using Access databases through specific ODBC drivers?

[IrinaPykhova]

we have a big number of code samples which use MS Access. Most of them use typed data sets and other stuff generated by VS as described here: https://docs.microsoft.com/en-us/visualstudio/data-tools/connect-to-data-in-an-access-database-windows-forms?view=vs-2017. It is all based on OleDB provider and includes a lot of VS-generated code. Most of developers never look at VS-generated code, so expecting them to change it to use ODBC instead of OleDB looks like nightmare. And what if someone wants to share this code between desktop and .Net Core apps?

[ste4net]

Thank you @orellabac for posting this. Very helpful! Have you had the experience of porting an existing application using this approach? Did you find any limitations or clear advantages when using Access databases through specific ODBC drivers?

From my experience is not possible (easy) to install ODBC Access driver 32 and 64bit version.
A lot of system requires 32Bit version and you can't install 64 Bit version driver if you are using office 32.

I had no issues like this using oledb in classic Framework version of my app.

[orellabac]

OleDb has no issue in classic framework. This issue is only related to .NEt core.
What I have installed is only the access runtime from https://www.microsoft.com/en-us/download/details.aspx?id=13255
I have not experienced installing this runtime with a 32 bit version of office.

[divega]

System.Data.OleDb was ported to .NET Core in https://github.com/dotnet/corefx/pull/37101 by @maryamariyan. It will be released as a package and as part of the Windows Compatibility Pack in 3.0.

Thanks everyone for the feedback.

[divega]

Everyone,

System.Data.OleDb for .NET Core will be available as part of preview 6 (and much sooner in our nightly builds). Once it is available, it would be great if you could give it a try and let us know how it is working for you. We want to make sure this works with various existing OLEDB providers and effectively unblocks you.

@IrinaPykhova, would it be possible to have at least some of the samples you mentioned converted to run on .NET Core and use the new package?

Thanks!

[voltcode]

Now that System.Data.OleDb is going to be available on .NET Core, is there a chance that Adomd libraries will be ported as well? What's the best place to report such need? There are many enterprise apps that talk to SQL Server Analysis Services that are screaming to be ported to .NET Core, but can't due to lack of ported client and administration libraries !

[isaacabraham]

@divega great news! What versions of netcore will this be supported on? Just 3.x onwards?

[danmosemsft]

@isaacabraham yes, .NET Core 3.0+

Now that System.Data.OleDb is going to be available on .NET Core, is there a chance that Adomd libraries will be ported as well? What's the best place to report such need? T

@voltcode please open a new issue feel free to tag @divega.

[danmosemsft]

Also, @isaacabraham to echo @divega above, please do install Preview 6 when it comes out (probably next month) and verify for us that OleDB works for you. It is easier and quicker to fix issues before final release, than in servicing. We would also welcome more tests, if you are interested in contributing!

[isaacabraham]

@danmosemsft will try :-) The application in question unfortunately also uses Microsoft.Office.Interop.Excel however, which I don't believe is going to be ported across to netcore. But I will see if we can at least test out the OLE code.

[agilmore30]

Hi

First, thanks for porting OleDb over to .NET. It's very important for bulk load type applications.

As soon as this is available, I'll be testing for both SQL Server and IBM DB2 iSeries.

I have a .NET Core 3.0 ETL type application that accesses an IBM iSeries DB2 data source via ODBC currently. The ODBC data source is very slow. There are reports that the Microsoft DB2 OleDb driver is twice as fast as any other provider, including IBMs own OleDb provider (See this link)

Also, the SQL Server OleDb provider is reported as 2.5x faster than the ADO.NET source, and approximately 3.8x faster than the ODBC Source (link)

[danmosemsft]

Thanks @agilmore30. The preview should be our in about a week I expect

[divega]

Everyone,

Now that System.Data.OleDb is available on NuGet.org, it would be great if you could take it for a spin on .NET Core 3.0 preview 6. If you have an existing application, sample code, or a test that was blocked from working on .NET Core for lack of this functionality, please try now. We are hoping to get your help to cover a variety of OLEDB providers and scenarios, so we can identify any issues with the port before RTM.

Thanks!

[agilmore30]

Hi

Just wanted to confirm I have got this to work using OleDb connecting to SQL Server. It worked with a variety of data types, the schema of the table is below:

CREATE TABLE [psg].[SODB_TypeTable] (
[InInt] int NULL,
[First] nchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Second] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Street] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Suburb] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumberDec] decimal(18, 6) NULL,
[bigNumber] bigint NULL,
[BitNumber] bit NULL,
[Date] nvarchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateAndTime] datetime NULL,
[BigDateTime] nvarchar(27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateTimeofset] nvarchar(34) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Floaty] float NULL,
[MoneyMoney] money NULL,
[NumericNum] decimal(18, 0) NULL,
[Realy] real NULL,
[smallDate] smalldatetime NULL,
[SmallNum] smallint NULL,
[JustTime] nvarchar(16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HashKey] binary(20) NOT NULL,
[Inserted_Batch_Execution_Id] int NULL,
[Updated_Batch_Execution_Id] int NULL,
[Row_Effective_Date] datetime2(7) NULL,
[Row_End_Date] datetime2(7) NULL,
[Row_Is_Latest] bit NOT NULL,
[Row_Is_Deleted] bit NOT NULL,
[Row_Is_Intra_Batch_Change] bit NOT NULL,
[Reject_Ind] bit NOT NULL,
[Row_Is_Duplicate] bit NOT NULL,
[Row_Is_Late_Arriving] bit NOT NULL
)

I'll be testing Db2 running on Windows and ISeries shortly.

[maryamariyan]

Just wanted to confirm I have got this to work using OleDb connecting to SQL Server. It worked with a variety of data types, the schema of the table is below:

@agilmore30 that's awesome.

I'll be testing Db2 running on Windows and ISeries shortly

Thank you for your engagement. Please continue to keep us posted with the outcome

[IrinaPykhova]

@IrinaPykhova, would it be possible to have at least some of the samples you mentioned converted to run on .NET Core and use the new package?

we verified OleDB under .Net Core 3 with couple of our samples. It works fine with traditional limitation (it works for x86 only). We will let you know if have any issues

[agilmore30]

Hi.

Just want to confirm this works with Microsoft OleDb provider for DB2 (Provider=DB2OLEDB.1;) connecting to a local DB2/MVS instance, with a variety of data types:

[orellabac]

Hi I am using .NET Core Preview 8 but I am still getting:

InvalidOperationException: 'The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.'

I have installed the Access Jet Engine from https://www.microsoft.com/en-us/download/details.aspx?id=13255 and have tried targetting x86 and x64. Any ideas?

[danmosemsft]

@maryamariyan is this familiar?

[alexstrickland]

Extremely happy to discover that this worked very well with a Clipper DBF file export after I upgraded the project to .NET Standard without thinking about OleDbConnection.

[danielSt-dev]

Hi,
FYI, we get a AccessViolationException using System.Data.OleDb and "Microsoft OLE DB Provider for Visual FoxPro 9.0".

see issue https://github.com/dotnet/runtime/issues/981

[JoeHz]

I am also getting an AccessViolationException with Microsoft.Jet.OLEDB.4.0, connecting to an Access .mdb file that's older than the Bruce Willis movie "Armageddon". Using identical code that ran fine under wpf/.net framework, but now trying to port to .net core.

Curiously, a CommandType.StoredProcedure runs fine, but CommandType.Text get this exception.

[mguinness]

@JoeHz Have you tried setting OLE DB Services in the connection string? Also you could try making IIS Express run in 32 bit mode.

[JoeHz]

@mguinness I had not, but I just tried it and it fails in the same way.

[JoeHz]

There is no IIS involved here. Using this version of OLEDB, one can only compile for 32 bit.

The failing code follows:

OleDbCommand cmd2 = new OleDbCommand("Select * from [PlayerNumbers] where [Section] = ? AND [Table] = ? AND [Direction] = ?;", window._cn);

                    foreach (var person in people)
                    {
                        cmd2.Parameters.Clear();

                        cmd2.Parameters.AddWithValue("[Section]", person.SectionId);
                        cmd2.Parameters.AddWithValue("[Table]", person.TableNumber);
                        cmd2.Parameters.AddWithValue("[Direction]", person.Direction);

                        OleDbDataReader reader2 = cmd2.ExecuteReader(); //System.AccessViolationException occurs here

[mguinness]

The only other thing I can suggest is to try to handle corrupted state exception in managed code using COMPlus_legacyCorruptedStateExceptionsPolicy environment variable. Further details at CLR Inside Out - Handling Corrupted State Exceptions.

[jader1313]

I tested using a LocalDB database and OLE DB Driver for SQL Server and got a error too.

"Provider=MSOLEDBSQL;Server=(localdb)MSSQLLocalDB;Database=SarathlalDb;DataTypeCompatibility=80;Integrated Security=SSPI; "

In .Net Core 3.0 or 3.1 tests with 1 parameter works, but with 2 or more parameters error occurs.

@JoeHz tests the command with a parameter just to confirm what I said:

Select * from [PlayerNumbers] where [Section] = ?

[danmosemsft]

This issue is closed so comments don't reliably get seen. Please open new issues for any distinct remaining issues. Any debugging you could do would help speed things up.

[JoeHz]

@jader1313 You want to open the issue that @danmosemsft says should be created? I'll add my $.02 to it. I have parameterless queries that run fine but all my parametered queries fail. I don't believe I have any with one and only one parameter. Will try to get to your test case this week.

[jader1313]

@JoeHz I replied on dotnet/corefx#42596