SqlClient: Retry not working with SqlDataAdapter.Fill(table) (.net framework)

I haven’t been following this in detail, but FWIW hiding base methods - so as to return the more specific provider type - is a pretty standard part of how the ADO.NET API is designed: it allows for a form of covariance.

For example, DbCommand has a protected abstract ExecuteDbDataReader method, as well as a public non-virtual ExecuteReader which calls the former. Both methods are typed to return DbDataReader.

Now, an ADO.NET provider must override ExecuteDbDataReader with its implementation, which will return a provider-specific implementation of DbDataReader (e.g. SqlDataReader). But the public user AIP - ExecuteReader - still returns DbDataReader, meaning that users can’t access any provider-specific APIs. So the provider hides ExecuteReader with its own version, which returns the specific type (SqlDataReader), allowing users to automatically get access to provider-specific features without downcasting etc.

(again, I have no idea how any of this is related to retries - just commenting on the method hiding part!)

@lcheunglci It’s very nice 👌 to hear that I will try this solution Finally I want to thank 😊 you for your hard work.

the reason why we had to new was because ExecuteReader in DbCommand was not virtual and therefore we cannot override it.

Note that ExecuteReader in DbCommand (and implements the IDbCommand.ExecuteReader discussed above) simply calls ExecuteDbDataReader, which is itself abstract. In other words, IDbCommand.ExecuteReader should indeed return an instance of SqlDataReader.

(Providers are expected to indeed replace ExecuteReader with new, but only to return the provider-specific implementation of DbDataReader (e.g. SqlCommand.ExecuteReader returns SqlDataReader, exposing SqlClient-specific APIs). This is a general pattern in ADO.NET for implementing a sort of “covariance”.)

Note also that DbDataAdapter in general is a layer on top of DbCommand and DbDataReader, so at least in principle whatever works in a DbCommand implementation should also work via DbDataAdapter. There may be some additional complexity in SqlClient specifically that makes this difficult though - I’m not familiar with the internals.

After reading the implementation of SqlCommand.ExecuteReader(), I see that there’s logic that checks if a RetryLogicProvider is provided in the SqlCommand and if so, the SqlReader returned is wrapped with RetryLogicProvider; however, in the core3.1 implementation of DbDataAdapter.FillInternal where the SqlDataAdapter inherits from, it grabs an IDataReader from ICommand.ExecuteReader. I think the issue is usage of the new keyword in the SqlCommand.ExecuteReader() which means the ICommand used in the DbDataAdapter won’t be able to use RetryLogicProvider instead it uses the one from DbCommand, which doesn’t use the RetryLogicProvider. I see that @DavoudEshtehari was the last person making a change to that line. I’ll double check with him to see why it was implemented this way, and if we can override the ExecuteReader so it doesn’t use the DbCommand’s version of it.

I appreciate this hard work, and I want to thank 😊 you very much

Thanks @MichaelAzzer , I was able to reproduce it locally with your sample project. I’ll take a look and debug it. It’s nice to hear that, thank you very much for your time

Hi, is there a sample project that I can use to reproduce this?

Yes, I prepared a simple sample to produce the problem

this zipped file contains WinForms sample with database script.sql sample.zip