public static bool Connect(
string key,
Credentials credentials,
DataAccessTools.ConnectMode connectMode,
DataAccessTools.LogMode logMode,
ref DataAccessTools tools)
{
if (tools == null)
{
return(false);
}
bool rt;
try
{
//Initialize tools / connect
//tools.AuxLogger = auxLogger;
rt = tools.Initialize(credentials,
connectMode, key);
}
catch (Exception eX)
{
throw new ApplicationException(
"Could not create connection to " +
credentials.DBSchema + " @ " +
credentials.DBHost + ":" +
credentials.DBPort + ". Exception thrown: " +
eX);
}
return(rt);
}
/// <summary>
/// Reconnects the application to the database
/// </summary>
/// <param name="tools"></param>
public static void Reconnect(ref DataAccessTools tools)
{
if (tools == null)
{
return;
}
tools.Reconnect();
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public static DataAccessTools CreateDataAccessTools()
{
//Allocate tools object
var rt = new DataAccessTools();
//Return initialized object
return(rt);
}
/// <summary>
///
/// </summary>
/// <param name="procSchema"></param>
/// <param name="procPackage"></param>
/// <param name="procStoredProc"></param>
/// <param name="inputParameters"></param>
/// <param name="outputParameters"></param>
/// <param name="refCursorParamNames"></param>
/// <param name="errorNumberParamName"></param>
/// <param name="errorDescParamName"></param>
/// <param name="key"></param>
/// <param name="errorNumber"></param>
/// <param name="errorDesc"></param>
/// <param name="datasetOutput"></param>
/// <param name="tools"></param>
/// <returns></returns>
public static bool ExecuteStoredProc(
string procSchema,
string procPackage,
string procStoredProc,
List <OracleProcParam> inputParameters,
List <TupleType <string, DataTypeConstants.PawnDataType, int> > outputParameters,
List <PairType <string, string> > refCursorParamNames,
string errorNumberParamName,
string errorDescParamName,
string key,
out string errorNumber,
out string errorDesc,
out DataSetOutput datasetOutput,
ref DataAccessTools tools)
{
datasetOutput = null;
errorNumber = string.Empty;
errorDesc = string.Empty;
if (tools == null)
{
return(false);
}
if (string.IsNullOrEmpty(procSchema) ||
string.IsNullOrEmpty(procPackage) ||
string.IsNullOrEmpty(procStoredProc))
{
return(false);
}
DataSet dSet;
if (!tools.ExecuteStoredProc(procSchema, procPackage, procStoredProc,
inputParameters, outputParameters, refCursorParamNames,
errorNumberParamName,
errorDescParamName,
key,
out errorNumber,
out errorDesc,
out dSet))
{
return(false);
}
//Allocate and initialize data return set
datasetOutput = new DataSetOutput(dSet);
//Return true
return(true);
}
/// <summary>
/// Rolls back any changes made in a non-committed transaction block
/// </summary>
/// <param name="key"></param>
/// <param name="name"></param>
/// <param name="tools"></param>
/// <returns></returns>
public static bool RollbackTransactionBlock(string key, string name, ref DataAccessTools tools)
{
if (string.IsNullOrEmpty(key) || tools == null)
{
return(false);
}
name = name ?? "NoName";
tools.log(LogLevel.INFO, "Rolling back {0} transaction block with key {1}", name, key);
if (!tools.rollbackTransactionBlock(key))
{
tools.log(LogLevel.ERROR, "Could not rollback {0} transaction block with key {1}", name, key);
return(false);
}
return(true);
}
/// <summary>
/// Starts a transaction block
/// </summary>
/// <param name="key"></param>
/// <param name="name"></param>
/// <param name="tools"></param>
/// <returns></returns>
public static bool StartTransactionBlock(string key, string name, ref DataAccessTools tools)
{
if (string.IsNullOrEmpty(key) || tools == null)
{
return(false);
}
name = name ?? "NoName";
tools.log(LogLevel.INFO, "Starting {0} transaction block with key {1}", name, key);
if (!tools.startTransactionBlock(key))
{
tools.log(LogLevel.WARN, "Could not start {0} transaction block with key {1}", name, key);
return(false);
}
return(true);
}
/// <summary>
/// Retrieves a .NET standard DateTime from a various assortment of oracle date time types
/// </summary>
/// <param name="format"></param>
/// <param name="date">Input date object</param>
/// <param name="tools"></param>
/// <returns>Returns valid DateTime if successful, otherwise DateTime.MinValue</returns>
public static DateTime GetDateTimeFromOracleObject(
DataAccessTools.OracleDateTimeFormat format,
object date,
ref DataAccessTools tools)
{
if (date == null || tools == null)
{
return(DateTime.MinValue);
}
DateTime dateTime;
if (!tools.ConvertToDateTimeFromOracleInternal(format, date, out dateTime))
{
return(DateTime.MinValue);
}
return(dateTime);
}
/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <param name="tools"></param>
/// <param name="vars">
/// The zero index and even indices are param names
/// and the Odd indices are param values
/// </param>
/// <returns></returns>
public static string PrepareVariableQuery(
ref DataAccessTools tools,
string query,
params PairType <string, string>[] vars)
{
tools.log(LogLevel.DEBUG, "DataAccessTools::PrepareVariableQuery");
if (string.IsNullOrEmpty(query))
{
tools.log(LogLevel.ERROR, "- Query is invalid");
return(string.Empty);
}
//Must be valid and not empty);
if (vars == null || vars.Length <= 0)
{
tools.log(LogLevel.ERROR, "- No variables provided for query");
return(string.Empty);
}
tools.log(LogLevel.DEBUG, "- {0} variables provided", vars.Length);
//Perform string replacement operations
foreach (var p in vars)
{
if (p == null || string.IsNullOrEmpty(p.Left))
{
tools.log(LogLevel.ERROR, "-- Found null variable name");
return(string.Empty);
}
var paramName = PARAM_SEP + p.Left + PARAM_SEP;
//Null is valid in SQL, so we must allow for native null in pair right values
var paramValue = p.Right ?? "null";
tools.log(LogLevel.DEBUG, "-- {0} => {1} specified", paramName, paramValue);
try
{
query = query.Replace(paramName, paramValue);
}
catch (Exception eX)
{
tools.log(LogLevel.ERROR, "-- Exception thrown when performing replacement: {0}", eX);
return(string.Empty);
}
}
return(query);
}
/// <summary>
///
/// </summary>
/// <param name="dataTableOnly"></param>
/// <param name="query"></param>
/// <param name="tableName"></param>
/// <param name="key"></param>
/// <param name="returnSet"></param>
/// <param name="tools"></param>
/// <returns></returns>
public static bool ExecuteQuery(
bool dataTableOnly,
string query,
string tableName,
string key,
out DataReturnSet returnSet,
ref DataAccessTools tools)
{
returnSet = null;
if (tools == null)
{
return(false);
}
if (string.IsNullOrEmpty(query))
{
return(false);
}
Dictionary <string, int> columns;
List <List <object> > data;
DataTable dTable;
if (!tools.ExecuteQuery(dataTableOnly, query, tableName, key,
out columns, out data, out dTable))
{
return(false);
}
if (data != null)
{
//Create return set
if (!dataTableOnly)
{
returnSet = new DataReturnSet(columns, data);
}
else
{
returnSet = new DataReturnSet(dTable);
}
}
//Return true
return(true);
}
/// <summary>
/// Executes any form of a query other than a select (insert,update,delete,merge)
/// </summary>
/// <param name="query"></param>
/// <param name="key"></param>
/// <param name="tools"></param>
/// <returns></returns>
public static bool ExecuteInsertUpdateDeleteQuery(
string query,
string key,
ref DataAccessTools tools)
{
if (string.IsNullOrEmpty(query) || string.IsNullOrEmpty(key) || tools == null)
{
return(false);
}
tools.log(LogLevel.INFO, "Executing query {0}", query);
if (!tools.ExecuteInsertUpdateDeleteQuery(query, key))
{
tools.log(LogLevel.WARN, "Could not execute query {0}", query);
return(false);
}
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="dataTableOnly"></param>
/// <param name="query"></param>
/// <param name="tableName"></param>
/// <param name="key"></param>
/// <param name="returnSet"></param>
/// <param name="tools"></param>
/// <param name="vars">
/// The zero index and even indices are param names
/// and the Odd indices are param values
/// </param>
/// <returns></returns>
public static bool ExecuteVariableQuery(
bool dataTableOnly,
string query,
string tableName,
string key,
out DataReturnSet returnSet,
ref DataAccessTools tools,
params PairType <string, string>[] vars)
{
returnSet = null;
if (tools == null)
{
return(false);
}
tools.log(LogLevel.DEBUG, "DataAccessTools::ExecuteVariableQuery");
if (string.IsNullOrEmpty(query))
{
tools.log(LogLevel.ERROR, "- Query is invalid");
return(false);
}
tools.log(LogLevel.DEBUG, "- Executing query: {0}", query);
Dictionary <string, int> columns;
List <List <object> > data;
DataTable dTable;
//Prepare variable query
string finalQuery = PrepareVariableQuery(ref tools, query, vars);
if (string.IsNullOrEmpty(finalQuery))
{
tools.log(LogLevel.ERROR, "- Variable query could not be generated");
return(false);
}
//Log transformed query
tools.log(LogLevel.INFO, "Transformed query: {0}", finalQuery);
//Execute variable query
if (!tools.ExecuteQuery(dataTableOnly, finalQuery, tableName, key,
out columns, out data, out dTable))
{
return(false);
}
if (data != null)
{
//Create return set
if (!dataTableOnly)
{
returnSet = new DataReturnSet(columns, data);
}
else
{
returnSet = new DataReturnSet(dTable);
}
}
//Return true
return(true);
}