protected override string BuildWhereClauses(PropertyInfo[] propertyInfo, List <Tuple <string, string, string> > filterConditions)
{
List <string> whereClauses = new List <string>();
List <Tuple <string, string, string> > processedFilterConditions = new List <Tuple <string, string, string> >();
if (filterConditions != null)
{
dataFieldEncapsultedParameters.Clear();
int parameterCount = 1;
foreach (Tuple <string, string, string> filterCondition in filterConditions)
{
dataFieldEncapsultedParameters.Add(filterCondition, "@param" + (parameterCount++).ToString());
}
foreach (var property in propertyInfo)
{
bool filterOnProperty = false;
if (filterConditions.Select(x => x.Item1).ToList().ConvertAll(x => x.ToLower()).Contains(property.Name.ToLower()))
{
filterOnProperty = true;
}
if (filterOnProperty)
{
Tuple <string, string, string> filterCondition = filterConditions.Where(x => x.Item1.ToLower() == property.Name.ToLower()).First();
if (filterCondition.Item2 != string.Empty)
{
processedFilterConditions.Add(filterCondition);
whereClauses.Add(
string.Format(
"{0} {1} {2}",
property.Name,
filterCondition.Item3,
dataFieldEncapsultedParameters[filterCondition]));
}
}
}
var remainingFilterConditions = filterConditions.Except(processedFilterConditions).ToList();
foreach (Tuple <string, string, string> remainingFilterCondition in remainingFilterConditions)
{
if (UbjectMetadata.IsMetadataField(remainingFilterCondition.Item1))
{
whereClauses.Add(
string.Format(
"{0} {1} {2}",
remainingFilterCondition.Item1,
remainingFilterCondition.Item3,
dataFieldEncapsultedParameters[remainingFilterCondition]));
}
}
}
return(string.Join(" AND ", whereClauses).Trim());
}
protected virtual string BuildWhereClauses(PropertyInfo[] propertyInfo, List <Tuple <string, string, string> > filterConditions)
{
List <string> whereClauses = new List <string>();
List <Tuple <string, string, string> > processedFilterConditions = new List <Tuple <string, string, string> >();
if (filterConditions != null)
{
foreach (var property in propertyInfo)
{
bool filterOnProperty = false;
if (filterConditions.Select(x => x.Item1).ToList().ConvertAll(x => x.ToLower()).Contains(property.Name.ToLower()))
{
filterOnProperty = true;
}
if (filterOnProperty)
{
Tuple <string, string, string> filterCondition = filterConditions.Where(x => x.Item1.ToLower() == property.Name.ToLower()).First();
if (filterCondition.Item2 != string.Empty)
{
processedFilterConditions.Add(filterCondition);
whereClauses.Add(property.Name + " " + filterCondition.Item3 + " '" + EscapeQueryArgument(filterCondition.Item2) + "'");
}
}
}
List <Tuple <string, string, string> > remainingFilterConditions = filterConditions.Except(processedFilterConditions).ToList();
foreach (Tuple <string, string, string> remainingFilterCondition in remainingFilterConditions)
{
if (UbjectMetadata.IsMetadataField(remainingFilterCondition.Item1))
{
whereClauses.Add(remainingFilterCondition.Item1 + " " + remainingFilterCondition.Item3 + " '" + EscapeQueryArgument(remainingFilterCondition.Item2) + "'");
}
}
}
return(string.Join(" AND ", whereClauses).Trim());
}
public override Dictionary <UbjectMetadata, object> GetObjects(Type type, List <Tuple <string, string, string> > filterProperties = null)
{
var connection = Utilities.CreateDbConnection(dbManager);
IDbCommand command = connection.CreateCommand();
StringBuilder commandText = new StringBuilder();
var propertyInfo = type.GetPropertiesEx();
commandText.Append(string.Format("{0} {1} {2} {3}", "SELECT", "*", "FROM", QueryTableName));
string whereClause = BuildWhereClauses(type.GetPropertiesEx(), filterProperties);
if (whereClause != string.Empty)
{
commandText.Append(string.Format(" {0} {1}", "WHERE", whereClause));
}
commandText.Append(";");
foreach (var dataFieldEncapsultedParameter in dataFieldEncapsultedParameters)
{
command.Parameters.Add(new SQLiteParameter(dataFieldEncapsultedParameter.Value, dataFieldEncapsultedParameter.Key.Item2));
}
connection.Open();
command.CommandText = commandText.ToString();
IDataReader tableReader = command.ExecuteReader();
Dictionary <UbjectMetadata, object> newObjects = new Dictionary <UbjectMetadata, object>();
while (tableReader.Read())
{
object newObject = null;
if (type.IsInterface)
{
newObject = UbjectDIBindings.Resolve(type);
}
else
{
newObject = Activator.CreateInstance(type);
}
var ubjectMetadata = new UbjectMetadata();
for (int fieldIndex = 0; fieldIndex < tableReader.FieldCount; fieldIndex++)
{
string fieldName = tableReader.GetName(fieldIndex).ToString();
object fieldValue = tableReader.GetValue(fieldIndex);
if (UbjectMetadata.IsMetadataField(fieldName))
{
ubjectMetadata.SetPropertyValue(fieldName, fieldValue);
}
else
{
foreach (var property in propertyInfo)
{
if (fieldName.ToLower() == property.Name.ToLower())
{
if ((fieldValue != DBNull.Value) && (fieldValue != null))
{
if (property.PropertyType.IsEnum)
{
property.SetValue(newObject, Enum.Parse(property.PropertyType, fieldValue.ToString()));
}
else
{
property.SetValue(newObject, Utilities.ChangeType(fieldValue, property.PropertyType));
}
}
break;
}
}
}
}
newObjects.Add(ubjectMetadata, newObject);
}
connection.Close();
return(newObjects);
}
public virtual void DeleteObject(UbjectMetadata ubjectMetadata)
{
DeleteObjectFromTable(BuildWhereClauses(null, null, null, ubjectMetadata));
}
protected virtual string BuildWhereClauses(object data, PropertyInfo[] propertyInfo, List <string> filterProperties, UbjectMetadata ubjectMetadata)
{
List <string> whereClauses = new List <string>();
if ((data != null) && (propertyInfo != null) && (filterProperties != null))
{
foreach (var property in propertyInfo)
{
bool filterOnProperty = true;
if (!filterProperties.ConvertAll(x => x.ToLower()).Contains(property.Name.ToLower()))
{
filterOnProperty = false;
}
if (filterOnProperty)
{
if (property.GetValue(data).ToStringEx() != string.Empty)
{
whereClauses.Add(property.Name + " = '" + property.GetValue(data) + "'");
}
}
}
}
if (ubjectMetadata != null)
{
whereClauses.Add(UbjectMetadata.PRIMARY_KEY + " = '" + ubjectMetadata.PrimaryKey + "'");
}
return(string.Join(" AND ", whereClauses).Trim());
}
public virtual List <IDbTableManager> GetAllDbTableManagers()
{
List <IDbTableManager> dbTableManagers = new List <IDbTableManager>();
using (var tableConnection = Utilities.CreateDbConnection(this))
{
IDbCommand showTableCommand = tableConnection.CreateCommand();
showTableCommand.CommandText = ShowTablesCommand;
tableConnection.Open();
IDataReader tableReader = showTableCommand.ExecuteReader();
using (var columnsConnection = Utilities.CreateDbConnection(this))
{
columnsConnection.Open();
while (tableReader.Read())
{
for (int tableFieldIndex = 0; tableFieldIndex < 1 /*tableReader.FieldCount*/; tableFieldIndex++)
{
IDbTableManager dbTableManager = Utilities.CreateDbTableManager(this);
dbTableManager.TableName = tableReader.GetValue(tableFieldIndex).ToString();
IDbCommand showColumnsCommand = columnsConnection.CreateCommand();
showColumnsCommand.CommandText = ShowColumnsCommand(dbTableManager.TableName);
IDataReader schemaReader = showColumnsCommand.ExecuteReader();
while (schemaReader.Read())
{
for (int schemaFieldIndex = 0; schemaFieldIndex < schemaReader.FieldCount; schemaFieldIndex++)
{
string fieldName = schemaReader.GetName(schemaFieldIndex).ToString();
if (fieldName == ColumnCommandFieldName)
{
string columnName = schemaReader.GetValue(schemaFieldIndex).ToString();
if (!UbjectMetadata.IsMetadataField(columnName))
{
dbTableManager.Columns.Add(columnName);
}
break;
}
}
}
schemaReader.Close();
dbTableManagers.Add(dbTableManager);
}
}
columnsConnection.Close();
}
tableReader.Close();
tableConnection.Close();
}
return(dbTableManagers);
}
