Dapper is throwing System.Data.SqlClient.SqlConnection exception after upgrading System.Data.SqlClient to version 4.5.0-preview2

Question

I recently upgraded one of my ASP.NET Core applications from a net461 app to ASP.NET Core 2.0 app since the only thing that was holding me back in the past was System.Transactions (which is now supported).

After upgrading I get an error when accessing any function that is using the following:

protected static TransactionScope GetTransactionScope()
{
    TransactionOptions transactionOptions = new TransactionOptions
    {
        IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted,
        Timeout = TransactionManager.MaximumTimeout
    };
    return new TransactionScope(TransactionScopeOption.Required, transactionOptions);
}

Error:

Enlisting in Ambient transactions is not supported.

Based on my google research I saw a few open issues on GitHub related to this and they all say that upgrading System.Data.SqlClient to the latest pre-release will fix the issue.

I believe this fixed that issue, but caused another one. I am using Dapper v1.50.2 in my project and I think it is unable to use the pre-release version of System.Data.SqlClient because I am now getting the following error:

The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception

Does anyone know how I can use Dapper and also get rid of this Ambient transactions error? I am not sure how System.Transactions was ported over when an error like this is occurring.

Answer 1

I want to share my solution with the issue message:

The type initializer for 'Microsoft.Data.SqlClient.SqlConnection' threw an exception.

Also the 'Microsoft.Data.SqlClient.SqlConnectionFactory' appeared in the issue.

This appears using Dapper or Microsoft.Data.SqlClient libraries. Let me show you, I have two C# projects:

1. ConnectionToDB (Project used in 2.)
2. ASP.NET Core App

The problem that I found was that 1. had all the neccessary libraries/dependencies to work but for any kind of reason was not working. The problem was that the Microsoft.Data.SqlClient has to be inside 2. too to work. I don't know why but this may help to anyone.

Regards!

Answer 2

Use NuGet package manager and search for "System.Data.SqlClient", install the latest and the exception goes away.

Answer 3

I've recently fixed this issue. First, in your Web.csproj replace

<PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.5" />

with

 <PackageReference Include="Microsoft.AspNetCore" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Antiforgery" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.ApplicationInsights.HostingStartup" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Authentication" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.Abstractions" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.Cookies" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.Core" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.Facebook" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.Google" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.JwtBearer" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.MicrosoftAccount" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.OAuth" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.OpenIdConnect" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.Twitter" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Authorization" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Authorization.Policy" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.AzureAppServices.HostingStartup" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.AzureAppServicesIntegration" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.CookiePolicy" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Cors" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Cryptography.Internal" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Cryptography.KeyDerivation" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.DataProtection" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.DataProtection.Abstractions" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.DataProtection.AzureStorage" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.DataProtection.Extensions" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Diagnostics" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Diagnostics.Abstractions" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Hosting" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Hosting.Abstractions" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Hosting.Server.Abstractions" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Html.Abstractions" Version="2.0.0" />
<PackageReference Include="Microsoft.AspNetCore.Http" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Http.Abstractions" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Http.Extensions" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Http.Features" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.HttpOverrides" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Identity" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.JsonPatch" Version="2.0.0" />
<PackageReference Include="Microsoft.AspNetCore.Localization" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Localization.Routing" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.MiddlewareAnalysis" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Mvc" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.Abstractions" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.ApiExplorer" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.Core" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.Cors" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.DataAnnotations" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.Formatters.Json" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.Formatters.Xml" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.Localization" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.Razor" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.Razor.Extensions" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.Razor.ViewCompilation" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.RazorPages" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.TagHelpers" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.ViewFeatures" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.NodeServices" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.Owin" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Razor" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Razor.Language" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Razor.Runtime" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.ResponseCaching" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.ResponseCaching.Abstractions" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.ResponseCompression" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Rewrite" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Routing" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Routing.Abstractions" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Server.HttpSys" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Server.IISIntegration" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Server.Kestrel" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Server.Kestrel.Core" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Server.Kestrel.Https" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Server.Kestrel.Transport.Abstractions" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Server.Kestrel.Transport.Libuv" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.Session" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.SpaServices" Version="2.0.2" />
<PackageReference Include="Microsoft.AspNetCore.StaticFiles" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.WebSockets" Version="2.0.1" />
<PackageReference Include="Microsoft.AspNetCore.WebUtilities" Version="2.0.1" />
<PackageReference Include="Microsoft.CodeAnalysis.Razor" Version="2.0.1" />
<PackageReference Include="Microsoft.Data.Sqlite" Version="2.0.0" />
<PackageReference Include="Microsoft.Data.Sqlite.Core" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Caching.Abstractions" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Caching.Memory" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Caching.Redis" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Configuration" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Configuration.Abstractions" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Configuration.AzureKeyVault" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Configuration.Binder" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Configuration.CommandLine" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Configuration.EnvironmentVariables" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Configuration.FileExtensions" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Configuration.Ini" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Configuration.UserSecrets" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Configuration.Xml" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.DependencyInjection" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.DependencyInjection.Abstractions" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.DiagnosticAdapter" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.FileProviders.Abstractions" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.FileProviders.Composite" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.FileProviders.Embedded" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.FileProviders.Physical" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.FileSystemGlobbing" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Hosting.Abstractions" Version="2.0.1" />
<PackageReference Include="Microsoft.Extensions.Identity.Core" Version="2.0.1" />
<PackageReference Include="Microsoft.Extensions.Identity.Stores" Version="2.0.1" />
<PackageReference Include="Microsoft.Extensions.Localization" Version="2.0.1" />
<PackageReference Include="Microsoft.Extensions.Localization.Abstractions" Version="2.0.1" />
<PackageReference Include="Microsoft.Extensions.Logging" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Logging.Abstractions" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Logging.AzureAppServices" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Logging.Configuration" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Logging.Console" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Logging.EventSource" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Logging.TraceSource" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.ObjectPool" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Options" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Options.ConfigurationExtensions" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.Primitives" Version="2.0.0" />
<PackageReference Include="Microsoft.Extensions.WebEncoders" Version="2.0.0" />
<PackageReference Include="Microsoft.Net.Http.Headers" Version="2.0.1" />
<PackageReference Include="Microsoft.VisualStudio.Web.BrowserLink" Version="2.0.1" />

in this package references I removed all references on EntityFramework and SqlServer caching. then, add reference to older System.Data.SqlClient

<PackageReference Include="System.Data.SqlClient" Version="4.3.0" />

Hope it helps!