Imagine you’re a database administrator tracing query activity on a server. You’d want all the clues you can get to help you figure out what’s going on. One clue is the name of the client program associated with each database session. This name, passed from client to Microsoft SQL Server when a connection is established, can be used by the DBA to differentiate between multiple applications running on a single client and to focus on (or filter out) activity from the same application across multiple clients.
Does your application pass a
meaningful
program name to the database server?
Out of the box,
SqlConnection
defaults this name to “.Net SqlClient Data Provider” on .Net Framework and “Core .Net SqlClient Data Provider” on .Net Core. These default values aren’t particularly helpful to the DBA, as they don’t reveal the identity of the program using the session. True, these defaults indicate the technology that that program was built using—but the DBA can find this information elsewhere, as his management views separately report the client interface library used by the session.
In the
sys.dm_exec_sessions management view
output below, notice how much more informative the last row is because it contains a meaningful program name.
However, this approach
doesn’t take into account the possibility that the user’s connection string could already contain the
Application Name
keyword
. Appending
Application Name
onto the string could result in that keyword being used twice in the connection string—and that could produce unexpected behavior.
The Builder is Better
This possibility can be accommodated by using
SqlConnectionStringBuilider
.
var defaultApplicationName = new SqlConnectionStringBuilder().ApplicationName;
var builder = new SqlConnectionStringBuilder(userConnetionString);
if (builder.ApplicationName == defaultApplicationName) {
builder.ApplicationName = "Super Cool App";
var connectionString = builder.ConnectionString;
The code is a little more complex but much more robust. It only sets the connection string’s
Application Name
if that property (including its alias—the builder understands and respects aliases) is either not set or set to the default value.
*
Also, since SqlConnectionStringBuilder handles escaping, you’re saved from needing to know the connection string’s syntax rules for escaping any special characters that might be used in the application name.
*
Unfortunately, SqlConnectionStringBuilder does not provide a way to differentiate between “not set” and “set to the default.” If the connection string does not contain an application name, the builder sets that name to the default (instead of to null or to an empty string)—which is exactly the same value that property would have if it the user manually set that property to the default in the connection string.
Related posts:
-
Bug: Single <Application.Resources> Entry Ignored
-
How is a WPF XAML file tied into the application’s executable environment?
-
SQL for C# Developers
-
WPF, Where Is Your “static Main()” Method?
I have used this approach and it has helped me create solutions that would otherwise been much more complicated. I had to write a business rules app where multiple applications would feed data that had to be verified with identical business rules regardless of the fact that there were different applications sending in the data in different formats and data structures.
Simple enough to name the connection in order to give indication to the SQL procs for which app was sending in the data, and conditionals in that same SQL to handle the different data structures expected from each different app.
↓
