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

    var result = _scriptsService.GetScriptWithReaderUsingNextResult(traceTree);

    return CreateView(_traceTreesRepository.RootTraceTree, result);
}
    • Trace Entries: 3
        ...LanguageAndORM.Business.ScriptsService.GetScriptWithReaderUsingNextResult: 
        public IReadOnlyList<dynamic> GetScriptWithReaderUsingNextResult(TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    var scriptPackage = CreateExampleScriptPackage(traceTree);

    return _sqlExecuter.GetScriptItems(
        scriptPackage.Script,
        reader => reader.NextResult(),
        reader => reader[0],
        traceTree);
}
      • Trace Entries: 1
          ...LanguageAndORM.Business.ScriptsService.CreateExampleScriptPackage: 
          private static ExampleScriptPackage CreateExampleScriptPackage(TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    Select trueSelect = new /*WYSE*/Select().Columns(Const.True());
    Select falseSelect = new /*WYSE*/Select().Columns(Const.False());

    var ifTrueOrFalseSelect = new /*WYSE*/If(/*WYSE*/Is.True, trueSelect, falseSelect);
    var oddEvenSwitch = /*WYSE*/Variable.Number("oddEvenSwitch", new SqlTypes.Int());
    var oddEevenSwitchDeclare = new /*WYSE*/Declare(
        oddEvenSwitch,
        /*WYSE*/Const.Number(1).WithComment("Set to '0' to get even digits"));

    Select selectAscending = createDigitsSelect(OrderDirection.Asc, oddEvenSwitch)
        .WithComment("Ascending");
    Select selectDescending = createDigitsSelect(OrderDirection.Desc, oddEvenSwitch)
        .WithComment("Descending");

    var script = new /*WYSE*/Script(
        new /*WYSE*/SetNoCount(true),
        new /*WYSE*/Comment("The following looks iffy... ;-)"),
        ifTrueOrFalseSelect,
        oddEevenSwitchDeclare,
        selectAscending,
        selectDescending,
        createSubScript());

    return new ExampleScriptPackage(script, selectAscending, selectDescending);

    static /*WYSE*/Select createDigitsSelect(OrderDirection orderDirection, NumberVar oddEvenSwitch)
    {
        var stringSplit = new /*WYSE*/StringSplit("0,1,2,3,4,5,6,7,8,9", ',');

        return new /*WYSE*/Select()
            .Columns(stringSplit.Value)
            .From(stringSplit)
            .Where((/*WYSE*/Convert.ToNumber(new SqlTypes.Int(), stringSplit.Value) + oddEvenSwitch) % 2 == 0)
            .OrderBy(stringSplit.Value, orderDirection);
    }

    static /*WYSE*/Script createSubScript()
    {
        var alphabetTableVariable = new AlphabetTableVariable();

        return new /*WYSE*/Script(
            new /*WYSE*/Comment("Superfluous comment for an unused subscript"),
            new /*WYSE*/Declare(alphabetTableVariable),
            // Inherits from WYSE InsertInto
            new AlphabetTableVariable.FillWithLetters(alphabetTableVariable));
    }
}
      • Trace Entries: 2
          ...LanguageAndORM.DatabaseAccess.SqlExecuter.GetScriptItems: 
          public IReadOnlyList<TItem> GetScriptItems<TItem>(
    /*WYSE*/IScript script,
    Action<DbDataReader> readerInit,
    Converter<DbDataReader, TItem> converter,
    TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    return Get<IReadOnlyList<TItem>>(
        script,
        reader =>
        {
            readerInit(reader);
            return reader./*WYSE*/GetItems(converter).ToList();
        },
        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: 

            SET NOCOUNT ON;

/* The following looks iffy... ;-) */;

IF (1 = 1)
BEGIN
    SELECT 1 [_True_3];
END
ELSE
BEGIN
    SELECT 0 [_False];
END;

DECLARE @oddEvenSwitch AS int = 1 /* Set to '0' to get even digits */;

SELECT /* Ascending */ [_StringSplit_1].[value] [_ValueExpression_1]
FROM STRING_SPLIT('0,1,2,3,4,5,6,7,8,9', ',') [_StringSplit_1]
WHERE (((CONVERT(int, [_StringSplit_1].[value]) + @oddEvenSwitch) % 2) = 0)
ORDER BY [_StringSplit_1].[value] ASC;

SELECT /* Descending */ [_StringSplit_2].[value] [_ValueExpression_2]
FROM STRING_SPLIT('0,1,2,3,4,5,6,7,8,9', ',') [_StringSplit_2]
WHERE (((CONVERT(int, [_StringSplit_2].[value]) + @oddEvenSwitch) % 2) = 0)
ORDER BY [_StringSplit_2].[value] DESC;

/* Superfluous comment for an unused subscript */;

DECLARE @Alphabet AS TABLE (
    [Letter] char(1) NOT NULL
);

INSERT INTO @Alphabet
([Letter])
SELECT [_StringSplit_3].[value] [_ValueExpression_3]
FROM STRING_SPLIT('A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z', ',') [_StringSplit_3];

Command Text: 

SET NOCOUNT ON;

/* The following looks iffy... ;-) */;

IF (1 = 1)
BEGIN
    SELECT 1 [_True_3];
END
ELSE
BEGIN
    SELECT 0 [_False];
END;

DECLARE @oddEvenSwitch AS int = 1 /* Set to '0' to get even digits */;

SELECT /* Ascending */ [_StringSplit_1].[value] [_ValueExpression_1]
FROM STRING_SPLIT('0,1,2,3,4,5,6,7,8,9', ',') [_StringSplit_1]
WHERE (((CONVERT(int, [_StringSplit_1].[value]) + @oddEvenSwitch) % 2) = 0)
ORDER BY [_StringSplit_1].[value] ASC;

SELECT /* Descending */ [_StringSplit_2].[value] [_ValueExpression_2]
FROM STRING_SPLIT('0,1,2,3,4,5,6,7,8,9', ',') [_StringSplit_2]
WHERE (((CONVERT(int, [_StringSplit_2].[value]) + @oddEvenSwitch) % 2) = 0)
ORDER BY [_StringSplit_2].[value] DESC;

/* Superfluous comment for an unused subscript */;

DECLARE @Alphabet AS TABLE (
    [Letter] char(1) NOT NULL
);

INSERT INTO @Alphabet
([Letter])
SELECT [_StringSplit_3].[value] [_ValueExpression_3]
FROM STRING_SPLIT('A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z', ',') [_StringSplit_3];