Replacing System.Data.SqlClient with Microsoft.Data.SqlClient

Question

Hi,

In our project we were using below components to catch data and communicate with SQL server:

System.Data
Microsoft.ApplicationBlocks.Data

Now, there is a request to replace the System.Data.SqlClient with Microsoft.Data.SqlClient due to end of support for that library.
I made the required changes in most of the places but am getting errors from Microsoft.ApplicationBlocks.Data component like:

Error BC30518 Overload resolution failed because no accessible 'ExecuteReader' can be called with these arguments:
'Public Shared Overloads Function ExecuteReader(connectionString As String, commandType As CommandType, commandText As String) As SqlDataReader': Value of type 'SqlConnection' cannot be converted to 'String'.
'Public Shared Overloads Function ExecuteReader(connectionString As String, spName As String, ParamArray parameterValues As Object()) As SqlDataReader': Value of type 'SqlConnection' cannot be converted to 'String'.
'Public Shared Overloads Function ExecuteReader(connection As SqlConnection, commandType As CommandType, commandText As String) As SqlDataReader': Value of type 'SqlConnection' cannot be converted to 'SqlConnection'.
'Public Shared Overloads Function ExecuteReader(connection As SqlConnection, spName As String, ParamArray parameterValues As Object()) As SqlDataReader': Value of type 'SqlConnection' cannot be converted to 'SqlConnection'.
'Public Shared Overloads Function ExecuteReader(transaction As SqlTransaction, commandType As CommandType, commandText As String) As SqlDataReader': Value of type 'SqlConnection' cannot be converted to 'SqlTransaction'.
'Public Shared Overloads Function ExecuteReader(transaction As SqlTransaction, spName As String, ParamArray parameterValues As Object()) As SqlDataReader': Value of type 'SqlConnection' cannot be converted to 'SqlTransaction'.

The Microsoft.ApplicationBlocks.Data is returning or accepting parameters of type data.sql and is not working with microsoft.sql.

How can I resolve this issue, is there any replacement for Microsoft.ApplicationBlocks.Data that works with Microsoft.Data?

Thanks.

Answer 1

Firstly System.Data.SqlClient isn't going anywhere. It is part of the core framework and will remain supported until well after .NET 6.0 is out of support. It has been deprecated in lieu of Microsoft.Data.SqlClient however the newer library has lots of issues so if you run into problems then stick with the old library.

At the top of the list includes the fact that the binary relies on native binaries that must also be part of your deployment and are locked while the process is running. This causes problems for web apps that aren't stopped before trying to be deployed.

Another issue is that it currently relies on Azure binaries even though it doesn't actually require Azure. The issue is a low level dependency that even MS said they are not sure how to remove.

Yet another issue is that it doesn't support all the more advanced SQL types yet. You should therefore review what you need to ensure it meets your purpose before switching.

In regards to the actual issue you're having, you won't be able to use it. I should point out that Microsoft.ApplicationBlocks has been deprecated for a while. If you have any code that relies on that library then you should be looking to replace it. The library hasn't been supported for over 8 years and doesn't work with newer frameworks (aka .NET Core+).

Even if the library were supported, the library is based upon System.Data. Therefore all the types it uses requires the types from System,.Data. The versions provided in Microsoft.Data, while sharing the same name, are not the same types and therefore not interchangeable. You cannot use Microsoft.Data.SqlClient with the app block library. Microsoft.Data is a complete rewritten of the data libraries and only share the common System.Data interfaces in common. Since the app block library was specifically written to target System.Data.SqlConnection it cannot be used with any other library. If you really, really need this functionality then you'll have to grab the source code, update it to work with Microsoft.Data.SqlClient and then add it to your own code.