/// <summary>
/// Generates the update query.
/// </summary>
/// <param name="node">The node.</param>
/// <param name="queryObject">The query object.</param>
/// <returns></returns>
private string GenerateUpdateQuery(Utils.TreeNode <Type>?node, TMappedClass queryObject)
{
if (node is null)
{
return(string.Empty);
}
var Builder = ObjectPool.Get();
var Splitter = string.Empty;
//Generate parent queries
for (int x = 0, nodeNodesCount = node.Nodes.Count; x < nodeNodesCount; x++)
{
var Parent = node.Nodes[x];
var Result = GenerateUpdateQuery(Parent, queryObject);
if (!string.IsNullOrEmpty(Result))
{
Builder.AppendLine(Result);
}
}
var Mapping = MappingInformation.Mappings[node.Data];
string ReturnValue;
if (Mapping.ReferenceProperties.Count == 0)
{
ReturnValue = Builder.ToString();
ObjectPool.Return(Builder);
return(ReturnValue);
}
var ParameterList = ObjectPool.Get();
var WhereClause = ObjectPool.Get();
var FromClause = ObjectPool.Get();
//Adding reference properties
foreach (var ReferenceProperty in Mapping.ReferenceProperties)
{
ParameterList.Append(Splitter).Append(GetColumnName(ReferenceProperty)).Append("=").Append(GetParameterName(ReferenceProperty));
Splitter = ",";
}
//From clause generation
FromClause.AppendLine(GetTableName(Mapping))
.Append(GenerateFromClause(node, queryObject));
//Where clause generation
WhereClause.Append(GenerateWhereClause(node, queryObject));
//Generating final query
Builder.AppendLineFormat(@"UPDATE {0}
SET {1}
FROM {2}WHERE {3};", GetTableName(Mapping), ParameterList, FromClause, WhereClause);
ReturnValue = Builder.ToString();
ObjectPool.Return(Builder);
ObjectPool.Return(ParameterList);
ObjectPool.Return(WhereClause);
ObjectPool.Return(FromClause);
return(ReturnValue);
}
/// <summary>
/// Generates the where clause.
/// </summary>
/// <param name="node">The node.</param>
/// <param name="queryObject">The query object.</param>
/// <returns>The where clause</returns>
private string GenerateWhereClause(Utils.TreeNode <Type> node, TMappedClass queryObject)
{
var Result = ObjectPool.Get();
var Mapping = MappingInformation.Mappings[node.Data];
var Separator = string.Empty;
for (int x = 0, nodeNodesCount = node.Nodes.Count; x < nodeNodesCount; x++)
{
var ParentNode = node.Nodes[x];
var ParentResult = GenerateWhereClause(ParentNode, queryObject);
if (!string.IsNullOrEmpty(ParentResult))
{
Result.Append(Separator).Append(ParentResult);
Separator = "\r\nAND ";
}
}
foreach (var IDProperty in Mapping.IDProperties)
{
Result.AppendFormat("{0}{1}={2}", Separator, GetColumnName(IDProperty), GetParameterName(IDProperty));
Separator = "\r\nAND ";
}
var ReturnValue = Result.ToString();
ObjectPool.Return(Result);
return(ReturnValue);
}
/// <summary>
/// Generates from clause.
/// </summary>
/// <param name="node">The node.</param>
/// <param name="queryObject">The query object.</param>
/// <returns>The from clause</returns>
private string GenerateFromClause(Utils.TreeNode <Type> node, TMappedClass queryObject)
{
var Result = ObjectPool.Get();
var Mapping = MappingInformation.Mappings[node.Data];
for (int x = 0, nodeNodesCount = node.Nodes.Count; x < nodeNodesCount; x++)
{
var ParentNode = node.Nodes[x];
var ParentMapping = MappingInformation.Mappings[ParentNode.Data];
var TempIDProperties = ObjectPool.Get();
var Separator = string.Empty;
foreach (var IDProperty in ParentMapping.IDProperties)
{
TempIDProperties.AppendFormat("{0}{1}={2}", Separator, GetParentColumnName(Mapping, IDProperty), GetColumnName(IDProperty));
Separator = " AND ";
}
foreach (var IDProperty in ParentMapping.AutoIDProperties)
{
TempIDProperties.AppendFormat("{0}{1}={2}", Separator, GetParentColumnName(Mapping, IDProperty), GetColumnName(IDProperty));
Separator = " AND ";
}
Result.AppendLineFormat("INNER JOIN {0} ON {1}", GetTableName(ParentMapping), TempIDProperties)
.Append(GenerateFromClause(ParentNode, queryObject));
ObjectPool.Return(TempIDProperties);
}
var ReturnValue = Result.ToString();
ObjectPool.Return(Result);
return(ReturnValue);
}
/// <summary>
/// Generates the parameter list.
/// </summary>
/// <param name="node">The node.</param>
/// <param name="data">The data.</param>
/// <returns></returns>
private string GenerateParameterList(Utils.TreeNode <Type> node, QueryData <TMappedClass> data)
{
var Result = ObjectPool.Get();
var Mapping = MappingInformation.Mappings[node.Data];
var Separator = string.Empty;
for (int x = 0, nodeNodesCount = node.Nodes.Count; x < nodeNodesCount; x++)
{
var ParentNode = node.Nodes[x];
var ParentResult = GenerateParameterList(ParentNode, data);
if (!string.IsNullOrEmpty(ParentResult))
{
Result.Append(Separator).Append(ParentResult);
Separator = ",";
}
}
foreach (var IDProperty in Mapping.IDProperties)
{
Result.AppendFormat(CultureInfo.InvariantCulture, "{0}{1} AS {2}", Separator, GetColumnName(IDProperty), "[" + IDProperty.Name + "]");
Separator = ",";
}
foreach (var ReferenceProperty in Mapping.ReferenceProperties.Where(x => data.SelectValues.Count == 0 ||
data.SelectValues.Any(y => y.Name == x.Name)))
{
Result.AppendFormat(CultureInfo.InvariantCulture, "{0}{1} AS {2}", Separator, GetColumnName(ReferenceProperty), "[" + ReferenceProperty.Name + "]");
Separator = ",";
}
var ReturnValue = Result.ToString();
ObjectPool.Return(Result);
return(ReturnValue);
}
/// <summary>
/// Generates the select query.
/// </summary>
/// <param name="node">The node.</param>
/// <param name="ids">The ids.</param>
/// <returns></returns>
private string GenerateSelectQuery(Utils.TreeNode <Type>?node, Memory <Dynamo> ids)
{
if (node is null)
{
return(string.Empty);
}
var Builder = ObjectPool.Get();
var ParameterList = ObjectPool.Get();
var FromClause = ObjectPool.Get();
var WhereClause = ObjectPool.Get();
var Mapping = MappingInformation.Mappings[node.Data];
//Get From Clause
FromClause.Append(GetTableName(Mapping))
.Append(GenerateFromClause(node));
//Get parameter listing
ParameterList.Append(GenerateParameterList(node));
//Get Where Clause
WhereClause.Append(GenerateWhereClause(ids));
//Generate final query
Builder
.Append("SELECT ")
.Append(ParameterList)
.AppendLine()
.Append("FROM ")
.Append(FromClause)
.AppendLine();
if (WhereClause.Length > 0)
{
Builder.Append("WHERE ")
.Append(WhereClause)
.AppendLine();
}
var ReturnValue = Builder.ToString().TrimEnd('\r', '\n', ' ', '\t') + ";";
ObjectPool.Return(Builder);
ObjectPool.Return(ParameterList);
ObjectPool.Return(FromClause);
ObjectPool.Return(WhereClause);
return(ReturnValue);
}
/// <summary>
/// Generates the order by clause.
/// </summary>
/// <param name="node">The node.</param>
/// <param name="data">The data.</param>
/// <returns>Order by clause</returns>
private string GenerateOrderByClause(Utils.TreeNode <Type> node, QueryData <TMappedClass> data)
{
var Builder = ObjectPool.Get();
var Splitter = string.Empty;
string ReturnValue = string.Empty;
if (data.OrderByValues.Count == 0)
{
var Mapping = MappingInformation.Mappings[node.Data];
for (int x = 0, nodeNodesCount = node.Nodes.Count; x < nodeNodesCount; x++)
{
var ParentNode = node.Nodes[x];
var ParentResult = GenerateOrderByClause(ParentNode, data);
if (!string.IsNullOrEmpty(ParentResult))
{
return(ParentResult);
}
}
foreach (var IDProperty in Mapping.IDProperties)
{
Builder.Append(Splitter)
.Append(GetColumnName(IDProperty));
Splitter = ",";
}
ReturnValue = Builder.ToString();
ObjectPool.Return(Builder);
return(ReturnValue);
}
foreach (var Column in data.OrderByValues.OrderBy(x => x.Order))
{
Builder.Append(Splitter)
.Append(Column.Property.Name)
.Append(Column.Direction == Direction.Descending ? " DESC" : "");
Splitter = ",";
}
ReturnValue = Builder.ToString();
ObjectPool.Return(Builder);
return(ReturnValue);
}
/// <summary>
/// Generates the insert query.
/// </summary>
/// <param name="node">The node.</param>
/// <returns>The resulting query</returns>
private string GenerateInsertQuery(Utils.TreeNode <Type>?node)
{
if (node is null)
{
return(string.Empty);
}
var Builder = ObjectPool.Get();
var ParameterList = ObjectPool.Get();
var ValueList = ObjectPool.Get();
var DeclareProperties = ObjectPool.Get();
var SetProperties = ObjectPool.Get();
var IDReturn = ObjectPool.Get();
var Splitter = string.Empty;
var Mapping = MappingInformation.Mappings[node.Data];
//Generate parent queries
for (int x = 0, nodeNodesCount = node.Nodes.Count; x < nodeNodesCount; x++)
{
var Parent = node.Nodes[x];
Builder.AppendLine(GenerateInsertQuery(Parent));
}
//Reference properties
foreach (var ReferenceProperty in Mapping.ReferenceProperties
.Where(x => string.IsNullOrEmpty(x.ComputedColumnSpecification)))
{
ParameterList.Append(Splitter).Append(GetColumnName(ReferenceProperty));
ValueList.Append(Splitter).Append(GetParameterName(ReferenceProperty));
Splitter = ",";
}
//Non auto incremented ID Properties
foreach (var IDProperty in Mapping.IDProperties.Where(x => !x.AutoIncrement))
{
ParameterList.Append(Splitter).Append(GetColumnName(IDProperty));
ValueList.Append(Splitter).Append(GetParameterName(IDProperty));
Splitter = ",";
}
//Parent ID and auto ID properties
foreach (var ParentMapping in node.Nodes.ForEach(x => MappingInformation.Mappings[x.Data]))
{
foreach (var IDProperty in ParentMapping.IDProperties)
{
ParameterList.Append(Splitter).Append(GetParentColumnName(Mapping, IDProperty));
ValueList.Append(Splitter).Append(GetParentParameterName(IDProperty));
Splitter = ",";
}
foreach (var AutoIDProperty in ParentMapping.AutoIDProperties)
{
ParameterList.Append(Splitter).Append(GetParentColumnName(Mapping, AutoIDProperty));
ValueList.Append(Splitter).Append(GetParentParameterName(AutoIDProperty));
Splitter = ",";
}
}
//ID Properties to pass to the next set of queries
foreach (var IDProperty in Mapping.IDProperties)
{
SetProperties.Append("SET ").Append(GetParentParameterName(IDProperty)).Append("=").Append(IDProperty.AutoIncrement ? "SCOPE_IDENTITY()" : GetParameterName(IDProperty)).AppendLine(";");
if (IDProperty.AutoIncrement)
{
IDReturn.AppendLineFormat("SELECT {0} AS [{1}];", GetParentParameterName(IDProperty), IDProperty.Name);
}
}
//Auto ID properties to pass to the next set of queries
foreach (var AutoIDProperty in Mapping.AutoIDProperties)
{
SetProperties.Append("SET ").Append(GetParentParameterName(AutoIDProperty)).AppendLine("=SCOPE_IDENTITY();");
}
//Build the actual queries
if (node.Parent != null || Mapping.IDProperties.Any(x => x.AutoIncrement))
{
Builder.Append(DeclareProperties.ToString());
}
if (Mapping.IDProperties.All(x => x.AutoIncrement) && Mapping.ReferenceProperties.Count == 0 && Mapping.AutoIDProperties.Count == 0)
{
Builder.AppendLineFormat("INSERT INTO {0} DEFAULT VALUES;", GetTableName(Mapping));
}
else
{
Builder.AppendLineFormat("INSERT INTO {0}({1}) VALUES ({2});", GetTableName(Mapping), ParameterList, ValueList);
}
if (node.Parent != null || Mapping.IDProperties.Any(x => x.AutoIncrement))
{
Builder.Append(SetProperties.ToString());
if (Mapping.IDProperties.Any(x => x.AutoIncrement))
{
Builder.Append(IDReturn);
}
}
var Result = Builder.ToString();
ObjectPool.Return(Builder);
ObjectPool.Return(ParameterList);
ObjectPool.Return(ValueList);
ObjectPool.Return(DeclareProperties);
ObjectPool.Return(SetProperties);
ObjectPool.Return(IDReturn);
return(Result);
}
/// <summary>
/// Generates the select query.
/// </summary>
/// <param name="node">The node.</param>
/// <param name="data">The data.</param>
/// <returns></returns>
private string GenerateSelectQuery(Utils.TreeNode <Type>?node, QueryData <TMappedClass> data)
{
if (node is null)
{
return(string.Empty);
}
var Builder = ObjectPool.Get();
var ParameterList = ObjectPool.Get();
var FromClause = ObjectPool.Get();
var WhereClause = ObjectPool.Get();
var OrderByClause = ObjectPool.Get();
var Mapping = MappingInformation.Mappings[node.Data];
//Get From Clause
FromClause.Append(GetTableName(Mapping));
FromClause.Append(GenerateFromClause(node));
//Get parameter listing
ParameterList.Append(GenerateParameterList(node, data));
//Get Where Clause
WhereClause.Append(GenerateWhereClause(data, Mapping));
//Get Order By clause
OrderByClause.Append(GenerateOrderByClause(node, data));
if (data.Count)
{
Builder.AppendLine("SELECT COUNT(*) AS Count FROM (");
}
//Generate final query
Builder
.Append("SELECT")
.Append(data.Distinct ? " DISTINCT " : " ")
.Append(ParameterList)
.AppendLine()
.Append("FROM ")
.Append(FromClause)
.AppendLine();
if (WhereClause.Length > 0)
{
Builder.Append(WhereClause)
.AppendLine();
}
if (!data.Count)
{
if (OrderByClause.Length > 0)
{
Builder.Append("ORDER BY ")
.Append(OrderByClause)
.AppendLine();
}
if (data.Top > 0 || data.Skip > 0)
{
Builder.Append("OFFSET ").Append(data.Skip).AppendLine(" ROWS")
.Append("FETCH NEXT ").Append(data.Top).Append(" ROWS ONLY");
}
}
if (data.Count)
{
Builder.AppendLine().Append(") AS _InternalQuery");
}
var ReturnValue = Builder.ToString().TrimEnd('\r', '\n', ' ', '\t') + ";";
ObjectPool.Return(Builder);
ObjectPool.Return(ParameterList);
ObjectPool.Return(FromClause);
ObjectPool.Return(WhereClause);
ObjectPool.Return(OrderByClause);
return(ReturnValue);
}
public void SetHeapNode(Utils.TreeNode <Plane> treeNode)
{
this.heapNode = treeNode;
}
