How to Create a Virtual Numbers Table in SQL

7. November 2021 08:14 by Parakh in SQL Server  //  Tags:   //   Comments
The article shows how to generate a virtual table of numbers to generate sequential numbers on demand. A user defined table-valued function can be created to emit out such numbers.

A number of problems exist out in the world of SQL, which requires a range of sequential numbers available on demand. This can be achieved either by generating the numbers on demand virtually or by storing them in a table and using them as and when required. In this blog post, I will shed light on how to go about generating such a sequence with the help of a window function and then refine it to generate a range of sequential numbers within the specified bounds.

Short overview of window functions

Window functions are part of the ANSI SQL standard and are fundamentally analytical functions that operate on a section of data, called partition or window. Partition or window can be a subset of the entire result set or the entire result set itself as defined by the SQL query.

Row_Number is a window function that falls in the category of ranking functions and generates a sequence number for every row within the window as defined in the SQL query. An example is going to make it clear:

Create Table dbo.CustomerOrders
(
   Id                   int  Identity(1,1)  Primary Key,
   CustomerId           int,
   OrderId              int,
   OrderValue           money,
   DateOfOrder          Date
);
Go
 
Insert Into dbo.CustomerOrders 
(CustomerId, OrderId, OrderValue, DateOfOrder) 
Values
(1, 200, 300, GETDATE()-1),
(1, 201, 450, GETDATE()+1),
(2, 202, 500, GETDATE()-2),
(3, 203, 600, GETDATE()+3),
(4, 204, 800, GETDATE()+3),
(2, 205, 650, GETDATE()-3),
(1, 206, 600, GETDATE()+3);
Go
 
Select Id, CustomerId, OrderId, OrderValue, DateOfOrder,
Row_Number() Over(Partition By CustomerId Order By OrderValue) 
as RowNumber
From dbo.CustomerOrders


When you run the example mentioned in the SQL query it will produce a result that looks like:

Figure 1 Functioning of Row Number function

 

 

 

 

 

 

 

 

 

 

 

 

 

Figure 1 Functioning of Row Number function

As you can see in the example, the Row_Number function worked on the window or partition created based on the customer id and has numbered the rows sequentially. There are three orders placed by the customer with the customer id of 1, two orders placed by the customer with customer id of 2, and one order each placed by customers with customer id of 3 and 4 correspondingly. The rows have been ordered for each customer based on the value of the order in an ascending manner.

I will not delve into the functioning of the window functions; different clauses associated with them or explain the Row_Number function here. They deserve an entire blog post unto themselves. For more information on window functions and Row_Number function, please refer to the information provided in the references section at the end of this article.

Creating a Virtual Number Table in SQL

There are problems like generating a contiguous range of dates, identifying gaps (Islands problem), splitting strings that use a contiguous set of numbers, and in such problems, we would require a table with a sequence of numbers. The approach discussed in this article hinges on the understanding of the Common Table Expression (CTE) and the Row_Number window function.

The overarching approach of this technique is to generate a sequence of 1s with the help of a series of CTEs and then use the Row_Number window function that would ride the sequence of 1s to generate a sequence of ascending integer numbers.

In order to generate the seed values, consider the following CTE:

Level0 As (Select 1 as SeedValue1 Union All Select 1 As SeedValue2)

When you run the CTE, it generates the following output:

Figure 2 Seed values as emitted by the seed CTE

 

 

 

 

 

 

 

 

 

 

 

Figure 2 Seed values as emitted by the seed CTE

We then proceed to create a series of CTEs which by virtue of cross join between consecutive CTEs create an exponential sequence of 1s.

With 
 
-- 2^1 = 2 values
Level0 As (Select 1 as SeedValue1 Union All Select 1 As SeedValue2), 
 
-- 2^2 = 4 values

Level1 As (Select 1 As GeneratedValues From Level0 As A

Cross Join Level0 As B),

 
-- 2^4 = 16 values
Level2 As (Select 1 As GeneratedValues From Level1 As A 
Cross Join Level1 As B), 
 
-- 2^8 = 256 values

Level3 As (Select 1 As GeneratedValues From Level2 As A

Cross Join Level2 As B),

 
-- 2^16 = 65,536 values
Level4 As (Select 1 As GeneratedValues From Level3 As A 
Cross Join Level3 As B), 

 

We then generate unique sequential numbers with the help of Row_Number window function working over the complete result set as its window. The full SQL query that would be able to generate a total of 42,99,67,296 values, would look something like:

With 
 
-- 2^1 = 2 values
Level0 As (Select 1 as SeedValue1 Union All Select 1 As SeedValue2), 
 
-- 2^2 = 4 values

Level1 As (Select 1 As GeneratedValues From Level0 As A

Cross Join Level0 As B),

 
-- 2^4 = 16 values
Level2 As (Select 1 As GeneratedValues From Level1 As A 
Cross Join Level1 As B), 
 
-- 2^8 = 256 values

Level3 As (Select 1 As GeneratedValues From Level2 As A

Cross Join Level2 As B),

 
-- 2^16 = 65,536 values
Level4 As (Select 1 As GeneratedValues From Level3 As A 
Cross Join Level3 As B), 
 
-- 2^32 = 42,99,67,296 values

Level5 As (Select 1 As GeneratedValues From Level4 As A C

ross Join Level4 As B),

 
-- Obtain unique integers
Numbers As (Select Row_Number() Over(Order By GeneratedValues) 
As GeneratedValues From Level5) 

 

As the first refinement, we can generate only the required number of sequential numbers as required by introducing the concept of paging implemented with the help of the Offset-Fetch clause. That way the total number of sequential numbers that will get generated will get dictated by the upper bound mentioned by the user. The SQL query will then filter the requisite number of sequential numbers and provide the output.

Declare 
@LowerBound Bigint = 1,
@UpperBound Bigint = 429967296; 
 
-- By default, the CTE will always generate numbers starting with 1 going all the way upto 42,99,67,296
-- SQL Engine is smart and will only generate numbers up until the upperbound when defined
With 
 
-- 2^1 = 2 values
Level0 As (Select 1 as SeedValue1 Union All Select 1 As SeedValue2), 
 
-- 2^2 = 4 values

Level1 As (Select 1 As GeneratedValues From Level0 As A

Cross Join Level0 As B),

 
-- 2^4 = 16 values
Level2 As (Select 1 As GeneratedValues From Level1 As A 
Cross Join Level1 As B), 
 
-- 2^8 = 256 values

Level3 As (Select 1 As GeneratedValues From Level2 As A

Cross Join Level2 As B),

 
-- 2^16 = 65,536 values
Level4 As (Select 1 As GeneratedValues From Level3 As A 
Cross Join Level3 As B), 
 
-- 2^32 = 42,99,67,296 values

Level5 As (Select 1 As GeneratedValues From Level4 As A

Cross Join Level4 As B),

 
-- Obtain unique integers
Numbers As (Select Row_Number() Over(Order By GeneratedValues) 
As GeneratedValues From Level5) 
 
-- Return the numbers between the lower and the upper bound
Select @LowerBound + GeneratedValues – 1 
As GeneratedNumbers From Numbers
Order By GeneratedValues
Offset 0 Rows 
Fetch First @UpperBound - @LowerBound + 1 Rows Only;
Go

An example of execution of this query:

02 Execution Result of Complete QueryFigure 3 Result of execution of query between the lower bound of 1 and upper bound of 15

As the second refinement, we can create a table-valued function and execute it where ever and whenever we require a sequence of numbers

-- Function to return a table of numbers between lowerbound and upperbound
Create Function dbo.GetNumbers(@LowerBound As Bigint, @UpperBound As Bigint) 
Returns Table
As 
Return
 
-- By default, the CTE will always generate numbers starting with 1 going all the way upto 42,99,67,296
-- SQL Engine is smart and will only generate numbers up until the upperbound when defined
With 
 
-- 2^1 = 2 values
Level0 As (Select 1 as SeedValue1 Union All Select 1 As SeedValue2), 
 
-- 2^2 = 4 values

Level1 As (Select 1 As GeneratedValues From Level0 As A C

ross Join Level0 As B),

 
-- 2^4 = 16 values
Level2 As (Select 1 As GeneratedValues From Level1 As A 
Cross Join Level1 As B), 
 
-- 2^8 = 256 values

Level3 As (Select 1 As GeneratedValues From Level2 As A

Cross Join Level2 As B),

 
-- 2^16 = 65,536 values
Level4 As (Select 1 As GeneratedValues From Level3 As A 
Cross Join Level3 As B), 
 
-- 2^32 = 42,99,67,296 values

Level5 As (Select 1 As GeneratedValues From Level4 As A

Cross Join Level4 As B),

 
-- Obtain unique integers
Numbers As (Select Row_Number() Over(Order By GeneratedValues) 
As GeneratedValues From Level5) 
 
-- Return the numbers between the lower and the upper bound
Select 
@LowerBound + GeneratedValues - 1 
As GeneratedNumbers From Numbers
Order By GeneratedValues
Offset 0 Rows 
Fetch First @UpperBound - @LowerBound + 1 Rows Only;
Go

 

An example of the execution of the function:

Figure 4 Result of execution of the table valued function between the lower bound of 10 and upper bound of 30

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Figure 4 Result of execution of the table valued function between the lower bound of 10 and upper bound of 30

Hope this was helpful.

References:

1. T-SQL Window Functions: For data analysis and beyond (Developer Reference) by Itzik Ben-Gan
2. Common Table Expressions in SQL Server
3. Window Functions in SQL Server
4. Row Number Window Function in SQL Server

Normalization In Relational Databases

21. October 2021 00:44 by Parakh in Database Design  //  Tags: ,   //   Comments
Post tries to teach the concept of normalization in relational databases in a simple to understand manner with the help of a real life example.

Designing a performant data model to accommodate data for an application can be a bit of a challenging task for a new developer. The difficulty arises from the lack of understanding of the nature of the data itself and using the tools relational database design to divide data logically into tables. In this short article, I have tried to shed light on normalization in relational databases.

Normalization in relational databases is the process of minimizing the duplication of data. This is done by spreading data into different tables, all logically connected via relationships. Normalization is a play on divide and rule strategy in the computer science domain.

Before we move further into the article, some of the terminologies bear a small introduction here.

Atomicity: In the context of relational databases, atomicity refers to the condition of data when it cannot be broken any further without loss of its comprehensiveness.


Attribute: Attribute describes characteristics of data. That can be the max length of the first name or the fact that the date of birth of a person in an application meant to store passport details can only go back in the past, say, 125 years.

Field: A field represents a data point which is a fundamental unit that makes up a record. A field stores an atomic value in an optimized form.

Record: A record represents a complete unit of data that is stored in a table. A record is made up of fields. Column: A column is an implementation in relational databases to store a field.

Row: A row is an implementation in relational databases to store a record.

Table: A table is a data structure in a relational database that is made up of rows and columns and facilitates the storage and querying of data.

Key: In the English language the word key as an adjective means something of crucial importance. There are different kinds of keys in a relational database and they play a crucial role in the functioning of a database.

Primary Key: A primary key is a construct in a table in a relational database that can be used to uniquely identify a record. We will discuss various kinds of primary keys at an opportune point in the article.

Now let’s take an example and thoroughly understand normalization.

Example: Consider that we have to store information about books in our database. The information comprises of the following:

  1. Book’s title
  2. Book’s author(s)
  3. ISBN
  4. Publisher’s name
  5. Publisher’s contact
  6. Date of publication
  7. Cost
  8. Category or categories in which the book can be filed
  9. The book should be individually identifiable based on the ISBN
  10. The book should be searchable on the basis of one of the categories that it can be filed in or the publisher’s name

Now, the way a new developer would think would be on the lines of accommodating all this information in just one table. Let’s understand the problems that one would encounter in such a scenario.

ISBN

Title

Authors

Publisher

PublisherContact

DateOfPublication

Price

Category

9781408855652

Harry Potter and the Philosopher's Stone

J.K. Rowling

Pottermore Publishing

Harry Potter

8-12-2015

200

Fantasy, Fiction

9780007547999

Algorithms to Live By: The Computer Science of Human Decisions

Brian Christian, Tom Griffiths

William Collins

John Doe

26-04-2017

250

Computer Science

938943243X

Rangbhoomi

Munshi Premchand

Fingerprint Publishing

Parakh Singhal

01-09-2019

672

Hindi Literature


Per the requirements, we can have multiple authors for a book and, a book can be categorized in multiple categories. Thus, we have the potential of storing multiple values for each of the fields of “Authors” and “Categories.”

Problems with this design: Anytime the number of authors changes for a book (by addition or update of a chapter(s) written by some new author, or by an existing author selling his stake in the works) or we want to increase or decrease the categories in which a book can be filed, we will have to read the existing data, modify it and then write it back into the existing row. This is tedious and time-consuming from a programming standpoint. A simple row deletion or addition independent of the existing row(s) would be far simpler and faster.

In order to eliminate the set of problems that arise under the category of organization of data in relational databases, we have the tool of normalization.

Normalization is a process under which we minimize data duplication and straighten relationships among the attributes of data. The various stages in this process are called "normal forms." There are 4 normal forms with a focus on organizing data progressively. In this article, we will focus on the 1st Normal Form, 2nd Normal Form, and the 3rd Normal Form.

1st Normal Form

The academic definition of the first normal form is that a table is in the first normal form if a relation has all the attributes in the atomic state.

In simple words, the definition merely states not to store multiple values in a field. In our example above, both the fields of “Categories” and “Authors” violate the first normal form, as we are storing multiple values in those fields. When we store only a single coherent value in a field that cannot be broken any further, and hence atomic, we achieve the 1st Normal Form. Therefore, we need to store authors and categories separately in our table, which would then look something like this:

ISBN

Title

Authors

Publisher

PublisherContact

DateOfPublication

Price

Category

9781408855652

Harry Potter and the Philosopher's Stone

J.K. Rowling

Pottermore Publishing

Harry Potter

8-12-2015

200

Fantasy

9781408855652

Harry Potter and the Philosopher's Stone

J.K. Rowling

Pottermore Publishing

Harry Potter

8-12-2015

200

Fiction

9780007547999

Algorithms to Live By: The Computer Science of Human Decisions

Brian Christian

William Collins

John Doe

26-04-2017

250

Computer Science

9780007547999

Algorithms to Live By: The Computer Science of Human Decisions

Tom Griffiths

William Collins

John Doe

26-04-2017

250

Computer Science

938943243X

Rangbhoomi

Munshi Premchand

Fingerprint Publishing

Parakh Singhal

01-09-2019

672

Hindi Literature

Now we have atomic values in all our fields and the table complies with the first normal form.

But the compliance with the first normal form has given rise to another problem. Now we cannot use the ISBN field as the primary key as it exists in multiple rows.

This is a good place to introduce the three types of primary keys that may exist in your data. They are:

  1. Natural Primary Key
  2. Composite Primary Key
  3. Surrogate Primary Key

Natural Primary Key: A primary key is called a natural primary key if the data that needs to be accommodated in a table has a field that is unique and can be used to identify a record uniquely among other records. In our example, before we implemented the first normal form, the ISBN can be organically used to uniquely identify a record, thus forming a natural primary key.

Composite Primary Key: A natural primary key that is made up of a bunch of fields in the data that needs to be accommodated in a table, is called a composite primary key. Composite primary key comes into the picture when a single field cannot be used to uniquely identify a record from among other records, but a combination of certain fields is unique and can serve the purpose. In our example, after the implementation of the first normal form, a combination of ISBN and author name can be used to uniquely identify a record from among other records, as an author’s name is only going to appear once for an ISBN. Thus, the combination of author name and ISBN can form a composite primary key.

Surrogate Primary Key: A key that is not related to data naturally in any manner whatsoever is called a surrogate primary key. Generally, it is a key that gets generated automatically by the database engine. The developer is just required to select the data type of the surrogate primary key. In our example, we can implement an int type or a GUID type surrogate primary key instead of relying on a composite primary key as described before.

Because we cannot use just the field of ISBN as the primary key, we will have to go for either a composite primary key or a surrogate primary key. In our case, we can go for a composite primary key and remain within the confines of our natural data, without introducing a surrogate primary key.

2nd Normal Form

In the second normal form, we work with a table that is already in the first normal form. Then we remove the partial dependency from the table if any.

Partial Dependency: Partial dependency is a condition whereby the fields contained within a record depend only upon one portion of the primary key.

In our example, when we achieved the first normal form, we introduced a composite primary key which was made up of the ISBN and the author’s name fields. Hence, the data in the table depended on two fields that made up the primary key. To push our example to comply with the second normal form, we will have to bifurcate the data into two distinct tables – each portion of the composite primary key representing a separate table. In our example we will create two tables, one housing data about authors and the other housing data about the books.

Our tables would look something like this:

Books table with the field “ISBN” as the natural primary key:

ISBN

Title

Publisher

PublisherContact

DateOfPublication

Price

Category

9781408855652

Harry Potter and the Philosopher's Stone

Pottermore Publishing

Harry Potter

8-12-2015

200

Fantasy

9781408855652

Harry Potter and the Philosopher's Stone

Pottermore Publishing

Harry Potter

8-12-2015

200

Fiction

9780007547999

Algorithms to Live By: The Computer Science of Human Decisions

William Collins

John Doe

26-04-2017

250

Computer Science

9780007547999

Algorithms to Live By: The Computer Science of Human Decisions

William Collins

John Doe

26-04-2017

250

Computer Science

938943243X

Rangbhoomi

Fingerprint Publishing

Parakh Singhal

01-09-2019

672

Hindi Literature


Authors table with the field “AuthorId” as the surrogate primary key:

AuthorId

Authors

1

J.K. Rowling

2

Brian Christian

3

Tom Griffiths

4

Munshi Premchand


Now both the tables do not have any partial dependency and have graduated to the second normal form.

3rd Normal Form

In the third normal form, we work with a table that is in the second normal form and aim to remove any transitive dependency, if any.

Transitive Dependency: Transitive dependency is a condition whereby a field is not dependent on the primary key in any way.

In our example, the publisher’s contact is dependent on the publisher and does not in any way connect to the book and hence ISBN, which happens to the natural primary key in our Books table. We will push the publisher’s contact into a separate table to achieve the third normal form. Our tables would then look something like this:

Books table with ISBN as the natural primary key and the transitive dependency of publisher’s contact field removed:

ISBN

Title

Publisher

DateOfPublication

Price

Category

9781408855652

Harry Potter and the Philosopher's Stone

Pottermore Publishing

8-12-2015

200

Fantasy

9781408855652

Harry Potter and the Philosopher's Stone

Pottermore Publishing

8-12-2015

200

Fiction

9780007547999

Algorithms to Live By: The Computer Science of Human Decisions

William Collins

26-04-2017

250

Computer Science

9780007547999

Algorithms to Live By: The Computer Science of Human Decisions

William Collins

26-04-2017

250

Computer Science

938943243X

Rangbhoomi

Fingerprint Publishing

01-09-2019

672

Hindi Literature


Authors table with the field “AuthorId” as the surrogate primary key:

AuthorId

Authors

1

J.K. Rowling

2

Brian Christian

3

Tom Griffiths

4

Munshi Premchand


Publisher Contact table with the field “Publisher” as the natural primary key:

Publisher

PublisherContact

Pottermore Publishing

Harry Potter

William Collins

John Doe

Fingerprint Publishing

Parakh Singhal


We can further improve upon the final design by adopting surrogate primary keys in all our tables. That way if there’s any update of data in fields acting as natural primary keys, we will not have to update the same in the tables utilizing them as foreign keys. Also, we reduced the duplication of data in the Books table by creating a bridge entity to accommodate the many-to-many relationship between a book and the categories into which it can be classified.

Data model with surrogate primary keys:

Normalized Data Model with Bridge Tables to handle Many-To-Many RelationshipsFigure 1 Normalized Data Model with Bridge Tables to handle Many-To-Many Relationships


In a nutshell:

1st Normal Form: Aim for atomicity of data by elimination of multiple values in a single field and identification of a field that can act as a primary key.

2nd Normal Form: Make sure the table already exists in the first normal form and eliminate any partial dependencies.

3rd Normal Form: Make sure the table already exists in the second normal form and eliminate any transitive dependencies.

I hope this article helped you in learning the concept of normalization.

Events and Delegates – Part 3 of 3

19. November 2020 13:14 by Parakh in   //  Tags:   //   Comments

In part 2 of this series, I introduced delegates as the implementation of the callback mechanism in .net. We saw how a delegate act as a pipeline connecting code that generates information to the code that uses that information.

In this post, I will introduce events and how they fit neatly into the entire picture and complement the abstraction that we started with delegates.

Non-Technical Overview of Events

Events in real life are important occurrences which, when happen, result in something done. Similarly, events in object-oriented programming languages represent important occurrences which when exercised need something done.

Technical Overview

In the previous post we saw how we can use a delegate as a pipeline to broadcast the information from an object to the subscribers.

Events take this concept to the next level and defer the work of creating the underlying delegate to the compiler. All a developer has to worry about is creating the right kind of event that fits in the domain model.

Events can be created with the help of the “event” keyword. We have to use a user defined delegate or a .net framework provided delegate to let the compiler know what type of underlying delegate needs to be formed behind the scenes. To keep the article short and focus on understanding the concept, I will only cover the EventHandler delegate provided by the .net framework in this post.

The EventHandler delegate is provided as a ready to use delegate. Its signature comprises passing an object type and an object of the EventArgs class. The first parameter is an object of the class that exposes the event itself, and the EventArgs object represents additional information related to the emitted event that might not be contained in the object of the class itself. The delegate returns a void.

public void EventHandler (object? sender, EventArgs e);
 

To pass any event information that might not be contained in the object, we can create a user-defined class derived from EventArgs class, and provide the peripheral information and functionality related to the emitted event via properties and methods.

The concepts covered in the technical overview will become a bit clearer in the example code covered in use cases.

Use Cases of Events

Events are calls that are raised when programmed conditions are met. Some examples are:

1. To broadcast changes in an object’s state
2. To broadcast fulfilment of a condition

We saw in the previous post how we have to create additional code to register and un-register methods that want to use the delegates. With the event keyword we can bypass all that boilerplate code and directly work with events and event handlers.

Broadcast changes in an object’s state

I am going to continue an example discussed in my previous post. Human beings are sensitive to their ambient temperature. In the code below, we will create a class “Human” and broadcast the response of its object to different temperatures. In my previous post, this was implemented with the help of delegates, but in this post, I will use events to implement the same.

public class Human
    {
        // Basic properties
        public int Id { get; set; }
        public string Name { get; set; }
        public float MinTemp { get; set; }
        public float MaxTemp { get; set; }
        private string feeling;
        public string Feeling
        {
            get
            {
                return feeling;
            }
            set
            {
                feeling = value;
 
                // If there are subscribers to the OnTemperatureChanged event
                // change in Feeling property will trigger off the event
                if (OnTemperatureChanged != null)
                {
                    OnTemperatureChanged(this, new EventArgs());
                }
            }
        }
 
        // Events
        public event EventHandler OnTemperatureChanged;
 
        public Human()
        {
 
        }
 
        // User defined constructor
        public Human(int id, string name, float minTemp, float maxTemp, string feeling)
        {
            Id = id;
            Name = name;
            MinTemp = minTemp;
            MaxTemp = maxTemp;
            Feeling = feeling;
        }
 
        public void TemperatureSensation(float temperature)
        {
 
            if (temperature > MaxTemp)
            {
                // If the OnTemperatureChanged event's invocation list is not empty
                // change in Feeling property's value will trigger the event to fire off
                Feeling = "I am feeling hot";
            }
            else if (temperature < MinTemp)
            {
                Feeling = "I am feeling cold";
            }
            else
            {
                Feeling = "I am feeling normal";
            }
        }
    }

In the code above, we have a class "Human" with five properties, of which three are of importance - MinTemp, MaxTemp and Feeling. The method TemperatureSensation accepts a float type parameter and depending on where the value stands in the spectrum between MinTemp and MaxTemp, sets the value of the Feeling property. We have an explicit implementation of the Feeling property and if there are subscribers to the OnTemperatureChanged event, it fires off when the value of the Feeling property’s underlying variable is set to a new value. In the following code, we create an object of the Human class and test the functionality of event declared in the Human class.

class Program
    {
        static void Main(string[] args)
        {
            Human human = new Human() 
            {
                Id = 1,
                Name = "Parakh Singhal",
                MaxTemp = 45f,
                MinTemp = 10f, 
                Feeling = null
            };
 
            human.OnTemperatureChanged += OnTemperatureChangeHandler;
 
            human.TemperatureSensation(8f);
            human.TemperatureSensation(25f);
            human.TemperatureSensation(50f);
 
            Console.WriteLine("Press any key to terminate the program...");
            Console.ReadKey();
        }
 
        public static void OnTemperatureChangeHandler(object sender, EventArgs e)
        {
            if (sender is Human)
            {
                Human human = sender as Human;
                Console.WriteLine(human.Feeling);
            }
        }
    }

 

In the code above we created an object of the Human class and instantiated it with some data. Then, we enrolled in a method “OnTemperatureChangeHandler” that matched the “OnTemperatureChanged“ event’s underlying delegate’s signature and return type. Then we invoked the TemperatureSensation method on the human object which then broadcasted the messages to the event’s subscribed members.

The code when executed provides the following output:

3 of n 01 Object State

Contrast this code with the one created in the previous post, where not only we operated on the back of a delegate object, but also created the registration and un-registration methods for the methods to get enrolled in the delegate’s invocation list, to prevent direct access to delegate object’s invocation list. We also had more lines of programming as we had to trigger off the delegate wherever the state of the object was deemed to change. The new code is not only concise but also more maintainable.

Here we leverage the code that gets created by C# compiler for us. The event keyword, behind the scenes gets expanded into a delegate accepting the standard input parameters of an object type and an EventArgs class object and returning a void. We can then enrol methods with the same signature and return type, using the overridden “+” operator. Methods can be un-registered using the “-“ operator.

Broadcast of fulfilment of a certain condition

Some conditions when they get fulfilled, warrant an intimation. The information about the fulfilment of condition is broadcasted via events and the subscribers then process the information as they deem fit.

Let’s take an example of a library where the information about successful checkout of a book is processed.

 

public class Author
    {
        public int AuthorId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }
public class Book
    {
        public int BookId { get; set; }
        public string ISBN { get; set; }
        public string Title { get; set; }
        public Author Author { get; set; }
        public int PageCount { get; set; }
        public bool IsCheckedOut { get; set; }
    }
public class Member
    {
        public int MemberId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int NumberOfBooksCheckedOut { get; set; }
        public List<Book> BooksCheckedOut { get; set; }
    }
public Library()
        {
            Books = new List<Book>()
                        {
                            new Book()
                            {
                                BookId = 1,
                                Title = "Alice in Wonderland",
                                IsCheckedOut = false,
                                PageCount = 200,
                                Author = new Author()
                                {
                                    AuthorId = 1,
                                    FirstName = "Lewis",
                                    LastName = "Carroll"
                                }
                            },
                            new Book()
                            {
                                BookId = 2,
                                Title = "Bad Blood",
                                IsCheckedOut = false,
                                PageCount = 350,
                                Author = new Author()
                                {
                                    AuthorId = 2,
                                    FirstName = "John",
                                    LastName = "Carreyrou"
                                }
                            },
                            new Book()
                            {
                                BookId =  3,
                                Title = "The Dream Machine",
                                IsCheckedOut = false,
                                PageCount = 250,
                                Author = new Author()
                                {
                                    AuthorId = 3,
                                    FirstName="Mitchell",
                                    LastName = "Waldrop"
                                }
                            },
                            new Book()
                            {
                                BookId = 4,
                                Title = "The Structure of Scientific Revolution",
                                IsCheckedOut = false,
                                PageCount = 500,
                                Author = new Author()
                                {
                                    AuthorId = 4,
                                    FirstName = "Thomas",
                                    LastName=  "Kuhn"
                                }
                            },
                            new Book()
                            {
                                BookId =5,
                                Title = "Sapiens: A Brief History of Humankind",
                                IsCheckedOut = false,
                                PageCount = 450,
                                Author =new Author()
                                {
                                    AuthorId = 5,
                                    FirstName = "Yuval",
                                    LastName = "Hariri"
                                }
                            }
                        };
            Members = new List<Member>()
                        {
                            new Member()
                            {
                                MemberId = 1,
                                FirstName = "Parakh",
                                LastName = "Singhal",
                                NumberOfBooksCheckedOut = 0
                            },
                            new Member()
                            {
                                MemberId = 2,
                                FirstName = "Prateek",
                                LastName = "Mathur",
                                NumberOfBooksCheckedOut = 0
                            },
                            new Member()
                            {
                                MemberId =3,
                                FirstName = "Sumant",
                                LastName = "Sharma",
                                NumberOfBooksCheckedOut = 0
                            }
                        };
        }
 
        public void CheckOutBook(int memberId, int bookId)
        {
            Book book = Books.Find(book => book.BookId == bookId);
            Member member = Members.Find(member => member.MemberId == memberId);
 
            if (book.IsCheckedOut)
            {

Console.WriteLine("Apologies, but book is already checked out.

Please select another book.");

            }
            else if (member.NumberOfBooksCheckedOut >= 2)
            {

Console.WriteLine("Apologies, but there are already 2 books

checked out to you.");

            }
            else
            {
                book.IsCheckedOut = true;
                member.NumberOfBooksCheckedOut += 1;

OnSuccessfulCheckOut?.Invoke(this,

new LibraryEventArgs() { Message = "Checkout of book is successful" });

 
            }
        }
    }
 
public sealed class LibraryEventArgs : EventArgs
    {
        public string Message { get; set; }
    }

 

The model created above is consumed in the console application.

class Program
    {
        static void Main(string[] args)
        {
            Library library = new Library();
 
            library.OnSuccessfulCheckOut += CheckoutSuccessfulEventHandler;
 
 
            library.CheckOutBook(1, 1);
 
            Console.WriteLine("Press any key to exit the program...");
            Console.ReadKey();
        }
 
        private static void CheckoutSuccessfulEventHandler(object sender, EventArgs e)
        {
            if (sender is Library && e is LibraryEventArgs)
            {
                LibraryEventArgs libraryEventArgs = e as LibraryEventArgs;
                Console.WriteLine(libraryEventArgs.Message);
            }
        }    
}

 

When executed the following output appears:

3 of n 02 Object State

The example shows usage of event for emitting information for an important activity with the help of event. In the example when the book is successfully checked out, the event OnSuccesfulCheckOut is fired, provided that the underlying delegate’s invocation list is not empty, and the information regarding the successful checkout of the book is emitted to the subscribed event handlers.

Summary

Delegates help separation with the process of emitting the information and processing that information, events take things to the next level by raising the level of abstraction and using a more natural vocabulary.

I hope that this post helped you in rounding out the concept of events and delegates and how events are built on the foundation of delegates, which is the reason, delegates are discussed first in academic texts.

References

  1. Pro C# 8 with .NET Core 3 by Andrew Troelsen and Phil Japikse
  2. CLR via C# by Jeffrey Richter

Delegates And Events – Part 2 of 3

19. November 2020 12:42 by Parakh in Visual C#  //  Tags: ,   //   Comments
This post explains the concept of delegates in a lucid manner, giving simple to understand code examples.

In my last post, I explained the concept of events, delegates, and event handlers at a high level. In this post, let’s take a look at delegates. Why delegates and not events, because among them, they are the tricky ones and can be understood in relative isolation.

Delegates form the glue connecting the events and event handlers and are instrumental in conveying to all the subscribed event handlers about the firing of an event. What I will try to show in this post are the following:
1. High-level conceptual coverage of delegates,
2. Technical overview of delegates,
3. How a delegate can execute a connected method, aka an event handler

High-Level Overview:

Consider the following scenario where you have a pipeline going out from a pumping station and connected to that pipeline are houses and industries. Houses will use the supplied water for cleaning, cooking, etc. while the industries will use the same water for chemical and other manufacturing purposes. Water Pumping station supplying water to homes and industries


 

The pumping station here is synonymous with an event firing when an action of interest takes place. The water is the information conveyed by the event, and the houses and industries are the event handlers which process the supplied water, aka the information in the desired form. Delegates form the plumbing connecting the events and event handlers.

Before we move any further, we must understand what is a callback mechanism.

Callback Mechanism

Essentially, a callback mechanism that lets us chain pieces of code together in such a way that the first piece calls the second. The call can be made in two ways:

1) Synchronous callbacks, in which the first code (calling code) waits for the second code (called code) to finish the work and deliver the result, and, only after that resumes execution.
E.g. You can have a windows form application which has a button which upon clicking starts execution of a long running process. While the process is running the windows form will remain unresponsive.

2) Asynchronous callbacks, where the first code calls the second code but does not wait for it to finish the work. The work by the second piece of code is carried out on a different thread.

Callback mechanism is known by different names in different programming languages. In C, it is known as function pointers, and in C# it is called “delegates”.

In C#, delegates form the plumbing that allows one code to call another code synchronously or asynchronously. Adding to that is the fact that they are type-safe, i.e. you can only call methods that comply with the delegate’s signature and return type. In this post we will focus only on the synchronous calling via delegates

Use cases for delegates

Delegates can be used in two scenarios:

1. To broadcast changes in an object’s state
2. To broadcast fulfillment of a condition

Delegates behind the scenes are a special type of class, which means that we need to create an object of delegate type to use it for callback purposes.

Let’s dive into practical examples that show the use cases in action.

Broadcast changes in an object’s state


Consider human beings and their sensitiveness to ambient temperature. We can create a class “Human” and broadcast the response of its object to different temperatures.

public class Human
    {
        // Basic properties
        public int Id { get; set; }
        public string Name { get; set; }
        public float MinTemp { get; set; }
        public float MaxTemp { get; set; }
 
        //Delegate declaration
        public delegate void TemperatureSensationHandler(string message);
 
        // Member variable of the declared delegate
        public TemperatureSensationHandler temperatureSensationMembers;
 
        public Human()
        {
        }
 
        // User defined constructor
        public Human(int id, string name, float minTemp, float maxTemp)
        {
            Id = id;
            Name = name;
            minTemp = MinTemp;
            maxTemp = MaxTemp;
        }
 
        public void TemperatureSensation(float temperature)
        {
            if (temperatureSensationMembers!= null)
            {
                if (temperature > MaxTemp)
                {
                    temperatureSensationMembers("I am feeling hot");
                }
                else if (temperature < MinTemp)
                {
                    temperatureSensationMembers("I am feeling cold");
                }
                else
                {
                    temperatureSensationMembers("I am feeling normal");
                }
            }         
        }
    }

 

In the code above, we have a class "Human" with four properties, of which two are of importance - MinTemp and MaxTemp. The method TemperatureSensation accepts a float type parameter and depending upon where the value stands in the spectrum between MinTemp and MaxTemp, broadcasts an appropriate message. In the following code, we create an object of the Human class and test the functionality of delegate declared in the Human class.

class Program
    {
        static void Main(string[] args)
        {
            Human human = new Human() 
{ Id = 1, Name = "Parakh", MinTemp = 25.5f, MaxTemp = 45.0f };
 
            human.temperatureSensationMembers += OnTemperatureSensationEvent;
            human.TemperatureSensation(100);
            human.TemperatureSensation(30);
            human.TemperatureSensation(10);
            Console.ReadLine();
        }
 
        public static void OnTemperatureSensationEvent(string message)
        {
            Console.WriteLine(message);
        }
    }

 

In the code above we created an object of the Human class and instantiated it with some data. Then, we enrolled in the delegate object a method that matched the delegate’s signature and return type. Then we invoked the TemperatureSensation method on the human object which then broadcasted the messages to the delegate’s subscribed members.

The above code when executed yields the following outputBoradcast changes in an object's state

Now there’s one flaw in the code above. Delegate class at the .Net framework level overrides the “+” and “-“ operators, allowing the subscription and unsubscription of complying methods. It means that having direct access to the delegate object is sensitive and a developer should not be able to empty a delegate object’s invocation list by using the “-“ operator repeatedly.

We thus encapsulate the subscription and unsubscription of the methods to the delegate object via methods.

public class Human
    {
        // Basic properties
        public int Id { get; set; }
        public string Name { get; set; }
        public float MinTemp { get; set; }
        public float MaxTemp { get; set; }
 
        //Delegate declaration
        public delegate void TemperatureSensationHandler(string message);
 
        // Member variable of the declared delegate
        private TemperatureSensationHandler temperatureSensationMembers;
 
        //Registration and un-registration methods
        public void RegisterTempSensationHandlerMethods(TemperatureSensationHandler methodToRegister)
        {
            temperatureSensationMembers += methodToRegister;
        }
 
        public void UnRegisterTempSensationMethods(TemperatureSensationHandler methodToUnregister)
        {
            if (temperatureSensationMembers != null)
            {
                temperatureSensationMembers -= methodToUnregister;
            }
        }
 
        public Human()
        {
 
        }
 
        // User defined constructor
        public Human(int id, string name, float minTemp, float maxTemp)
        {
            Id = id;
            Name = name;
            minTemp = MinTemp;
            maxTemp = MaxTemp;
        }
 
        public void TemperatureSensation(float temperature)
        {
            if (temperatureSensationMembers!= null)
            {
                if (temperature > MaxTemp)
                {
                    temperatureSensationMembers("I am feeling hot");
                }
                else if (temperature < MinTemp)
                {
                    temperatureSensationMembers("I am feeling cold");
                }
                else
                {
                    temperatureSensationMembers("I am feeling normal");
                }
            }         
        }
    }

With the modified code, we now can register and un-register the methods without exposing the delegate object to a developer.

The code above yields the same output.

Boradcast changes in an object's state

Broadcast of fulfilment of a condition


Some conditions warrant broadcast of information when they materialize. Examples would include intimation upon sending or receiving a message, dispatch, and delivery of a physical item in case of e-commerce applications, etc.

Here we will take an example of checking out a book from a library.

We have four classes - Book, Member, Author, and Library. The idea in this example is to broadcast a message upon execution of certain activities like searching for a book, searching for member records, and successful or unsuccessful checking out of a book.

public class Author
   {
        public int AuthorId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }
 
public class Book
    {
        public int BookId { get; set; }
        public string ISBN { get; set; }
        public string Title { get; set; }
        public Author Author { get; set; }
        public int PageCount { get; set; }
        public bool IsCheckedOut { get; set; }
    }
 
public class Member
    {
        public int MemberId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int NumberOfBooksCheckedOut { get; set; }
        public List<Book> BooksCheckedOut { get; set; }
    }
 
    public class Library
    {
        public int LibraryId { get; set; }
        public List<Book> Books { get; set; }
        public List<Member> Members { get; set; }
        public Library()
        {
            LibraryId = 1;
            Books = new List<Book>()
                        {
                            new Book()
                            {
                                BookId = 1, 
                                Title = "Alice in Wonderland", 
                                IsCheckedOut = false, 
                                PageCount = 200,
                                Author = new Author() 
                                { 
                                    AuthorId = 1, 
                                    FirstName = "Lewis", 
                                    LastName = "Carroll"
                                }
                            },
                            new Book()
                            { 
                                BookId = 2, 
                                Title = "Bad Blood", 
                                IsCheckedOut = false, 
                                PageCount = 350,
                                Author = new Author()
                                { 
                                    AuthorId = 2, 
                                    FirstName = "John", 
                                    LastName = "Carreyrou"
                                }
                            },
                            new Book()
                            { 
                                BookId =  3, 
                                Title = "The Dream Machine", 
                                IsCheckedOut = false, 
                                PageCount = 250,
                                Author = new Author()
                                { 
                                    AuthorId = 3, 
                                    FirstName="Mitchell", 
                                    LastName = "Waldrop"
                                }
                            },
                            new Book()
                            { 
                                BookId = 4, 
                                Title = "The Structure of Scientific Revolution", 
                                IsCheckedOut = false,
                                PageCount = 500, 
                                Author = new Author()
                                { 
                                    AuthorId = 4, 
                                    FirstName = "Thomas", 
                                    LastName=  "Kuhn"
                                }
                            },
                            new Book()
                            { 
                                BookId =5,
                                Title = "Sapiens: A Brief History of Humankind", 
                                IsCheckedOut = false,
                                PageCount = 450, 
                                Author =new Author() 
                                { 
                                    AuthorId = 5, 
                                    FirstName = "Yuval", 
                                    LastName = "Hariri"
                                }
                            }
                        };
            Members = new List<Member>()
                        {
                            new Member()
                            { 
                                MemberId = 1, 
                                FirstName = "Parakh", 
                                LastName = "Singhal", 
                                NumberOfBooksCheckedOut = 0
                            },
                            new Member()
                            { 
                                MemberId = 2, 
                                FirstName = "Prateek", 
                                LastName = "Mathur", 
                                NumberOfBooksCheckedOut = 0
                            },
                            new Member()
                            { 
                                MemberId =3, 
                                FirstName = "Sumant", 
                                LastName = "Sharma", 
                                NumberOfBooksCheckedOut = 0
                            }
                        };
 
        }
 
        //Delegate which can accept methods having a string parameter
        public delegate void CheckoutHandler(string message);
 
        private CheckoutHandler checkOutHandlerMembers;
 
        public void RegisterCheckOutHandlerMembers(CheckoutHandler methodToRegister)
        {
            checkOutHandlerMembers += methodToRegister;
        }
 
        public void UnRegisterCheckOutHandlerMembers(CheckoutHandler methodToUnregister)
        {
            if (checkOutHandlerMembers != null)
            {
                checkOutHandlerMembers -= methodToUnregister;
            }
        }
 
        public void CheckOutBook(int MemberId, int bookId)
        {
            //The operation of search will be printed out by the program only 
            //if there are members to the delegate type
            if (checkOutHandlerMembers != null)
            {
                checkOutHandlerMembers("Searching for book and Member records");
            }
 
            Book bookToBeCheckedOut = Books.Find(book => book.BookId == bookId);
            Member MemberCheckingOut = Members.Find(Member => Member.MemberId == MemberId);
 
            //The operation of success in finding the book and Member will
            //only be printed if there are members to the delegate type
            if (bookToBeCheckedOut != null &&
                MemberCheckingOut != null && 
                checkOutHandlerMembers != null)
            {
                checkOutHandlerMembers("Book and Member records found.");
            }
 
            if (bookToBeCheckedOut.IsCheckedOut == false)
            {

//The final result, whether the book can be checked

//out needs to be printed whether

                //there are any members to the delegate type. 
                //That's why if-else statement with duplicate
                //console writes
                if (checkOutHandlerMembers != null)
                {
                    checkOutHandlerMembers("Congratulations, the book is available");
                }
                else
                {
                    Console.WriteLine("Congratulations, the book is available");
                }
            }
            else
            {
                if (checkOutHandlerMembers != null)
                {

checkOutHandlerMembers("Apologies, but the book is already

checked out to an existing Member.");

                }
                else
                {

Console.WriteLine("Apologies, but the book is

already checked out to an existing Member.");

                }
            }
        }
    }
}

 

The model created above is consumed in the console application.

class Program
    {
        static void Main(string[] args)
        {
            Library library = new Library();
            library.RegisterCheckOutHandlerMembers(OnCheckingOutEvent);            
            library.CheckOutBook(1, 1);
            
            Console.ReadLine();
        }
 
        public static void OnCheckingOutEvent(string message)
        {
            Console.WriteLine(message);
        }
    }

 

When executed the following output appears:

Broadcast of fulfillment of a conditionIn the output shown above, the program emitted certain messages related to the searching of the member and book details and successful checkout of the book. Now, remember, the domain is responsible for emitting the information, but what we do with the information is entirely in the hands of the developer. We can compose an email or an SMS and embed the emitted message or have it logged in a log file or a database.

Summary

Delegates help in separating the act of broadcasting information and how that information is consumed.
I hope this post helped in laying a strong foundation by presenting the concept of delegates in an easy to understand manner.

References

  1. Pro C# 8 with .NET Core 3 by Andrew Troelsen and Phil Japikse
  2. CLR via C# by Jeffrey Richter

Delegates And Events–Part 1 of 3

19. November 2020 09:45 by Parakh in Visual C#  //  Tags: , ,   //   Comments
This post attempts at explaning the concept of delegates and events in C# as it relates to real life, in as simple a manner as possible.

In my experience, if there’s one topic in C# that baffles the programmers the most, it is the topic of events and delegates. And the root of the state of confusion is not the lack of attempt on the programmer’s part, but the way the books on programming languages explain the concept – in a dry academic sense, devoid of any relatability to real life.

This blog post is my attempt to explain the concept of events and delegates in an easy way.

Events in real life are occurrences where something of importance happened to an entity. Let me elaborate on that. Consider a normal human being. Events in the life of a normal human being would be:
1. Birth
2. First day of school
3. Graduation from school
4. First day of college
5. Graduation from college
6. First pay-check
7. Getting married
8. First child
…and so on. The aforementioned occurrences would normally be considered important, hence endowed with a special term, events.

Whenever an event occurs, there’s something done. Birth of a child is celebrated, graduation from school is celebrated with a farewell party, marriages are celebrated with near and dear ones, etc.

This is what is the inspiration for the abstraction, aptly called events in modern object-oriented programming languages. Events are important occurrences in the life-span of an entity. An entity can be a button in a Windows Form, a dropdown on a web page, or some kind of a custom activity in a user-defined object like a domain class. Correspondingly, the event would be pressing a button, changing the selected element in a dropdown or value of a property in a domain class.

Whenever some event occurs or gets fired, something is done. That something is done in some defined code, called the event handler. An event handler is essentially a method, with details of what needs to be done when the event is fired.

Now that we have understood the concept of events and event handlers, let’s understand the concept of delegates. Delegates are like the consciousness that pushes us to do something when an event occurs. Essentially, a delegate is the glue that ties an event and an event handler together.

To summarise:
When something of importance happens -> Event
What we do in case of an event -> Event Handler
The glue connecting the event and event handler –> Delegate

I hope that this post was able to sow the idea of events and delegates your mind in a non-technical and relatable way. In future posts, I will shed more light on the topic.

Month List

ParakhSinghal@Twitter.com

Note: For Customization and Configuration, CheckOut Recent Tweets Documentation