public IList <TObject> SelectObjects <TObject>(string whereExpression, Transaction.IsolationLevel isolation)
where TObject : DataObject
{
var dataObjects = SelectObjectsImpl <TObject>(whereExpression, isolation);
return(dataObjects ?? new List <TObject>());
}
public IList <TObject> SelectAllObjects <TObject>(Transaction.IsolationLevel isolation)
where TObject : DataObject
{
var dataObjects = SelectAllObjectsImpl <TObject>(isolation);
return(dataObjects ?? new List <TObject>());
}
/// <summary>
/// Selects a single object, if more than
/// one exist, the first is returned
/// </summary>
/// <typeparam name="TObject"></typeparam>
/// <param name="whereExpression"></param>
/// <param name="isolation"></param>
/// <returns></returns>
public TObject SelectObject <TObject>(string whereExpression, Transaction.IsolationLevel isolation)
where TObject : DataObject
{
var objs = SelectObjects <TObject>(whereExpression, isolation);
if (objs.Count > 0)
{
return(objs[0]);
}
return(null);
}
/// <summary>
/// Selects all objects from a given table in the database.
/// This is SLOW SLOW SLOW. You should cache these results rather than reading from the XML file each time.
/// </summary>
/// <param name="objectType">the type of objects to retrieve</param>
/// <returns>an array of <see cref="DataObject" /> instances representing the selected objects</returns>
protected override IList <TObject> SelectAllObjectsImpl <TObject>(Transaction.IsolationLevel isolation)
{
string tableName = DataObject.GetTableName(typeof(TObject));
if (Log.IsDebugEnabled)
{
Log.Debug("1. select all " + tableName);
}
DataSet ds = GetDataSet(tableName);
if (ds != null)
{
Connection.LoadDataSet(tableName, ds);
DataTable table = ds.Tables[tableName];
DataRow[] rows = table.Select();
int count = rows.Length;
//Create an array of our destination objects
var objs = new List <TObject>(count);
for (int i = 0; i < count; i++)
{
var remap = (TObject)(Activator.CreateInstance(typeof(TObject)));
FillObjectWithRow(ref remap, rows[i], false);
objs.Add(remap);
}
if (Log.IsDebugEnabled)
{
Log.Debug("2. select all " + tableName);
}
return(objs);
}
return(new List <TObject>());
}
/// <summary> /// Selects objects from a given table in the database based on a given set of criteria. (where clause) /// </summary> /// <param name="objectType"></param> /// <param name="whereClause"></param> /// <param name="isolation"></param> /// <returns></returns> protected abstract DataObject[] SelectObjectsImpl(Type objectType, string whereClause, Transaction.IsolationLevel isolation);
/// <summary>
/// Selects objects from a given table in the database based on a given set of criteria. (where clause)
/// This is SLOW SLOW SLOW. You should cache these results rather than reading from the XML file each time.
/// </summary>
/// <param name="objectType">the type of objects to retrieve</param>
/// <param name="whereClause">the where clause to filter object selection on</param>
/// <returns>an array of <see cref="DataObject" /> instances representing the selected objects that matched the given criteria</returns>
protected override IList <TObject> SelectObjectsImpl <TObject>(string statement, Transaction.IsolationLevel isolation)
{
string tableName = GetTableOrViewName(typeof(TObject));
if (Log.IsDebugEnabled)
{
Log.Debug("1. select objects " + tableName + " " + statement);
}
DataSet ds = GetDataSet(tableName);
if (ds != null)
{
Connection.LoadDataSet(tableName, ds);
DataTable table = ds.Tables[tableName];
if (Log.IsDebugEnabled)
{
Log.Debug("2. " + tableName + " " + statement);
}
DataRow[] rows = table.Select(statement);
if (Log.IsDebugEnabled)
{
Log.Debug("3. " + tableName + " " + statement);
}
int count = rows.Length;
var objs = new List <TObject>(count);
for (int i = 0; i < count; i++)
{
var remap = (TObject)(Activator.CreateInstance(typeof(TObject)));
FillObjectWithRow(ref remap, rows[i], false);
objs.Add(remap);
}
if (Log.IsDebugEnabled)
{
Log.Debug("4. select objects " + tableName + " " + statement);
}
return(objs);
}
return(new List <TObject>());
}
/// <summary>
/// Raw SQL Select Implementation with Parameters for Single Prepared Query
/// </summary>
/// <param name="SQLCommand">Command for reading</param>
/// <param name="parameter">Collection of Parameters for Single Read</param>
/// <param name="Reader">Reader Method</param>
/// <param name="Isolation">Transaction Isolation</param>
protected void ExecuteSelectImpl(string SQLCommand, IEnumerable <QueryParameter> parameter, Action <IDataReader> Reader, Transaction.IsolationLevel Isolation)
{
ExecuteSelectImpl(SQLCommand, new [] { parameter }, Reader, Isolation);
}
/// <summary>
/// Retrieve a Collection of DataObjects Sets from database filtered by Parametrized Where Expression
/// </summary>
/// <param name="tableHandler">Table Handler for these DataObjects</param>
/// <param name="whereExpression">Parametrized Where Expression</param>
/// <param name="parameters">Parameters for filtering</param>
/// <param name="isolation">Isolation Level</param>
/// <returns>Collection of DataObjects Sets matching Parametrized Where Expression</returns>
protected override IList <IList <DataObject> > SelectObjectsImpl(DataTableHandler tableHandler, string whereExpression, IEnumerable <IEnumerable <QueryParameter> > parameters, Transaction.IsolationLevel isolation)
{
var columns = tableHandler.FieldElementBindings.ToArray();
string command = null;
if (!string.IsNullOrEmpty(whereExpression))
{
command = string.Format("SELECT {0} FROM `{1}` WHERE {2}",
string.Join(", ", columns.Select(col => string.Format("`{0}`", col.ColumnName))),
tableHandler.TableName,
whereExpression);
}
else
{
command = string.Format("SELECT {0} FROM `{1}`",
string.Join(", ", columns.Select(col => string.Format("`{0}`", col.ColumnName))),
tableHandler.TableName);
}
var primary = columns.FirstOrDefault(col => col.PrimaryKey != null);
var dataObjects = new List <IList <DataObject> >();
ExecuteSelectImpl(command, parameters, reader => {
var list = new List <DataObject>();
var data = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(data);
var obj = Activator.CreateInstance(tableHandler.ObjectType) as DataObject;
// Fill Object
var current = 0;
foreach (var column in columns)
{
DatabaseSetValue(obj, column, data[current]);
current++;
}
// Set Primary Key
if (primary != null)
{
obj.ObjectId = primary.GetValue(obj).ToString();
}
list.Add(obj);
obj.Dirty = false;
obj.IsPersisted = true;
}
dataObjects.Add(list.ToArray());
}, isolation);
return(dataObjects.ToArray());
}
protected void ExecuteSelectImpl(string SQLCommand, Action <IDataReader> Reader, Transaction.IsolationLevel Isolation)
=> ExecuteSelectImpl(SQLCommand, new [] { Array.Empty <QueryParameter>() }, Reader);
/// <summary>
/// Retrieve a Collection of DataObjects Sets from database filtered by Parametrized Where Expression
/// </summary>
/// <param name="tableHandler">Table Handler for these DataObjects</param>
/// <param name="whereExpression">Parametrized Where Expression</param>
/// <param name="parameters">Parameters for filtering</param>
/// <param name="isolation">Isolation Level</param>
/// <returns>Collection of DataObjects Sets matching Parametrized Where Expression</returns>
protected override IList <IList <DataObject> > SelectObjectsImpl(DataTableHandler tableHandler, string whereExpression, IEnumerable <IEnumerable <QueryParameter> > parameters, Transaction.IsolationLevel isolation)
{
var columns = tableHandler.FieldElementBindings.ToArray();
string command = null;
if (!string.IsNullOrEmpty(whereExpression))
{
command = string.Format("SELECT {0} FROM `{1}` WHERE {2}",
string.Join(", ", columns.Select(col => string.Format("`{0}`", col.ColumnName))),
tableHandler.TableName,
whereExpression);
}
else
{
command = string.Format("SELECT {0} FROM `{1}`",
string.Join(", ", columns.Select(col => string.Format("`{0}`", col.ColumnName))),
tableHandler.TableName);
}
var primary = columns.FirstOrDefault(col => col.PrimaryKey != null);
var dataObjects = new List <IList <DataObject> >();
ExecuteSelectImpl(command, parameters, reader => FillQueryResultList(reader, tableHandler, columns, primary, dataObjects));
return(dataObjects.ToArray());
}
public IList <TObject> SelectAllObjects <TObject>(Transaction.IsolationLevel isolation)
where TObject : DataObject
{
return(SelectAllObjects <TObject>());
}
public IList <TObject> SelectObjects <TObject>(string whereExpression, Transaction.IsolationLevel isolation)
where TObject : DataObject
{
return(SelectObjects <TObject>(whereExpression, new [] { new QueryParameter[] { } }).First().ToArray());
}
public TObject SelectObject <TObject>(string whereExpression, Transaction.IsolationLevel isolation)
where TObject : DataObject
{
return(SelectObjects <TObject>(whereExpression, isolation).FirstOrDefault());
}
/// <summary> /// Retrieve a Collection of DataObjects Sets from database filtered by Parametrized Where Expression /// </summary> /// <param name="tableHandler">Table Handler for these DataObjects</param> /// <param name="whereExpression">Parametrized Where Expression</param> /// <param name="parameters">Parameters for filtering</param> /// <param name="isolation">Isolation Level</param> /// <returns>Collection of DataObjects Sets matching Parametrized Where Expression</returns> protected abstract IList <IList <DataObject> > SelectObjectsImpl(DataTableHandler tableHandler, string whereExpression, IEnumerable <IEnumerable <QueryParameter> > parameters, Transaction.IsolationLevel isolation);
/// <summary>
/// Selects objects from a given table in the database based on a given set of criteria. (where clause)
/// </summary>
/// <typeparam name="TObject">the type of objects to retrieve</typeparam>
/// <param name="whereClause">the where clause to filter object selection on</param>
/// <returns>an array of <see cref="DataObject" /> instances representing the selected objects that matched the given criteria</returns>
/// <returns></returns>
protected abstract IList <TObject> SelectObjectsImpl <TObject>(string whereClause, Transaction.IsolationLevel isolation)
where TObject : DataObject;
protected void ExecuteSelectImpl(string SQLCommand, QueryParameter param, Action <IDataReader> Reader, Transaction.IsolationLevel Isolation)
=> ExecuteSelectImpl(SQLCommand, new [] { new [] { param } }, Reader);
/// <summary>
/// Selects all objects from a given table in the database.
/// </summary>
/// <typeparam name="TObject">the type of objects to retrieve</typeparam>
/// <returns>an array of <see cref="DataObject" /> instances representing the selected objects</returns>
/// <returns></returns>
protected abstract IList <TObject> SelectAllObjectsImpl <TObject>(Transaction.IsolationLevel isolation)
where TObject : DataObject;
/// <summary>
/// Selects objects from a given table in the database based on a given set of criteria. (where clause)
/// </summary>
/// <param name="objectType">the type of objects to retrieve</param>
/// <param name="whereClause">the where clause to filter object selection on</param>
/// <returns>an array of <see cref="DataObject" /> instances representing the selected objects that matched the given criteria</returns>
protected override IList <TObject> SelectObjectsImpl <TObject>(string whereClause, Transaction.IsolationLevel isolation)
{
string tableName = GetTableOrViewName(typeof(TObject));
var dataObjects = new List <TObject>(64);
// build sql command
var sb = new StringBuilder("");
bool first = true;
bool usePrimaryKey = false;
BindingInfo[] bindingInfo = GetBindingInfo(typeof(TObject));
for (int i = 0; i < bindingInfo.Length; i++)
{
if (!bindingInfo[i].HasRelation)
{
if (!first)
{
sb.Append(", ");
}
else
{
first = false;
}
sb.Append("`" + bindingInfo[i].Member.Name + "`");
if (bindingInfo[i].UsePrimaryKey)
{
usePrimaryKey = true;
}
}
}
if (usePrimaryKey)
{
sb.Insert(0, "SELECT ");
}
else
{
sb.Insert(0, "SELECT `" + tableName + "_ID`, ");
}
sb.Append(" FROM `" + tableName + "`");
if (whereClause != null && whereClause.Trim().Length > 0)
{
sb.Append(" WHERE " + whereClause);
}
string sql = sb.ToString();
if (Log.IsDebugEnabled)
{
Log.Debug("IList<TObject> SelectObjectsImpl: " + sql);
}
// read data and fill objects
Connection.ExecuteSelect(sql, delegate(IDataReader reader)
{
var data = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(data);
var obj = Activator.CreateInstance(typeof(TObject)) as TObject;
int field = 0;
if (usePrimaryKey == false)
{
// fill the silly TableName_ID field
obj.ObjectId = (string)data[0];
field = 1;
}
bool hasRelations = false;
// we can use hard index access because we iterate the same order here
for (int i = 0; i < bindingInfo.Length; i++)
{
BindingInfo bind = bindingInfo[i];
if (!hasRelations)
{
hasRelations = bind.HasRelation;
}
if (!bind.HasRelation)
{
object val = data[field++];
if (val != null && !val.GetType().IsInstanceOfType(DBNull.Value))
{
if (bind.Member is PropertyInfo)
{
Type type = ((PropertyInfo)bind.Member).PropertyType;
try
{
if (type == typeof(bool))
{
// special handling for bool
((PropertyInfo)bind.Member).SetValue(obj,
(val.ToString() == "0") ? false : true,
null);
}
else if (type == typeof(DateTime))
{
// special handling for datetime
if (val is MySqlDateTime)
{
((PropertyInfo)bind.Member).SetValue(obj,
((MySqlDateTime)val).GetDateTime(),
null);
}
else
{
((PropertyInfo)bind.Member).SetValue(obj,
((IConvertible)val).ToDateTime(null),
null);
}
}
else
{
((PropertyInfo)bind.Member).SetValue(obj, val, null);
}
}
catch (Exception e)
{
if (Log.IsErrorEnabled)
{
Log.Error(
tableName + ": " + bind.Member.Name + " = " + val.GetType().FullName +
" doesnt fit to " + bind.Member.DeclaringType.FullName, e);
}
continue;
}
}
else if (bind.Member is FieldInfo)
{
((FieldInfo)bind.Member).SetValue(obj, val);
}
}
}
}
dataObjects.Add(obj);
obj.Dirty = false;
if (hasRelations)
{
FillLazyObjectRelations(obj, true);
}
obj.IsPersisted = true;
}
}
, isolation);
return(dataObjects.ToArray());
}
/// <summary>
/// Raw SQL Select Implementation
/// </summary>
/// <param name="SQLCommand">Command for reading</param>
/// <param name="Reader">Reader Method</param>
/// <param name="Isolation">Transaction Isolation</param>
protected void ExecuteSelectImpl(string SQLCommand, Action <IDataReader> Reader, Transaction.IsolationLevel Isolation)
{
ExecuteSelectImpl(SQLCommand, new [] { new QueryParameter[] { } }, Reader, Isolation);
}
/// <summary>
/// Selects all objects from a given table in the database.
/// </summary>
/// <param name="objectType">the type of objects to retrieve</param>
/// <returns>an array of <see cref="DataObject" /> instances representing the selected objects</returns>
protected override IList <TObject> SelectAllObjectsImpl <TObject>(Transaction.IsolationLevel isolation)
{
return(SelectObjectsImpl <TObject>("", isolation));
}
/// <summary> /// Raw SQL Select Implementation with Parameters for Prepared Query /// </summary> /// <param name="SQLCommand">Command for reading</param> /// <param name="parameters">Collection of Parameters for Single/Multiple Read</param> /// <param name="Reader">Reader Method</param> /// <param name="Isolation">Transaction Isolation</param> protected abstract void ExecuteSelectImpl(string SQLCommand, IEnumerable <IEnumerable <QueryParameter> > parameters, Action <IDataReader> Reader, Transaction.IsolationLevel Isolation);
/// <summary>
/// Execute select on sql database
/// Close returned datareader when done or use using(reader) { ... }
/// </summary>
/// <param name="sqlcommand"></param>
/// <param name="callback"></param>
public void ExecuteSelect(string sqlcommand, QueryCallback callback, Transaction.IsolationLevel isolation)
{
if (connType == ConnectionType.DATABASE_MYSQL)
{
if (log.IsDebugEnabled)
{
log.Debug("SQL: " + sqlcommand);
}
bool repeat = false;
do
{
repeat = false;
using (var conn = new MySqlConnection(connString))
{
using (var cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = sqlcommand;
conn.Open();
long start = (DateTime.UtcNow.Ticks / 10000);
using (var reader = cmd.ExecuteReader())
{
try
{
callback(reader);
}
catch (Exception es)
{
if (log.IsWarnEnabled)
{
log.Warn("Exception in Select Callback : ", es);
}
}
}
if (log.IsDebugEnabled)
{
log.Debug("SQL Select (" + isolation + ") exec time " + ((DateTime.UtcNow.Ticks / 10000) - start) + "ms");
}
else if ((DateTime.UtcNow.Ticks / 10000) - start > 500 && log.IsWarnEnabled)
{
log.Warn("SQL Select (" + isolation + ") took " + ((DateTime.UtcNow.Ticks / 10000) - start) + "ms!\n" + sqlcommand);
}
}
catch (Exception e)
{
if (!HandleException(e))
{
if (log.IsErrorEnabled)
{
log.Error("ExecuteSelect: \"" + sqlcommand + "\"\n", e);
}
throw;
}
repeat = true;
}
}
}
}while (repeat);
return;
}
else if (connType == ConnectionType.DATABASE_SQLITE)
{
if (log.IsDebugEnabled)
{
log.Debug("SQL: " + sqlcommand);
}
bool repeat = false;
do
{
repeat = false;
using (var conn = new SQLiteConnection(connString))
{
using (var cmd = new SQLiteCommand(sqlcommand, conn))
{
try
{
conn.Open();
long start = (DateTime.UtcNow.Ticks / 10000);
using (var reader = cmd.ExecuteReader())
{
try
{
callback(reader);
}
catch (Exception es)
{
if (log.IsWarnEnabled)
{
log.Warn("Exception in Select Callback : ", es);
}
}
finally
{
reader.Close();
}
}
if (log.IsDebugEnabled)
{
log.Debug("SQL Select (" + isolation + ") exec time " + ((DateTime.UtcNow.Ticks / 10000) - start) + "ms");
}
else if ((DateTime.UtcNow.Ticks / 10000) - start > 500 && log.IsWarnEnabled)
{
log.Warn("SQL Select (" + isolation + ") took " + ((DateTime.UtcNow.Ticks / 10000) - start) + "ms!\n" + sqlcommand);
}
}
catch (Exception e)
{
if (!HandleException(e))
{
if (log.IsErrorEnabled)
{
log.Error("ExecuteSelect: \"" + sqlcommand + "\"\n", e);
}
throw;
}
repeat = true;
}
finally
{
conn.Close();
}
}
}
}while (repeat);
return;
}
if (log.IsWarnEnabled)
{
log.Warn("SQL Selects not supported for this connection type");
}
}
