WEBINAR:
On-Demand
Building the Right Environment to Support AI, Machine Learning and Deep Learning
Adding Database Connection Strings
Add a
connectionStrings
section to the
app.config
file at the same level as
appSettings
. The following template shows both SQL Server and Oracle connection strings:
<connectionStrings>
<clear/>
<add name="Main Office SQL Server"
connectionString="Server=SQLMAIN\YourNamedInstance;database=YourDatabase;Integrated Security=SSPI;"
providerName="System.Data.SqlClient" />
<add name="Branch Office SQL Server"
connectionString="Server=SQLBRANCH;database=YourOtherDatabase;User ID=LmtdUser;Password=Secret;"
providerName="System.Data.SqlClient" />
<add name="Main Office Oracle Server"
connectionString="user id=OraUser;password=OraPwd"
providerName="Oracle.DataAccess.Client"/>
</connectionStrings>
Notice the use of the clear
element to block inheritance from any other config files, such as the machine.config
file. Make sure that only the connection strings specified in your monitoring application's app.config
file are used. Also note the providerName
elements used in the connectionStrings
section. To keep things simple, these are the names of the DbFactory invariants.
Using the template as a guide, add a connectionStrings
section to your app.config
file. If any of your connection strings contain passwords, you must take proper care to secure the connection strings.
Database Factories
To make the database-monitoring program as robust as possible, use DbFactory classes to allow the use of generic Database Access Layer (DAL) code. DbFactory classes work with DbProviderFactories
, which are uniquely identified by their invariant names. Although you could let your application inherit DbProviderFactories
from the machine.config
, use the clear
element once again to block inheritance. This is so that the monitoring application is controlled only by its app.config
file. The DbProviderFactories
are contained in System.Data
, which is at the same level as appSettings
.
Using the following XML as a guide, add a DbProviderFactories
section to your app.config
file:
<system.data>
<DbProviderFactories>
<clear/>
<add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client"
description="Oracle Data Provider for .NET"
type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess,
Version=10.2.0.100, Culture=neutral, PublicKeyToken=89b483f429c47342" />
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient"
description=".Net Framework Data Provider for SqlServer"
type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089" />
</DbProviderFactories>
</system.data>
The machine hosting your monitoring application must have the client connectivity software installed. You can find the exact syntax for the DbProviderFactories
by referring to the machine.config
on your machine.
Using a Generic List with DbFactoryClasses
You can simplify the code by creating a generic list to store all of the database connection strings and status information. Storing status information is necessary to prevent a flood of duplicate messages when a database server is down for a prolonged period of time. Since your database status polling internal is likely to be shorter than it takes to bring a database application back to full working status, you must persist the time of a database connection failure. By comparing the time of the last failure to the RepeatNotificationMinutes
, you can suppress redundant error emails:
- Within the DbMonitorWrapper project, add a class library file and name it
Database.cs
or Database.vb
.
- Add C#
using
or VB Imports
statements for System.Data
and System.Data.Common
to both class files (DbMonitor and Database).
- Add C# using or VB Imports statements for
System.Threading
to the DbMonitor file.
- Add the necessary code as shown here:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
namespace DbMonitorWrapper
{
class Database
{
string _FriendlyName;
public string FriendlyName
{
get { return _FriendlyName; }
set { _FriendlyName = value; }
}
string _Invariant;
public string Invariant
{
get { return _Invariant; }
set { _Invariant = value; }
}
DbProviderFactory _Factory;
public DbProviderFactory Factory
{
get { return _Factory; }
set { _Factory = value; }
}
DbConnection _Connection;
public DbConnection Connection
{
get { return _Connection; }
set { _Connection = value; }
}
DateTime _LastFailure;
public DateTime LastFailure
{
get { return _LastFailure; }
set { _LastFailure = value; }
}
public Database() { }
public Database(string FriendlyName, string Invariant, DbProviderFactory Factory,
DbConnection Connection, string ConnectionString)
{
this.FriendlyName = FriendlyName;
this.Invariant = Invariant;
this.Factory = Factory;
this.Connection = Connection;
this.Connection.ConnectionString = ConnectionString;
}
public Database(string FriendlyName, string Invariant, DbProviderFactory Factory,
DbConnection Connection, string ConnectionString, DateTime LastFailure)
{
this.FriendlyName = FriendlyName;
this.Invariant = Invariant;
this.Factory = Factory;
this.Connection = Connection;
this.Connection.ConnectionString = ConnectionString;
this.LastFailure = LastFailure;
}
}
}
- Add two new functions to
DbMonitor.cs
(or .vb
) to create a generic list, and use DbFactory
classes to test the connections:
public class DbMonitor
{
static string SMTPServer = ConfigurationManager.AppSettings["SMTPServer"];
static int SMTPPort = Convert.ToInt32(ConfigurationManager.AppSettings["SMTPPort"]);
static string MailFrom = ConfigurationManager.AppSettings["MailFrom"];
static string MailTo = ConfigurationManager.AppSettings["MailTo"];
public static int IntervalMsec = Convert.ToInt32(ConfigurationManager.AppSettings["IntervalMsec"]);
static int RepeatNotificationMinutes =
Convert.ToInt32(ConfigurationManager.AppSettings["RepeatNotificationMinutes"]);
static List<Database> Databases = new List<Database>();
public static void InitializeList()
{
DateTime lastFailure =
System.DateTime.Now.AddMinutes(-RepeatNotificationMinutes);
foreach (ConnectionStringSettings conString in ConfigurationManager.ConnectionStrings)
{
Console.WriteLine(conString.ConnectionString);
Console.WriteLine(conString.Name);
Console.WriteLine(conString.ProviderName);
DbProviderFactory factory =
DbProviderFactories.GetFactory(conString.ProviderName);
DbConnection connection = factory.CreateConnection();
Databases.Add(new Database (conString.Name, conString.ProviderName
, factory, connection, conString.ConnectionString, lastFailure));
}
}
public static void TestConnections()
{
for (int i = 0; i < Databases.Count; i++)
{
Database db = Databases[i];
try
{
db.Connection.Open();
}
catch (Exception ex)
{
DateTime rightNow = System.DateTime.Now;
TimeSpan sinceLastError = rightNow.Subtract(db.LastFailure);
if (sinceLastError.Minutes >= RepeatNotificationMinutes)
{
db.LastFailure = rightNow;
SendMail(db.FriendlyName);
Console.WriteLine("ERROR " + db.FriendlyName);
}
}
finally //
{
db.Connection.Close();
}
}
}
public static void SendMail(string FriendlyName)
Notice how the DbFactory
classes are provider independent.
- Build the DbMonitorWrapper project and resolve any errors.
- Modify the TestMonitor project to call
TestConnections
instead of SendMail
:
private void monitorTimer_Elapsed(object sender, ElapsedEventArgs e)
{
DbMonitor.TestConnections();
}
- Build the TestMonitor project and resolve any errors.
- Run the TestMonitor project and confirm it is working as intended.
- Before proceeding to the next step, consider changing the
IntervalMsec
and RepeatNotificationMinutes
values to longer time intervals that are more suitable for actual monitoring than debugging.