Framework Demo

This demo, based on SqlServer, is meant to show the versatility of the WYSE framework and the freedom that goes wit it. Combining .NET with SQL to create something that is more and better than the sum of the two parts. The following are examples of how better database interaction could take shape, but should not be considered as 'this is how you should do it'. WYSE is built to offer the freedom to do it the way you want. The idea is to be able to code without having to make code quality damaging compromises.

...and since code speaks louder than words demo code (stack trace) and database results are shown in a developer-tools-like panel as you interact with the server.

Advanced Language Features

The framework supports several ways of building queries and also facilitates dynamic SQL statements decoration. The latter enables to statements to be modified, which in turn offers the possibility to neatly encapsulate query building code centrally.

Because WYSE's SQL statements have the fluent structure of plain SQL but are actually .NET class-based, code reuse becomes a lot easier and more flexible and powerful, especially if your tables/views share characteristics.

The following example uses custom Selects that are tailored to SQL tables that all have an 'Id' column and an 'IsDeleted' column. Because of this, the logic that goes with that is encapsulated in a base Subselect class (that, in turn, inherits from WYSE's Select class)

Additionally, the wrapped Selects implement the ability to produce so-called GenericMappings. They are WYSE's way of taking the mapping code out of the individual joins and storing them centrally. This benefits code reuse greatly (and they make some other unique and cool hierarchical things possible too).

Get a dashboard by id (complex query)

Building on the previous example featuring custom Selects... The following examples feature a shared/generic repository base that implements basic CRUD operations.

This repository base uses a centralized custom QueryBackbone that can decorate a query in a way that paging is added.

Get an address by id Get addresses (with centralized custom paging) Create an address Delete an address by id Upsert an address by id

As an alternative for building queries the fluent way, WYSE also has builders for Selects, Inserts, Deletes, and Updates. In their simple form they are good as a short and easy way to create basic statements. Beyond that they are great for conditionally adding commonly used query parts like OrderBy, Top and Having for example. They are also useful as a base to supply default parts for your application's queries.

Among other things, The next demo example shows a builder that guarantees proper usage of an IsDeleted column in the Where clause.

Get customers using a builder (with paging)

To be able to dynamically rebuild a given query based on your needs and the rules you dictate can be a very powerful way to elevate your database usage. The versatility it offers can help reduce the need for having to code similar SQL statements (or parts of them) throughout a code base. And that will improve your software's overall maintainability.

In the three examples below the custom QueryBackbone is used to add this demo's division-based security SQL code when/where needed. In this demo that is anywhere in a query where a table is used that has a DivisionId column (like the Customer table) and because of that an additional InnerJoin is needed.
Please note: This custom division security is hierarchical in nature.

Get customer fluently Get customers fluently (with paging) Get customers with generic mapping (with paging)

In the case of MySql and SqlServer WYSE supports row-level security (RLS) through SQL statement decoration. This means SQL statements can be constructed without bothering with security, and afterward WYSE rebuilds the original statement based on the security situation.
MySql supports user-based RLS. With SqlServer both user and group-based RLS are supported.

A 'Select' is security-decorated An 'Insert' is security-decorated (within a rollbacked transaction) An 'Update' is security-decorated (within a rollbacked transaction) A 'Delete' is security-decorated (within a rollbacked transaction)

SQL is versatile, but there are things it is not good at. For example, cases where one row has to be paired with the next. To help the developer out WYSE comes with a means to enumerate transitions generically. The following example uses that.

A list of price transitions with a minimal price increase

Everything changes in time. Because of that data oftentimes pertains to a certain period. In those cases where you want to store periods that are guaranteed adjacent (going from one record to another) what you want to store is a single date, and not a from/to (because it is more cumbersome to manage and maintain validity).
WYSE assists here by offering a specialized dictionary. Using it you can easily go from records stored with a single date (in this case 'StartingFrom') to a period-key/item dictionary.
The following example uses the dictionary to retrieve the periods a product had a certain price.

A dictionary with date ranges for a key and items as the values

PS Besides DateTime, other types can be used in pairs to form a range making a dictionary key.

The small window you are using greatly diminishes your demo experience. Please consider viewing using a larger window.

Behind The Scenes Explanation

This is where you get to see what's happening behind the scenes when you interact with the site on the left side.

The view consists of three (collapsible) parts...

1. Full Code, Queries, And Results

The server handles the web requests that are the result of you interacting with the site.
In doing so, the demo backend code is executed which in turn uses WYSE.

The relevant/notable methods taking part in that process are shown in the first vertical tab.
Basically you are seeing a (partial) stack trace.
It is tree structure of which the nodes are collapsible/expandable for your browsing convenience.

The leaves of the STACK TRACE TREE are where interaction with the database takes place.
That is generally where you will find the generated SQL statements and the JSON of the converted database results.

2. Queries And Results Only

A filtered list of entries where only the database queries and results are shown.

3. API Results

If applicable: this shows the JSON of the call to the server as if it had been an API call.

  • Trace Entries: 2
      ...Framework.Controllers.HomeController.GetAllProductPriceTransitions: 
      [HttpGet]
public async Task<ViewResult> GetAllProductPriceTransitions(
    [FromQuery] decimal minimalPriceIncrease)
{
    var traceTree = _traceTreesRepository.Record();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    var result = await _productService.GetAllProductPriceTransitionsResult(
        traceTree,
        minimalPriceIncrease);

    return CreateView(traceTree, result);

}
    • Trace Entries: 2
        ...Framework.Services.ProductService.GetAllProductPriceTransitionsResult: 
        public async Task<IReadOnlyList<Transition<ProductPrice>>> GetAllProductPriceTransitionsResult(
    TraceTree parentTraceTree,
    decimal minimalPriceIncrease)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    // A ProductPrice does not have a date range, merely a StartingFrom
    var productPrices = await _productRepository.GetAllProductPrices(traceTree);

    return new /*WYSE*/TransitionEnumerable<ProductPrice>(productPrices)
        .Where(t => t.To.Price - t.From?.Price >= minimalPriceIncrease)
        .ToList();
}
      • Trace Entries: 3
          ...Framework.Repositories.Systems.ProductRepository.GetAllProductPrices: 
          public async Task<IReadOnlyList<ProductPrice>> GetAllProductPrices(TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    var productPriceTable = new ProductPriceTable(); 

    return await _sqlExecuter.GetItemsAsync(
        /*WYSE*/SelectBuilder.Build(productPriceTable),
        productPriceTable.Convert,
        traceTree);
}
        • Trace Entries: 1
            ...Framework.DataSpace.Access.SqlExecuter.GetItemsAsync: 
            public async Task<IReadOnlyList<TItem>> GetItemsAsync<TItem>(
    /*WYSE*/ISelect select,
    Converter<DbDataReader, TItem> converter,
    TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    return await Get(
        select,
        reader => reader./*WYSE*/GetItems(converter).ToList(),
        parentTraceTree);
}
        • Trace Entries: 2
            ...Framework.DataSpace.Access.SqlExecuter.Get: 
            private async Task<TItem> Get<TItem>(
    /*WYSE*/IExecutable executable,  /* A Select or a Script */
    Func<DbDataReader, TItem> getResult,
    TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    using SqlConnection connection = _connectionFactory.Create();
    connection.Open();

    // Aliases and parameter names can be given custom values.
    // If omitted, unique identifer values are supplied
    // In the case of this Demo, these generated values are overwritten,
    // based on the current render settings, with more legible values.
    // WYSE supports several kinds of Resetting/renaming.
    ResetIdentifiers(executable);

    using SqlCommand command = connection.CreateCommand()
        // A WYSE Render context determines certain (overridable) render settings.
        // Here the context is SqlServer. More SQL flavors are supported.
        // A WYSE render builder is used for stringifying
        // the strongly typed SQL statements.
        /*WYSE*/.For(executable, _renderContextFactory.Create(), _createRenderBuilder);

    try
    {
        using var reader = await command.ExecuteReaderAsync();

        var result = getResult(reader);

        traceTree.RecordDbCommandExecutionSuccess(
            command.CommandText,
            command.Parameters.ToDictionary(),
            result);

        return result;
    }
    catch (Exception exception)
    {
        traceTree.RecordDbCommandExecutionFailure(
            command.CommandText,
            command.Parameters.ToDictionary(),
            exception);

        return default;
    }
}

            Command Text: 

            SELECT
    [ProductPrice].[ProductId] [ProductPrice_ProductId],
    [ProductPrice].[Price] [ProductPrice_Price],
    [ProductPrice].[StartingFrom] [ProductPrice_StartingFrom]
FROM [ProductPrice] [ProductPrice]

Command Text: 

SELECT
    [ProductPrice].[ProductId] [ProductPrice_ProductId],
    [ProductPrice].[Price] [ProductPrice_Price],
    [ProductPrice].[StartingFrom] [ProductPrice_StartingFrom]
FROM [ProductPrice] [ProductPrice]