git_bsmd/bsmd.database/DBManagerAsync.cs

// Copyright (c) 2020-present schick Informatik
// Description: The new and mighty DB manager that uses async methods and 
// connection pooling.. will try it out in Maersk/PO numbers and expand over the whole
// app later

using log4net;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading;
using System.Threading.Tasks;

namespace bsmd.database
{
    public static class DBManagerAsync
    {
        #region Fields

        private static readonly ILog _log = LogManager.GetLogger(typeof(DBManagerAsync));        

        #endregion

        #region Properties

        public static string ConnectionString { get; set; }

        #endregion

        #region public methods

        public static async Task<int> SaveAsync(DatabaseEntity entity)
        {
            SqlCommand cmd = new SqlCommand();
            entity.PrepareSave(cmd);
            return await PerformNonQueryAsync(cmd);
        }

        #region convenience loading functions

        public static async Task<List<MessageCore>> LoadMaerskCoresByIntervalAsync(Dictionary<MessageCore.SearchFilterType, string> filterDict, bool loadXtraData = false)
        {
            SqlCommand cmd = new SqlCommand();
            MessageCore aMessageCore = new MessageCore();
            aMessageCore.PrepareLoadCommand(cmd, Message.LoadFilter.SEARCH_CORE_FILTERS, filterDict);
            SqlDataReader reader = await PerformCommandAsync(cmd);
            return await aMessageCore.LoadListAsync(reader);
        }

        public static async Task<MessageCore> LoadCoreByVisitIdAsync(string visitId)
        {
            SqlCommand cmd = new SqlCommand();
            MessageCore aMessageCore = new MessageCore();
            aMessageCore.PrepareLoadCommand(cmd, Message.LoadFilter.BY_VISITID, visitId);
            SqlDataReader reader = await PerformCommandAsync(cmd);
            List<MessageCore> resultList = await aMessageCore.LoadListAsync(reader);
            MessageCore result = null;
            if(resultList.Count > 0)
            {
                if(resultList.Count > 1)
                    _log.WarnFormat("more than one core found for VISIT-ID {0}", visitId);
                result = resultList[0];
            }
            return result;
        }

        public static async Task<MaerskData> LoadMaerskDataForCoreAsync(Guid messageCoreId)
        {
            SqlCommand cmd = new SqlCommand();
            MaerskData md = new MaerskData();
            md.PrepareLoadCommand(cmd, Message.LoadFilter.BY_CORE, messageCoreId);
            SqlDataReader reader = await PerformCommandAsync(cmd);
            List<MaerskData> resultList = await md.LoadListAsync(reader);
            MaerskData result = null;
            if(resultList.Count > 0)
            {
                if(resultList.Count > 1)
                    _log.WarnFormat("more than one xtra data found for core id {0}", messageCoreId);
                result = resultList[0];
            }
            return result;
        }


        #endregion

        #endregion

        #region async DB access methods

        internal static async Task<SqlDataReader> PerformCommandAsync(SqlCommand cmd)
        {
            SqlDataReader reader = null;
            
            try
            {
                SqlConnection connection = new SqlConnection(ConnectionString);
                await connection.OpenAsync();
                cmd.Connection = connection;
                reader = await cmd.ExecuteReaderAsync();
            }
            catch (SqlException ex)
            {
                Trace.WriteLine("SQL Exception:" + ex.Message);
                _log.Error("Error performing command", ex);
                _log.DebugFormat("Query: {0}", cmd.CommandText);
                _log.Debug("Parameters:");
                for (int i = 0; i < cmd.Parameters.Count; i++)
                {
                    _log.DebugFormat("{0}:{1}", cmd.Parameters[i].ParameterName, cmd.Parameters[i].Value);
                }
            }

            return reader;
        }

        internal static async Task<int> PerformNonQueryAsync(SqlCommand cmd)
        {
            int result = -1;
            // await _asyncSemaphore.WaitAsync();
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    await connection.OpenAsync();
                    cmd.Connection = connection;
                    result = await cmd.ExecuteNonQueryAsync();
                }
            }
            catch (SqlException ex)
            {
                Trace.WriteLine("SQL Exception:" + ex.Message);
                _log.Error("Error performing command", ex);
                _log.DebugFormat("Query: {0}", cmd.CommandText);
                _log.Debug("Parameters:");
                for (int i = 0; i < cmd.Parameters.Count; i++)
                {
                    _log.DebugFormat("{0}:{1}", cmd.Parameters[i].ParameterName, cmd.Parameters[i].Value);
                }
            }

            return result;
        }

        internal static async Task<int?> PerformReadIntQueryAsync(SqlCommand cmd)
        {
            int? result = null;

            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    cmd.Connection = connection;
                    object r = await cmd.ExecuteScalarAsync();
                    if (r == null) { result = null; }
                    else if (r == DBNull.Value) { result = null; }
                    else { result = (int)r; }
                }
            }
            catch (SqlException ex)
            {
                _log.Error("Error performing command", ex);
                _log.DebugFormat("Query: {0}", cmd.CommandText);
                _log.Debug("Parameters:");
                for (int i = 0; i < cmd.Parameters.Count; i++)
                {
                    _log.DebugFormat("{0}:{1}", cmd.Parameters[i].ParameterName, cmd.Parameters[i].Value);
                }
            }

            return result;
        }

        #endregion

    }
}