In this article we turn our attention to System.Data.SqlClient, which is the SQL Server driver.
When the app is deployed to a test or production server, an environment variable can be used to set the connection string to a real database server.
Visual Studio
SQL Server Express LocalDB
LocalDB is a lightweight version of the SQL Server Express database engine that’s targeted for program development. LocalDB starts on demand and runs in user mode, so there’s no complex configuration. By default, LocalDB database creates *.mdf files in the C:/Users/<user/>directory.
- From the View menu, open SQL Server Object Explorer (SSOX).
- Right click on the
Movietable and select View Designer:
Note the key icon next to ID. By default, EF creates a property named ID for the primary key.
- Right click on the
Movietable and select View Data:
Visual Studio Code
SQLite
The SQLite website states:
SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine. SQLite is the most used database engine in the world.
There are many third party tools you can download to manage and view a SQLite database. The image below is from DB Browser for SQLite. If you have a favorite SQLite tool, leave a comment on what you like about it.
Visual Studio for Mac
SQLite
The SQLite website states:
SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine. SQLite is the most used database engine in the world.
There are many third party tools you can download to manage and view a SQLite database. The image below is from DB Browser for SQLite. If you have a favorite SQLite tool, leave a comment on what you like about it.
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.
