Language And ORM Demo

Language And Object Relational Mapping Demo

This demo, based on SqlServer, is meant to showcase the strongly typed excellence and range of the WYSE framework, along with its groundbreaking (Hierarchical) Object Relation Mapping capabilities. 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.

Below you can find, grouped firstly by SQL statement type and later by object relational mapping feature, a list of links. These links are server calls in which increasingly advanced WYSE features of language and ORM are used.

Basic Query 1 Basic Query 2 Basic Query With Top Query With Complex Expression Field Values Query With Built-In Function Field Values Query With Case Statement Distinct Enum Values Query With OrderBy Query With Advanced OrderBy With Paging Query With Where Query With Advanced Where Exists Query Join Query Apply Query Union Query Aggregates Query Query With GroupBy And Having Query With RowNumber Over Query Of A Subselect Query With A Select As A Field Query With Join With A Select Query With CTE View Query Scalar Valued Function Query Table Valued Function Query Stored Procedure Query Async Query Using Async Iteration

Basic Insert Insert With Identity Insert With Select From Table Variable Insert With Select From Temp Table

Basic Update Update With Top Update With Join Update Using Stored Procedure

Basic Delete Delete With Top Delete With Join

Merge

Generate a script that inserts multiple items Upsert: Ensure a record exists based on a key Reader reads the default/first select Reader sets next result Reader does a lookup of a select

Basic One To One Relation (Version 1) Basic One To One Relation (Version 2) Basic One To Many Relation (Version 1) Basic One To Many Relation (Version 2) Entangled Properties (Version 1) Entangled Properties (Version 2) Complex Hierarchies (Version 1) Complex Hierarchies (Version 2) Generic Mappings (Version 1) Generic Mappings (Version 2) Generic Mappings With SelectBuilder (Version 1) Generic Mappings With SelectBuilder (Version 2) Custom And Generic Mappings Mix Mappings Using Subselects Generic Mappings Using Subselects Using Async Iteration

Plain Text Expression Building

Be sure to check out the Report Generator demo. That playable demo explores what is possible in combination with self-constructed plain text where clauses. So it is functionally similar to GraphQL.

Working With State (BETA)

The 'Generic Mappings With SelectBuilder' examples listed above showcase how objects can be fully instantiated hierarchically. But that is only the 'reading' part. The 'writing' part is where State comes into play. With this, given an object with properties that have properties and so forth, a script will be generated to sync the state of the database with with that of the object. This script will contain all the necessary inserts, updates and/or deletes. The deletes are important when a property is a list of objects, so with 1:n relationships.

Hierarchical State Persistence

Retrieve a subclassed list (Version 1) Retrieve a subclassed list (Version 2)

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

Trace Entries: 2

...LanguageAndORM.Controllers.HomeController.Merge:

[HttpGet]
public ViewResult Merge()
{
    var traceTree = _traceTreesRepository.Record();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    var result = _mergesService.GetMergeResult(traceTree);

    return CreateView(_traceTreesRepository.RootTraceTree, result);
}

Trace Entries: 4

...LanguageAndORM.Business.MergesService.GetMergeResult:

public MergeResult GetMergeResult(TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    var renderContext = _renderContextFactory.Create();

    using SqlConnection connection = _connectionFactory.Create();
    connection.Open();
    using SqlTransaction transaction = connection.BeginTransaction();

    var addressesBeforeMerge = getAddresses();

    merge();  // See local function below

    var addressesAfterMerge = getAddresses();  // See local function below

    transaction.Rollback();

    return new MergeResult(addressesBeforeMerge, addressesAfterMerge);

    IReadOnlyList<Address> getAddresses()
    {
        var addressTable = new AddressTable();
        var select = /*WYSE*/SelectBuilder.Build(addressTable);

        _identifiersResetter.ResetAliases(select);

        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(select, renderContext, _createRenderBuilder);
        command.Transaction = transaction;

        try
        {
            using DbDataReader reader = command.ExecuteReader();

            var items = reader./*WYSE*/GetItems(addressTable.Convert).ToList();

            traceTree.RecordDbCommandExecutionSuccess(
                command.CommandText,
                command.Parameters.ToDictionary(),
                items);

            return items;
        }
        catch (Exception exception)
        {
            traceTree.RecordDbCommandExecutionFailure(
                command.CommandText,
                command.Parameters.ToDictionary(),
                exception);

            throw;
        }
    }

    void merge()
    {
        var addressTable = new AddressTable();
        var townsSource = new /*WYSE*/StringSplit("Town1,Town2,Town3,Town5,Town6,Town7,Town8", ','); // 'Not Town4'
        Merge merge = new /*WYSE*/Merge()
            .Top(60).Percent()
            .Into(addressTable)
            .Using(townsSource)
            .On(addressTable.Town == townsSource.Value)
            .WhenMatchedThenUpdate(
                addressTable.Town./*WYSE*/WithValue(addressTable.Town + " - Updated"))
            .WhenNotMatchedByTargetThenInsert(
                addressTable.Town./*WYSE*/WithValue(townsSource.Value + " - Added"),
                addressTable.Confirmed./*WYSE*/WithValue(true))
            .WhenNotMatchedBySourceThenDelete();

        _identifiersResetter.ResetAliases(merge);

        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(merge, renderContext, _createRenderBuilder);
        command.Transaction = transaction;

        try
        {
            var result = command.ExecuteNonQuery();

            traceTree.RecordDbCommandExecutionSuccess(
                command.CommandText,
                command.Parameters.ToDictionary(),
                result);
        }
        catch (Exception exception)
        {
            traceTree.RecordDbCommandExecutionFailure(
                command.CommandText,
                command.Parameters.ToDictionary(),
                exception);
        }
    }
}

Command Text:

SELECT
    [dbo_Address].[Town] [dbo_Address_Town],
    [dbo_Address].[Confirmed] [dbo_Address_Confirmed],
    [dbo_Address].[LastChanged] [dbo_Address_LastChanged],
    [dbo_Address].[ExternalId] [dbo_Address_ExternalId],
    [dbo_Address].[Id] [dbo_Address_Id]
FROM [dbo].[Address] [dbo_Address]

Command Text:

MERGE TOP(60) PERCENT
INTO [dbo].[Address] [dbo_Address]
USING STRING_SPLIT('Town1,Town2,Town3,Town5,Town6,Town7,Town8', ',') [_StringSplit] ON ([dbo_Address].[Town] = [_StringSplit].[value])
WHEN MATCHED THEN UPDATE
    SET [Town] = CONCAT([dbo_Address].[Town], ' - Updated')
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Town], [Confirmed]) VALUES (CONCAT([_StringSplit].[value], ' - Added'), 1)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

Command Text:

SELECT
    [dbo_Address].[Town] [dbo_Address_Town],
    [dbo_Address].[Confirmed] [dbo_Address_Confirmed],
    [dbo_Address].[LastChanged] [dbo_Address_LastChanged],
    [dbo_Address].[ExternalId] [dbo_Address_ExternalId],
    [dbo_Address].[Id] [dbo_Address_Id]
FROM [dbo].[Address] [dbo_Address]

Command Text:

SELECT
    [dbo_Address].[Town] [dbo_Address_Town],
    [dbo_Address].[Confirmed] [dbo_Address_Confirmed],
    [dbo_Address].[LastChanged] [dbo_Address_LastChanged],
    [dbo_Address].[ExternalId] [dbo_Address_ExternalId],
    [dbo_Address].[Id] [dbo_Address_Id]
FROM [dbo].[Address] [dbo_Address]

Command Text:

MERGE TOP(60) PERCENT
INTO [dbo].[Address] [dbo_Address]
USING STRING_SPLIT('Town1,Town2,Town3,Town5,Town6,Town7,Town8', ',') [_StringSplit] ON ([dbo_Address].[Town] = [_StringSplit].[value])
WHEN MATCHED THEN UPDATE
    SET [Town] = CONCAT([dbo_Address].[Town], ' - Updated')
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Town], [Confirmed]) VALUES (CONCAT([_StringSplit].[value], ' - Added'), 1)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

Command Text:

SELECT
    [dbo_Address].[Town] [dbo_Address_Town],
    [dbo_Address].[Confirmed] [dbo_Address_Confirmed],
    [dbo_Address].[LastChanged] [dbo_Address_LastChanged],
    [dbo_Address].[ExternalId] [dbo_Address_ExternalId],
    [dbo_Address].[Id] [dbo_Address_Id]
FROM [dbo].[Address] [dbo_Address]

Language And Object Relational Mapping Demo

Plain Text Expression Building

Working With State (BETA)

Behind The Scenes Explanation

1. Full Code, Queries, And Results

2. Queries And Results Only

3. API Results

Minimize SQL:
Naming strategy: