HYTOS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.SqlClient;

using System.ComponentModel.DataAnnotations.Schema;

using System.Data.SQLite;

using Dapper;

namespace ID2.Manager.Dapper.Repository

{

    public abstract class BaseRepository : IBaseRepository

    {

        protected readonly IDbConnection _DbConnection;

        protected IDbTransaction _DbTransaction = null;

        protected BaseRepository(string connectionStr) : this(connectionStr, DatabaseType.MSSQL) { }

        protected BaseRepository(string connectionStr, DatabaseType dbType)

        {

            switch (dbType)

            {

                case DatabaseType.SQLITE:

                    this._DbConnection = this.SQLiteDBConnection(connectionStr);

                    break;

                case DatabaseType.MSSQL:

                default:

                    this._DbConnection = this.DBConnection(connectionStr);

                    break;

            }

            SqlMapper.TypeMapProvider = type =>

            {

                // create fallback default type map

                var fallback = new DefaultTypeMap(type);

                return new CustomPropertyTypeMap(type, (t, column) =>

                {

                    var property = t.GetProperties().FirstOrDefault(prop =>

                        prop.GetCustomAttributes(typeof(ColumnAttribute), true)

                            .Cast<ColumnAttribute>()

                            .Any(attr => attr.Name == column));

                    // if no property matched - fall back to default type map

                    if (property == null)

                    {

                        property = fallback.GetMember(column)?.Property;

                    }

                    return property;

                });

            };

        }

        public SqlConnection DBConnection(string connectionStr)

        {

            return new SqlConnection(connectionStr);

        }

        public SQLiteConnection SQLiteDBConnection(string connectionStr)

        {

            return new SQLiteConnection(connectionStr, true);

        }

        public IDbTransaction BeginTransaction()

        {

            if (this._DbConnection.State != ConnectionState.Open)

            {

                this._DbConnection.Open();

            }

            return this._DbConnection.BeginTransaction();

        }

        public void Dispose()

        {

            if (this._DbTransaction != null)

            {

                this._DbTransaction.Commit();

            }

            if (this._DbConnection != null)

            {

                this._DbConnection.Close();

            }

        }

        public IEnumerable<T> Query<T>(string sql)

        {

            if (this._DbConnection.State != ConnectionState.Open)

            {

                this._DbConnection.Open();

            }

            return this._DbConnection.Query<T>(sql);

        }

        public IEnumerable<TReturn> MultiQuery<TFirst, TSecond, TFourth, TReturn>(string sql, Func<TFirst, TSecond, TFourth, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)

        {

            if (this._DbConnection.State != ConnectionState.Open)

            {

                this._DbConnection.Open();

            }

            return this._DbConnection.Query<TFirst, TSecond, TFourth, TReturn >(sql, map: map, param: param, transaction: transaction, buffered: buffered, splitOn: splitOn, commandTimeout: commandTimeout, commandType: commandType);

        }

        public T QueryFirst<T>(string sql)

        {

            if (this._DbConnection.State != ConnectionState.Open)

            {

                this._DbConnection.Open();

            }

            return this._DbConnection.QueryFirst<T>(sql);

        }

        public T QueryFirstOrDefault<T>(string sql, object param, IDbTransaction dbTran = null)

        {

            if (this._DbConnection.State != ConnectionState.Open)

            {

                this._DbConnection.Open();

            }

            return this._DbConnection.QueryFirstOrDefault<T>(sql, param, transaction: dbTran);

        }

        public IEnumerable<T> Query<T>(string sql, object param)

        {

            if (this._DbConnection.State != ConnectionState.Open)

            {

                this._DbConnection.Open();

            }

            return this._DbConnection.Query<T>(sql, param);

        }

        public IEnumerable<T> Query<T>(string sql, object param, CommandType commandType)

        {

            if (this._DbConnection.State != ConnectionState.Open)

            {

                this._DbConnection.Open();

            }

            return this._DbConnection.Query<T>(sql, param, commandType: commandType);

        }

        public async Task<IEnumerable<T>> QueryAsync<T>(string sql)

        {

            if (this._DbConnection.State != ConnectionState.Open)

            {

                this._DbConnection.Open();

            }

            return await this._DbConnection.QueryAsync<T>(sql);

        }

        public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object param)

        {

            if (this._DbConnection.State != ConnectionState.Open)

            {

                this._DbConnection.Open();

            }

            return await this._DbConnection.QueryAsync<T>(sql, param);

        }

        public int Execute(string query, IDbTransaction dbTran)

        {

            return this.Execute(query, null, dbTran);

        }

        public int Execute(string query, object param, IDbTransaction dbTran)

        {

            if (this._DbConnection.State != ConnectionState.Open)

            {

                this._DbConnection.Open();

            }

            return this._DbConnection.Execute(query, param, dbTran);

        }

        public int Execute(string SQL, SqlMapper.IDynamicParameters parameters, IDbTransaction transaction, CommandType commandType)

        {

            if (_DbConnection.State != System.Data.ConnectionState.Open)

            {

                _DbConnection.Open();

            }

            return _DbConnection.Execute(SQL, parameters, transaction, commandType: commandType);

        }

        public async Task<int> ExecuteAsync(string query, object param, IDbTransaction dbTran)

        {

            if (this._DbConnection.State != ConnectionState.Open)

            {

                this._DbConnection.Open();

            }

            return await this._DbConnection.ExecuteAsync(query, param, dbTran);

        }

        public async Task<int> ExecuteAsync(string SQL, SqlMapper.IDynamicParameters parameters, IDbTransaction transaction, CommandType commandType)

        {

            if (_DbConnection.State != System.Data.ConnectionState.Open)

            {

                _DbConnection.Open();

            }

            return await _DbConnection.ExecuteAsync(SQL, parameters, transaction, commandType: commandType);

        }

        public T ExecuteScalar<T>(string query, object param, IDbTransaction dbTran)

        {

            if (_DbConnection.State != ConnectionState.Open)

            {

                _DbConnection.Open();

            }

            return this._DbConnection.ExecuteScalar<T>(query, param, dbTran);

        }

    }

}