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.

Trace Entries: 2

...Framework.Controllers.HomeController.UpdateCustomerUsingSecurityDecoration:

[HttpGet]
public async Task<ViewResult> UpdateCustomerUsingSecurityDecoration()
{
    var traceTree = _traceTreesRepository.Record();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    var customer = new Customer(
        _customer1Id,
        "John Doe: Updated",
        _division1Id,
        new Address(id: new Guid("22222222-2222-2222-2222-222222222222"), "Home town"),
        billingAddress: null);

    var result = await _customerService.UpdateCustomerUsingSecurityDecoration(
        customer,
        _user2Id,  /* User with Write rights for customer */
        traceTree);

    return CreateView(traceTree, result);
}

Trace Entries: 2

...Framework.Services.CustomerService.UpdateCustomerUsingSecurityDecoration:

public async Task<bool> UpdateCustomerUsingSecurityDecoration(
    Customer customer,
    Guid userId,
    TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    var customerTable = new CustomerTable();
    var update = /*WYSE*/UpdateBuilder.BuildByKey(customerTable, customer);

    var securityDecorator = new /*WYSE*/SecurityDecorator(
        new PrincipalTable(),  /* Users and groups the users can be member of */
        new PrincipalMembershipTable(),  /* Users/groups membership */
        membershipCteName: "Membership");  // If omitted default a generated Guid-name

    // Decorate the original Update in such a way that only if 'userId'
    // has the necessary Write rights the update takes place.
    var decoratedUpdate = securityDecorator.Decorate(update, userId);

    var result = await _sqlExecuter.ExecuteNonQueryWithRollbackAsync(
        decoratedUpdate,
        traceTree);

    return result == 1;
}

Trace Entries: 2

...Framework.DataSpace.Access.SqlExecuter.ExecuteNonQueryWithRollbackAsync:

public async Task<int> ExecuteNonQueryWithRollbackAsync(
    /*WYSE*/IExecutable executable,
    TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    using SqlConnection connection = _connectionFactory.Create();
    connection.Open();
    using var transaction = connection.BeginTransaction();

    // 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);
    command.Transaction = transaction;

    try
    {
        var result = await command.ExecuteNonQueryAsync();

        traceTree.RecordDbCommandExecutionSuccess(
            command.CommandText,
            command.Parameters.ToDictionary(),
            result);

        transaction.Rollback();

        return result;
    }
    catch (Exception exception)
    {
        traceTree.RecordDbCommandExecutionFailure(
            command.CommandText,
            command.Parameters.ToDictionary(),
            exception);

        return -1;
    }
}

Command Parameters:

@PrincipalId

22222222-2222-2222-2222-222222222222

Command Text:

WITH [Membership] ([PrincipalId], [MemberOfPrincipalId])
AS
(
    SELECT
        [56154_FrameworkDemoSystems_dbo_PrincipalMembership_1].[PrincipalId] [56154_FrameworkDemoSystems_dbo_PrincipalMembership_PrincipalId_1],
        [56154_FrameworkDemoSystems_dbo_PrincipalMembership_1].[MemberOfPrincipalId] [56154_FrameworkDemoSystems_dbo_PrincipalMembership_MemberOfPrincipalId_1]
    FROM [56154_FrameworkDemoSystems].[dbo].[PrincipalMembership] [56154_FrameworkDemoSystems_dbo_PrincipalMembership_1]
    UNION ALL
    SELECT
        [56154_FrameworkDemoSystems_dbo_PrincipalMembership_1].[PrincipalId] [56154_FrameworkDemoSystems_dbo_PrincipalMembership_PrincipalId_1],
        [_GuidMembershipCte_1].[MemberOfPrincipalId] [_GuidCteColumn_1]
    FROM [Membership] [_GuidMembershipCte_1]
    INNER JOIN [56154_FrameworkDemoSystems].[dbo].[PrincipalMembership] [56154_FrameworkDemoSystems_dbo_PrincipalMembership_1] ON ([_GuidMembershipCte_1].[PrincipalId] = [56154_FrameworkDemoSystems_dbo_PrincipalMembership_1].[MemberOfPrincipalId])
)
UPDATE [56154_FrameworkDemoCustomers_demo_Customer]
SET [56154_FrameworkDemoCustomers_demo_Customer].[Created] = '2025-05-23 14:18:09.615',
    [56154_FrameworkDemoCustomers_demo_Customer].[LastModified] = '2025-05-23 14:18:09.615',
    [56154_FrameworkDemoCustomers_demo_Customer].[IsDeleted] = 0,
    [56154_FrameworkDemoCustomers_demo_Customer].[FullName] = 'John Doe: Updated',
    [56154_FrameworkDemoCustomers_demo_Customer].[DivisionId] = '11111111-1111-1111-1111-111111111111',
    [56154_FrameworkDemoCustomers_demo_Customer].[HomeAddressId] = '22222222-2222-2222-2222-222222222222',
    [56154_FrameworkDemoCustomers_demo_Customer].[BillingAddressId] = NULL
FROM [56154_FrameworkDemoCustomers].[demo].[Customer] [56154_FrameworkDemoCustomers_demo_Customer]
CROSS APPLY
(
    SELECT TOP(1) 1 [_NumberConst_1]
    FROM [56154_FrameworkDemoSystems].[dbo].[Principal] [56154_FrameworkDemoSystems_dbo_Principal]
    LEFT JOIN [Membership] [_GuidMembershipCte_2] ON ([56154_FrameworkDemoSystems_dbo_Principal].[Id] = [_GuidMembershipCte_2].[MemberOfPrincipalId])
    INNER JOIN [56154_FrameworkDemoCustomers].[demo].[CustomerPrincipalRights] [56154_FrameworkDemoCustomers_demo_CustomerPrincipalRights] ON ([56154_FrameworkDemoSystems_dbo_Principal].[Id] = [56154_FrameworkDemoCustomers_demo_CustomerPrincipalRights].[PrincipalId])
    WHERE
    (
        (
            ([56154_FrameworkDemoSystems_dbo_Principal].[Id] = @PrincipalId)
            OR
            ([_GuidMembershipCte_2].[PrincipalId] = @PrincipalId)
        )
        AND
        ([56154_FrameworkDemoCustomers_demo_CustomerPrincipalRights].[CustomerId] = [56154_FrameworkDemoCustomers_demo_Customer].[Id])
        AND
        (([56154_FrameworkDemoCustomers_demo_CustomerPrincipalRights].[RightType] & 2) = 2) /* Has 'Write' flag */
    )
) [_Select_3]
WHERE ([56154_FrameworkDemoCustomers_demo_Customer].[Id] = '11111111-1111-1111-1111-111111111111')

Command Parameters:

@PrincipalId

22222222-2222-2222-2222-222222222222

Command Text:

WITH [Membership] ([PrincipalId], [MemberOfPrincipalId])
AS
(
    SELECT
        [56154_FrameworkDemoSystems_dbo_PrincipalMembership_1].[PrincipalId] [56154_FrameworkDemoSystems_dbo_PrincipalMembership_PrincipalId_1],
        [56154_FrameworkDemoSystems_dbo_PrincipalMembership_1].[MemberOfPrincipalId] [56154_FrameworkDemoSystems_dbo_PrincipalMembership_MemberOfPrincipalId_1]
    FROM [56154_FrameworkDemoSystems].[dbo].[PrincipalMembership] [56154_FrameworkDemoSystems_dbo_PrincipalMembership_1]
    UNION ALL
    SELECT
        [56154_FrameworkDemoSystems_dbo_PrincipalMembership_1].[PrincipalId] [56154_FrameworkDemoSystems_dbo_PrincipalMembership_PrincipalId_1],
        [_GuidMembershipCte_1].[MemberOfPrincipalId] [_GuidCteColumn_1]
    FROM [Membership] [_GuidMembershipCte_1]
    INNER JOIN [56154_FrameworkDemoSystems].[dbo].[PrincipalMembership] [56154_FrameworkDemoSystems_dbo_PrincipalMembership_1] ON ([_GuidMembershipCte_1].[PrincipalId] = [56154_FrameworkDemoSystems_dbo_PrincipalMembership_1].[MemberOfPrincipalId])
)
UPDATE [56154_FrameworkDemoCustomers_demo_Customer]
SET [56154_FrameworkDemoCustomers_demo_Customer].[Created] = '2025-05-23 14:18:09.615',
    [56154_FrameworkDemoCustomers_demo_Customer].[LastModified] = '2025-05-23 14:18:09.615',
    [56154_FrameworkDemoCustomers_demo_Customer].[IsDeleted] = 0,
    [56154_FrameworkDemoCustomers_demo_Customer].[FullName] = 'John Doe: Updated',
    [56154_FrameworkDemoCustomers_demo_Customer].[DivisionId] = '11111111-1111-1111-1111-111111111111',
    [56154_FrameworkDemoCustomers_demo_Customer].[HomeAddressId] = '22222222-2222-2222-2222-222222222222',
    [56154_FrameworkDemoCustomers_demo_Customer].[BillingAddressId] = NULL
FROM [56154_FrameworkDemoCustomers].[demo].[Customer] [56154_FrameworkDemoCustomers_demo_Customer]
CROSS APPLY
(
    SELECT TOP(1) 1 [_NumberConst_1]
    FROM [56154_FrameworkDemoSystems].[dbo].[Principal] [56154_FrameworkDemoSystems_dbo_Principal]
    LEFT JOIN [Membership] [_GuidMembershipCte_2] ON ([56154_FrameworkDemoSystems_dbo_Principal].[Id] = [_GuidMembershipCte_2].[MemberOfPrincipalId])
    INNER JOIN [56154_FrameworkDemoCustomers].[demo].[CustomerPrincipalRights] [56154_FrameworkDemoCustomers_demo_CustomerPrincipalRights] ON ([56154_FrameworkDemoSystems_dbo_Principal].[Id] = [56154_FrameworkDemoCustomers_demo_CustomerPrincipalRights].[PrincipalId])
    WHERE
    (
        (
            ([56154_FrameworkDemoSystems_dbo_Principal].[Id] = @PrincipalId)
            OR
            ([_GuidMembershipCte_2].[PrincipalId] = @PrincipalId)
        )
        AND
        ([56154_FrameworkDemoCustomers_demo_CustomerPrincipalRights].[CustomerId] = [56154_FrameworkDemoCustomers_demo_Customer].[Id])
        AND
        (([56154_FrameworkDemoCustomers_demo_CustomerPrincipalRights].[RightType] & 2) = 2) /* Has 'Write' flag */
    )
) [_Select_3]
WHERE ([56154_FrameworkDemoCustomers_demo_Customer].[Id] = '11111111-1111-1111-1111-111111111111')

Framework Demo

Advanced Language Features

Behind The Scenes Explanation

1. Full Code, Queries, And Results

2. Queries And Results Only

3. API Results

Minimize SQL:
Naming strategy: