private QBuilder TaskHistoryQBuilder()
{
const string latestCompletion = "LatestCompletion";
var innerQbuilder = new QBuilder()
.UseTableBoundSelector <TaskExecutionHistory>()
.SelectAggregated(taskExecutionHistory => taskExecutionHistory.EndTime, latestCompletion, "Max")
.Select(taskExecutionHistory => taskExecutionHistory.TaskDefinitionId)
.Then()
.UseTableBoundGrouper <TaskExecutionHistory>()
.GroupBy(taskExecutionHistory => taskExecutionHistory.TaskDefinitionId);
var outerQbuilder = new QBuilder(TaskHistoryAlias)
.UseTableBoundSelector <TaskExecutionHistory>()
.Select(taskExecutionHistory => taskExecutionHistory.EndTime)
.Select(taskExecutionHistory => taskExecutionHistory.Id)
.Select(taskExecutionHistory => taskExecutionHistory.Message)
.Select(taskExecutionHistory => taskExecutionHistory.StartTime)
.Select(taskExecutionHistory => taskExecutionHistory.Succeeded)
.Select(taskExecutionHistory => taskExecutionHistory.TaskDefinitionId)
.Then()
.UseJoiner()
.UseDerivedTableJoiner <TaskExecutionHistory>()
.InnerJoin(taskExecutionHistory => taskExecutionHistory.TaskDefinitionId, innerQbuilder, nameof(TaskExecutionHistory.TaskDefinitionId))
.UseDerivedTableJoiner <TaskExecutionHistory>()
.InnerJoin(taskExecutionHistory => taskExecutionHistory.EndTime, innerQbuilder, latestCompletion)
.Then();
return(outerQbuilder);
}
void MainWindow_Loaded(object sender, RoutedEventArgs e)
{
Loaded -= MainWindow_Loaded;
QBuilder.QueryColumnListOptions.UseCustomExpressionBuilder = AffectedColumns.ConditionColumns | AffectedColumns.ExpressionColumn;
// set syntax provider
QBuilder.SyntaxProvider = new MSSQLSyntaxProvider();
// Fill metadata container from the XML file. (For demonstration purposes.)
try
{
QBuilder.MetadataLoadingOptions.OfflineMode = true;
QBuilder.MetadataContainer.ImportFromXML("Northwind.xml");
QBuilder.InitializeDatabaseSchemaTree();
QBuilder.SQL = @"SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, [Order Details].ProductID,
[Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Orders.OrderID > 0 AND [Order Details].Discount > 0" ;
QBuilder.QueryColumnListOptions.UseCustomExpressionBuilder = AffectedColumns.ConditionColumns &
AffectedColumns.ExpressionColumn;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//////////////////////////////////////////////////////////////////////////
/// 3rd way:
///
/// This method demonstrates loading of metadata through .NET data providers
/// unsupported by our QueryBuilder component. If such data provider is able
/// to execute SQL queries, you can use our EventMetadataProvider with handling
/// it's ExecSQL event. In this event the EventMetadataProvider will provide
/// you SQL queries it use for the metadata retrieval. You have to execute
/// a query and return resulting data reader object.
///
/// Note: In this sample we are using GenericSyntaxProvider that tries
/// to detect the the server type automatically. In some conditions it's unable
/// to detect the server type and it also has limited syntax parsing abilities.
/// For this reason you have to use specific syntax providers in your application,
/// e.g. MySQLSyntaxProver, OracleSyntaxProvider, etc.
//////////////////////////////////////////////////////////////////////////
private void btn3Way_Click(object sender, EventArgs e)
{
if (_dbConnection != null)
{
try
{
_dbConnection.Close();
_dbConnection.Open();
// allow QueryBuilder to request metadata
QBuilder.MetadataLoadingOptions.OfflineMode = false;
ResetQueryBuilderMetadata();
QBuilder.MetadataProvider = _way3EventMetadataProvider;
QBuilder.InitializeDatabaseSchemaTree();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "btn3Way_Click()");
}
}
else
{
MessageBox.Show("Please setup a database connection by clicking on the \"Connect\" menu item before testing this method.");
}
}
public Form1()
{
InitializeComponent();
// set syntax provider
QBuilder.SyntaxProvider = new MSSQLSyntaxProvider {
ServerVersion = MSSQLServerVersion.MSSQL2012
};
// Fill metadata container from the XML file. (For demonstration purposes.)
try
{
QBuilder.MetadataLoadingOptions.OfflineMode = true;
QBuilder.MetadataContainer.ImportFromXML("Northwind.xml");
QBuilder.InitializeDatabaseSchemaTree();
QBuilder.SQL = @"SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, [Order Details].ProductID,
[Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Orders.OrderID > 0 AND [Order Details].Discount > 0" ;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Initialize current properties for the site
/// </summary>
public CurrentProvider()
{
Person = new Person();
User = new User();
RequestBuilder = new QBuilder();
DataManipulation = new DataManipulator();
}
public void ItDoesComplexJoins()
{
var taskExecutionHistoryQBuilder = TaskHistoryQBuilder();
var query = new QBuilder("f")
.UseTableBoundSelector <Schedule>()
.Select(schedule => schedule.NextRunTime)
.Then()
.UseTableBoundSelector <TaskDefinition>()
.Select(taskDefinition => taskDefinition.Name, nameof(TaskDescription.DisplayLabel))
.Select(taskDefinition => taskDefinition.StaticName)
.Then()
.UseDerivedTableSelector(taskExecutionHistoryQBuilder)
.Select(nameof(TaskExecutionHistory.StartTime), nameof(TaskDescription.LastStartTime))
.Then()
.UseTableBoundJoinBuilder <Schedule, TaskDefinition>()
.InnerJoin(schedule => schedule.TaskDefinitionId, taskDefinition => taskDefinition.Id)
.UseTableBoundFilter <TaskDefinition>()
.WhereEqualTo(taskDefinition => taskDefinition.CompanyId, 1)
.And <TaskDefinition>()
.WhereEqualTo(taskDefinition => taskDefinition.Deleted, 0)
.Then()
.UseJoiner()
.UseDerivedTableJoiner <TaskDefinition>()
.LeftJoin(taskDefinition => taskDefinition.Id, taskExecutionHistoryQBuilder, nameof(TaskExecutionHistory.TaskDefinitionId))
/*.UseDerivedTableJoiner<Ender>()
* .LeftJoin(ender => ender.EndTime, taskExecutionHistoryQBuilder, nameof(TaskExecutionHistory.StartTime))*/
.Then()
.Build();
}
// Start is called before the first frame update
void Start()
{
qBuilder = new QBuilder().OnComplete(OnBuilderReady);
keycodes = new List <KeyCode> {
KeyCode.Alpha1, KeyCode.Alpha2, KeyCode.Alpha3, KeyCode.Alpha4, KeyCode.Alpha5
};
keycodesLength = keycodes.Count;
}
public QBuilder RightJoin <TLeftField, TRightField>(Expression <Func <TLeftTable, TLeftField> > leftFieldNameDescriptor, Expression <Func <TRightTable, TRightField> > rightFieldNameDescriptor)
{
var leftField = _fieldNameResolver.GetFieldName(leftFieldNameDescriptor);
var rightField = _fieldNameResolver.GetFieldName(rightFieldNameDescriptor);
QBuilder.UseJoiner().RightJoin <TLeftTable, TRightTable>(leftField, rightField);
return(QBuilder);
}
public QBuilder GroupBy <TField>(Expression <Func <TTable, TField> > fieldNameDescriber)
{
var fieldName = new FieldNameResolver().GetFieldName(fieldNameDescriber);
QBuilder.UseGrouper()
.GroupBy <TTable>(fieldName);
return(QBuilder);
}
//////////////////////////////////////////////////////////////////////////
/// 2rd way:
/// This method demonstrates on-demand manual filling of metadata structure using
/// corresponding MetadataContainer.ItemMetadataLoading event
//////////////////////////////////////////////////////////////////////////
private void btn2Way_Click(object sender, EventArgs e)
{
// allow QueryBuilder to request metadata
QBuilder.MetadataLoadingOptions.OfflineMode = false;
QBuilder.MetadataProvider = null;
QBuilder.MetadataContainer.ItemMetadataLoading += way2ItemMetadataLoading;
QBuilder.InitializeDatabaseSchemaTree();
}
public GroupBuilder GroupBy <TTable>(string field)
{
GroupFields.Add(new GroupDescription
{
FieldName = field,
TableName = QBuilder.TableNameResolver(typeof(TTable)),
});
return(this);
}
public WhereConjuntionBuilder Where(Type tableType, string field, string condition)
{
_wheres.Add(new WhereDescription
{
Clause = $"{QBuilder.TableNameAliaser.GetTableAlias(QBuilder.TableNameResolver(tableType))}.{field} {condition}",
Conjunction = _nextConjuntion,
ParenthesesId = CurrentParentheses.Id,
});
return(_whereConjunctionBuilder);
}
private void QueueJoin <TLeftTable, TRightTable>(string leftField, string rightField, string joinType)
{
Joins.Add(new JoinDescription
{
LeftField = leftField,
LeftTable = QBuilder.TableNameResolver(typeof(TLeftTable)),
RightField = rightField,
RightTable = QBuilder.TableNameResolver(typeof(TRightTable)),
JoinType = joinType,
});
}
private void MenuItem_RefreshMetadata_OnClick(object sender, RoutedEventArgs e)
{
if (QBuilder.SQLContext == null || QBuilder.SQLContext.MetadataProvider == null ||
!QBuilder.SQLContext.MetadataProvider.Connected)
{
return;
}
// Force the query builder to refresh metadata from current connection
// to refresh metadata, just clear MetadataContainer and reinitialize metadata tree
QBuilder.MetadataContainer.Clear();
QBuilder.InitializeDatabaseSchemaTree();
}
private void InitializeSqlContext()
{
try
{
QBuilder.Clear();
BaseMetadataProvider metadataProvaider = null;
// create new SqlConnection object using the connections string from the connection form
if (!_selectedConnection.IsXmlFile)
{
metadataProvaider = _selectedConnection.ConnectionDescriptor.MetadataProvider;
}
// setup the query builder with metadata and syntax providers
QBuilder.SQLContext.MetadataContainer.Clear();
QBuilder.MetadataProvider = metadataProvaider;
QBuilder.SyntaxProvider = _selectedConnection.ConnectionDescriptor.SyntaxProvider;
QBuilder.MetadataLoadingOptions.OfflineMode = metadataProvaider == null;
if (metadataProvaider == null)
{
QBuilder.MetadataContainer.ImportFromXML(_selectedConnection.ConnectionString);
}
// Instruct the query builder to fill the database schema tree
QBuilder.InitializeDatabaseSchemaTree();
}
finally
{
if (QBuilder.MetadataContainer.LoadingOptions.OfflineMode)
{
TsmiOfflineMode.IsChecked = true;
}
if (CBuilder.QueryTransformer != null)
{
CBuilder.QueryTransformer.SQLUpdated -= QueryTransformer_SQLUpdated;
}
CBuilder.QueryTransformer = new QueryTransformer
{
Query = QBuilder.SQLQuery,
SQLGenerationOptions = QBuilder.SQLGenerationOptions
};
CBuilder.QueryTransformer.SQLUpdated += QueryTransformer_SQLUpdated;
DataGridResult.QueryTransformer = CBuilder.QueryTransformer;
DataGridResult.SqlQuery = QBuilder.SQLQuery;
}
}
private bool TableNotKnown(string table)
{
var joiner = QBuilder.UseJoiner();
if (joiner.JoinsExist == false)
{
return(false);
}
else
{
return(joiner.TableNotKnown(table));
}
}
public void NonDerivedTableJoinsWorkFine()
{
var query = new QBuilder("t")
.UseTableBoundSelector <WorkflowInstance>()
.Select(t => t.Id)
.Then()
.UseTableBoundSelector <WorkflowInstanceState>()
.Select(t => t.Created)
.Then()
.UseTableBoundJoinBuilder <WorkflowInstance, WorkflowInstanceState>()
.InnerJoin(l => l.Id, r => r.WorkflowInstanceId)
.Build();
}
internal void InitialDerivedTableJoin(string rightField, string rightTable, QBuilder derivedTable, string derivedFieldName, string joinType)
{
var derivedTableJoinDescription = new DerivedTableJoinDescription
{
RightField = rightField,
RightTable = rightTable,
LeftField = derivedFieldName,
QBuilder = derivedTable,
JoinType = joinType,
};
JoinedDerivedTables.Add(derivedTable.DerivedTableName);
TranslateToJoinDescription(derivedTableJoinDescription);
}
//////////////////////////////////////////////////////////////////////////
/// 1st way:
/// This method demonstrates the direct access to the internal metadata
/// objects collection (MetadataContainer).
//////////////////////////////////////////////////////////////////////////
private void btn1Way_Click(object sender, EventArgs e)
{
// prevent QueryBuilder to request metadata
QBuilder.MetadataLoadingOptions.OfflineMode = true;
QBuilder.MetadataProvider = null;
MetadataContainer metadataContainer = QBuilder.MetadataContainer;
metadataContainer.BeginUpdate();
try
{
metadataContainer.Clear();
MetadataNamespace schemaDbo = metadataContainer.AddSchema("dbo");
// prepare metadata for table "Orders"
MetadataObject orders = schemaDbo.AddTable("Orders");
// fields
orders.AddField("OrderId");
orders.AddField("CustomerId");
// prepare metadata for table "Order Details"
MetadataObject orderDetails = schemaDbo.AddTable("Order Details");
// fields
orderDetails.AddField("OrderId");
orderDetails.AddField("ProductId");
// foreign keys
MetadataForeignKey foreignKey = orderDetails.AddForeignKey("OrderDetailsToOrders");
using (MetadataQualifiedName referencedName = new MetadataQualifiedName())
{
referencedName.Add("Orders");
referencedName.Add("dbo");
foreignKey.ReferencedObjectName = referencedName;
}
foreignKey.Fields.Add("OrderId");
foreignKey.ReferencedFields.Add("OrderId");
}
finally
{
metadataContainer.EndUpdate();
}
QBuilder.InitializeDatabaseSchemaTree();
}
public MainWindow()
{
InitializeComponent();
// add connections
var metadataContainer = QBuilder.MetadataContainer;
foreach (var connectionDescription in _connections)
{
var name = connectionDescription.Key;
var innerContext = connectionDescription.Value;
metadataContainer.AddConnection(name, innerContext);
}
QBuilder.InitializeDatabaseSchemaTree();
}
/// <summary>
/// Initialize current provider for the site (make a full copy)
/// </summary>
/// <param name="copiedProvider">Source for the copy</param>
public CurrentProvider
(
CurrentProvider copiedProvider
)
{
Person = new Person
(
copiedProvider.Person
);
User = new User
(
copiedProvider.User
);
RequestBuilder = new QBuilder();
DataManipulation = new DataManipulator();
}
private void MenuItem_LoadMetadata_OnClick(object sender, RoutedEventArgs e)
{
var fileDialog = new OpenFileDialog {
Filter = "XML files (*.xml)|*.xml|All files (*.*)|*.*"
};
if (fileDialog.ShowDialog() != true)
{
return;
}
QBuilder.MetadataContainer.LoadingOptions.OfflineMode = true;
QBuilder.MetadataContainer.ImportFromXML(fileDialog.FileName);
// Instruct the query builder to fill the database schema tree
QBuilder.InitializeDatabaseSchemaTree();
}
public void SqlServerPagingIsDoneCorrectly()
{
var query = new QBuilder()
.UseTableBoundSelector <User>()
.Select(a => a.Id)
.Then()
.UseTableBoundSelector <WorkflowInstance>()
.Select(a => a.UserId)
.Then()
.UseTableBoundJoinBuilder <User, WorkflowInstance>()
.InnerJoin(u => u.Id, w => w.UserId)
.UseSqlServerPagingBuilder <User>()
.PageBy(a => a.Name, 1, 4)
.Build();
var x = 9;
}
internal JoinBuilder JoinDerivedTable <TRightTable, TRightField>(Expression <Func <TRightTable, TRightField> > rightFieldNameDescriptor, QBuilder derivedTable, string derivedFieldName, string joinType)
{
var alreadyUsedDerivedTableInPreviousJoin = JoinedDerivedTables.FirstOrDefault(a => a.Equals(derivedTable.DerivedTableName, StringComparison.InvariantCultureIgnoreCase))
!= null;
var rightField = _fieldNameResolver.GetFieldName(rightFieldNameDescriptor);
var rightTable = QBuilder.TableNameResolver(typeof(TRightTable));
if (alreadyUsedDerivedTableInPreviousJoin)
{
SecondaryDerivedTableJoin(rightField, rightTable, derivedTable, derivedFieldName, joinType);
}
else
{
InitialDerivedTableJoin(rightField, rightTable, derivedTable, derivedFieldName, joinType);
}
return(this);
}
public QBuilder PageBy <TField>(Expression <Func <TTable, TField> > fieldNameDescriber, uint page, ushort pageSize, bool orderAscending = true)
{
var fieldName = new FieldNameResolver().GetFieldName(fieldNameDescriber);
var tableName = QBuilder.TableNameAliaser.GetTableAlias <TTable>();
var range = PageRangeCalculator.GetPageRange(0, page, pageSize);
var orderClause = $"Order By `{fieldName}`";
if (orderAscending)
{
orderClause += " Asc";
}
else
{
orderClause += " Desc";
}
QBuilder.SetSuffix($" {orderClause} Limit {range.Start},{range.PageSize}");
return(QBuilder);
}
public QBuilder PageBy <TField>(Expression <Func <TTable, TField> > fieldNameDescriber, uint page, ushort pageSize, bool orderAscending = true)
{
new DataValidator()
.AddFailureCondition(page < 1, $"Database query requested for page '{page}'. Pages must be greater than or equal to 1", false)
.AddFailureCondition(pageSize < 1, $"Pages must have at least one record. Page size '{pageSize}' is not valid", false)
.ThrowExceptionOnInvalidRules();
const string rowNumber = "RowNumber";
string orderSuffix = orderAscending ? "Asc" : "Desc";
var fieldName = new FieldNameResolver().GetFieldName(fieldNameDescriber);
var table = QBuilder.TableNameAliaser.GetTableAlias <TTable>();
var range = PageRangeCalculator.GetPageRange(AbsoluteFirstRecordIndex, page, pageSize);
QBuilder.UseSelector()
.SetSelectPrefix($"ROW_NUMBER() OVER (ORDER BY [{table}].[{fieldName}] {orderSuffix}) AS {rowNumber},")
.Then()
.UseFilter();
QBuilder.SetSuffix($"Where {rowNumber} >= {range.Start} AND {rowNumber} <= {range.End}");
return(QBuilder);
}
public void DerivedTableJoinsWorkFine()
{
var derivedQuery = new QBuilder("derived")
.UseTableBoundSelector <WorkflowInstanceState>()
.SelectAggregated(t => t.Created, "Created", "Max")
.Select(t => t.WorkflowInstanceId)
.Then()
.UseTableBoundGrouper <WorkflowInstanceState>()
.Then();
var query = new QBuilder("t")
.UseTableBoundSelector <WorkflowInstance>()
.Select(t => t.Id)
.Then()
.UseDerivedTableSelector(derivedQuery)
.Select("*")
.Then()
.UseJoiner()
.UseDerivedTableJoiner <WorkflowInstance>()
.InnerJoin(t => t.Id, derivedQuery, nameof(WorkflowInstanceState.WorkflowInstanceId))
.InnerJoin <User, WorkflowInstance>(nameof(User.Id), nameof(WorkflowInstance.UserId))
.Then()
.Build();
}
public WhereConjuntionBuilder(WhereBuilder whereBuilder, QBuilder qBuilder)
: this(qBuilder)
{
_whereBuilder = whereBuilder;
}
public TableBoundWhereBuilder <TTable> Or <TTable>()
{
Or();
return(QBuilder.UseTableBoundFilter <TTable>());
}
public TableBoundWhereBuilder <TTable> And <TTable>()
{
And();
return(QBuilder.UseTableBoundFilter <TTable>());
}
