/// <summary>
/// Creates the SQL query based on conditions using the current dialect.
/// Generally, you can call this method for debugging reasons.
/// </summary>
/// <param name="searchOptions">The search definitions.</param>
/// <param name="connection">The Connection.</param>
/// <param name="justCount">Indicates that this is a COUNT query.</param>
/// <param name="skip">Number of rows to skip when paging</param>
/// <param name="take">Number of rows to take when paging</param>
/// <param name="parameters">Outputs the generated parameters.</param>
/// <returns>A SQL query.</returns>
public static string GetSearchSql(SearchOptions searchOptions, bool justCount, int?skip, int?take, ConnectionStringSettings connection, out TenorParameter[] parameters)
{
GeneralDialect dialect = DialectFactory.CreateDialect(connection);
if (searchOptions == null)
{
throw (new ArgumentNullException("searchOptions", "You must specify a SearchOptions instance."));
}
TableInfo table = TableInfo.CreateTableInfo(searchOptions.baseType);
if (connection == null)
{
connection = table.GetConnection();
}
//Read Projections
List <Projection> projections = new List <Projection>();
foreach (Projection p in searchOptions.Projections)
{
projections.Add(p);
}
//Read Joins
List <Join> joins = new List <Join>();
joins.AddRange(GetPlainJoins(searchOptions, dialect));
//Get necessary fields to create the select statement.
StringBuilder sqlFields = new StringBuilder();
List <FieldInfo> fieldInfos = new List <FieldInfo>();
List <SpecialFieldInfo> spFields = new List <SpecialFieldInfo>();
List <FieldInfo> lenFields = new List <FieldInfo>();
FieldInfo[] allFields;
if (justCount)
{
allFields = EntityBase.GetFields(searchOptions.baseType, true); //lets count using distinct subquery
}
else if (searchOptions.Projections.Count > 0)
{
allFields = ReadProjections <FieldInfo>(projections, null, EntityBase.GetFields(searchOptions.baseType));
}
else
{
allFields = EntityBase.GetFields(searchOptions.baseType); //lets get all fields
}
foreach (FieldInfo f in allFields)
{
if (f.PrimaryKey || (!f.LazyLoading && !justCount)) //primary keys and eager fields must be loaded
{
fieldInfos.Add(f);
}
else if (!justCount && f.LazyLoading && f.FieldType.IsAssignableFrom(typeof(BinaryStream)))
{
lenFields.Add(f);
}
// when paging, at least one sorting criterion is needed
if (skip.HasValue && take.HasValue && searchOptions.Sorting.Count == 0 && f.PrimaryKey)
{
searchOptions.Sorting.Add(f.RelatedProperty.Name);
}
}
if (!justCount) //we don't need it on counting
{
SpecialFieldInfo[] fields = EntityBase.GetSpecialFields(searchOptions.baseType);
if (searchOptions.Projections.Count > 0)
{
fields = ReadProjections <SpecialFieldInfo>(projections, null, fields);
}
spFields.AddRange(fields);
}
sqlFields.Append(dialect.CreateSelectSql(table.RelatedTable, null, fieldInfos.ToArray(), spFields.ToArray(), lenFields.ToArray()));
//adding values from eager loading types
foreach (ForeignKeyInfo fkInfo in searchOptions.eagerLoading.Keys)
{
fieldInfos = new List <FieldInfo>();
spFields = new List <SpecialFieldInfo>();
lenFields = new List <FieldInfo>();
//select all fields, or only the projection.
FieldInfo[] allEagerFields = EntityBase.GetFields(fkInfo.ElementType);
if (searchOptions.Projections.Count > 0)
{
allEagerFields = ReadProjections <FieldInfo>(projections, fkInfo.RelatedProperty.Name, allEagerFields);
}
foreach (FieldInfo f in allEagerFields)
{
if (f.PrimaryKey || !f.LazyLoading)
{
fieldInfos.Add(f);
}
else if (f.LazyLoading && f.FieldType.IsAssignableFrom(typeof(BinaryStream)))
{
lenFields.Add(f);
}
}
//spfields
SpecialFieldInfo[] allSpFields = EntityBase.GetSpecialFields(fkInfo.ElementType);
if (searchOptions.Projections.Count > 0)
{
allSpFields = ReadProjections <SpecialFieldInfo>(projections, fkInfo.RelatedProperty.Name, allSpFields);
}
spFields.AddRange(allSpFields);
//joins: joins was made on GetPlainJoins.
sqlFields.Append(", ");
sqlFields.Append(dialect.CreateSelectSql(fkInfo.ElementType, searchOptions.eagerLoading[fkInfo], fieldInfos.ToArray(), spFields.ToArray(), lenFields.ToArray()));
}
//Sorting (not necessary for count queries)
string sqlSort = string.Empty;
if (!justCount)
{
string appendToSelect = null;
sqlSort = dialect.CreateSortSql(searchOptions.Sorting, table.RelatedTable, joins.ToArray(), searchOptions.Distinct, skip.HasValue && take.HasValue, out appendToSelect);
if (!string.IsNullOrEmpty(appendToSelect))
{
sqlFields.Append(appendToSelect);
}
}
//Check if we found all projections:
if (projections.Count > 0)
{
throw new InvalidProjectionException(projections[0]);
}
//Creates the where part
string sqlWhere = dialect.CreateWhereSql(searchOptions.Conditions, searchOptions.baseType, joins.ToArray(), out parameters);
// Creates the join parts
string sqlJoins = dialect.CreateJoinsSql(joins.ToArray());
// Creates the entire sql string
string sql = dialect.CreateFullSql(searchOptions.baseType, searchOptions.Distinct, justCount, searchOptions.Top, skip, take, sqlFields.ToString(), sqlJoins, sqlSort, sqlWhere);
Tenor.Diagnostics.Debug.DebugSQL("GetSearchSql()", sql, parameters, connection);
#if DEBUG
LastSearches.Push(sql);
#endif
return(sql);
}
/// <summary>
/// Checks if the entity already exists based on the properties passed and inserts if it doesn't. In case of canUpdate is true and the entity already exists, updates it to the current values.
/// </summary>
/// <param name="conditionalProperties">Properties to be considered when checking existence of the entity.</param>
/// <param name="canUpdate">If true, specifies that if a record exists, it will be updated.</param>
/// <param name="connection">The connection.</param>
public virtual bool SaveConditional(string[] conditionalProperties, bool canUpdate, ConnectionStringSettings connection)
{
if (Validate())
{
if (conditionalProperties == null || conditionalProperties.Length == 0)
{
throw new ArgumentNullException("conditionalProperties");
}
TableInfo table = TableInfo.CreateTableInfo(this.GetType());
if (connection == null)
{
connection = table.GetConnection();
}
FieldInfo[] fields = EntityBase.GetFields(this.GetType(), null, conditionalProperties);
if (fields.Length != conditionalProperties.Length)
{
throw new MissingFieldsException(this.GetType(), true);
/*
* throw (new ArgumentException("Cannot find one or more ConditionalFields", "conditionalProperties"));
*/
}
FieldInfo[] fieldsPrimary = EntityBase.GetFields(this.GetType(), true);
FieldInfo autoKeyField = null;
TenorParameter[] parameters = null;
GeneralDialect dialect = null;
string insertQuery = GetSaveSql(false, connection, null, out autoKeyField, out parameters, out dialect);
//updateQuery doesn't need parameters cause it's already set.\
TenorParameter[] parameters2 = null;
string updateQuery = GetSaveSql(true, connection, null, out autoKeyField, out parameters2, out dialect);
parameters2 = null;
string query = dialect.CreateConditionalSaveSql(insertQuery, updateQuery, conditionalProperties, fieldsPrimary);
DataTable result = Helper.QueryData(connection, query.ToString(), parameters);
if (!canUpdate && System.Convert.ToInt32(result.Rows[0][0]) == -1)
{
return(false);
}
else if (canUpdate && System.Convert.ToInt32(result.Rows[0][0]) == -1)
{
return(false);
}
else
{
if (autoKeyField != null)
{
autoKeyField.SetPropertyValue(this, Convert.ChangeType(result.Rows[0][0], autoKeyField.FieldType));
}
return(true);
}
}
else
{
return(false);
}
}
