//using System;
//using System.Collections.Generic;
//using System.Data;
//using System.Linq;
////using System.Linq.Dynamic.Core;
//using System.Text;
//using System.Threading.Tasks;
//using Dapper;
//using System.Linq;
//using Microsoft.Data.SqlClient;
//using System.Data.Common;
//using FrameworkDAL.CustomCode.Authentication;
//using FrameworkDAL.CustomCode.Connection;
//using GB5Shared.DTO.Framework.Login;
//namespace FrameworkDAL.CustomCode.QueryExecutor
//{
//    public class QueryExecutor : IQueryExecutor
//    {
//        private string _connectionstring;
//        //private readonly string _ConnectionName;
//        private readonly IApplicationConnection _Appconnection;
        

//        // Constructor with Dependency Injection
//        public QueryExecutor(IApplicationConnection Appconnection)
//        {
//            _Appconnection = Appconnection;
//        }
         
//        public async Task<T> QuerySingleAsync<T>(LoginDTO LoginDTO, string sql, object parameters = null)
//        {
//            _connectionstring = await _Appconnection.DBConnectionStringCached(LoginDTO);
//            await using (SqlConnection connection = new SqlConnection(_connectionstring))
//            {
//                int commandTimeout = 1200;
//                return await connection.QuerySingleOrDefaultAsync<T>(sql, parameters, commandTimeout: commandTimeout);
//            }
//        }
         
//        public async Task<IEnumerable<T>> QueryAsync<T>(LoginDTO LoginDTO,string sql, object parameters = null)
//        {
//            _connectionstring = await _Appconnection.DBConnectionStringCached(LoginDTO);
//            await using (SqlConnection connection = new SqlConnection(_connectionstring))
//            {
//                int commandTimeout = 1200;
//                return await connection.QueryAsync<T>(sql, parameters, commandTimeout: commandTimeout);
//            }
//        }

//        public async Task<IEnumerable<T>> QueryAsync<T>(string ConnectionName , string sql, object parameters = null)
//        {
//            _connectionstring = await _Appconnection.DBConnectionStringCached(ConnectionName);
//            await using (SqlConnection connection = new SqlConnection(_connectionstring))
//            {
//                int commandTimeout = 1200;
//                return await connection.QueryAsync<T>(sql, parameters, commandTimeout: commandTimeout);
//            }
//        }

//        public async IAsyncEnumerable<T> ExecuteQueryAsync<T>(LoginDTO LoginDTO, string sql, object parameters = null)
//        {
//            _connectionstring = await _Appconnection.DBConnectionStringCached(LoginDTO);
//            await using var connection = new SqlConnection(_connectionstring);
//            await connection.OpenAsync();
//            int commandTimeout = 1200;
//            var result = await connection.QueryAsync<T>(sql, parameters, commandTimeout: commandTimeout); // Await here

//            foreach (var item in result) // Iterate normally
//            {
//                yield return item;
//            }
//        }


//        public async Task<int> ExecuteAsync(LoginDTO LoginDTO, string sql, object parameters = null)
//        {
//            _connectionstring = await _Appconnection.DBConnectionStringCached(LoginDTO);
//            await using (SqlConnection connection = new SqlConnection(_connectionstring))
//            {
//                int commandTimeout = 1200;
//                return await connection.ExecuteAsync(sql, parameters, commandTimeout: commandTimeout);
//            }
//        }

//        public async IAsyncEnumerable<T> StreamAsync<T>(LoginDTO LoginDTO, string sql, object parameters = null)
//        {
//            _connectionstring = await _Appconnection.DBConnectionStringCached(LoginDTO);
//            await using (SqlConnection connection = new SqlConnection(_connectionstring))
//            {
//                var items = await connection.QueryAsync<T>(sql, parameters).ConfigureAwait(false);
//                foreach (var item in items)
//                {
//                    yield return item;
//                }
//            }
//        }

//        public async Task<PagedResult<T>> QueryPagedAsync<T>(LoginDTO LoginDTO ,string sql, object parameters = null)
//        {
//            _connectionstring = await _Appconnection.DBConnectionStringCached(LoginDTO);
//            await using (SqlConnection connection = new SqlConnection(_connectionstring))
//            {
//                var items = await connection.QueryAsync<T>(sql, parameters);
//                var countSql = "SELECT COUNT(*) FROM (" + sql + ") AS Total";
//                var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);

//                return new PagedResult<T>
//                {
//                    Items = items.ToList(),
//                    TotalCount = totalCount
//                };
//            }
//        }

//        public async Task<int> BulkInsertAsync<T>(LoginDTO LoginDTO,string sql, IEnumerable<T> items)
//        {
//            _connectionstring = await _Appconnection.DBConnectionStringCached(LoginDTO);
//            await using (SqlConnection connection = new SqlConnection(_connectionstring))
//            {
//                return await connection.ExecuteAsync(sql, items);
//            }
//        }

//        public async Task<IEnumerable<TReturn>> QueryAsync<TFirst, TSecond, TReturn>(
//    LoginDTO loginDTO,
//    string sql,
//    Func<TFirst, TSecond, TReturn> map,
//    object parameters = null,
//    string splitOn = "Id")
//        {
//            _connectionstring = await _Appconnection.DBConnectionStringCached(loginDTO);
//            await using var connection = new SqlConnection(_connectionstring);
//            return await connection.QueryAsync(sql, map, parameters, splitOn: splitOn);
//        }

//        public async Task<IEnumerable<TReturn>> QueryAsyncnew<TFirst, TSecond, TReturn>(
//            LoginDTO loginDTO,
//            string sql,
//            Func<TFirst, TSecond, TReturn> map,
//            object parameters = null,
//            string splitOn = "Id")
//        {
//            _connectionstring = await _Appconnection.DBConnectionStringCached(loginDTO);
//            await using var connection = new SqlConnection(_connectionstring);

//            return await connection.QueryAsync<TFirst, TSecond, TReturn>(
//                sql,
//                map,
//                param: parameters,
//                splitOn: splitOn
//            );
//        }





//    }

//    public class PagedResult<T>
//    {
//        public IEnumerable<T> Items { get; set; }
//        public int TotalCount { get; set; }
//    }


//}