Friday, May 19, 2023

SQL Server Integration not working in VS2022

Here is what you need to do, you need to install SSIS Project for VS 2022

To install Analysis Services, Integration Services, and Reporting Services project support, run the SSDT standalone installer or direct download from here.

The installer lists available Visual Studio instances to add SSDT tools. If Visual Studio isn't already installed, selecting Install a new SQL Server Data Tools instance installs SSDT with a minimal version of Visual Studio, but for the best experience, we recommend using SSDT with the latest version of Visual Studio

Saturday, May 13, 2023

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.

Thursday, February 09, 2023

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!!

Saturday, January 28, 2023

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
  

Thursday, January 26, 2023

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

2023-01-26_23-34-09

Happy formatting!!