Explain Unit of Work pattern

The Unit of Work pattern is a software design pattern that provides a way to manage transactions and coordinate the work of multiple repositories in an application. It helps maintain data consistency and integrity by ensuring that multiple operations are treated as a single unit of work and are either all committed or all rolled back.

The main purpose of the Unit of Work pattern is to abstract the underlying data access code and provide a high-level interface for managing transactions and coordinating changes to multiple entities. It ensures that all changes made within a unit of work are tracked and persisted consistently.

Here's a basic example of the Unit of Work pattern:

public interface IUnitOfWork : IDisposable
{
    void BeginTransaction();
    void Commit();
    void Rollback();
    void SaveChanges();
    IRepository<TEntity> GetRepository<TEntity>() where TEntity : class;
}

public class UnitOfWork : IUnitOfWork
{
    private readonly DbContext _context;
    private readonly Dictionary<Type, object> _repositories;
    private DbContextTransaction _transaction;

    public UnitOfWork(DbContext context)
    {
        _context = context;
        _repositories = new Dictionary<Type, object>();
    }

    public void BeginTransaction()
    {
        _transaction = _context.Database.BeginTransaction();
    }

    public void Commit()
    {
        _transaction.Commit();
        _transaction = null;
    }

    public void Rollback()
    {
        _transaction.Rollback();
        _transaction = null;
    }

    public void SaveChanges()
    {

        _context.SaveChanges();
    }

    public IRepository<TEntity> GetRepository<TEntity>() where TEntity : class
    {
        if (_repositories.ContainsKey(typeof(TEntity)))
        {
            return (IRepository<TEntity>)_repositories[typeof(TEntity)];
        }

        var repository = new Repository<TEntity>(_context);
        _repositories.Add(typeof(TEntity), repository);
        return repository;
    }

    public void Dispose()
    {
        _transaction?.Dispose();
        _context.Dispose();
    }
}
  

In this example, the IUnitOfWork interface defines the methods for beginning a transaction, committing or rolling back the transaction, saving changes, and retrieving repositories. The UnitOfWork class implements this interface and provides the concrete implementation.

The UnitOfWork class maintains an instance of the database context (DbContext) and a dictionary of repositories. The repositories are created lazily and stored in the dictionary to ensure that the same repository instance is used throughout the unit of work.

By using the Unit of Work pattern, you can ensure that multiple operations across different repositories are treated as a single unit of work. This allows you to maintain data consistency, perform atomic commits or rollbacks, and simplify the management of transactions in your application.

How to Find Tables that Contain a Specific Column in SQL Server?

Basic concept to understand about SQL Server is that of catalog views, which are effectively database tables (catalogs in this case) that display system-wide information about the SQL Server Database Engine.

All catalog views are accessed via a SELECT SQL statement FROM a specific catalog within the sys. namespace.
For example, the following statement can be used to view information about all database tables in the system via the sys.tables catalog

use mobility
go
select  
        s.[name]            'Schema',
        t.[name]            'Table',
        c.[name]            'Column',
        d.[name]            'Data Type',
        c.[max_length]      'Length',
        d.[max_length]      'Max Length',
        d.[precision]       'Precision',
        c.[is_identity]     'Is Id',
        c.[is_nullable]     'Is Nullable',
        c.[is_computed]     'Is Computed',
        d.[is_user_defined] 'Is UserDefined',
        t.[modify_date]     'Date Modified',
        t.[create_date]     'Date created'
from        sys.schemas s
inner join  sys.tables  t
on s.schema_id = t.schema_id
inner join  sys.columns c
on t.object_id = c.object_id
inner join  sys.types   d
on c.user_type_id = d.user_type_id
where c.name like '%ProjectManagerID%'
  

Hope this helps!!

Use RAND() in User Defined Function

Here is the issue,  you cannot call a non-deterministic function from inside a user-defined function.

So there is a workaround, By creating a view to return RAND(), call RAND() function inside a view and use that view inside your function, something like below.

CREATE VIEW ReturnRANDValue
AS
SELECT RAND() AS Value
  

Here is the function where we call above view inside this function

CREATE FUNCTION [dbo].[ReturnMobileNotificationCount] (
	@MobilityOrderID INT,
	@MobilityOrderItemID INT,
	@LineStatusMasterID INT,
	@LineSubStatusMasterID INT
	)
RETURNS BIT
AS
BEGIN
	DECLARE @LineSubStatusMatch BIT = 0

	SELECT MobilityOrderID,
		MobilityOrderItemID,
		LineStatusMasterID,
		LineSubStatusMasterID,
		HistoryID,
		ROW_NUMBER() OVER (
			ORDER BY (
					SELECT Value
					FROM ReturnRANDValue
					) DESC
			) SerialNo
	FROM MobileNotificationCriteriaHistory MNC(NOLOCK)
	WHERE MobilityOrderID = @MobilityOrderID
		AND MobilityOrderItemID = @MobilityOrderItemID
	ORDER BY HistoryID DESC

	RETURN @LineSubStatusMatch
END
  

How do you auto format code in Visual Studio?

Visual Studio 2019 & 2022

1. Format Document, While you're holding down Ctrl button, first press K then D
2. Format Selection, While you're holding down Ctrl button, first press K then F

or just click Edit => Advanced => Format Document / Format Selection

Happy formatting!!

Manually install an SSL certificate on my IIS 10 server

Once you have your certificate approved and ready do the following steps

Convert your .crt file to a .cer file

1. Locate your downloaded .crt file, and double-click to open it.
2. Select the Details tab, and then the Copy to File button.
3. Select Next in the Certificate Wizard.
4. Select Base-64 encoded X.509(.CER) and then select Next.
5. Select Browse, locate where you want to save your .CER file, and type in a name for your certificate.
6. Select Next and then Finished.

Copy your certificate files onto the server

1. Find the directory on your server where certificate and key files are stored, then upload your intermediate certificate (gd_iis_intermediates.p7b or similar) and primary certificate (.cer file that you just converted) into that folder.

Add a Certificate Snap-in to the Microsoft Management Console (MMC)

1. Click on your Start Menu, then click Run.
2. In the prompt, type mmc and click OK.
3. Click File, then click Add/Remove Snap-in.
4. On the new window, click the Add button.
5. On the new window, select Certificates and click Add.
6. Select Computer account for the snap-in and click Next.
7. Click Local computer and click Finish.
8. Click Close on the Add Standalone Snap-in window.
9. Click OK on the Add/Remove Snap-in window.

Import the Intermediate SSL Certificate

1. In the MCC Console, click ▸ to expand Certificates (Local Computer).
2. Right click on the Intermediate Certification Authorities folder, hover over All Tasks and click Import.
3. On the new window, click Next.
4. Click Browse, find your gd_iis_intermediates.p7b intermediate certificate file and click Open.
5. Click Next, verify that the certificate information is proper and click Finish.
6. Close the the import was successful notification.

Install your SSL certificate

1. Click on your Start Menu, then click Run.
2. In the prompt, type inetmgr and click OK to launch the Internet Information Services (IIS) Manager.
3. Under the Connections panel on the left, click on your Server Name.
4. In the main panel under the IIS section, double click on Server Certificates.
5. Under the Actions panel on the right, click Complete Certificate Request.
6. On the new window, click ... to browse, find your previously uploaded primary certificate file and click Open.
7. Add a Friendly name to easily identify this certificate in the future.
8. In the certificate store option, select Web Hosting and click OK.

Bind the SSL certificate

1. Under the Connections panel on the left, click ▸ to expand the Sites folder.
2. Click the Site Name that you plan to install the SSL certificate onto.
3. Under the Actions panel on the right, find the Edit Site section and click Bindings.
4. On the new window, click Add and fill out the following information:
   • Type: select https.
   • IP Address: select All Unassigned.
   • Port: type in 443.
   • Host name: leave this empty.
   • SSL Certificate: select your recently installed SSL.
5. Click OK to confirm, then Close for the Site Bindings window.

Restart IIS

1. Under the Actions panel on the right, find the Manage Website section and click Restart.