In our last article we looked at System.Data in .NET Core 3.0. Now we turn our attention to System.Data.SqlClient, which is the SQL Server driver.
SqlClient Minor Memory Improvements #34134
As the title implies, change 34134 is not significant on its own. But it does show an interesting technique that is useful when trying to reduce an application's memory consumption or GC frequency.
Consider this code, which uses an anonymous function.
TaskCompletionSource<object> tcs = new TaskCompletionSource<object>();
[…]
AsyncHelper.ContinueTask(writeTask, tcs, () => tcs.SetResult(null));
This type of anonymous function is known as a "closure" because it captures or "closes over" a local variable ("tcs"). To create a closure, the compiler has to create a hidden class that holds all of the captured variables. Both the original function and the delegate to the anonymous function hold a reference to an instance of this hidden class. For more detail, see Richard Carr's article titled C# Closures.
To avoid creating a closure, you can change the anonymous function to accept a state parameter. This parameter is then fed to the anonymous function at a later date. Here is an example:
AsyncHelper.ContinueTaskWithState(writeTask, tcs, state: tcs, onSuccess: state => ((TaskCompletionSource<object>)state).SetResult(null));
If this pattern looks familiar, it's because it started becoming popular for .NET back in the 4.5 era when the state object pattern was added to Task.ContinueWith. You can also see the pattern in ConcurrentDictionary's GetOrAdd method.
There are other memory-related performance optimizations in .NET Core 3.0 of a similar vein.
Add Datetime Read Span Path for Netcore #31044
In terms of raw performance, one of the most significant updates to .NET in recent years is the introduction of the Span<T> type. By eliminating the need for temporary buffers and strings when parsing binary and character data, GC pressure can be dramatically reduced. When Microsoft updated the Bing search engine to .NET Core 2.1 it saw a 34% drop in latency.
While in some cases the use of Span<T> is handled internally, applying it to binary data often requires updating code. To see an example of using Span<T> directly, look at pull request 31044.
Add support for UTF8 Feature Extension #30917
Pull request 30917 brings .NET into alignment with SQL Server 2019's new support for UTF8. The primary motivation for this feature is the ability to add internationalization to legacy applications currently using 8-bit characters. Enabling this feature on a char or varChar column allows UTF-8 text to be stored instead of the usual non-Unicode text while still supporting sorting and indexing.
Internally, .NET applications use UTF-16. So, for most scenarios you should still use nChar and nVarChar for your Unicode needs. This feature is mostly for databases that use both .NET and legacy clients.
Azure Active Directory Authentication Using Access Token #30342
Nathan Davidson explains the background of this feature:
The desktop .NET Framework 4.6 and newer has an AccessToken property on the SqlConnection class (MSDN) which can be used to authenticate to a SQL Azure database using an access token issued by Azure AD (examples here). However, this property is not present on the version of SqlConnection provided in the System.Data.SqlClient NuGet package, including the latest preview v4.3.0-preview1-24530-04. In case it's an important detail, the project in question is a library project targeting 'netstandard1.6'.
The lack of the AccessToken property makes it difficult, if not impossible, to support modern directory-based auth scenarios for Azure SQL DB while also targeting .NET Standard (I have not attempted to use the connection string-based AAD options, but for various reasons those would not be applicable for my project). I have yet to find a workaround other than setting the target framework to 'net46', which I would like to avoid except as a last resort.
SqlFileStream Implementation on Windows #29879
SQL Server FileStream allows the database to transitionally store files. While the files are physically stored on a file server, the database ensures they can only be manipulated within the context of a SQL query. While somewhat difficult to use, this avoids many of the problems you normally encounter when storing large amounts of unstructured data within a table.
The SqlFileStream implementation is only available for Windows. Specifically, when both the client and the server are running on a Windows machine. SQL Server on Linux simply doesn't support this feature, and if the client is on Linux, "currently there is no way to open a file over SMB and pass the Extended Attributes".
Added PoolBlockingPeriod Connection Property #29697
When using connection pooling, which is on by default, and a login error occurs the whole pool becomes temporary blocked from attempting new connections for five seconds. If the next connection fails, blocking occurs again for 10 ten seconds. This repeats with the time doubling with each attempt until the blocking period reaches one minute or a successful connection resets the counter.
While appropriate for most on-premise SQL Server scenarios, SQL Azure is far more prone to transient errors that are automatically resolved within a few seconds. For this reason, connections default to not using blocking with SQL Azure.
Originally added in .NET 4.6.2, the PoolBlockingPeriod connection string setting allows you to change the behavior to always or never block regardless of the type of database being connected to. Change 29697 adds PoolBlockingPeriod to .NET Core.
