2

Quite simply, how do you get Datasets Designer in visual studio to use Microsoft.Data.SqlClient as a provider?

I did the following in a NetStandard2.0 Project.

Add nuget Microsoft.Data.SqlClient
Add new Dataset to project.
Opened the dataset designer and added a new TableAdapter.
Set connection string etc and all the other bits. Designer then created the adapter.
Click the TableAdapter -> Properties -> Connection and I see the provider is System.Data.SqlClient
Changed System.Data.SqlClient to Microsoft.Data.SqlClient
Clicked Save and got the following error.

Property accessor 'DbObjectName' on object '' threw the following exception. 'Cannot obtain provider factory for data provider named 'Microsoft.Data.SqlClient'

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
Gaz83
  • 2,293
  • 4
  • 32
  • 57
  • 2
    Discussed [here](https://github.com/dotnet/SqlClient/issues/239#issuecomment-537770942); there is a provider factory, but it's not registered as part of the framework by default like `System.Data.SqlClient` is, so you need to add it to `app.config` yourself. Disclaimer: untested. – Jeroen Mostert Sep 17 '21 at 11:29
  • Funny because I had just been reading that. My code is all in a NetStandard 2.0 library, which does not use an app.config, so is it a simple case of just adding a config file or is there an alternative? – Gaz83 Sep 17 '21 at 11:47
  • From .NET Standard 2.1 onwards, you can use [`DbProviderFactories.RegisterFactory`](https://learn.microsoft.com/dotnet/api/system.data.common.dbproviderfactories.registerfactory). Not an option for .NET Standard 2.0, where I recommend any of 1) abandoning it and going 2.1, 2) multi-targeting your library to Framework and Core instead of using Standard or 3) requiring applications that do the `app.config` dance themselves. In theory there's also 4) using nasty reflection to detect a spot at runtime to register the factory, but that goes against the grain of "Standard". – Jeroen Mostert Sep 17 '21 at 11:54
  • There's also option 5, which I forgot: redesigning your library so the provider isn't hard-coded and the application has to pass it in themselves. Rarely attractive because most DB code is rather tied to an engine, but you never know, maybe yours is the exception. This may require abandoning the dataset designer (in favor of things like hand-written code backed by an ORM like Dapper), or at least tweaking the generated source. – Jeroen Mostert Sep 17 '21 at 11:56
  • Thanks for all of that. I may have to rethink this then. My issue is that I have 7 windows services and an old but massive asp.net classic web forms website to migrate. So far I have moved all windows services to .net5 worker services, including a database library which they all share. I went with netstandard2.0 because the asp web site can support it still. Website is .net4.8 so I can't use .netstandard2.1. – Gaz83 Sep 17 '21 at 12:05
  • 1
    Multi-targeting to .NET Standard 2.1 and .NET 4.8 sounds like the path of least resistance. You may even want to consider using `System.Data.SqlClient` as the provider for the 4.8 version with some `#if`s, simply to minimize any chance of incompatibilities, even if `Microsoft.Data.SqlClient` is highly compatible and unlikely to cause problems. If your library gets mixed with others that rely on `System.Data.SqlClient`, for example, there may be trouble since one library's objects don't mix with the other. – Jeroen Mostert Sep 17 '21 at 12:07
  • Yeah I agree. Thanks for the help. – Gaz83 Sep 17 '21 at 12:13

0 Answers0