Webiant Logo Webiant Logo
☌

  1. No results found.

    Try your search with a different keyword or use * as a wildcard.

BaseDataProvider.cs

using System.Data.Common;
using System.Linq.Expressions;
using System.Reflection;
using FluentMigrator;
using LinqToDB;
using LinqToDB.Data;
using LinqToDB.DataProvider;
using LinqToDB.Tools;
using Nop.Core;
using Nop.Core.Infrastructure;
using Nop.Data.Mapping;
using Nop.Data.Migrations;

namespace Nop.Data.DataProviders;

public abstract partial class BaseDataProvider
{
    #region Utilities

    /// 
    /// Gets a connection to the database for a current data provider
    /// 
    /// Connection string
    /// Connection to a database
    protected abstract DbConnection GetInternalDbConnection(string connectionString);

    /// 
    /// Creates the database connection
    /// 
    protected virtual DataConnection CreateDataConnection()
    {
        return CreateDataConnection(LinqToDbDataProvider);
    }

    /// 
    /// Creates the database connection
    /// 
    /// Data provider
    /// Database connection
    protected virtual DataConnection CreateDataConnection(IDataProvider dataProvider)
    {
        ArgumentNullException.ThrowIfNull(dataProvider);

        var dataConnection = new DataConnection(dataProvider, CreateDbConnection(), NopMappingSchema.GetMappingSchema(ConfigurationName, LinqToDbDataProvider))
        {
            CommandTimeout = DataSettingsManager.GetSqlCommandTimeout()
        };

        return dataConnection;
    }

    /// 
    /// Creates a connection to a database
    /// 
    /// Connection string
    /// Connection to a database
    protected virtual DbConnection CreateDbConnection(string connectionString = null)
    {
        return GetInternalDbConnection(!string.IsNullOrEmpty(connectionString) ? connectionString : GetCurrentConnectionString());
    }

    /// 
    /// Gets a data hash from database side
    /// 
    /// Array for a hashing function
    /// Data hash
    /// 
    /// For SQL Server 2014 (12.x) and earlier, allowed input values are limited to 8000 bytes.
    /// https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql
    /// 
    [Sql.Expression("CONVERT(VARCHAR(128), HASHBYTES('SHA2_512', SUBSTRING({0}, 0, 8000)), 2)", ServerSideOnly = true, Configuration = ProviderName.SqlServer)]
    [Sql.Expression("SHA2({0}, 512)", ServerSideOnly = true, Configuration = ProviderName.MySql)]
    [Sql.Expression("encode(digest({0}, 'sha512'), 'hex')", ServerSideOnly = true, Configuration = ProviderName.PostgreSQL)]
    protected static string SqlSha2(object binaryData)
    {
        throw new InvalidOperationException("This function should be used only in database code");
    }

    #endregion

    #region Methods

    /// 
    /// Initialize database
    /// 
    public virtual void InitializeDatabase()
    {
        var migrationManager = EngineContext.Current.Resolve();

        var targetAssembly = typeof(NopDbStartup).Assembly;
        migrationManager.ApplyUpMigrations(targetAssembly);

        var typeFinder = Singleton.Instance;
        var mAssemblies = typeFinder.FindClassesOfType()
            .Select(t => t.Assembly)
            .Where(assembly => !assembly.FullName?.Contains("FluentMigrator.Runner") ?? false)
            .Distinct()
            .ToArray();

        //mark update migrations as applied
        foreach (var assembly in mAssemblies)
            migrationManager.ApplyUpMigrations(assembly, MigrationProcessType.Update, true);
    }

    /// 
    /// Creates a new temporary storage and populate it using data from provided query
    /// 
    /// Name of temporary storage
    /// Query to get records to populate created storage with initial data
    /// Storage record mapping class
    /// 
    /// A task that represents the asynchronous operation
    /// The task result contains the iQueryable instance of temporary storage
    /// 
    public virtual Task> CreateTempDataStorageAsync(string storeKey, IQueryable query)
        where TItem : class
    {
        return Task.FromResult>(new TempSqlDataStorage(storeKey, query, CreateDataConnection()));
    }



    /// 
    /// Get hash values of a stored entity field
    /// 
    /// A function to test each element for a condition.
    /// A key selector which should project to a dictionary key
    /// A field selector to apply a transform to a hash value
    /// Entity type
    /// Dictionary
    public virtual async Task> GetFieldHashesAsync(Expression> predicate,
        Expression> keySelector,
        Expression> fieldSelector) where TEntity : BaseEntity
    {
        if (keySelector.Body is not MemberExpression keyMember ||
            keyMember.Member is not PropertyInfo keyPropInfo)
        {
            throw new ArgumentException($"Expression '{keySelector}' refers to method or field, not a property.");
        }

        if (fieldSelector.Body is not MemberExpression member ||
            member.Member is not PropertyInfo propInfo)
        {
            throw new ArgumentException($"Expression '{fieldSelector}' refers to a method or field, not a property.");
        }

        var hashes = GetTable()
            .Where(predicate)
            .Select(x => new
            {
                Id = Sql.Property(x, keyPropInfo.Name),
                Hash = SqlSha2(Sql.Property(x, propInfo.Name))
            });

        return await AsyncIQueryableExtensions.ToDictionaryAsync(hashes, p => p.Id, p => p.Hash);
    }

    /// 
    /// Returns queryable source for specified mapping class for current connection,
    /// mapped to database table or view.
    /// 
    /// Entity type
    /// Queryable source
    public virtual IQueryable GetTable() where TEntity : BaseEntity
    {
        var options = new DataOptions()
            .UseConnectionString(LinqToDbDataProvider, GetCurrentConnectionString())
            .UseMappingSchema(NopMappingSchema.GetMappingSchema(ConfigurationName, LinqToDbDataProvider));

        return new DataContext(options)
        {
            CommandTimeout = DataSettingsManager.GetSqlCommandTimeout()
        }
        .GetTable();
    }

    /// 
    /// Inserts record into table. Returns inserted entity with identity
    /// 
    /// 
    /// 
    /// 
    /// A task that represents the asynchronous operation
    /// The task result contains the inserted entity
    /// 
    public virtual async Task InsertEntityAsync(TEntity entity) where TEntity : BaseEntity
    {
        using var dataContext = CreateDataConnection();
        entity.Id = await dataContext.InsertWithInt32IdentityAsync(entity);
        return entity;
    }

    /// 
    /// Inserts record into table. Returns inserted entity with identity
    /// 
    /// 
    /// 
    /// Inserted entity
    public virtual TEntity InsertEntity(TEntity entity) where TEntity : BaseEntity
    {
        using var dataContext = CreateDataConnection();
        entity.Id = dataContext.InsertWithInt32Identity(entity);
        return entity;
    }

    /// 
    /// Updates record in table, using values from entity parameter.
    /// Record to update identified by match on primary key value from obj value.
    /// 
    /// Entity with data to update
    /// Entity type
    /// A task that represents the asynchronous operation
    public virtual async Task UpdateEntityAsync(TEntity entity) where TEntity : BaseEntity
    {
        using var dataContext = CreateDataConnection();
        await dataContext.UpdateAsync(entity);
    }

    /// 
    /// Updates record in table, using values from entity parameter.
    /// Record to update identified by match on primary key value from obj value.
    /// 
    /// Entity with data to update
    /// Entity type
    public virtual void UpdateEntity(TEntity entity) where TEntity : BaseEntity
    {
        using var dataContext = CreateDataConnection();
        dataContext.Update(entity);
    }

    /// 
    /// Updates records in table, using values from entity parameter.
    /// Records to update are identified by match on primary key value from obj value.
    /// 
    /// Entities with data to update
    /// Entity type
    /// A task that represents the asynchronous operation
    public virtual async Task UpdateEntitiesAsync(IEnumerable entities) where TEntity : BaseEntity
    {
        //we don't use the Merge API on this level, because this API not support all databases.
        //you may see all supported databases by the following link: https://linq2db.github.io/articles/sql/merge/Merge-API.html#supported-databases
        foreach (var entity in entities)
            await UpdateEntityAsync(entity);
    }

    /// 
    /// Updates records in table, using values from entity parameter.
    /// Records to update are identified by match on primary key value from obj value.
    /// 
    /// Entities with data to update
    /// Entity type
    public virtual void UpdateEntities(IEnumerable entities) where TEntity : BaseEntity
    {
        //we don't use the Merge API on this level, because this API not support all databases.
        //you may see all supported databases by the following link: https://linq2db.github.io/articles/sql/merge/Merge-API.html#supported-databases
        foreach (var entity in entities)
            UpdateEntity(entity);
    }

    /// 
    /// Deletes record in table. Record to delete identified
    /// by match on primary key value from obj value.
    /// 
    /// Entity for delete operation
    /// Entity type
    /// A task that represents the asynchronous operation
    public virtual async Task DeleteEntityAsync(TEntity entity) where TEntity : BaseEntity
    {
        using var dataContext = CreateDataConnection();
        await dataContext.DeleteAsync(entity);
    }

    /// 
    /// Deletes record in table. Record to delete identified
    /// by match on primary key value from obj value.
    /// 
    /// Entity for delete operation
    /// Entity type
    public virtual void DeleteEntity(TEntity entity) where TEntity : BaseEntity
    {
        using var dataContext = CreateDataConnection();
        dataContext.Delete(entity);
    }

    /// 
    /// Performs delete records in a table
    /// 
    /// Entities for delete operation
    /// Entity type
    /// A task that represents the asynchronous operation
    public virtual async Task BulkDeleteEntitiesAsync(IList entities) where TEntity : BaseEntity
    {
        using var dataContext = CreateDataConnection();
        if (entities.All(entity => entity.Id == 0))
        {
            foreach (var entity in entities)
                await dataContext.DeleteAsync(entity);
        }
        else
        {
            await dataContext.GetTable()
                .Where(e => e.Id.In(entities.Select(x => x.Id)))
                .DeleteAsync();
        }
    }

    /// 
    /// Performs delete records in a table
    /// 
    /// Entities for delete operation
    /// Entity type
    public virtual void BulkDeleteEntities(IList entities) where TEntity : BaseEntity
    {
        using var dataContext = CreateDataConnection();
        if (entities.All(entity => entity.Id == 0))
            foreach (var entity in entities)
                dataContext.Delete(entity);
        else
            dataContext.GetTable()
                .Where(e => e.Id.In(entities.Select(x => x.Id)))
                .Delete();
    }

    /// 
    /// Performs delete records in a table by a condition
    /// 
    /// A function to test each element for a condition.
    /// Entity type
    /// 
    /// A task that represents the asynchronous operation
    /// The task result contains the number of deleted records
    /// 
    public virtual async Task BulkDeleteEntitiesAsync(Expression> predicate) where TEntity : BaseEntity
    {
        using var dataContext = CreateDataConnection();
        return await dataContext.GetTable()
            .Where(predicate)
            .DeleteAsync();
    }

    /// 
    /// Performs delete records in a table by a condition
    /// 
    /// A function to test each element for a condition.
    /// Entity type
    /// 
    /// The number of deleted records
    /// 
    public virtual int BulkDeleteEntities(Expression> predicate) where TEntity : BaseEntity
    {
        using var dataContext = CreateDataConnection();
        return dataContext.GetTable()
            .Where(predicate)
            .Delete();
    }

    /// 
    /// Performs bulk insert operation for entity collection.
    /// 
    /// Entities for insert operation
    /// Entity type
    /// A task that represents the asynchronous operation
    public virtual async Task BulkInsertEntitiesAsync(IEnumerable entities) where TEntity : BaseEntity
    {
        using var dataContext = CreateDataConnection(LinqToDbDataProvider);
        await dataContext.BulkCopyAsync(new BulkCopyOptions(), entities.RetrieveIdentity(dataContext));
    }

    /// 
    /// Performs bulk insert operation for entity collection.
    /// 
    /// Entities for insert operation
    /// Entity type
    public virtual void BulkInsertEntities(IEnumerable entities) where TEntity : BaseEntity
    {
        using var dataContext = CreateDataConnection(LinqToDbDataProvider);
        dataContext.BulkCopy(new BulkCopyOptions(), entities.RetrieveIdentity(dataContext));
    }

    /// 
    /// Executes command asynchronously and returns number of affected records
    /// 
    /// Command text
    /// Command parameters
    /// 
    /// A task that represents the asynchronous operation
    /// The task result contains the number of records, affected by command execution.
    /// 
    public virtual async Task ExecuteNonQueryAsync(string sql, params DataParameter[] dataParameters)
    {
        using var dataConnection = CreateDataConnection(LinqToDbDataProvider);
        var command = new CommandInfo(dataConnection, sql, dataParameters);

        return await command.ExecuteAsync();
    }

    /// 
    /// Executes command using System.Data.CommandType.StoredProcedure command type and
    /// returns results as collection of values of specified type
    /// 
    /// Result record type
    /// Procedure name
    /// Command parameters
    /// 
    /// A task that represents the asynchronous operation
    /// The task result contains the returns collection of query result records
    /// 
    public virtual Task> QueryProcAsync(string procedureName, params DataParameter[] parameters)
    {
        using var dataConnection = CreateDataConnection(LinqToDbDataProvider);
        var command = new CommandInfo(dataConnection, procedureName, parameters);

        var rez = command.QueryProc()?.ToList();
        return Task.FromResult>(rez ?? new List());
    }

    /// 
    /// Executes SQL command and returns results as collection of values of specified type
    /// 
    /// Type of result items
    /// SQL command text
    /// Parameters to execute the SQL command
    /// 
    /// A task that represents the asynchronous operation
    /// The task result contains the collection of values of specified type
    /// 
    public virtual Task> QueryAsync(string sql, params DataParameter[] parameters)
    {
        using var dataContext = CreateDataConnection();
        return Task.FromResult>(dataContext.Query(sql, parameters)?.ToList() ?? new List());
    }

    /// 
    /// Truncates database table
    /// 
    /// Performs reset identity column
    /// Entity type
    public virtual async Task TruncateAsync(bool resetIdentity = false) where TEntity : BaseEntity
    {
        using var dataContext = CreateDataConnection(LinqToDbDataProvider);
        await dataContext.GetTable().TruncateAsync(resetIdentity);
    }

    #endregion

    #region Properties

    /// 
    /// Linq2Db data provider
    /// 
    protected abstract IDataProvider LinqToDbDataProvider { get; }

    /// 
    /// Database connection string
    /// 
    protected static string GetCurrentConnectionString()
    {
        return DataSettingsManager.LoadSettings().ConnectionString;
    }

    /// 
    /// Name of database provider
    /// 
    public string ConfigurationName => LinqToDbDataProvider.Name;

    #endregion
}