async and await in C#

In this article we will discuss about the two new keywords that were introduced in C# 5.0, for implementing asynchronous programming. So let’s start with some basic their definitions, rules we have to follow to implement it.

await: As per MSDN, await is

The await operator is applied to a task in an asynchronous method to suspend the execution of the method until the awaited task completes. The task represents ongoing work.

This means, if we apply this keyword to a method, and when the control reaches that method, that method will start running in asynchronous mode and the control will move on to the next statement in the program. This is simply done by using the syntax

await Method_Name()

async: Simply adding this keyword will not work. The method in which await is used, must be marked with the async keyword. So if we need to make a method (say Method1Async()) asynchronous, using await, inside a method named Method2(), we call it using the code as:

function async void Method2()
  await Method1Async();

Task: The task or function which is to be made asynchronous, has a return type of the type Task which represents void return type or Task<TResult>, where TResult is the type of data returned by the function. So, for our method above, i.e. Method2, assuming that it returns an integer value, the signature will be of the following type:

function Task<Int32> Method2()
    return 1;

So this was the basic discussion. Now let’s convert the above code into an example. For this, our asynchronous method or Method2, will be something which takes time to complete and returns us an integer value. In our case, we will simply make it sleep using Thread.Sleep and then return an integer value. So it’s signature will become like the following:

Async and await in C#

Next, we create another function inside which we will call this function using await keyword. The reason we are not calling this function in the Main function is that we need to make the parent function as async and Main function cannot be made async. So we have to introduce an intermediate function named Method1(). Next, we simply need to call this function in the Main function. We are also printing different values, which will help us to evaluate and understand the flow of the program in better way.  So our complete code becomes like the following:

Async and await in C#

Run this code and see the results:

Async and await in C#

Let’s try to understand the flow of the program with the above output.

1. The program execution starts with the execution of the Main function and prints the very first line “Starting the ASYNCHRONOUS process“.
2. Next, it moves on to call Method1, which in turns Method2. As the Method2 is marked with the await keyword, it starts its execution in an asyncrhonous mode, suspend the further execution of the Method1 and immediately returns to the calling statement in the Main function, without printing the message in the very next line.
3. Then it continues the Main function and starts processing the for loop and print the message.
4. Finally when execution of the asynchronous method is completed, it again resumes the execution of the Method2 and prints the results, with the return value received from Method2.

So this was about the use of the async and await keywords. Hope you enjoyed reading it…!!!

Posted in C# | Leave a comment

OUTPUT clause in SQL Server

In my previous article, we discussed about the concept of Inserted & Deleted tables in SQL Server. Moving on the same lines, we will now discuss about the concept of OUTPUT keyword. The basic advantage of this keyword is that it can provide the records affected by the INSERT, UPDATE, DELETE & MERGE queries being executed. The output provided by it can be stored in any normal table, a table variable or temporary table.

OUTPUT clause has access two temporary or in memory SQL tables called, INSERTED and DELETED tables. These tables are populated when any INSERT/UPDATE/DELETE operation is performed on a table. As a result, OUTPUT clause can give us the affected records by referencing these tables. So let’s see how we can do this. But before we proceed with the discussion, we need to discuss key points related to these tables:

  • When a new record is inserted in a table, say Employee, a new record is added in the INSERTED table.
  • When a record is updated in a table, a new record, with the old values, is added in the DELETED table and a new record, with the new values is added in the INSERTED table.
  • When a record is deleted from a table, a new record, with the values of the record being deleted, is added in the DELETED table

For our discussion, we will use two tables named Employee and Department. So let’s discuss the cases one by one:

1. OUTPUT with the INSERT statement: Simply mention name the columns that you would like to be fetched,  using the Inserted.Column_Name syntax, just after the INSERT INTO and before the actual values to be inserted. See the query below:

OUTPUT clause in SQL

2. OUTPUT with DELETE statement: The concept remains the same except the Inserted.Column_Name is replaced with DELETED.Column_Name, as we are looking for the records that are affected by the DELETE statement. See the query below:

OUTPUT clause in SQL

3. OUTPUT with UPDATE statement: For any UPDATE operation, the records to be updated are first deleted and then inserted into the temporary tables. So we can get both the old and new record values. Let’s update the records and see the results:

OUTPUT clause in SQL

As we can see the results, we have the old value as the Deleted one and new value as the Inserted one.

4. OUTPUT with JOIN UPDATE/DELETE: Apart from these operations, we can also use the OUTPUT to get the records affected when we are using a JOIN statement to UPDATE/DELETE bulk records. For ex, we create a table Department and insert sample data into it. Also some related records are inserted into the Employee table. So we have the setup below:

OUTPUT clause in SQL

To test this case, we will update the records in the Employee table, for those who belong to the 'HR' department i.e. records with the Id as 1, 2, 18 and 19. So let’s write a query with INNER JOIN, to update the results use the OUTPUT keyword to see the affected records. So we write our query as:

OUTPUT clause in SQL

Now let’s see how we can store these values in the temporary tables or table variables. For this, we will add some more records in the table.

Store data into Temporary table: We simply create a new temporary table and insert the records into it using the INTO keyword. See the UPDATE query below, which stores the data into a temporary table:

OUTPUT clause in SQL

Store data into Table variable: No changes except that the temporary table is replaced with a table variable now. We will now use it with the DELETE statement. So our query changes to the one below:

OUTPUT clause in SQL

This keyword becomes very powerful option when we have stored procedures, where we are manipulating the data and need to know what and how records are getting affected. So this was about the use of the OUTPUT keyword. Happy SQLing…!!!

Posted in SQL Server | Leave a comment

Inserted and Deleted tables in SQL

In this article, we will discuss about the concept of Inserted and Deleted tables in SQL. Let’s start with their brief description. As per MSDN:

DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions.

For different DML operations being performed in the trigger, these tables are affected in the following manner:

  • If any record is being inserted in the main table, a new entry, of the record being created, is also inserted in the INSERTED table.
  • If any record is being deleted from the main table, an entry of the record being deleted, is inserted in the DELETED table.
  • If any record is being updated in the main table, an entry of that record (before it was updated), is added to the DELETED table and another entry of that record (after it was updated), is inserted into the INSERTED table.

Now let’s test these cases. For this, we create a new table called Products and a DML trigger on it. Our trigger will be same for Insert/Update/Delete operations. The purpose of this trigger will be to display records from following tables, whenever any DML operation is performed on the table:

  • Data of main table
  • Data of Inserted table
  • Data of Deleted table

Our trigger will look like the following:

Inserted & Deleted tables in SQL

Now let’s test the cases.

Create trigger and Inserted & Deleted tables: Let’s insert 2 new records in the table and discuss the output.

Inserted & Deleted tables in SQL

As we discussed above, insertion of new record in the main table, adds the same record in the Inserted table but no record is added in the Deleted table. In our case, insertion of the first record with ProductId = 1, adds the same record in Inserted table. When second record is inserted, trigger is executed again and only second record is added in the Inserted table. In both the cases, there is no insertion in the Deleted table.

Delete trigger and Inserted & Deleted tables: Now let’s execute the Delete query and see the output.

Inserted & Deleted tables in SQL

As we can see above, when we delete the record with ProdcutId = 2, its copy is inserted into the Deleted table. But no record is added in the Inserted table.

Update trigger and Inserted & Deleted tables: Now let’s update a record in the table and see the results.

Inserted & Deleted tables in SQL

As we discussed above, when we update a record, its copy is added to the Deleted table, before it was updated and its new copy is added to the Inserted table, after the record is updated in the main table. In our example, the record with ProductId = 1 is added to the Deleted table, with the old values and the same record with the new values is added to the Inserted table.

One very important point to be discussed here is that the OUTPUT clause in SQL always have access to these tables. We will discuss about this clause in my next article. So this was about the concept of Inserted and Deleted table. Hope you enjoyed reading it…!!!

Posted in SQL Server | Tagged | Leave a comment

checked & unchecked keyword in C#

In this article, we will discuss about the checked and unchecked keywords. Let’s start with their definitions first. As per MSDN checked means,

The checked keyword is used to explicitly enable overflow checking for integral-type arithmetic operations and conversions.

and unchecked means,

The unchecked keyword is used to suppress overflow-checking for integral-type arithmetic operations and conversions.

Here, overflow checking means that when the value of any integral-type exceeds its range, it does not raises any exception, instead will give unexpected results. Let’s discuss with an example of integers.

We know that range on an integer is 2147483647. Let’s see what happens when we try to add more to it, say add 50 to itSee the code below:

checked-unchecked in C#

It gives the compile time error :

Error 3 The operation overflows at compile time in checked mode ...\checked\Program.cs 23 29 checked.

So, at-least directly we cannot do this. Also, in most of our cases, we work with values from database. So we fetch them, store in variables and then perform any operations on it. In such a case, there will be no compile time or run time error, instead will be unexpected results. Let’s check see what happens:

checked-unchecked in C#

As we discussed, an unexpected result. The unexpected result is generated in a pattern. Rion Williams provided a great explanation here. The add operation adds a given number up-to maximum possible value of its range. After that it resets itself to its minimum value of its range and then starts adding again. In our case, we were adding 50 to 2147483647. So let’s see how it works.

  • It starts adding 1 at a time. By first addition of 1, it resets itself to minimum value of -2147483648. Now further 49 is to be added.
  • Then it re-starts from its minimum value of its range, which is -2147483648 and starts adding them one by one.

-2147483648 + 49 = -2147483599

We can handle this overflow of the value using the checked keyword. It will not give correct result but at-least it will give you exception of the type System.OverflowException and you can easily identify the issue. So apply the checked keyword, wrap the code in try-catch block and perform the alternate operation in catch block. Let’s change the code accordingly now:

checked-unchecked in C#

So you can now at-least handle such kind of situation, instead of giving incorrect results to the user.

Now, earlier in our discussion, we discussed that directly we cannot add more value to the maximum value of integer. What if we need to do this. This where we can use the unchecked keyword. Just use the same code with the unchecked keyword and it will compile and will even run to give the results. See the code below:

checked-unchecked in C#

So to summarize the discussion:

  • If we are directly trying to add something to maximum value of integer, we will get compile time error.
  • To avoid the above compile time error, we can use the unchecked keyword.
  • Using the unchecked keyword or indirectly adding more values to a maximum value of its range, results in unexpected results, generated in a pattern.
  • To handle the overflow value, we can use the checked keyword, apply try-catch block and handle the operation in catch block.
  • These keywords  can be applied as blocks of code or directly on the variables(like in our discussion above). See the code below:

checked-unchecked in C#

So this about the use of checked-unchecked keywords. Hope you enjoyed reading it…!!!

Posted in C# | Leave a comment

Apply in SQL Server

The apply keyword was introduced mainly for the use of selecting data by combining a select query with a table valued function, which is nothing but select query on another table (or result of any business logic in the function). To understand it better, let’s call our main table as the left table and the second table (in the table valued function) as the right table.

Let’s start by creating sample data. We will have two tables Employee and Department. Here, our Employee table will be the left table and the Department table will be the right table. Each employee can only belong to one department.

Cross apply in SQL , Outer apply in sql

Our aim will be to join these two tables to get the required data. One solution which immediately comes to mind is use of  LEFT, RIGHT or INNER JOIN, which will depend on our requirement. Let’s see the results with the use of JOINS.

Cross apply in SQL , Outer apply in sqlThe same results can be achieved using the apply keyword. It works the same way as the co-related sub query, with the fact that the select query on which apply is used i.e. the right table, will be executed for each and every record of the main table or the left table. It can be applied in two different ways:

  • CROSS APPLY: This works same as the INNER JOIN on two queries.
  • OUTER APPLY: This works same as the LEFT JOIN on two queries.

Let’s change the queries using these two forms of apply and see the results.

Cross apply in SQL , Outer apply in sqlAs we can see above, CROSS APPLY gives the same result as the INNER JOIN and OUTER APPLY gives the same result as the LEFT OUTER JOIN. The difference with the JOIN is that APPLY results in execution of the Select statement of Department query, for each record of Employee record (same as that of a co-related sub-query).

Next, suppose we were using the co-related sub query. But we need to view the rest of the columns of the second table i.e. the Department table. Can we do that ? Doesn’t seems to be, unless we add some twist in the query. But this can be easily done with APPLY keyword. Simply add name of the columns we want to view, in the select statement of Department and we are done. Let’s change our queries and see the results:

Cross apply in SQL , Outer apply in sql

Another possible and big use of APPLY is with the table valued function. We create a simple function which returns Department details by Id. Next we replace our Select statement for Department, with call to the user defined function. See the query below:

Cross apply in SQL , Outer apply in sqlSo, depending on the requirements, we can add or remove the names of the columns in the SELECT statement of the function call. To summarize, we can use the apply keyword as :

  • A co-related sub query with the advantage of selecting multiple columns
  • A join with the table valued user defined function to select multiple columns from the second table

So this was about the use of the apply keyword.

Posted in SQL Server | Tagged | Leave a comment