Mahedee Hasan

Mahedee Hasan

I am a Microsoft MVP (9th times), programmer, software developer, software architect, consultant, author, researcher, technical blogger, technical speaker, trainer and learner. I have more than 15 years experience in the field of software design and development.

Optimistic Concurrency Management in ASP.NET Core Application Using Raw SQL and ADO.NET

7 minute read

In a previous article here, I discussed various ways to deal with concurrency. Optimistic concurrency is a common method for handling such situations. This article is designed to illustrate the practical aspects of managing optimistic concurrency in an ASP.NET Core application using raw SQL and ADO.NET.

Tools and Technology Used

  • ASP.net core Web API
  • Visual C#
  • Entity Framework
  • SQL Server

Step 1: Create a database in SQL Server name - ConcurrencySQLDB

Step 2: Create a table name Product

  • Run the following script to create the table
CREATE TABLE [dbo].[Products](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[Price] [decimal](18, 2) NOT NULL,
	[Quantity] [int] NOT NULL,
	[RowVersion] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Step 3: Create a asp.net core web api project name ConcurrencyHandling.API

Step 4: Install the following nuget packages in the project.

System.Data.SqlClient

Step 5: Create a Model class name Product in Models folder

  • Product class to store Product Information
  • In the following class RowVersion property is a Guid which is used to track the updated version of the record.

Product.cs


namespace Concurrency.API.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; } = String.Empty;
        public decimal Price { get; set; } = 0;
        public int Quantity { get; set; } = 0;
        public Guid RowVersion { get; set; }
    }
}

Step 6: Create a DBConnector class to connect with database in Persistence folder.

DBConnector.cs


using System.Data.SqlClient;

namespace Concurrency.API.Persistence
{
    // This class is used to create a connection to the database.
    public class DBConnector : IDisposable
    {
        public SqlConnection connection;

        public DBConnector(string connectionString)
        {
            connection = new SqlConnection(connectionString);
            this.connection.Open();
        }

        public void Dispose()
        {
            connection.Close();
        }
    }
}

Step 7: Add connection string in appsettings.json file

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=ConcurrencySQLDB;User Id=sa;Password=MyPassword;TrustServerCertificate=True;"
  },
  "AllowedHosts": "*"
}

Step 8: Create IProductRepository in Repositories/Interface folder

IProductRepository.cs

using Concurrency.API.Models;

namespace Concurrency.API.Repositories.Interfaces
{
    public interface IProductRepository
    {
        Task<IEnumerable<Product>> GetProducts();
        Task<Product> GetProduct(int id);
        Task<Product> AddProduct(Product product);
        Task<Product> UpdateProduct(Product product);
        Task<Product> DeleteProduct(int id, Guid rowVersion);
    }
}

Step 9: Create ProductRepository in Repositories/Implementation folder

ProductRepository.cs

using Concurrency.API.Models;
using Concurrency.API.Persistence;
using Concurrency.API.Repositories.Interfaces;
using System.Data;
using System.Data.SqlClient;

namespace Concurrency.API.Repositories.Implementations
{
    public class ProductRepository : IProductRepository
    {
        private DBConnector _connector;
        public ProductRepository(DBConnector dBConnector)
        {
            _connector = dBConnector;
        }
        public Task<Product> AddProduct(Product model)
        {
            try
            {   
                if(this._connector.connection.State == ConnectionState.Closed)
                    this._connector.connection.Open();
                var cmd = this._connector.connection.CreateCommand() as SqlCommand;
                SqlTransaction transaction = this._connector.connection.BeginTransaction("");
                cmd.Transaction = transaction;
                cmd.CommandText = @"INSERT INTO Products (Name, Price, Quantity, RowVersion) 
                OUTPUT inserted.Id                
                VALUES (@Name, @Price, @Quantity, @RowVersion); SELECT SCOPE_IDENTITY();";

                Guid rowVersion = Guid.NewGuid();
                cmd.Parameters.AddWithValue("@Name", model.Name);
                cmd.Parameters.AddWithValue("@Price", model.Price);
                cmd.Parameters.AddWithValue("@Quantity", model.Quantity);
                cmd.Parameters.AddWithValue("@RowVersion", rowVersion);

                // cmd.ExecuteScalar() returns the first column of the first row in the result
                // set returned by the query. Additional columns or rows are ignored.
                int insertedId = Convert.ToInt32(cmd.ExecuteScalar());
                if (insertedId > 0)
                {
                    transaction.Commit();
                }

                model.Id = insertedId;
                model.RowVersion = rowVersion;
                return Task.FromResult(model);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // close connection
                this._connector.Dispose();
            }
            return null;
        }

        public Task<Product> GetProduct(int id)
        {
            var product = new Product();
            try
            {
                if (this._connector.connection.State == ConnectionState.Closed)
                    this._connector.connection.Open();
                var cmd = this._connector.connection.CreateCommand() as SqlCommand;
                cmd.CommandText = @"SELECT Id, Name, Price, Quantity, RowVersion FROM Products WHERE Id = @Id";
                cmd.Parameters.AddWithValue("@Id", id);
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    product.Id = Convert.ToInt32(reader["Id"]);
                    product.Name = reader["Name"] != DBNull.Value ? reader["Name"].ToString() : String.Empty;
                    product.Price = reader["Price"] != DBNull.Value ? Convert.ToDecimal(reader["Price"]) : 0;
                    product.Quantity = reader["Quantity"] != DBNull.Value ? Convert.ToInt32(reader["Quantity"]) : 0;
                    product.RowVersion = reader["RowVersion"] != DBNull.Value ? (Guid)reader["RowVersion"] : Guid.Empty;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // close connection
                this._connector.Dispose();
            }

            // Task.FromResult is a helper method that creates a Task that's completed successfully
            // with the specified result.
            return Task.FromResult(product);
        }

        public async Task<IEnumerable<Product>> GetProducts()
        {
            var products = new List<Product>();

            try
            {
                if (this._connector.connection.State == ConnectionState.Closed)
                    this._connector.connection.Open();
                var cmd = this._connector.connection.CreateCommand() as SqlCommand;
                cmd.CommandText = @"SELECT Id, Name, Price, Quantity, RowVersion FROM Products";
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    var product = new Product()
                    {
                        Id = Convert.ToInt32(reader["Id"]),
                        Name = reader["Name"] != DBNull.Value ? reader["Name"].ToString() : String.Empty,
                        Price = reader["Price"] != DBNull.Value ? Convert.ToDecimal(reader["Price"]) : 0,
                        Quantity = reader["Quantity"] != DBNull.Value ? Convert.ToInt32(reader["Quantity"]) : 0,
                        RowVersion = reader["RowVersion"] != DBNull.Value ? (Guid)reader["RowVersion"] : Guid.Empty
                    };
                    products.Add(product);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // close connection
                this._connector.Dispose();
            }
            return products;
        }

        public Task<Product> UpdateProduct(Product product)
        {

            try
            {
                if(this._connector.connection.State == ConnectionState.Closed)
                    this._connector.connection.Open();
                var cmd = this._connector.connection.CreateCommand() as SqlCommand;
                SqlTransaction transaction = this._connector.connection.BeginTransaction("");
                cmd.Transaction = transaction;
                cmd.CommandText = @"UPDATE Products SET Name = @Name, Price = @Price, Quantity = @Quantity, RowVersion = @RowVersion
                    WHERE Id = @Id AND RowVersion = @RowVersion";

                Guid rowVersion = Guid.NewGuid();
                cmd.Parameters.AddWithValue("@Id", product.Id);
                cmd.Parameters.AddWithValue("@Name", product.Name);
                cmd.Parameters.AddWithValue("@Price", product.Price);
                cmd.Parameters.AddWithValue("@Quantity", product.Quantity);
                cmd.Parameters.AddWithValue("@RowVersion", rowVersion);

                int effectedRow = cmd.ExecuteNonQuery();
                if (effectedRow > 0)
                {
                    transaction.Commit();
                }

                product.RowVersion = rowVersion;
                return Task.FromResult(product);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // close connection
                this._connector.Dispose();
            }
        }

        public Task<Product> DeleteProduct(int id, Guid rowVersion)
        {
            try
            {
                if (this._connector.connection.State == ConnectionState.Closed)
                    this._connector.connection.Open();
                var cmd = this._connector.connection.CreateCommand() as SqlCommand;
                SqlTransaction transaction = this._connector.connection.BeginTransaction("");
                cmd.Transaction = transaction;
                cmd.CommandText = @"DELETE FROM Products WHERE Id = @Id AND RowVersion = @RowVersion";
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.Parameters.AddWithValue("@RowVersion", rowVersion);

                int effectedRow = cmd.ExecuteNonQuery();
                if (effectedRow > 0)
                {
                    transaction.Commit();
                }
                return Task.FromResult(new Product());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // close connection
                this._connector.Dispose();
            }
        }
    }
}
  • Look at the update and delete query. You will see, row version is checked while updating or deleting rows.

Step 10: Create IProductService in Services/Interfaces class

IProductService.cs

using Concurrency.API.Models;

namespace Concurrency.API.Services.Interfaces
{
    public interface IProductService
    {
        Task<IEnumerable<Product>> GetProducts();
        Task<Product> GetProduct(int id);
        Task<Product> AddProduct(Product product);
        Task<Product> UpdateProduct(Product product);
        Task<Product> DeleteProduct(int id, Guid rowVersion);
    }
}

Step 11: Create ProductService in Services/Implementation class

ProductService.cs


using Concurrency.API.Models;
using Concurrency.API.Repositories.Interfaces;
using Concurrency.API.Services.Interfaces;

namespace Concurrency.API.Services.Implementations
{
    public class ProductService : IProductService
    {
        IProductRepository _productRepository;
        public ProductService(IProductRepository productRepository)
        {
            _productRepository = productRepository;
        }
        public Task<Product> AddProduct(Product product)
        {
            return _productRepository.AddProduct(product);
        }

        public Task<Product> GetProduct(int id)
        {
            return _productRepository.GetProduct(id);
        }

        public Task<IEnumerable<Product>> GetProducts()
        {
            return _productRepository.GetProducts();
        }

        public Task<Product> UpdateProduct(Product product)
        {
            return _productRepository.UpdateProduct(product);
        }

        public Task<Product> DeleteProduct(int id, Guid rowVersion)
        {
            return _productRepository.DeleteProduct(id, rowVersion);
        }
    }
}

Step 12: Configure Program class as follows.

Program.cs

using Concurrency.API.Persistence;
using Concurrency.API.Repositories.Implementations;
using Concurrency.API.Repositories.Interfaces;
using Concurrency.API.Services.Implementations;
using Concurrency.API.Services.Interfaces;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();


string connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddTransient(_ => new DBConnector(connectionString));

builder.Services.AddScoped<IProductRepository, ProductRepository>();
builder.Services.AddScoped<IProductService, ProductService>();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

Step 13: Create a Controller class ProductsController in Controllers folder.

ProductsController.cs


using Concurrency.API.Models;
using Concurrency.API.Persistence;
using Concurrency.API.Services.Interfaces;
using Microsoft.AspNetCore.Mvc;

namespace Concurrency.API.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ProductsController : ControllerBase
    {
        private DBConnector _connector;
        private readonly IProductService _productService;

        public ProductsController( DBConnector connector, IProductService productService)
        {
            _connector = connector;
            _productService = productService;
        }

        // GET: api/Products
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Product>>> GetProducts()
        {
            var products = await _productService.GetProducts();

            if (products == null)
            {
                return NotFound();
            }
            return Ok(products);
        }

        // GET: api/Products/5
        [HttpGet("{id}")]
        public async Task<ActionResult<Product>> GetProduct(int id)
        {
            var product = await _productService.GetProduct(id);
            if (product == null)
            {
                return NotFound();
            }
            return Ok(product);
        }

        // PUT: api/Products/5
        [HttpPut("{id}")]
        public async Task<IActionResult> PutProduct(int id, Product product)
        {
            if (id != product.Id)
            {
                return BadRequest();
            }
            var result = await _productService.UpdateProduct(product);
            if (result == null)
            {
                return NotFound();
            }
            return Ok(result);
        }

        // POST: api/Products
        [HttpPost]
        public async Task<ActionResult<Product>> PostProduct(Product product)
        {
            var result = await _productService.AddProduct(product);
            if (result == null)
            {
                return NotFound();
            }
            return Ok(result);
        }

        // DELETE: api/Products/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteProduct(int id, Guid rowVersion)
        {
            var result = await _productService.DeleteProduct(id, rowVersion);

            if (result == null)
            {
                return NotFound();
            }
            return Ok(result);
        }
    }
}

Step 14: Run the application and test concurrency

  • Create a new record by Post method in swagger. Sample example for post data.
{
  "id": 0,
  "name": "ASUS Laptop",
  "price": 1200,
  "quantity": 230
}
  • Update the record using the Put method in Swagger. A sample input is provided below. Meanwhile, open another tab and make an update to the record. Your record version will be altered. If you attempt to update the record using the previous record version, it will not updated due to concurrency. However, if no one else has updated it and you are using the correct record version, you can update the record.
{
    "id": 5,
    "name": "ASUS Laptop",
    "price": 1300,
    "quantity": 230,
    "rowVersion": "04e92587-8c5d-4a27-b5d5-bbee9fc84458"
}
  • Delete the record using the Delete method in Swagger. Meanwhile, open another tab and update the record. Your record version will be changed. If you attempt to delete the record using the previous record version, it will throw an exception due to concurrency. However, if no one else has updated it and you are using the correct record version, you can delete the record without encountering any problem.

Source code

You May Also Enjoy