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.

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
    • Trace Entries: 2
        ...LanguageAndORM.Controllers.HomeController.GenericMappingsWithSelectBuilderVersion2: 
        [HttpGet]
public ViewResult GenericMappingsWithSelectBuilderVersion2()
{
    var traceTree = _traceTreesRepository.Record();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    var result = _hormsService.GetGenericMappingsWithSelectBuilderVersion2Result(traceTree);

    return CreateView(_traceTreesRepository.RootTraceTree, result);
}
      • Trace Entries: 2
          ...LanguageAndORM.Business.HormsService.GetGenericMappingsWithSelectBuilderVersion2Result: 
          public Customer GetGenericMappingsWithSelectBuilderVersion2Result(
    TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    var customerTable = new CustomerTable();  // Root table of the hierarchy
    var genericMappings = GenericMappingsRepository.AllTableBased
        /*WYSE*/.DownstreamOf<CustomerTable>();

    var selectBuilder = new /*WYSE*/SelectBuilder(
        customerTable,
        typeof(Customer),
        genericMappings)
    {
        Where = new Where(customerTable.Id == 2)
    };

    // This gets the the whole object hierarchy of Customer,
    // with all the properties set (single object and lists) recursively.
    // JsonSerializerOptions.ReferenceHandler.Preserve to handle
    // circular referencing due to entanglement.
    return _sqlExecuter.GetItemTreeGeneric<CustomerTable, Customer>(
        selectBuilder.Build(),
        customerTable,
        genericMappings,
        traceTree);
}
        • Trace Entries: 2
            ...LanguageAndORM.DatabaseAccess.SqlExecuter.GetItemTreeGeneric: 
            public TItem GetItemTreeGeneric<TList, TItem>(
    /*WYSE*/ISelect select,
    TList primaryList,
    IReadOnlyList</*WYSE*/GenericMapping> genericMappings,
    TraceTree parentTraceTree)
    where TList : /*WYSE*/IDbDataReaderConvertableList<TItem>
    where TItem : /*WYSE*/IKeyEquatable
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    return Get(
        select,
        reader => reader./*WYSE*/GetItemTree<TList, TItem>(
            select,
            primaryList,
            genericMappings),
        traceTree);
}
          • Trace Entries: 2
              ...LanguageAndORM.DatabaseAccess.SqlExecuter.Get: 
              private TItem Get<TItem>(
    /*WYSE*/IExecutable executable,
    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 DbDataReader reader = command.ExecuteReader();

        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
    [dbo_Customer].[FullName] [dbo_Customer_FullName],
    [dbo_Customer].[HomeAddressId] [dbo_Customer_HomeAddressId],
    [dbo_Customer].[BillingAddressId] [dbo_Customer_BillingAddressId],
    [dbo_Customer].[Id] [dbo_Customer_Id],
    [dbo_Address_1].[Town] [dbo_Address_Town_1],
    [dbo_Address_1].[Confirmed] [dbo_Address_Confirmed_1],
    [dbo_Address_1].[LastChanged] [dbo_Address_LastChanged_1],
    [dbo_Address_1].[ExternalId] [dbo_Address_ExternalId_1],
    [dbo_Address_1].[Id] [dbo_Address_Id_1],
    [dbo_Address_2].[Town] [dbo_Address_Town_2],
    [dbo_Address_2].[Confirmed] [dbo_Address_Confirmed_2],
    [dbo_Address_2].[LastChanged] [dbo_Address_LastChanged_2],
    [dbo_Address_2].[ExternalId] [dbo_Address_ExternalId_2],
    [dbo_Address_2].[Id] [dbo_Address_Id_2],
    [dbo_Purchase].[CustomerId] [dbo_Purchase_CustomerId],
    [dbo_Purchase].[ProductId] [dbo_Purchase_ProductId],
    [dbo_Purchase].[DeliveryId] [dbo_Purchase_DeliveryId],
    [dbo_Purchase].[PaymentMethodId] [dbo_Purchase_PaymentMethodId],
    [dbo_Purchase].[Id] [dbo_Purchase_Id],
    [dbo_Delivery].[Date] [dbo_Delivery_Date],
    [dbo_Delivery].[AddressId] [dbo_Delivery_AddressId],
    [dbo_Delivery].[Id] [dbo_Delivery_Id],
    [dbo_Address_3].[Town] [dbo_Address_Town_3],
    [dbo_Address_3].[Confirmed] [dbo_Address_Confirmed_3],
    [dbo_Address_3].[LastChanged] [dbo_Address_LastChanged_3],
    [dbo_Address_3].[ExternalId] [dbo_Address_ExternalId_3],
    [dbo_Address_3].[Id] [dbo_Address_Id_3],
    [dbo_Product].[Name] [dbo_Product_Name],
    [dbo_Product].[Price] [dbo_Product_Price],
    [dbo_Product].[Id] [dbo_Product_Id]
FROM [dbo].[Customer] [dbo_Customer]
LEFT JOIN [dbo].[Address] [dbo_Address_1] ON ([dbo_Customer].[HomeAddressId] = [dbo_Address_1].[Id])
LEFT JOIN [dbo].[Address] [dbo_Address_2] ON ([dbo_Customer].[BillingAddressId] = [dbo_Address_2].[Id])
LEFT JOIN [dbo].[Purchase] [dbo_Purchase] ON ([dbo_Customer].[Id] = [dbo_Purchase].[CustomerId])
LEFT JOIN [dbo].[Delivery] [dbo_Delivery] ON ([dbo_Purchase].[DeliveryId] = [dbo_Delivery].[Id])
LEFT JOIN [dbo].[Address] [dbo_Address_3] ON ([dbo_Delivery].[AddressId] = [dbo_Address_3].[Id])
LEFT JOIN [dbo].[Product] [dbo_Product] ON ([dbo_Purchase].[ProductId] = [dbo_Product].[Id])
WHERE ([dbo_Customer].[Id] = 2)

    • Trace Entries: 2
        ...LanguageAndORM.Controllers.HomeController.DeleteWithJoin: 
        [HttpGet]
public ViewResult DeleteWithJoin()

{
    var traceTree = _traceTreesRepository.Record();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    var result = _deletesService.GetDeleteWithJoinResult(traceTree);

    return CreateView(_traceTreesRepository.RootTraceTree, result);
}
      • Trace Entries: 2
          ...LanguageAndORM.Business.DeletesService.GetDeleteWithJoinResult: 
          public int GetDeleteWithJoinResult(TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    var purchaseTable = new PurchaseTable();
    var customerTable = new CustomerTable();
    Native.Delete delete = new /*WYSE*/Native.Delete(purchaseTable)  // SqlServer specific Delete
        .From(purchaseTable)
        .InnerJoin(customerTable, purchaseTable.CustomerId == customerTable.Id)
        .Where(customerTable.Id < 3);

    return _sqlExecuter.ExecuteWithRollback(delete, traceTree);
}
        • Trace Entries: 2
            ...LanguageAndORM.DatabaseAccess.SqlExecuter.ExecuteWithRollback: 
            public int ExecuteWithRollback(/*WYSE*/IExecutable executable, TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    using SqlConnection connection = _connectionFactory.Create();
    connection.Open();
    using SqlTransaction 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 = command.ExecuteNonQuery();

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

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

        return -1;
    }
}

            Command Text: 

            DELETE [dbo_Purchase]
FROM [dbo].[Purchase] [dbo_Purchase]
INNER JOIN [dbo].[Customer] [dbo_Customer] ON ([dbo_Purchase].[CustomerId] = [dbo_Customer].[Id])
WHERE ([dbo_Customer].[Id] < 3)

Command Text: 

DELETE [dbo_Purchase]
FROM [dbo].[Purchase] [dbo_Purchase]
INNER JOIN [dbo].[Customer] [dbo_Customer] ON ([dbo_Purchase].[CustomerId] = [dbo_Customer].[Id])
WHERE ([dbo_Customer].[Id] < 3)

Command Text: 

SELECT
    [dbo_Customer].[FullName] [dbo_Customer_FullName],
    [dbo_Customer].[HomeAddressId] [dbo_Customer_HomeAddressId],
    [dbo_Customer].[BillingAddressId] [dbo_Customer_BillingAddressId],
    [dbo_Customer].[Id] [dbo_Customer_Id],
    [dbo_Address_1].[Town] [dbo_Address_Town_1],
    [dbo_Address_1].[Confirmed] [dbo_Address_Confirmed_1],
    [dbo_Address_1].[LastChanged] [dbo_Address_LastChanged_1],
    [dbo_Address_1].[ExternalId] [dbo_Address_ExternalId_1],
    [dbo_Address_1].[Id] [dbo_Address_Id_1],
    [dbo_Address_2].[Town] [dbo_Address_Town_2],
    [dbo_Address_2].[Confirmed] [dbo_Address_Confirmed_2],
    [dbo_Address_2].[LastChanged] [dbo_Address_LastChanged_2],
    [dbo_Address_2].[ExternalId] [dbo_Address_ExternalId_2],
    [dbo_Address_2].[Id] [dbo_Address_Id_2],
    [dbo_Purchase].[CustomerId] [dbo_Purchase_CustomerId],
    [dbo_Purchase].[ProductId] [dbo_Purchase_ProductId],
    [dbo_Purchase].[DeliveryId] [dbo_Purchase_DeliveryId],
    [dbo_Purchase].[PaymentMethodId] [dbo_Purchase_PaymentMethodId],
    [dbo_Purchase].[Id] [dbo_Purchase_Id],
    [dbo_Delivery].[Date] [dbo_Delivery_Date],
    [dbo_Delivery].[AddressId] [dbo_Delivery_AddressId],
    [dbo_Delivery].[Id] [dbo_Delivery_Id],
    [dbo_Address_3].[Town] [dbo_Address_Town_3],
    [dbo_Address_3].[Confirmed] [dbo_Address_Confirmed_3],
    [dbo_Address_3].[LastChanged] [dbo_Address_LastChanged_3],
    [dbo_Address_3].[ExternalId] [dbo_Address_ExternalId_3],
    [dbo_Address_3].[Id] [dbo_Address_Id_3],
    [dbo_Product].[Name] [dbo_Product_Name],
    [dbo_Product].[Price] [dbo_Product_Price],
    [dbo_Product].[Id] [dbo_Product_Id]
FROM [dbo].[Customer] [dbo_Customer]
LEFT JOIN [dbo].[Address] [dbo_Address_1] ON ([dbo_Customer].[HomeAddressId] = [dbo_Address_1].[Id])
LEFT JOIN [dbo].[Address] [dbo_Address_2] ON ([dbo_Customer].[BillingAddressId] = [dbo_Address_2].[Id])
LEFT JOIN [dbo].[Purchase] [dbo_Purchase] ON ([dbo_Customer].[Id] = [dbo_Purchase].[CustomerId])
LEFT JOIN [dbo].[Delivery] [dbo_Delivery] ON ([dbo_Purchase].[DeliveryId] = [dbo_Delivery].[Id])
LEFT JOIN [dbo].[Address] [dbo_Address_3] ON ([dbo_Delivery].[AddressId] = [dbo_Address_3].[Id])
LEFT JOIN [dbo].[Product] [dbo_Product] ON ([dbo_Purchase].[ProductId] = [dbo_Product].[Id])
WHERE ([dbo_Customer].[Id] = 2)