AipGateway/AipGateway.API.Infrastructure/DataAccess/QueriesRepository.cs

using AipGateway.API.Domain.IRepositories.IGenericRepositories;
using AipGateway.API.Infrastructure.Configurations;
using AipGateway.API.Infrastructure.Persistence;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;
using System.Data;

namespace AipGateway.API.Infrastructure.DataAccess
{
    public class QueriesRepository<T> : IQueriesRepository<T> where T : class
    {
        private readonly ApplicationDbContext _context;
        private readonly ConnectionInfo _connectionInfo;
        public QueriesRepository(ApplicationDbContext context, ConnectionInfo connectionInfo)
        {
            _context = context;
            _connectionInfo = connectionInfo;
            _context.Database.SetCommandTimeout(4800); // Set database request timeout 8 mintutes
        }

        public void Attach(T entity)
        {
            var set = _context.Set<T>();
            set.Attach(entity);
        }
        public IQueryable<T> Query()
        {
            return _context.Set<T>();
        }
        public List<T> BindList(DataTable dt)
        {
            var serializeString = JsonConvert.SerializeObject(dt);
            return JsonConvert.DeserializeObject<List<T>>(serializeString);
        }
        private static TResponse BindObject<TResponse>(DataTable dt) where TResponse : class
        {
            var serializeString = JsonConvert.SerializeObject(dt);
            return JsonConvert.DeserializeObject<List<TResponse>>(serializeString)?.FirstOrDefault();
        }
        
        private static List<TResponse> BindList<TResponse>(DataTable dt) where TResponse : class
        {
            var serializeString = JsonConvert.SerializeObject(dt);
            return serializeString == "[]" ? new List<TResponse>() : JsonConvert.DeserializeObject<List<TResponse>>(serializeString);
        }


        public List<T> ExecuteSqlStoredProcedure(string sqlQuery, params SqlParameter[] parameters)
        {
            var storedProcedureResult = GetDataTableFromQuery(sqlQuery, true, parameters);
            return BindList(storedProcedureResult);
        }
        
        public IQueryable<T> ExecuteSqlQuery(string sqlQuery, params SqlParameter[] sqlParameters)
        {
            if (sqlParameters != null)
            {
                return _context.Set<T>().FromSqlRaw(sqlQuery, sqlParameters).IgnoreQueryFilters().AsQueryable();
            }
            return _context.Set<T>().FromSqlRaw(sqlQuery).AsQueryable();
        }

        public int ExecuteQueryScalar(string dmlQuery, params SqlParameter[] parameters)
        {
            var ID = 0;
            using (var connection = new SqlConnection(_connectionInfo.ConnectionString))
            {
                using (var cmd = new SqlCommand(dmlQuery, connection))
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddRange(parameters);
                    ID = (int)cmd.ExecuteScalar();
                }
            }
            return ID;
        }
        public void ExecuteQueryNonScalar(string dmlQuery, bool IsStoredProcedure = false, params SqlParameter[] parameters)
        {
            using (var connection = new SqlConnection(_connectionInfo.ConnectionString))
            {
                using (var cmd = new SqlCommand(dmlQuery, connection))
                {
                    if (IsStoredProcedure)
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddRange(parameters.ToArray());
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public DataSet GetDataSetFromQuery(string sqlQuery, bool IsStoredProcedure = false, params SqlParameter[] parameters)
        {
            var ds = new DataSet();
            using (var connection = new SqlConnection(_connectionInfo.ConnectionString))
            {
                connection.Open();
                var da = new SqlDataAdapter(sqlQuery, connection);
                da.SelectCommand.Parameters.Clear();
                da.SelectCommand.Parameters.AddRange(parameters);
                da.SelectCommand.CommandTimeout = 4800;
                da.SelectCommand.CommandType = IsStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
                da.Fill(ds);
            }
            return ds;
        }
        public DataTable GetDataTableFromQuery(string sqlQuery, bool IsStoredProcedure = false, params SqlParameter[] parameters)
        {
            var dt = new DataTable();
            using (var connection = new SqlConnection(_connectionInfo.ConnectionString))
            {
                connection.Open();
                var da = new SqlDataAdapter(sqlQuery, connection);
                da.SelectCommand.Parameters.Clear();
                da.SelectCommand.Parameters.AddRange(parameters);
                da.SelectCommand.CommandTimeout = 4800;
                da.SelectCommand.CommandType = IsStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
                da.Fill(dt);
            }
            return dt;
        }
        public SqlParameter CreateSqlParameter(string ParameterName, object value)
        {
            return new SqlParameter()
            {
                ParameterName = ParameterName,
                Value = value
            };
        }
    }
}