Hangfire.SqlServer needs to migrate to Microsoft.Data.SqlClient

[udlose]

The old System.Data.SqlClient has been around for many years but has been superseded by the Microsoft.Data.SqlClient nuget for as of May 2019. The old System.Data.SqlClient will only have bug fixes supplied moving forward and applications should begin to migrate to Microsoft.Data.SqlClient to take advantage of improvements and new features.

See Introducing the new Microsoft.Data.SqlClient

[odinserj]

There's no need to replace anything and break current code bases as Microsoft.Data.SqlClient package is fully supported via a special overload, please see an example below. However may be it worth to add a new overload like UseMicrosoftSqlServerStorage or something like that to simplify the configuration and increase awareness.

GlobalConfiguration.Configuration
    .UseSqlServerStorage(
        () => new Microsoft.Data.SqlClient.SqlConnection(@"Server=.\;Database=Hangfire.Sample;Trusted_Connection=True;"));

[udlose]

I can't see how this could work since all of the underlying implementation of Hangfire WRT SqlServer uses objects from System.Data.SqlClient. Have you tried using Microsoft.Data.SqlClient with Hangfire?

1. Hangfire.SqlServer.SqlCommandBatch uses type checking against System.Data.SqlClient.SqlConnection this would fail when checking against Microsoft.Data.SqlClient.SqlConnection instance
2. Hangfire.SqlServer.SqlCommandSet uses:

• System.Data.SqlClient.SqlConnection
• System.Data.SqlClient.SqlCommand
• System.Data.SqlClient.SqlTransaction

[flipdoubt]

I tried using the work around described by @odinserj and found I get the same result. I submitted a PR to CAP.

[flipdoubt]

This is fixed in CAP 5.0.2 by my PR.

@odinserj, if I create a pull request that updates Hangfire to use Microsoft.Data.SqlClient, are you open to accepting it? I ask only because this project has 51 open requests.

[odinserj]

Great, but is it that CAP project that shamelessly stolen look, feel and source code of Hangfire's Dashboard UI without any notification or permission and also relicensed it under the MIT license, despite the original one is licensed under LGPLv3?

[flipdoubt]

Uhhh, could be. Sounds like there is some history there. I'm not sure I'm going to stick with that project, as their use of SQL storage hooks into way more than we need it to.

I did look into the work required to upgrade to Microsoft.Data.SqlClient, but there are lots of backward compatibility issues. Is switching in the 2.0 code? Is that Hangfire's dev branch?

[odinserj]

Reported violation in https://github.com/dotnetcore/CAP/issues/852. However the following way of registering SQL Server-based storage should work fine in your case with the recent versions of Hangfire. Integration tests are performed for both System.Data.SqlClient and Microsoft.Data.SqlClient packages on each commit.

GlobalConfiguration.Configuration
    .UseSqlServerStorage(
        () => new Microsoft.Data.SqlClient.SqlConnection(@"Server=.\;Database=Hangfire.Sample;Trusted_Connection=True;"));

With Hangfire 1.8.0 betas it's also possible to use Microsoft.Data.SqlClient without passing the connection factory explicitly:

GlobalConfiguration.Configuration
    .UseSqlServerStorage(
        "connection_string",
        new SqlServerStorageOptions { PreferMicrosoftDataSqlClient = true });

[0xced]

I have opened #2011 which addresses the problem by not taking a hard dependency on either System.Data.SqlClient or Microsoft.Data.SqlClient. This also solves the issue with Hangfire.SqlServer.SqlCommandBatch which is now compatible with both SqlClient implementations.