376 lines
14 KiB
C#
376 lines
14 KiB
C#
using DbAPI.Connections;
|
|
using DbAPI.DAS;
|
|
using DbAPI.Database;
|
|
using DbAPI.Sensors;
|
|
using DbAPI.Channels;
|
|
using DbAPI.Groups;
|
|
using DbAPI.GroupHardware;
|
|
using DbAPI.TestSetups;
|
|
using DbAPI.Tags;
|
|
using DbAPI.CustomerDetails;
|
|
using DbAPI.LabratoryDetails;
|
|
using DbAPI.TestEngineerDetails;
|
|
using System;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using DbAPI.Errors;
|
|
using DbAPI.SPCaching;
|
|
|
|
namespace DbAPI
|
|
{
|
|
public class DbAPI
|
|
{
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="connection"></param>
|
|
/// <param name="storedProcedure"></param>
|
|
/// <param name="clientDbVersion"></param>
|
|
/// <param name="storedProcedureVersionToUse"></param>
|
|
/// <returns></returns>
|
|
public static ulong GetStoredProcedureToUse(IConnectionDetails connection, string storedProcedure, int clientDbVersion, out int storedProcedureVersionToUse)
|
|
{
|
|
storedProcedureVersionToUse = 0;
|
|
|
|
var ret = DbAPI.GetDatabaseVersion(connection, out int serverDbVersion);
|
|
if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
|
|
|
|
var maxSPVersion = Math.Min(clientDbVersion, serverDbVersion);
|
|
ret = DbAPI.GetStoredProcedureVersion(connection, storedProcedure, maxSPVersion, out storedProcedureVersionToUse);
|
|
|
|
return ret;
|
|
}
|
|
|
|
private static object StoredProcedureLock = new object();
|
|
private static Dictionary<string, List<SPCache>> _spLookup = new Dictionary<string, List<SPCache>>();
|
|
/// <summary>
|
|
/// retrieves what version fo stored procedure to use, allows caching
|
|
/// </summary>
|
|
/// <param name="connection"></param>
|
|
/// <param name="storedProcedure"></param>
|
|
/// <param name="clientDbVersion"></param>
|
|
/// <param name="storedProcedureVersionToUse"></param>
|
|
/// <returns></returns>
|
|
public static ulong GetStoredProcedureToUseCached(IConnectionDetails connection, string storedProcedure, int clientDbVersion, out int storedProcedureVersionToUse)
|
|
{
|
|
//step 1, check if we have a cached mapping of client version, connection version to version to use
|
|
lock (StoredProcedureLock)
|
|
{
|
|
if (_spLookup.ContainsKey(storedProcedure))
|
|
{
|
|
var match = _spLookup[storedProcedure].Find(sp => sp.ClientVersion == clientDbVersion && sp.DbVersion == connection.ConnectionDbVersion);
|
|
if (null != match)
|
|
{
|
|
storedProcedureVersionToUse = match.StoredProcedureVersion;
|
|
return ErrorCodes.ERROR_SUCCESS;
|
|
}
|
|
}
|
|
}
|
|
|
|
//we didn't, so figure out what to use
|
|
storedProcedureVersionToUse = 0;
|
|
|
|
var ret = DbAPI.GetDatabaseVersion(connection, out int serverDbVersion);
|
|
if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
|
|
|
|
var maxSPVersion = Math.Min(clientDbVersion, serverDbVersion);
|
|
ret = DbAPI.GetStoredProcedureVersion(connection, storedProcedure, maxSPVersion, out storedProcedureVersionToUse);
|
|
|
|
if (ErrorCodes.ERROR_SUCCESS == ret)
|
|
{
|
|
//store in the cache for future reference
|
|
lock (StoredProcedureLock)
|
|
{
|
|
if (!_spLookup.ContainsKey(storedProcedure))
|
|
{
|
|
_spLookup[storedProcedure] = new List<SPCache>();
|
|
}
|
|
_spLookup[storedProcedure].Add(new SPCache()
|
|
{ ClientVersion = clientDbVersion, DbVersion = connection.ConnectionDbVersion, StoredProcedureVersion = storedProcedureVersionToUse });
|
|
}
|
|
}
|
|
|
|
return ret;
|
|
}
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="connection"></param>
|
|
/// <param name="serverDbVersion"></param>
|
|
/// <returns></returns>
|
|
public static ulong GetDatabaseVersion(IConnectionDetails connection, out int serverDbVersion)
|
|
{
|
|
serverDbVersion = 0;
|
|
|
|
var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_DbVersionGet");
|
|
if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
|
|
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.Parameters.Add(new SqlParameter("@Version", SqlDbType.Int) { Value = null });
|
|
var reader = cmd.ExecuteReader();
|
|
var dbVersionsList = new List<int>();
|
|
while (reader.Read())
|
|
{
|
|
var version = Convert.ToInt32(reader["Version"]);
|
|
dbVersionsList.Add(version);
|
|
}
|
|
reader.Close();
|
|
|
|
serverDbVersion = dbVersionsList.Max();
|
|
|
|
Logging.LogManager.DBAPILogWriter("Result of using stored procedure sp_DbVersionGet to get current server db version is " + serverDbVersion);
|
|
}
|
|
|
|
catch (Exception ex)
|
|
{
|
|
Logging.LogManager.DBAPILogWriter("Exception while getting database version: " + ex.Message);
|
|
return ErrorCodes.ERROR_UNKNOWN;
|
|
}
|
|
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
|
|
return ErrorCodes.ERROR_SUCCESS;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Since sp_StoredProcedureVersionsGet returns all of the versions of a
|
|
/// stored procedure, less than or equal to the max passed in, this
|
|
/// function returns the maximum of these.
|
|
/// </summary>
|
|
/// <param name="connection"></param>
|
|
/// <param name="storedProcedure"></param>
|
|
/// <param name="maxSPVersion"></param>
|
|
/// <param name="storedProcedureVersionToUse"></param>
|
|
/// <returns></returns>
|
|
public static ulong GetStoredProcedureVersion(IConnectionDetails connection, string storedProcedure, int maxSPVersion, out int storedProcedureVersionToUse)
|
|
{
|
|
storedProcedureVersionToUse = 0;
|
|
|
|
var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_StoredProcedureVersionsGet");
|
|
if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
|
|
|
|
storedProcedureVersionToUse = 0;
|
|
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.Parameters.Add(new SqlParameter("@StoredProcedure", SqlDbType.NVarChar) { Value = storedProcedure });
|
|
cmd.Parameters.Add(new SqlParameter("@Version", SqlDbType.Int) { Value = maxSPVersion });
|
|
var reader = cmd.ExecuteReader();
|
|
var returnVersion = 0;
|
|
var maxVersionFound = 0;
|
|
while (reader.Read())
|
|
{
|
|
var version = Convert.ToInt32(reader["Version"]);
|
|
if (version > maxVersionFound)
|
|
{
|
|
maxVersionFound = version;
|
|
returnVersion = version;
|
|
}
|
|
}
|
|
reader.Close();
|
|
|
|
storedProcedureVersionToUse = returnVersion;
|
|
|
|
Logging.LogManager.DBAPILogWriter($"Result of using stored procedure sp_StoredProcedureVersionsGet to get Stored Procedure version of {storedProcedure} is {storedProcedureVersionToUse}");
|
|
}
|
|
|
|
catch (Exception ex)
|
|
{
|
|
Logging.LogManager.DBAPILogWriter($"Exception while getting Stored Procedure version of {storedProcedure}: {ex.Message}");
|
|
return ErrorCodes.ERROR_UNKNOWN;
|
|
}
|
|
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
|
|
return ErrorCodes.ERROR_SUCCESS;
|
|
}
|
|
|
|
/// <summary>
|
|
/// initializes loggers with given directory, size and log types
|
|
/// </summary>
|
|
/// <param name="logSize"></param>
|
|
/// <param name="path"></param>
|
|
/// <param name="logTypes">the event types that will be logged
|
|
/// This is a BitMask based on TraceEventType
|
|
/// Critical - Bit 0
|
|
/// Error - Bit 1
|
|
/// Warning - Bit 2
|
|
/// Information - Bit 3
|
|
/// Verbose - Bit 4
|
|
/// Start - Bit 8
|
|
/// Stop - Bit 9
|
|
/// Suspend - Bit 10
|
|
/// Resume - Bit 11
|
|
/// Transfer - Bit 12
|
|
/// </param>
|
|
|
|
public static bool _loggerInitialized = false;
|
|
public static void InitializeLogger(int logSize, string path, int logTypes)
|
|
{
|
|
Logging.LogManager.Initialize(logSize, path, logTypes);
|
|
_loggerInitialized = true;
|
|
}
|
|
public static void LogDBCaching(string message)
|
|
{
|
|
Logging.LogManager.Log(System.Diagnostics.TraceEventType.Information, Logging.LogManager.LogEvents.Information, $"{message}");
|
|
}
|
|
private static readonly DbAPI _instance = new DbAPI();
|
|
|
|
private readonly ConnectionManager _connectionManager = new ConnectionManager();
|
|
|
|
/// <summary>
|
|
/// Handles connections functions
|
|
/// <see cref="IConnections"/>
|
|
/// </summary>
|
|
public static IConnections Connections
|
|
{
|
|
get => _instance._connectionManager;
|
|
}
|
|
|
|
private readonly Database.Database _database = new Database.Database();
|
|
/// <summary>
|
|
/// Handles database functions
|
|
/// <see cref="IDatabase"/>
|
|
/// </summary>
|
|
public static IDatabase Database
|
|
{
|
|
get => _instance._database;
|
|
}
|
|
|
|
private readonly DataRecorders _das = new DataRecorders();
|
|
/// <summary>
|
|
/// Handles Data Recorder functions
|
|
/// <see cref="IDataRecorders"/>
|
|
/// </summary>
|
|
public static IDataRecorders DAS
|
|
{
|
|
get => _instance._das;
|
|
}
|
|
private readonly Sensors.Sensors _sensors = new Sensors.Sensors();
|
|
/// <summary>
|
|
/// handles sensor functions
|
|
/// <see cref="ISensors"/>
|
|
/// </summary>
|
|
public static ISensors Sensors
|
|
{
|
|
get => _instance._sensors;
|
|
}
|
|
|
|
private readonly Graphs _graphs = new Graphs();
|
|
/// <summary>
|
|
/// Handles graph functions
|
|
/// <see cref="IGraphs"/>
|
|
/// </summary>
|
|
public static IGraphs Graphs
|
|
{
|
|
get => _instance._graphs;
|
|
}
|
|
|
|
private readonly RegionsOfInterest _regionsOfInterest = new RegionsOfInterest();
|
|
/// <summary>
|
|
/// Handles ROI functions
|
|
/// <see cref="IGraphs"/>
|
|
/// </summary>
|
|
public static IRegionsOfInterest RegionsOfInterest
|
|
{
|
|
get => _instance._regionsOfInterest;
|
|
}
|
|
|
|
private readonly CalculatedChannels _calculatedChannels = new CalculatedChannels();
|
|
/// <summary>
|
|
/// Handles calculated channel functions
|
|
/// </summary>
|
|
public static ICalculatedChannels CalculatedChannels
|
|
{
|
|
get => _instance._calculatedChannels;
|
|
}
|
|
|
|
private readonly TestSetups.TestSetups _testSetups = new TestSetups.TestSetups();
|
|
/// <summary>
|
|
/// handles test setup functions
|
|
/// </summary>
|
|
public static ITestSetups TestSetups
|
|
{
|
|
get => _instance._testSetups;
|
|
}
|
|
|
|
private readonly Tags.Tags _tags = new Tags.Tags();
|
|
/// <summary>
|
|
/// Handles tag functions
|
|
/// </summary>
|
|
public static ITags Tags
|
|
{
|
|
get => _instance._tags;
|
|
}
|
|
|
|
private readonly Channels.Channels _channels = new Channels.Channels();
|
|
/// <summary>
|
|
/// handles channel functions
|
|
/// <see cref="IChannels"/>
|
|
/// </summary>
|
|
public static IChannels Channels
|
|
{
|
|
get => _instance._channels;
|
|
}
|
|
|
|
private readonly GroupHardware.GroupHardware _groupHardware = new GroupHardware.GroupHardware();
|
|
/// <summary>
|
|
/// handles GroupHardware functions
|
|
/// <see cref="IGroupHardware"/>
|
|
/// </summary>
|
|
public static IGroupHardware GroupHardware
|
|
{
|
|
get => _instance._groupHardware;
|
|
}
|
|
|
|
private readonly Groups.Groups _groups = new Groups.Groups();
|
|
/// <summary>
|
|
/// handles group functions
|
|
/// <see cref="IGroups"/>
|
|
/// </summary>
|
|
public static IGroups Groups
|
|
{
|
|
get => _instance._groups;
|
|
}
|
|
|
|
private readonly CustomerDetails.CustomerDetails _customerDetails = new CustomerDetails.CustomerDetails();
|
|
/// <summary>
|
|
/// handles CustomerDetails functions
|
|
/// </summary>
|
|
public static ICustomerDetails CustomerDetails
|
|
{
|
|
get => _instance._customerDetails;
|
|
}
|
|
|
|
private readonly LabratoryDetails.LabratoryDetails _labratoryDetails = new LabratoryDetails.LabratoryDetails();
|
|
/// <summary>
|
|
/// handles LabratoryDetails functions
|
|
/// </summary>
|
|
public static ILabratoryDetails LabratoryDetails
|
|
{
|
|
get => _instance._labratoryDetails;
|
|
}
|
|
|
|
private readonly TestEngineerDetails.TestEngineerDetails _testEngineerDetails = new TestEngineerDetails.TestEngineerDetails();
|
|
/// <summary>
|
|
/// handles TestEngineerDetails functions
|
|
/// </summary>
|
|
public static ITestEngineerDetails TestEngineerDetails
|
|
{
|
|
get => _instance._testEngineerDetails;
|
|
}
|
|
}
|
|
}
|