public SelectStatement SelectCustom(string statement, string type, string alias, ColumnType columnType = ColumnType.SelectWhereOrderBy, string[] dependentOnAliases = null, bool isVisible = true, int sortOrder = 0)
{
ColumnDef columnDef = new ColumnDef(null, alias, type, 0, false, false, null);
SelectColumn selectColumn = new SelectColumn(null, columnDef, alias, columnType, dependentOnAliases, isVisible, sortOrder, Aggregates.None, null, null, statement);
SelectColumns.Add(selectColumn);
return(this);
}
private void GetJoinAndColumnDef(string column, Action <Join, ColumnDef> action)
{
string alias = null;
string name;
if (column.Contains("."))
{
alias = column.Split('.')[0];
name = column.Split('.')[1];
}
else
{
name = column;
var allColumnDefs = _builder.ColumnDefs
.Join(Joins, j => j.ObjectDef, cd => cd.ObjectDef, (cd, j) => new { Join = j, ColumnDef = cd })
.Where(item => item.ColumnDef.Name == name)
.ToList();
if (allColumnDefs.Count == 1)
{
alias = allColumnDefs[0].Join.Alias;
}
else if (allColumnDefs.Count(item => item.ColumnDef.IsPrimaryKey) == 1)
{
alias = allColumnDefs.Single(item => item.ColumnDef.IsPrimaryKey).Join.Alias;
}
}
Join join = Joins.Single(item => item.Alias == alias);
ColumnDef columnDef = null;
if (join.SelectStatement != null)
{
SelectColumn selectColumn = join.SelectStatement.SelectColumns.Single(item => item.Alias == name);
if (selectColumn.AggregateColumnDef != null)
{
columnDef = selectColumn.AggregateColumnDef;
}
else
{
throw new NotImplementedException();
}
}
else
{
columnDef = _builder.ColumnDefs.Single(item => item.ObjectDef == join.ObjectDef && item.Name == name);
}
action(join, columnDef);
}
public void LoadDefinitions()
{
using (DataSet dataSet = _dataSource.GetDefinitions())
{
SchemaDefs = new List <SchemaDef>();
ObjectDefs = new List <ObjectDef>();
ColumnDefs = new List <ColumnDef>();
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
SchemaDef schemaDef = new SchemaDef(
dataRow.Field <int>("SchemaId"),
dataRow.Field <string>("Name"));
SchemaDefs.Add(schemaDef);
}
foreach (DataRow dataRow in dataSet.Tables[1].Rows)
{
ObjectDef objectDef = new ObjectDef(
dataRow.Field <int>("ObjectId"),
SchemaDefs.Single(item => item.SchemaId == dataRow.Field <int>("SchemaId")),
dataRow.Field <string>("Name"));
ObjectDefs.Add(objectDef);
}
foreach (DataRow dataRow in dataSet.Tables[2].Rows)
{
ObjectDef objectDef = ObjectDefs.Single(item => item.TableId == dataRow.Field <int>("ObjectId"));
ObjectDef referencedObjectDef = dataRow.Field <int?>("ReferencedObjectId") == null ? null : ObjectDefs.Single(item => item.TableId == dataRow.Field <int?>("ReferencedObjectId"));
ColumnDef columnDef = new ColumnDef(
objectDef,
dataRow.Field <string>("Name"),
dataRow.Field <string>("Type"),
dataRow.Field <short>("Length"),
dataRow.Field <bool>("IsNullable"),
dataRow.Field <bool>("IsPrimaryKey"),
referencedObjectDef);
ColumnDefs.Add(columnDef);
if (dataRow.Field <bool>("IsPrimaryKey"))
{
objectDef.PrimayKeyColumn = columnDef;
}
}
}
}
public SelectColumn(Join @join, ColumnDef columnDef, string alias, ColumnType columnType, string[] dependentOnAliases, bool isVisible, int orderByIndex, Aggregates aggregate, ColumnDef aggregateColumnDef, string isNull, string statement = null, SelectStatement optionsSelectStatement = null)
{
Join = @join;
ColumnDef = columnDef;
ColumnType = columnType;
Alias = alias;
DependentOnAliases = dependentOnAliases;
IsVisible = isVisible;
OrderByIndex = orderByIndex;
Aggregate = aggregate;
AggregateColumnDef = aggregateColumnDef;
IsNull = isNull;
Statement = statement;
OptionsSelectStatement = optionsSelectStatement;
}
public SelectStatement Join(string foreignKeyColumn, string alias)
{
/*
* usage: .CreateSelect("core.Product", "p").Join("p.ProductTypeId", "pt")
*
* joins the foreign key p.ProductTypeId to the referenced object on its primary key
*/
GetJoinAndColumnDef(foreignKeyColumn, (parentJoin, parentColumnDef) =>
{
ObjectDef objectDef = parentColumnDef.ReferencedObject;
ColumnDef columnDef = objectDef.PrimayKeyColumn;
Join join = new Join(parentJoin, parentColumnDef, columnDef, alias);
Joins.Add(join);
});
return(this);
}
public SelectStatement Join(string parentAlias, string primaryObject, string alias)
{
/*
* usage: .CreateSelect("core.ProductType", "pt").Join("pt", "core.Product", "p")
*
* joins the primary key of pt.ProductTypeId to the foreigh key of core.Product that references core.ProductType
*/
Join parentJoin = Joins.Single(item => item.Alias == parentAlias);
ObjectDef objectDef = _builder.ObjectDefs.Single(item => item.FullName == primaryObject);
ColumnDef columnDef = _builder.ColumnDefs.Single(item => item.ObjectDef == objectDef && item.ReferencedObject == parentJoin.ObjectDef);
Join join = new Join(parentJoin, parentJoin.ObjectDef.PrimayKeyColumn, columnDef, alias);
Joins.Add(join);
return(this);
}
public SelectStatement Select(string column, string alias = null, ColumnType columnType = ColumnType.SelectWhereOrderBy, bool isVisible = true, int sortOrder = 0, Aggregates aggregate = Aggregates.None, string isNull = null, SelectStatement optionsSelectStatement = null)
{
GetJoinAndColumnDef(column, (join, columnDef) =>
{
if (String.IsNullOrEmpty(alias))
{
alias = columnDef.Name;
}
ColumnDef aggregateColumnDef = null;
if (aggregate != Aggregates.None)
{
string type = "";
if (aggregate == Aggregates.Count)
{
type = "int";
}
else
{
throw new NotImplementedException();
}
aggregateColumnDef = new ColumnDef(null, alias, type, 0, true, false, null);
}
isVisible = isVisible && ((columnType & ColumnType.Identifier) == ColumnType.Identifier || (columnType & ColumnType.Select) == ColumnType.Select);
SelectColumn selectColumn = new SelectColumn(@join, columnDef, alias, columnType, null, isVisible, sortOrder, aggregate, aggregateColumnDef, isNull, optionsSelectStatement: optionsSelectStatement);
SelectColumns.Add(selectColumn);
});
return(this);
}