using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;
using System.Threading;
namespace BrainTechLLC.DAL
{
/// <summary>
/// Support for Ole DB connection to back-end database - uses OleDbDataReader
/// </summary>
/// <typeparam name="T"></typeparam>
public class DALSqlOleDb<T> : IDALDatabase<T> where T : DBLayer<T>, IDBLayer<T>, new()
{
public int SQLExecute(string sqlStatement, string parameterName, object parameterValue, out Exception exOut)
{
OleDbParameter[] sp = new OleDbParameter[1];
sp[0] = new OleDbParameter(parameterName, parameterValue);
sp[0].Direction = ParameterDirection.Input;
return SQLExecute(sqlStatement, sp, out exOut);
}
public int SQLExecute(string sqlStatement, string parameterName, string stringParameterValue, out Exception exOut)
{
OleDbParameter[] sp = new OleDbParameter[1];
sp[0] = new OleDbParameter(parameterName, stringParameterValue);
sp[0].Direction = ParameterDirection.Input;
return SQLExecute(sqlStatement, sp, out exOut);
}
public int SQLExecute(string sqlStatement, out Exception exOut) { return SQLExecute(sqlStatement, (SqlParameter[])null, out exOut); }
/// <summary>
/// Direct access to execute SQL commands (on any table) with a set of OleDb Parameters
/// (or null for no parameters) passed in via parameter "sp"
/// </summary>
/// <param name="sqlStatement">The Command to execute. Can include ? as long as there is a corresponding OleDbParameter passed in "sp"</param>
/// <param name="sp">An array of Sql Parameters that correspond with the ?'s in sqlStatement statement.</param>
/// <param name="exOut"></param>
/// <returns>Number of rows affected by the Execute statement. If 0, check exOut for any exceptions that might have occurred</returns>
public int SQLExecute(string sqlStatement, IDbDataParameter[] sqlParameters, out Exception exOut)
{
OleDbParameter[] oleDBParams = null;
if (sqlParameters != null)
{
oleDBParams = new OleDbParameter[sqlParameters.Length];
for (int i = 0; i < sqlParameters.Length; i++)
{
oleDBParams[i] = new OleDbParameter(sqlParameters[i].ParameterName, sqlParameters[i].Value);
}
}
exOut = null; int nRetVal = 0;
using (OleDbConnection connection = new OleDbConnection(DBLayer<T>._connectionString))
{
if (sqlStatement.Contains(" * ")) sqlStatement = sqlStatement.Replace(" * ", " ");
OleDbCommand objCmd = new OleDbCommand(sqlStatement, connection);
if (oleDBParams != null) { objCmd.Parameters.AddRange(oleDBParams); }
try
{
connection.Open();
}
catch (Exception ex)
{
#if LoggingOn
DALQueryInfo.AddMessage("SQLOLEDbExecute: " + sqlStatement + ", Error: " + ex.ToString());
#endif
exOut = ex; Debug.WriteLine("SQL Query: " + sqlStatement + ", Error: " + ex.ToString());
return nRetVal;
}
try
{
#if LoggingOn
DALQueryInfo.AddMessage("DB EXECUTE NONQUERY: " + sqlStatement);
#endif
//Debug.WriteLine(sqlStatement);
nRetVal = objCmd.ExecuteNonQuery();
}
catch (OleDbException exs) { exOut = exs; DALQueryInfo.AddMessage("SQLOLEDbExecute Query: " + sqlStatement + ", Error: " + exs.ToString()); Debug.WriteLine("SQLOLEDbExecute Query: " + sqlStatement + ", Error: " + exs.ToString()); }
catch (Exception ex) { exOut = ex; DALQueryInfo.AddMessage("SQLOLEDbExecute Query: " + sqlStatement + ", Error: " + ex.ToString()); Debug.WriteLine("SQLOLEDbExecute Query: " + sqlStatement + ", Error: " + ex.ToString()); }
finally
{
connection.Close();
}
}
return nRetVal;
}
public List<T> SQLLoadList(string sqlStatement)
{
Exception exOut;
string message;
ResultSet<T> res = SQLLoadRows(sqlStatement, null, null, out exOut, out message);
return (res == null) ? null : new List<T>(res.BusinessObjectList);
}
public DataSet SQLLoadDataSet(string sqlStatement)
{
Exception exOut;
string message;
ResultSet<T> res = SQLLoadRows(sqlStatement, null, null, out exOut, out message);
return (res == null) ? null : res;
}
public DataSet SQLLoadList(string sqlStatement, out Exception exOut, out string message)
{
ResultSet<T> res = SQLLoadRows(sqlStatement, null, null, out exOut, out message);
return (res == null) ? null : res;
}
public ResultSet<T> SQLLoadRows(string sqlStatement, SelectionSet selectionSet, out Exception exOut, out string message)
{
return SQLLoadRows(sqlStatement, selectionSet, (IDbDataParameter[])null, out exOut, out message);
}
public ResultSet<T> SQLLoadResultSet(string sqlStatement, out Exception exOut, out string message)
{
ResultSet<T> res = SQLLoadRows(sqlStatement, null, null, out exOut, out message);
return res;
}
/// <summary>
/// Heart of the OleDb Query functionality
/// </summary>
/// <param name="sqlStatement">SQL statement to execute</param>
/// <param name="selectionSet">The set of selected columns (can be defined - i.e. COUNT(*) AS CNT, MAX(ID) as MAXID</param>
/// <param name="sqlParameters">Support for OleDb parameters</param>
/// <param name="exOut"></param>
/// <param name="message"></param>
/// <returns></returns>
public ResultSet<T> SQLLoadRows(string sqlStatement, SelectionSet selectionSet, IDbDataParameter[] sqlParameters, out Exception exOut, out string message)
{
OleDbParameter[] oleDBParams = null;
if (sqlParameters != null)
{
oleDBParams = new OleDbParameter[sqlParameters.Length];
for (int i = 0; i < sqlParameters.Length; i++)
{
oleDBParams[i] = new OleDbParameter(sqlParameters[i].ParameterName, sqlParameters[i].Value);
}
}
message = ""; exOut = null;
int nPrefix = sqlStatement.IndexOf("WHERE", StringComparison.OrdinalIgnoreCase);
string sPrefix;
if (nPrefix < 0)
{
sPrefix = sqlStatement;
}
else
{
sPrefix = sqlStatement.Substring(0, nPrefix - 1);
}
ResultSet<T> ret = new ResultSet<T>();
DBLayer<T>.CheckColNames();
Dictionary<string, PropertyDescriptor> columnNamesAndProperties;
StringBuilder sb = new StringBuilder(256);
bool wantAddToCache = false;
if (!DBLayer<T>._propertyLookup.TryGetValue(sPrefix, out columnNamesAndProperties))
{
columnNamesAndProperties = new Dictionary<string, PropertyDescriptor>(DBLayer<T>._propertyCount * 2);
wantAddToCache = true;
}
if (wantAddToCache)
{
for (int i = 0; i < DBLayer<T>._propertyCount; i++)
{
string databaseColumnName = DBLayer<T>._columnNames[i];
if (!string.IsNullOrEmpty(databaseColumnName))
{
columnNamesAndProperties.Add(databaseColumnName, DBLayer<T>._propertyDescriptors[i]);
}
}
try { DBLayer<T>._propertyLookup.Add(sPrefix, columnNamesAndProperties); }
catch { }
}
if (DBLayer<T>.CacheAndPerformance._trackConcurrentDBQueries)
DBLayer<T>.CacheAndPerformance._currentSimultaneousDBQueries++;
PropertyDescriptor pi;
// using ensures disposal of the connection object after exiting the using() scope
using (OleDbConnection connection = new OleDbConnection(DBLayer<T>._connectionString))
{
OleDbCommand objCmd = new OleDbCommand(sqlStatement, connection);
try
{
// support for OleDb parameters (uses ? to denote placeholder)
if (oleDBParams != null)
{
objCmd.Parameters.AddRange(oleDBParams);
}
try
{
connection.Open();
OleDbDataReader sqlread = objCmd.ExecuteReader();
if (sqlread.HasRows)
{
#if LoggingOn
DALQueryInfo.AddMessage("FROM DB: " + sqlStatement);
#endif
// Read in the first row and gather field types, field names, and match up with
// class properties (cached in the future)
sqlread.Read();
int nFields = sqlread.FieldCount;
bool fNew = false;
string[] fieldNames;
PropertyDescriptor[] propertiesForDatabaseColumns;
object[] readValues = new object[nFields];
Type[] propertyTypesForColumns;
Type[] fieldTypes;
if (!DBLayer<T>._columnNameLookup.TryGetValue(sPrefix, out ret._columnNames))
{
ret.ColumnNames = new string[nFields];
fNew = true;
}
if (!DBLayer<T>._propertyFieldNameLookup.TryGetValue(sPrefix, out ret._retrieved))
{
fNew = true;
}
if (!DBLayer<T>._propertyTypesForDBColsLookup.TryGetValue(sPrefix, out propertyTypesForColumns))
{
propertyTypesForColumns = new Type[nFields];
fNew = true;
}
if (!DBLayer<T>._fieldNameLookup.TryGetValue(sPrefix, out fieldNames))
{
fieldNames = new string[nFields];
fNew = true;
}
if (!DBLayer<T>._propsForDBColsLookup.TryGetValue(sPrefix, out propertiesForDatabaseColumns))
{
propertiesForDatabaseColumns = new PropertyDescriptor[nFields];
fNew = true;
}
if (!DBLayer<T>._fieldTypeLookup.TryGetValue(sPrefix, out fieldTypes))
{
fieldTypes = new Type[nFields];
fNew = true;
}
if (fNew)
{
for (int n = 0; n < nFields; n++)
{
fieldNames[n] = sqlread.GetName(n);
fieldTypes[n] = sqlread.GetFieldType(n);
if (columnNamesAndProperties.TryGetValue(fieldNames[n], out pi))
{
propertiesForDatabaseColumns[n] = pi;
ret.ColumnNames[n] = pi.Name;
ret.Retrieved[pi.Name] = fieldNames[n];
propertyTypesForColumns[n] = pi.PropertyType;
}
else
{
pi = DBLayer<T>.CachedAttributes.CheckExtraColumns(fieldNames[n]);
if (pi != null)
{
ret.Retrieved[pi.Name] = fieldNames[n];
ret.ColumnNames[n] = pi.Name;
propertiesForDatabaseColumns[n] = pi;
propertyTypesForColumns[n] = pi.PropertyType;
}
}
}
try { DBLayer<T>._columnNameLookup.Add(sPrefix, ret.ColumnNames); }
catch { }
try { DBLayer<T>._propertyFieldNameLookup.Add(sPrefix, ret.Retrieved); }
catch { }
try { DBLayer<T>._propertyTypesForDBColsLookup.Add(sPrefix, propertyTypesForColumns); }
catch { }
try { DBLayer<T>._fieldNameLookup.Add(sPrefix, fieldNames); }
catch { }
try { DBLayer<T>._propsForDBColsLookup.Add(sPrefix, propertiesForDatabaseColumns); }
catch { }
try { DBLayer<T>._fieldTypeLookup.Add(sPrefix, fieldTypes); }
catch { }
}
int nPath = -1;
do
{
// Create a new instance of class T
T newClass = new T();
newClass._isNewInstance = false;
newClass._modified = true;
newClass._doNotRaisePropChanged = true;
AdditionalResultRow<T> additionalResultRow = null;
// Read values from the database
sqlread.GetValues(readValues);
if (nPath == 1)
{
for (int n = 0; n < nFields; n++)
{
DBLayer<T>.FillInValWithResult(newClass, readValues[n], fieldTypes[n], propertiesForDatabaseColumns[n], propertyTypesForColumns[n], sb);
}
}
else if (nPath == 0)
{
for (int n = 0; n < nFields; n++)
{
// If the returned value matches a Property, fill in the returned value
if (propertiesForDatabaseColumns[n] != null) { DBLayer<T>.FillInValWithResult(newClass, readValues[n], fieldTypes[n], propertiesForDatabaseColumns[n], propertyTypesForColumns[n], sb); }
else
{
// otherwise, return the value in the Additional Results structure
if (additionalResultRow == null) { additionalResultRow = new AdditionalResultRow<T>(additionalResultRow); }
AdditionalResult additionalResult = new AdditionalResult();
additionalResult.ResultName = fieldNames[n];
additionalResult.ResultType = fieldTypes[n];
additionalResult.ResultObject = readValues[n];
additionalResultRow.Results.Add(additionalResult);
}
}
}
else
{
for (int n = 0; n < nFields; n++)
{
// If the returned value matches a Property, fill in the returned value
if (propertiesForDatabaseColumns[n] != null)
{
if (nPath == -1) { nPath = 1; }
DBLayer<T>.FillInValWithResult(newClass, readValues[n], fieldTypes[n], propertiesForDatabaseColumns[n], propertyTypesForColumns[n], sb);
}
else
{
nPath = 0;
// otherwise, return the value in the Additional Results structure
if (additionalResultRow == null) { additionalResultRow = new AdditionalResultRow<T>(additionalResultRow); }
AdditionalResult additionalResult = new AdditionalResult();
additionalResult.ResultName = fieldNames[n];
additionalResult.ResultType = fieldTypes[n];
additionalResult.ResultObject = readValues[n];
additionalResultRow.Results.Add(additionalResult);
}
}
}
newClass._modified = false;
if (DBLayer<T>.CacheAndPerformance._enableObjectConservation)
{
long unique = DBLayer<T>.GetUniqueValue(newClass);
T foundClass = null;
lock (DBLayer<T>.CacheAndPerformance._objectConservationLock)
{
if (DBLayer<T>.CacheAndPerformance._objectConservationLookup.TryGetValue(unique, out foundClass))
{
newClass = foundClass;
}
else
{
if (DBLayer<T>.CacheAndPerformance._objectConservationLookup.Count > DBLayer<T>.CacheAndPerformance._maxUniqueObjectCountForConservation)
{
DBLayer<T>.CacheAndPerformance._objectConservationLookup.Clear();
}
DBLayer<T>.CacheAndPerformance._objectConservationLookup.Add(unique, newClass);
}
}
}
ResultRow<T> result = new ResultRow<T>(ret, (T)newClass);
newClass._doNotRaisePropChanged = DBLayer<T>._noPropertyChangeTracking;
if (additionalResultRow != null) { result.AdditionalResults = additionalResultRow; }
ret._cachedResultSet.Add(result);
}
while (sqlread.Read()); // per row
ret._selectionSet = selectionSet;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
#if LoggingOn
DALQueryInfo.AddMessage("OleDB Query failed: " + sqlStatement + " : " + ex.ToString());
#endif
}
finally
{
connection.Close();
if (DBLayer<T>.CacheAndPerformance._trackConcurrentDBQueries)
DBLayer<T>.CacheAndPerformance._currentSimultaneousDBQueries--;
}
}
catch (OleDbException ex) { exOut = ex; DALQueryInfo.AddMessage("OleDB Exception - Query failed: " + sqlStatement + " : " + ex.ToString()); Debug.Assert(false, ex.ToString()); }
catch (Exception ex) { exOut = ex; DALQueryInfo.AddMessage("OleDB Query failed: " + sqlStatement + " : " + ex.ToString()); Debug.Assert(false, ex.ToString()); }
}
return ret;
}
}
}
Submit an article or tip