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: 4
      ...ReportGenerator.Controllers.HomeController.Start: 
      [HttpGet]
public ViewResult Start()
{
    var traceTree = _traceTreesRepository.Record();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    var reportColumns = _reportGeneratorService.GetReportColumns(traceTree);
    var customersReport = _reportGeneratorService.GenerateFullReport(traceTree);
    
    var reportModel = new ReportModel(
        _traceTreesRepository.RootTraceTree,
        reportColumns,
        customersReport);

    return View("ReportGenerator", reportModel);
}
    • Trace Entries: 1
        ...ReportGenerator.Services.ReportGeneratorService.GetReportColumns: 
        public Tree</*WYSE*/IReadOnlyListDictionary> GetReportColumns(TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    // It is good practice to decouple internal ids from externally used ones.
    // WYSE provides the means to do so.
    // However, for a better, easier to follow demo the ids are kept the same here.
    var customerTable = DemoTemplateAliasing.CreateAliasTemplate<CustomerTable>("Customer");
    
    var genericMappings = _genericMappingsRepository.All
        /*WYSE*/.DownstreamOf<CustomerTable>();

    var selectBuilder = new /*WYSE*/SelectBuilder<CustomerTable, Customer>(
        customerTable,
        genericMappings);

    var select = selectBuilder.Build();

    // Default implementation of the interface that only switches from the '.'
    // in external ids and the '_' in an expression's alias
    var aliasTranslator = new /*WYSE*/AliasTranslator();
    
    return select./*WYSE*/GetListDictionaryTree(aliasTranslator);
}
    • Trace Entries: 1
        ...ReportGenerator.Services.ReportGeneratorService.GenerateFullReport: 
        // Generate a report with all columns and no where clause.
public IReadOnlyList<Customer> GenerateFullReport(TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    var customerTable = DemoTemplateAliasing.CreateAliasTemplate<CustomerTable>("Customer");
    
    var genericMappings = _genericMappingsRepository.All
        /*WYSE*/.DownstreamOf<CustomerTable>();

    var selectBuilder = new /*WYSE*/SelectBuilder<CustomerTable, Customer>(
        customerTable,
        genericMappings);
    // Alternatively...
    //var selectBuilder = new /*WYSE*/SelectBuilder(customerTable, typeof(Customer), genericMappings);

    var select = selectBuilder.Build();

    return _sqlExecuter.GetItemTrees<CustomerTable, Customer>(
        select,
        customerTable,
        genericMappings,
        parentTraceTree);
}
    • Trace Entries: 1
        ...ReportGenerator.DatabaseAccess.SqlExecuter.GetItemTrees: 
        public IReadOnlyList<TItem> GetItemTrees<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());

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

    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,
            _renderContextFactory.Create(new DefaultQueryExecutionSettings()),
            _createRenderBuilder);

    try
    {
        using DbDataReader reader = command.ExecuteReader();

        var items = reader.GetItemTrees<TList, TItem>(select, primaryList, genericMappings)
            .ToList();

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

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

        return default;
    }
}