TransactionScope: A simple way to handle transactions in .NET

Have you ever tried implementing transactions using C# code? Normally, we implement transactions in SQL where multiple Insert/Update statements takes part in it. A Transaction follows the ACID (Atomicity, Consistency, Isolation, Durability) rule where either all the statements get committed or all get canceled and rolled back. TransactionScope allows us to implement it at application level. There could be some scenarios where you are required to do different operations in the same database or even multiple databases (distributed transaction) or due to some other constraints, it cannot be done at database level. It is also very helpful for application developers if they have less exposure to database.

What is TransactionScope

TransactionScope got introduced with .NET 2.0 as part of  System.Transaction. It is a class which provides a simple way to make a set of operations as part of a transaction without worrying about the complexity behind the scene. If any of the operation fails in between, entire transaction would fail and rolled back  which undo all the operation that got completed. All this would be taken care by the framework, ensuring the data consistency.

How to use the TransactionScope?

To use this, you need to add the reference of System.Transactions reference which is part of framework libraries (normally it wont be added by default). Once it get added, add the namespace System.Transactions wherever we want to use this. The syntax would look as

try
{
    using (TransactionScope scope = new TransactionScope())
    {
        // Do Operation 1
        // Do Operation 2
        //...

        // if all the coperations complete successfully, this would be called and commit the trabsaction. 
        // In case of an exception, it wont be called and transaction is rolled back
        scope.Complete();
    }
}
catch (ThreadAbortException ex)
{
    // Handle exception
}

Here we can see that we have used Disposable block while creating instance of TransactionScope, it makes sure the dispose gets called when it gets out of the block and ends the transaction scope.

In one Transaction scope, we can do multiple operation connecting to different databases as

using (TransactionScope scope = new TransactionScope())
{
    using (con = new SqlConnection(conString1))
    {
        con.Open();

        // Do Operation 1
        // Do Operation 2
        //...
    }

    using (con = new SqlConnection(conString2))
    {
        con.Open();

        // Do Operation 1
        // Do Operation 2
        //...

    }

    scope.Complete();
}

Here we are using two connection strings to connection different databases. We can use as many based on our requirement. We can have nested transactions as well. It could be as

public void DoMultipleTransaction()
{       
    try
    {
        using (TransactionScope scope = new TransactionScope())
        {
            using (con = new SqlConnection(conString1))
            {
                con.Open();
                // Do Operation 1
            }

            OtherTransaction();
            scope.Complete();
        }
    }
    catch (ThreadAbortException ex)
    {
        // Handle exception
    }
}

private void OtherTransaction()
{
    using (TransactionScope scope = new TransactionScope())
    {
        using (con = new SqlConnection(conString2))
        {
            con.Open();
            // Do Operations
        }
        scope.Complete();
    }
}

Here the outermost transaction is called as rootscope and here even if the inner transaction (OtherTransaction above) gets completed by calling scope.Complete(), if the rootscope complete could not be called due to various reasons, then the complete transaction would be rolled back including inner transactions.

Note: You might get one of the following exception while executing distributed trsanctions

  1. MSDTC on server is unavailable
  2. Network access for Distributed Transaction Manager (MSDTC) has been disabled.

 

Both the error are due to the same reason, first one occurs when you have the database and the application the same server while 2 if on the other server. For same server, go to run-> cmd-> services.msc. Run the service named Distributed Transaction Coordinator and make the startup type automatic so that it gets started again in case of system restart. For 2, follow the link to cofigure MSDTC.

TransactionScope provides various TransactionScopeOptions which defines transactions behavior for the scope. Lets see an example

using (TransactionScope scope = new TransactionScope())
{
    // Do Operation
    using (TransactionScope scope1 = new TransactionScope(TransactionScopeOption.Required))
    {
        // Do Operation
        scope1.Complete();
    }
    using (TransactionScope scope2 = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        // Do Operation
        scope2.Complete();
    }
    using (TransactionScope scope3 = new TransactionScope(TransactionScopeOption.Suppress))
    {
        // Do Operation
        scope3.Complete();
    }

    scope.Complete();
}

Here we created three transactions under the parent transaction with different TransactionScopeOptions. By default the scope is required, which applies to parent transaction here. It is a rootscope which creates a new transaction, and mark it as an ambient transaction. scope1 is also created with required and as we have already an ambient transaction (scope) so it joins the parent transaction. scope2 got created with option as RequiresNew which means it is a new transaction which is independently works with ambient transaction. scope3 got created with suppress option, which means it doesn’t take part in any ambient transactions. It gets executed regardless whether ambient transaction executes successfully or not. All the ambient transactions gets committed once the parent (global) scope completes.

Hope you enjoyed this post and will be using transaction in your future requirements.

Cheers,
Brij

Advertisements

.NET Datetime vs SQL Datetime : Comparison, Issues and Workarounds

Hello All,

Recently, in an application where we were saving the .NET DateTime value in SQL database, we had to compare this DateTime later in the .NET application. We realized that even the same DateTime stamp was not equating once it got fetched from database. I got Once we investigated further we found that the time stamp that we were sending to save in database, was not saving with the complete value and there were some more issues. It looks common requirement where you saving a value in database and later comparing it to add some business logic.  So I am sharing here my findings.

# 1

SQL DateTime type only stores time till milliseconds (3 digits) while in .NET it is stored till ticks. One millisecond is equivalent to 10,000 ticks. It means if you construct the .NET DateTime object from the DateTime of database, then it will never match because it will have all zero after the milliseconds. Lets see an example.
Here I am showing the same .NET DateTime value at application, in database table and again .NET DateTime read from the DB.

.NET DateTime

{6/19/2017 9:24:14 PM}
      Date: {6/19/2017 12:00:00 AM}
      Day: 19
      DayOfWeek: Monday
      DayOfYear: 170
      Hour: 21
      Kind: Local
      Millisecond: 777
      Minute: 24
      Month: 6
      Second: 14
      Ticks: 636335042547778146
      TimeOfDay: {21:24:14.7778146}
      Year: 2017

Let’s see how it is saved in database.

SQL DateTime

In database table it looks like as


When we fetch from database and convert it into it returns the following object
{6/19/2017 9:24:14 PM}
      Date: {6/19/2017 12:00:00 AM}
      Day: 19
      DayOfWeek: Monday
      DayOfYear: 170
      Hour: 21
      Kind: Unspecified
      Millisecond: 777
      Minute: 24
      Month: 6
      Second: 14
      Ticks: 636335042547770000
      TimeOfDay: {21:24:14.7770000}
      Year: 2017

Here we see the TimeOfDay component in both the object then we find that while in first object we have 7778146 while in second 7770000, it means last 4 digits are chopped off and reset to 0.
So is it safe if we compare till milliseconds only (say by using ToString(“MM/dd/yyyy hh:mm:ss.fff”))?
NO
There are some more mystery to it. Let’s see in second point

# 2

Here, I saved a new record in the database and the .net DateTime as
{6/19/2017 11:49:55 PM}
      Date: {6/19/2017 12:00:00 AM}
      Day: 19
      DayOfWeek: Monday
      DayOfYear: 170
      Hour: 23
      Kind: Local
      Millisecond: 731
      Minute: 49
      Month: 6
      Second: 55
      Ticks: 636335129957315136
      TimeOfDay: {23:49:55.7315136}
      Year: 2017

and when we see the record in the table, we see

Oh.. here we see the 730 millisecond while in .NET object it was 731. Let’s fetch the same and check the .net object as

{6/19/2017 11:49:55 PM}
      Date: {6/19/2017 12:00:00 AM}
      Day: 19
      DayOfWeek: Monday
      DayOfYear: 170
      Hour: 23
      Kind: Unspecified
      Millisecond: 730
      Minute: 49
      Month: 6
      Second: 55
      Ticks: 636335129957300000
      TimeOfDay: {23:49:55.7300000}
      Year: 2017

Here again we see 730 millisecond. It means we are losing one millisecond. It also suggests that comparing the two values till milliseconds would also not work. But why this is happening. Let’s dig it more.

Actually SQL DateTime stores till 1/3 millisecond approximately so the last digit of millisecond would always be

**0
**3
**7
**0

and SQL rounds of the milliseconds passed to it so if you pass
001 turns to 000
002 turns to 003
004 turns to 004
005 turns to 007

009 turns to 010

So can we just chop off milliseconds and compare the DateTime?
NO

It may work most of the time. But there are few chances to fail. Say we have a time as 09.00.00.999 then it will turn to 09.00.01.000 so here second got increased by 1. Even minute/hour can be changed if similar situation occur. So here we just have the option to round off the time based on the above logic and then compare.

Do we have any other option?
Yes

DateTime2

To overcome this, there is a new SQL data type datetime2 was introduced in SQL server 2008 which got the ability to save the millisecond till 7th precision. It is like an extension of DateTime which saves the time more accurately. Lets have a look on that

So we see here that the time is saved as 2017-06-19 23:49:55.7353342 while the same was saved in DateTime (type) as 2017-06-19 23:49:55.737 (rounded off). If we now fetch the same and assign the .NET DateTime object we get the exact date time and equality works as expected.

Note – There is one more significant update in datetime2. In DateTime if we want to same a default minimum DateTime then it was 1753 but in DateTime it could be 0001.

Conclusion

In this post, we discussed the behavior of SQL DateTime object, its issues and possible workarounds. Then we saw that the how the issues got resolved in datetime2 which was introduced in SQL server 2008. Obviously, it takes more space in database as it saves the time more granular level. I have rarely seen that DateTime values are stored in datetime2 format, mostly in DateTime at least in legacy application. And many of us don’t know the exact difference or may face the issues that we discussed. If we are working on some legacy application then we may not able to change the SQL data type, in that scenario, putting the round off logic could work.

Hope you have enjoyed the post. Do share your valuable feedback.

Happy Coding,
Brij

Singleton vs Static class : Key Differences and Usages

The debate about Singleton vs Static is quite old and it is still continuing and there are lots of confusion around this as well. In this post, I am trying to explain these two concepts, key differences and its usages.In this post I will not focus on the basics of Static and Singleton and how to write that. If you are new to these keywords, I will advise you to learn about these first to get more benefited.

So first we are going to understand the characteristics of each . Lets start with static class

Static Class :

  1. Static classes cannot be instantiated so it restricts us in many ways like it cannot implement Interfaces, inherit any class etc.
  2. Any other scenarios where this keyword is required, it cannot be used like indexer etc. Also it cannot be used as method parameter, local variable etc for the same reason.
  3. Static classes can have only static members – constructor, fields, methods, properties, events.
  4. One cannot control when static constructors are called. It’s always earlier than first access of the class. So no parameters can be passed as well.

Internally when compiler compiles static class, it marks it as a abstract and sealed. So that no instance can be created and cannot be extended as well. Now let’s talk about Singleton

Singleton Class :

  1. As name suggests it allows to have only one instance of a class.
  2. The constructor of this class are marked as private so that accidentally one cannot create multiple instances and provides a static function/property which first create one instance and returns the same each time.
  3. As singleton is a normal class, it allows us to leverage all that features of object oriented programming concepts.

Memory Management :

There is much confusion around memory management of static class and Singleton class. In simple words, any class whether it is itself static or any member if marked as static then it would not be collected by Garbage Collector.

Static variables/classes are not stored in normal Heap and there is a separate space in memory to store static resources which holds the static classes and variables.This space is beyond the scope of GC and memory get release only when corresponding process/AppDomain unloads.

Because singleton holds a static reference which cannot be collected by GC so the instance cannot be collected and both (Static and Singleton) gets destroyed with the AppDomain/Process.

Some Key common characteristics :

  • As both of the static and singleton instances are have just one copy in memory throughout the whole application, both used for holding global state in an application.
  • Both are initialized lazily, it means for static classes it is initialized only when accessed first time and for Singleton, it gets created only when it is accessed first time.

The Differences

  1. Very first difference is that Static is a language feature and Singleton is a architectural pattern so both belongs to difference arena altogether.
  2. Now a days everybody is behind using Dependency Injection and Static does not fit there because it is interface driven.
  3. Unit Testing is another topic where you can find some way to create a mock for singleton instances but testing static is a nightmare.
  4. Being singleton is a just another class, it enable you to use Object oriented concepts.

Singleton approach is much more flexible as we can see that from the differences itself. We can use interface with it and implement it in a class and use in our application. If some requirement changes and later we require to change the logic then we can just remove the older implementation and replace with new one without hiccups as long as the interface is same.. Also testing is another key benefit.

Static classes is mainly recommended for having grouping of a bunch of utility methods that can be called independently but again testing could be a problem and benefits of OOP is gone. So most of the time static should be avoided.

Having said that using global variable (like static class or singleton) makes a strong coupling between the global data and all the places where it is used.

Cheers,
Brij

 

Asynchronous programming with async and await : explained

In one of my previous posts, I discussed about the synchronous and asynchronous programming model. We saw in details that how does it work in single and multi-threaded mode. This post is extension of that post and here we are going to discuss the two relatively new keyword async and await and how does that actually work. I got many questions around this so sharing it in details here. If you are not very clear about how synchronous and asynchronous programming works and how does it work in single and multi-threaded scenario then refer my previous post mentioned below.

Concurrency vs Multi-threading vs Asynchronous Programming : Explained

Continue reading