Language

On This Page

Strongly Typed SQL

The number one design principle of WYSE is to stay as close as possible to SQL and all its richness, but with the benefits of strongly typed .NET. Keep it WYSIWYG. This way, as a developer, you get the most out of your IDE tools and the compiler. Meanwhile experienced SQL Knowledgeable developers can still leverage their skills as before, making adoption easy and comfortable.

WYSE, by not robbing the database of doing what it is good at, allows the you to go for peak efficiency. With building SQL statements in WYSE there is no question what the end statement will be. No inefficient query generation. No magic. No digging in logs needed. You are in control.

Staying true to the source also means not discarding the specific strengths of well established SQL database types, such as SQL Server and MySql. For this reason what they have in common (ANSI SQL) is part of the WYSE Core and additional capabilities are part of separate packages that build on the common Core.

In a sense WYSE is a .NET/SQL hybrid. This shines through mostly in the fluent SQL WYSE offers and the way expressions are handled.

Entities

An entity, as it is known in WYSE, is broadly speaking something that is exposed by the database. In WYSE's Core that means ANSI-SQL things like...

Together these entities form the foundation on which everything in WYSE builds. They form the basis for...

Of course, different database types have added their own entity types, such as Functions in SqlServer. Those specific ones are not part of the Core but are placed in database-specific packages. It is also in those packages where entities like keys, indexes, constraints etcetera can be found; essential to database deployment.

Expressions

WYSE expressions form a bridge that unites the type safety of .NET with the way SQL databases deal with types. This is particularly relevant with numbers, since that is where the two differ most.

These expressions are used throughout WYSE; in Where clauses, in Joins, but also with anonymization... and more.

All the commonly used data types (nullable and not-nullable) are supported:

Here are a couple examples of expressions involving columns... 

var addressTable = new AddressTable();
var productPriceView = new ProductPriceView();
Expression expression = addressTable.Id > 1000;
expression = addressTable.Street.Like("%Maple%");
expression = addressTable.HouseNumberAddition.IsNotNull();
decimal salesTax = 0.08m;
expression = productPriceView.Price * (1 + salesTax);
expression = (productPriceView.Price >= 100 & productPriceView.Price < 1000) | addressTable.City == "New York";

Please note: beyond WYSE Core, other database flavors can have additional data types. SqlServer for example has 'HierarchyId'.

A Select As An Expression

As select statements in SQL can be used as values, to initialize variables for example, WYSE supports casting Selects to a desired type.

Fluent .NET SQL

Building SQL statements in WYSE is primarily done fluently, using the earlier mentioned entities and expressions. Plus you have a ton of built-in functions at your disposal. This gives you freedom, flexibility and power.

Also you can use more than just list entities like tables and views in your SQL statements. In Joins and Where Clauses for example you can also use (sub)Selects. That enables you to truly elevate your database usage; you gain access and control over the richness that is in the relations between the entities!

WYSE honors the database. But it also lets code do what it is good at. On that side of the equation WYSE lends benefits like reusability, encapsulation and inheritance to SQL statements. Also, it is easier to break SQL statements into more manageable chunks, enhancing the ever-important 'Naming Things' and overall readability.

Here are two examples of fluently built SQL statements... 

var customerTable = new CustomerTable();
var addressTable = new AddressTable()
Select select = new Select()
    .Columns(customerTable, addressTable.City)
    .From(customerTable)
    .LeftJoin(addressTable, customerTable.HomeAddressId == addressTable.Id)
    .Where(customerTable.FirstName != "John" & customerTable.LastName != "Doe")
    .OrderBy(customerTable.DateOfBirth);

var customerTable = new CustomerTable();
Update update = new Update(customerTable)
    .Set(customerTable.IsPreferred.WithValue(true),
        customerTable.DateOfBirth.WithValue(new DateTime(1984, 3, 14)))
    .Where(customerTable.Id == 1234);

All the major Core language elements are available:

Please note: Specific types of databases can support more sorts of elements. Like the 'Limit' in MySql or the 'Top' in SqlServer.

For an in-depth look on how to use WYSE Fluent language please check out the Language demo.

Builders

With the fluent approach you basically string SQL elements one after the other. In those cases however where you don't know in advance all the elements that need to be in the SQL statement you might be better served using a Statement Builder. With a builder you can set separate elements and when you are done with that you can have the full statement assembled. Which top level elements can be set depends on the statement type (select, insert, etc.) and on the flavor of SQL (ANSI, SqlServer, MySql). The Core SelectBuilder features the following elements:

Examples demonstrating when and how builders are useful can be found in the Framework demo.

Aggregates

The usual aggregates are supported in WYSE:

These aggregates can be used in conjunction with the SQL language elements GroupBy and Having. Have a look at this demo example.

Built-in Functions

Where many frameworks that aim to leverage SQL databases fall short is in the support of built-in functions. With WYSE the major functions are supported; ANSI (in Core) or otherwise (in database type specific packages).

I suggest you have a look at this working code example.

Scripts

Scripting is another area that is commonly overlooked by frameworks. With WYSE it is possible to build strongly typed scripts using ANSI or database specific language elements. Doing things like if/then/else branching database-side and much more is supported this way, further enhancing your SQL capabilities and efficiency.

Here you can see what a SQL Server upsert script could look like in WYSE.

Rendering

Because of separation of concern reasons managing Connections and Transactions, as well as creating DbCommands, has been purposely kept out of WYSE. This way you can still work with those as usual. WYSE however does support the initialization of a DbCommand based on a strongly typed SQL statement; setting the command text and the parameters. Because of that it is not necessary for you to do the statement rendering yourself. Should you still like to do so, WYSE has a versatile RenderBuilder for that.

Statement stringification comes in two forms:

In the demos, where applicable, you can play around with this. In those demos, click the top-right 'Render Settings' button to start doing so.

Statement Rebuilding

The automatic rebuilding of SQL statements that you have constructed can be very useful and powerful. This SQL statement decoration for example enables the (central) encapsulation of application-wide logic. You can think of automatically decorating all your queries to mind 'IsDeleted' flags tour tables might have. This way you don't have to put something like that it in throughout your codebase. Effectively it won't be forgotten because you don't have to think about it. For an example implementation of this have a look at the Framework demo.

To facilitate this, with WYSE you can set up a modification pipeline. This enables you to decorate your statements by having them processed by your custom modifiers that you have lined up.

On a lower level you can use the StatementRebuilder to selectively cut, insert, or re-insert changed SQL statement parts.

To make all the above possible WYSE has tools and methods available for inspecting strongly typed SQL statements.

Aliasing

Mainly in Select statements assigning aliases is essential. Nonetheless it is something that as a developer you don't want to be bothered with. That is why WYSE can take care of it for you. If you don't supply an alias where needed a uniquely generated alias will be created and used.

A second reason why WYSE's aliasing is so helpful becomes apparent with converting returned database results into domain objects. For more on this have a look at the ORM features of WYSE.

Beyond the aliasing capabilites mentioned before WYSE has helpers for automatically resetting aliases of full SQL statements using several strategies, all while ensuring uniqueness.

In the demos, where applicable, you can play around with this. In those demos, click the top-right 'Render Settings' button to start doing so.