Introducing Common Table Expressions in SQL Server

Common Table Expressions, or CTE for short, is simply a technique to create a temporary set of records that can be referenced within an INSERT, SELECT, UPDATE or DELETE statement.

Common table expressions were introduced by Microsoft in SQL Server 2005. They are not stored as objects in the database memory as their lifespan is equal to the execution time of the query. As soon as a query completes they are removed from the database memory. CTE can be referenced in a query as many times as you want and they can also be self-referencing.

(more…)

Swashbuckle (Swagger) Customization for WebAPI

Who has ever tested their WebAPI knows such tools as Postman or Advanced REST (extensions for Chrome). These tools are convenient in every way, except that they are not able to recognize which models the API accepts, which ones it returns and do not provide information about all possible endpoints. The Swashbuckle package solves this disadvantage. It builds Swagger specification generation and UI in the project. In this article, I will briefly describe how to bind it to the project and provide some details about authorization and work with “overloaded” endpoints.

(more…)

DateTime to DateTimeOffset Transition

Imagine that you want to convert your system from one state to another. The initial state is when DateTime is used everywhere, both in C# code and in the database. The final state is when DateTimeOffset is used everywhere. You want to make the transition smooth and make as few changes as possible. This description can be the beginning of a very interesting problem with a dead end at the end.

(more…)

Simplifying Converters for WPF

I have been working with WPF for about a year and some things annoying me very much. One of such things is converters. Why do I need to declare the implementation of a dubiously looking interface somewhere at the deep of a project and then search for it through CTRL + F by name when it is needed?

Well, it’s time to make a little easier on the routine of creating and using the converters. (more…)

Working with SQL Server in Hybrid Cloud Scenarios. Part 2

As a rule, impersonal information is stored in a public cloud, and the personalized part – in a private cloud. The question thus arises – how to combine both parts to return a single result at a user’s request? Suppose there is a table of customers divided vertically. The depersonalized columns were included in the table located in Windows Azure SQL Database, and columns with sensitive information (e.g., full name) remained in the local SQL Server. Both tables must be linked by the CustomerID key. Because they are located in different databases on different servers, the JOIN statement will not work. As a possible solution, we have considered the scenario, when the linkage was implemented on the local SQL Server. It served as a kind of entry point for the applications, and the cloud-based SQL Server was set up on it as a linked server. In this article, we will consider the case when both, the local and cloud servers, are equal in terms of the application, and the data merging occurs directly in it, i.e. at the business logic level.

(more…)

SSAS: Discipline, Accuracy, Attention to Details. Part 2 – OLAP Cube

Introduction

In this article, I will continue describing my experience with Microsoft Analysis Services. In addition to the previous article, I would like to write about unconventional solutions that were implemented in the recent project. These solutions got me close with Microsoft Analysis Services, and allowed me to do things that seemed to me impossible before. (more…)

Working with SQL Server in Hybrid Cloud Scenarios

A hybrid cloud is a fairly attractive model when implementing cloud computing in enterprise information systems since this approach combines the advantages of public and private clouds. On the one hand, it is possible to flexibly attract external resources when needed and reduce infrastructure costs. On the other hand, full control over data and applications that the enterprise does not want to outsource remains. However, in such a scenario, we inevitably face the task of integrating data from various sources. Suppose there is a table with customers, which is vertically divided into two parts. The depersonalized part was allocated in a public cloud, and the information personalizing the customers remained in a local database. For holistic processing inside the application, you need to combine both parts by CustomerID. There are various ways to do this. Conventionally, they can be divided into two large categories: data aggregation at the on-premise database server level which, in this case, will be a single sign on for accessing local and remote data, and data aggregation within the business logic. This article will consider the first approach.

(more…)

OdataToEntity – an easy way to create .Net Core OData services

When .Net Core was released, the old version of OData ASP.NET Web API turned out to be incompatible with the new platform. This fatal flaw allowed me to create my OData implementation on the .Net Core platform. After the creative rethinking of the previous implementation, I came to an understanding that it suffered from a complicated design with a lot of unnecessary abstractions. An idea to create an easy-to-use library that requires minimal coding came into my mind. I would like to present you OdataToEntity, the library for creating OData services without code writing; the only thing needed is data access context.

(more…)