Effective Database Store Naming Conventions Guide
Estimated reading time: 6 minutes
DataGate Source Profile
In order for DataGate to establish a connection from an application to a database server it is necessary to provide information about the server, communication protocol to use, user and password for the connection, etc. This information is captured in what DataGate calls a database Source Profile.
Monarch Base provides the class QSys.Runtime.Database
to encapsulate a DataGate Source Profile and a DataGate connection to a database.
QSys.Runtime.Database
provides a set of properties to establish programmatically the attributes of it Source Profile; it also provides methods to let DataGate set the attributes from configurations settings stored in text files or from other Configuration Sources as discussed below.
Database Name
Database Names are unique, human-friendly character strings associated with the properties of a Source Profile within a Configuration Source. Database Name identifiers may follow any mnemonic convention the user or enterprise chooses (for example, “milwaukee-10234-sql-server”). For purposes of comparison within DataGate, Database Name identifiers are culturally-sensitive, but case-insensitive.
The QSys.Runtime.Database
constructor accepts a Database Name string and it also provides the method SetDBName to set the string at run time.
Before the constructor or the SetDBName are executed, one or more Configuration Sources should be established via the static method PrepareNameStore()
.
Configuration Sources
The PrepareNameStore()
method supports the following sources:
- JSON Files
- User Secrets
- Environmental Variables
- Command Line Arguments
JSON Files
The following sample JSON file defines two Database Names profiles, the first one called “AppDB” and the second “HRDB”:
{
"asna": {
"dataGate": {
"sources": {
"AppDB": {
"server": "TESTSERV",
"user": "TESTUSER",
"password": "0Test.Psw9",
"platformAttribute": "*DATALINK",
"initialLibraryList": [ "ERCAP" ],
"port": 5160,
},
"HRDB": {
"server": "DEVSERV"
}
}
}
}
}
DataGate defines a DataGate Schema for JSON files defining named Source Profiles. Adding a reference to the schema file can assist on editors that provide intellisense like VS Code and Visual Studio.
{
"$schema": "asna-qsys-schema.json",
"asna": {
"dataGate": {
"sources": {
"AspenRND": {
"server": "MyMSSQL",
"label": "SQL",
"platformAttribute": "*SQLCLIENT",
"initialLibraryList": [
"MY_LIB"
],
"user": "*DOMAIN"
}
}
}
}
}
The DataGate JSON File
By convention a default JSON file called ‘asnasettings.json’ can be placed in the sub folder ‘ASNA/DataGate’ under the special folder LocalApplicationData.
If the file is not there, you can create it.
On a Windows system, the full path would be:
C:\Users\<USER>\AppData\Local\ASNA\DataGate\asnasettings.json
Where <USER>
is the name of the user running the application. This location and name are default options. A DataGate JSON file may be located in any accessible directory and given any valid file name.
Secrets
Similar to ASP.NET Core User Secrets, sensitive data can be kept in safe storage on a development machine. The most sensitive pieces of data on a Source Profile are the User and Password.
You can learn more about the Secret Manger here and how to manage user secrets with Visual Studio here.
Environmental Variables
Environment Variables are another place to set Source Profile attributes like this:
set asna__dataGate__sources__AppDB__password=TEMP1234
Command Line Arguments
Finally, the command line can receive arguments with Source Profile attributes, like this one:
dotnet run /asna:dataGate:sources:AppDB:password=TEMP1234
PrepareNameStore
The PrepareNameStore<T>()
method accepts an options parameter and a list of JSON configuration files.
public static bool PrepareNameStore<T>(NameStoreOptions options, params string[] configFiles) where T : class
The options
parameter, of enumerated type NameStoreOptions, has the following set of flags:
- UseJsonDefaultPath = 1,
- UseSecrets = 2,
- UseEnvironmentalVars = 4,
- UseCommandLineArgs = 8,
Configuration sources provide values to the store in order of increasing priority as given by the integer value shown above. When two sources define a value for a single attribute of a database name, the source with higher priority has precedence over the attribute value defined by the lower priority source.
An optional list of configuration files specified by parameter configFiles
are loaded in the order in which they are given, but after the default JSON file (if specified) and before the Secrets (if specified).
Notice that a website’s
appsettings.json
file can be used a source for database names.
The Secrets used are those associated with the assembly of the type <T>
passed to PrepareNameStore<T>()
The static method PrepareNameStore<T>()
should be called only once per process. It is typically called in the static constructor of the main application class; in the case of Monarch migrated applications, it is called in the static constructor of MyJob.
Here is a common pattern used:
public partial class MyJob : InteractiveJob
{
. . .
public Database MyDatabase = new Database("AppDB");
. . .
static MyJob()
{
Database.PrepareNameStore<MyJob>();
}
override protected void ExecuteStartupProgram()
{
. . .
MyDatabase.Open();
. . .
}
Here is an alternative example:
public partial class MyJob : Job
{
. . .
public Database MyDatabase = new Database("");
. . .
static MyJob()
{
Database.PrepareNameStore<MyJob>(NameStoreOptions.UseEnvironmentalVars | NameStoreOptions.UseCommandLineArgs,
@"C:\AppMain\ProductionSettings.json", @".\appsettings.json");
}
override protected void ExecuteStartupProgram()
{
. . .
MyDatabase.SetDatabaseName("AppDB");
MyDatabase.Open();
. . .
}