EasyDevCoreV4/EasyDevCore.Database.EntityFrameworkCore/DbDataReaderExtensions.cs

using EasyDevCore.Database;
using EasyDevCore.Database.EntityTable;
using EasyDevCore.Common;
using System.Data;
using System.Data.Common;
using System.Dynamic;
using System.Text;

namespace EasyDevCore.Database
{
    /// <summary>
    /// 
    /// </summary>
    public static class DbDataReaderExtensions
    {
        /// <summary>
        /// Gets the value.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader">The reader.</param>
        /// <param name="fieldName">Name of the field.</param>
        /// <param name="valueIfNull">The value if null.</param>
        /// <returns></returns>
        public static T GetValue<T>(this DbDataReader reader, string fieldName, T valueIfNull)
        {
            int colIndex = reader.GetOrdinal(fieldName);
            object value = reader.GetValue(colIndex);
            if (value == null) return valueIfNull;
            Type columnType = reader.GetFieldType(colIndex);
            if (typeof(T) == columnType)
            {
                return (T)value;
            }
            else
            {
                return (T)Convert.ChangeType(value, columnType);
            }
        }

        /// <summary>
        /// Gets the row.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <returns></returns>
        public static dynamic GetRow(this DbDataReader reader)
        {
            IDictionary<string, object> expandoObject = new ExpandoObject() as IDictionary<string, object>;
            var fieldCount = reader.FieldCount;
            for (var i = 0; i < fieldCount; i++)
                expandoObject.Add(reader.GetName(i), reader.IsDBNull(i) ? null : reader[i]);

            return expandoObject;
        }

        /// <summary>
        /// Gets the row asynchronous.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <returns></returns>
        public static async Task<dynamic> GetRowAsync(this DbDataReader reader, CancellationToken cancellationToken)
        {
            var expandoObject = new ExpandoObject() as IDictionary<string, object>;

            for (var i = 0; i < reader.FieldCount; i++)
            {
                object value = await reader.IsDBNullAsync(i, cancellationToken).ConfigureAwait(false) ? null : reader.GetValue(i);
                expandoObject.Add(reader.GetName(i), value);
            }
            return expandoObject;
        }

        /// <summary>
        /// Fetches to json.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <param name="indentFormat">if set to <c>true</c> [indent format].</param>
        /// <returns></returns>
        public static string FetchToJson(this DbDataReader reader, bool indentFormat = false)
        {
            if (reader.IsClosed) return null;
            var JSONString = new StringBuilder();
            if (indentFormat)
            {
                JSONString.AppendLine("[");
                while (reader.Read())
                {
                    JSONString.AppendLine("{");
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string value = "\"" + reader.GetName(i) + "\":"
                            + (reader.IsDBNull(i) ? "null" :
                                        "\"" + System.Web.HttpUtility.JavaScriptStringEncode(reader[i].StringFrom()) + "\""
                                );
                        if (i < reader.FieldCount - 1)
                        {
                            value += ",";
                        }
                        JSONString.AppendLine(value);
                    }
                    JSONString.AppendLine("}");
                }
                JSONString.AppendLine("]");
            }
            else
            {
                JSONString.Append("[");
                while (reader.Read())
                {
                    JSONString.Append("{");
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string value = "\"" + reader.GetName(i) + "\":"
                             + (reader.IsDBNull(i) ? "null" :
                                         "\"" + System.Web.HttpUtility.JavaScriptStringEncode(reader[i].StringFrom()) + "\""
                                 );
                        if (i < reader.FieldCount - 1)
                        {
                            value += ",";
                        }
                        JSONString.Append(value);
                    }
                    JSONString.Append("}");
                }
                JSONString.Append("]");
            }
            return JSONString.ToString();
        }

        /// <summary>
        /// Fetches to json asynchronous.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <param name="indentFormat">if set to <c>true</c> [indent format].</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <returns></returns>
        public static async Task<string> FetchToJsonAsync(this DbDataReader reader, bool indentFormat = false, CancellationToken cancellationToken = default)
        {
            if (reader.IsClosed) return null;

            var JSONString = new StringBuilder();
            if (indentFormat)
            {
                JSONString.AppendLine("[");
                while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
                {
                    JSONString.AppendLine("{");
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string value = "\"" + reader.GetName(i) + "\":"
                            + (await reader.IsDBNullAsync(i, cancellationToken).ConfigureAwait(false) ? "null" : "\"" +
                                System.Web.HttpUtility.JavaScriptStringEncode(reader[i].StringFrom()) + "\"");
                        if (i < reader.FieldCount - 1)
                        {
                            value += ",";
                        }
                        JSONString.AppendLine(value);
                    }
                    JSONString.AppendLine("}");
                }
                JSONString.AppendLine("]");
            }
            else
            {
                JSONString.Append("[");
                while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
                {
                    JSONString.Append("{");
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string value = "\"" + reader.GetName(i) + "\":"
                            + (await reader.IsDBNullAsync(i, cancellationToken).ConfigureAwait(false) ? "null" : "\"" +
                                System.Web.HttpUtility.JavaScriptStringEncode(reader[i].StringFrom()) + "\"");
                        if (i < reader.FieldCount - 1)
                        {
                            value += ",";
                        }
                        JSONString.Append(value);
                    }
                    JSONString.Append("}");
                }
                JSONString.Append("]");
            }
            return JSONString.ToString();
        }

        /// <summary>
        /// Fetches to list.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <returns></returns>
        public static IList<dynamic> FetchToDynamic(this DbDataReader reader)
        {
            if (reader.IsClosed) return null;
            List<dynamic> list = new();
            while (reader.Read())
            {
                list.Add(reader.GetRow());
            }
            return list;
        }

        /// <summary>
        /// Fetches to dynamic asynchronous.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <returns></returns>
        public static async Task<IList<dynamic>> FetchToDynamicAsync(this DbDataReader reader, CancellationToken cancellationToken = default)
        {
            if (reader.IsClosed) return null;
            List<dynamic> list = new();
            DataRecordDynamic wrapper = new(reader);
            while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
            {
                list.Add(await reader.GetRowAsync(cancellationToken).ConfigureAwait(false));
            }
            bool hasNextResult = await reader.NextResultAsync(cancellationToken).ConfigureAwait(false);
            if (!hasNextResult) await reader.CloseAsync().ConfigureAwait(false);
            return list;
        }

        /// <summary>
        /// Fetches the fill list internal.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader">The reader.</param>
        /// <param name="list">The list.</param>
        /// <returns></returns>
        private static IList<T> FetchFillListInternal<T>(this DbDataReader reader, IList<T> list)
            where T : class, new()
        {
            if (reader.IsClosed) return null;
            var mapper = new DbDataReaderMapper<T>(reader);
            mapper.Map(row => list.Add(row));
            bool hasNextResult = reader.NextResult();
            if (!hasNextResult) reader.Close();
            return list;
        }

        /// <summary>
        /// Fetches the fill list internal asynchronous.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader">The reader.</param>
        /// <param name="list">The list.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <returns></returns>
        private async static Task<IList<T>> FetchFillListInternalAsync<T>(this DbDataReader reader, IList<T> list, CancellationToken cancellationToken = default)
            where T : class, new()
        {
            if (reader.IsClosed) return null;
            var mapper = new DbDataReaderMapper<T>(reader);
            await mapper.MapAsync(row => list.Add(row), cancellationToken).ConfigureAwait(false);
            bool hasNextResult = await reader.NextResultAsync(cancellationToken).ConfigureAwait(false);
            if (!hasNextResult) await reader.CloseAsync().ConfigureAwait(false);
            return list;
        }


        /// <summary>
        /// Fetches the fill list.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader">The reader.</param>
        /// <param name="list">The list.</param>
        /// <returns></returns>
        public static int FetchFillList<T>(this DbDataReader reader, IList<T> list)
            where T : class, new()
        {
            int rowsCount = list.Count;
            return reader.FetchFillListInternal(list).Count - rowsCount;
        }

        /// <summary>
        /// Fetches the fill list asynchronous.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader">The reader.</param>
        /// <param name="list">The list.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <returns></returns>
        public static async Task<int> FetchFillListAsync<T>(this DbDataReader reader, IList<T> list, CancellationToken cancellationToken = default)
           where T : class, new()
        {
            int rowsCount = list.Count;
            return (await reader.FetchFillListInternalAsync(list, cancellationToken).ConfigureAwait(false)).Count - rowsCount;
        }

        /// <summary>
        /// Fetches to list.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="reader">The reader.</param>
        /// <param name="selector">The selector.</param>
        /// <returns></returns>
        public static IList<TResult> FetchToList<TResult>(this DbDataReader reader, Func<dynamic, TResult> selector)
        {
            if (reader.IsClosed) return null;
            List<TResult> list = new();
            DataRecordDynamic wrapper = new(reader);
            while (reader.Read())
            {
                var item = selector(wrapper);
                list.Add(item);
            }
            bool hasNextResult = reader.NextResult();
            if (!hasNextResult) reader.Close();
            return list;
        }

        /// <summary>
        /// Fetches to list asynchronous.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="reader">The reader.</param>
        /// <param name="selector">The selector.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <returns></returns>
        public static async Task<IList<TResult>> FetchToListAsync<TResult>(this DbDataReader reader, Func<dynamic, TResult> selector, CancellationToken cancellationToken = default)
        {
            if (reader.IsClosed) return null;
            List<TResult> list = new();
            DataRecordDynamic wrapper = new(reader);
            while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
            {
                var item = selector(wrapper);
                list.Add(item);
            }
            bool hasNextResult = await reader.NextResultAsync(cancellationToken).ConfigureAwait(false);
            if (!hasNextResult) await reader.CloseAsync().ConfigureAwait(false);
            return list;
        }

        /// <summary>
        /// Fetches to list.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader">The reader.</param>
        /// <param name="list">The list.</param>
        /// <returns></returns>
        public static IList<T> FetchToList<T>(this DbDataReader reader, IList<T> list = null)
            where T : class, new()
        {
            if (reader.IsClosed) return list;
            IList<T> listNew = list ?? new List<T>();
            listNew.Clear();
            return reader.FetchFillListInternal(listNew);
        }

        /// <summary>
        /// Fetches to list asynchronous.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader">The reader.</param>
        /// <param name="list">The list.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <returns></returns>
        public static async Task<IList<T>> FetchToListAsync<T>(this DbDataReader reader, IList<T> list = null, CancellationToken cancellationToken = default)
            where T : class, new()
        {
            if (reader.IsClosed) return list;
            IList<T> listNew = list ?? new List<T>();
            listNew.Clear();
            return await reader.FetchFillListInternalAsync(listNew, cancellationToken).ConfigureAwait(false);
        }

        /// <summary>
        /// Fetches the fill data set.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <param name="dataSet">The data set.</param>
        public static int FetchFillDataSet(this DbDataReader reader, DataSet dataSet)
        {
            if (dataSet.Tables.Count == 0) return 0;
            var totalRows = dataSet.Tables.Cast<DataTable>().Sum(tb => tb.Rows.Count);
            int i = 0;
            do
            {
                dataSet.Tables[i].Load(reader);
                i++;
            } while (dataSet.Tables.Count > i && !reader.IsClosed);
            return dataSet.Tables.Cast<DataTable>().Sum(tb => tb.Rows.Count) - totalRows;
        }

        /// <summary>
        /// Fetches the fill data set asynchronous.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <param name="dataSet">The data set.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <returns></returns>
        public static async Task<int> FetchFillDataSetAsync(this DbDataReader reader, DataSet dataSet, CancellationToken cancellationToken = default)
        {
            if (dataSet.Tables.Count == 0) return 0;
            var totalRows = dataSet.Tables.Cast<DataTable>().Sum(tb => tb.Rows.Count);
            int i = 0;
            do
            {
                await dataSet.Tables[i].LoadAsync(reader, cancellationToken).ConfigureAwait(false);
            } while (dataSet.Tables.Count > i && !reader.IsClosed);
            return dataSet.Tables.Cast<DataTable>().Sum(tb => tb.Rows.Count) - totalRows;
        }

        // <summary>
        /// <summary>
        /// Fetches to data set.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <param name="dataSet">The data set.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static DataSet FetchToDataSet(this DbDataReader reader, DataSet dataSet = null, EasyDbParameterCollection parameters = null)
        {
            if (dataSet != null) dataSet.Clear();
            DataSet newDataSet = dataSet ?? new DataSet();
            do
            {
                newDataSet.Tables.Add().Load(reader, LoadOption.Upsert);
                if (parameters != null)
                {
                    var result = ParameterUtils.GatherOutputParameters(0, parameters);
                    foreach (var item in result)
                    {
                        newDataSet.ExtendedProperties[item.Key] = item.Value;
                    }
                }
            } while (!reader.IsClosed);
            return newDataSet;
        }

        /// <summary>
        /// Fetches to data set asynchronous.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <param name="DataSet">The entity set.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <returns></returns>
        public static async Task<DataSet> FetchToDataSetAsync(this DbDataReader reader, DataSet DataSet = null, CancellationToken cancellationToken = default)
        {
            if (DataSet != null) DataSet.Clear();
            DataSet newDataSet = DataSet ?? new DataSet();
            do
            {
                await newDataSet.Tables.Add().LoadAsync(reader, cancellationToken).ConfigureAwait(false);
            } while (!reader.IsClosed);
            return newDataSet;
        }

        /// <summary>
        /// Fetches to table.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <param name="table">The table.</param>
        /// <returns></returns>
        public static int FetchFillTable(this DbDataReader reader, DataTable table)
        {
            int totalRows = table.Rows.Count;
            table.Load(reader);
            return table.Rows.Count - totalRows;
        }

        /// <summary>
        /// Fetches to table.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <param name="table">The table.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <returns></returns>
        public static async Task<int> FetchFillTableAsync(this DbDataReader reader, DataTable table, CancellationToken cancellationToken = default)
        {
            if (reader.IsClosed) return 0;
            int totalRows = table.Rows.Count;
            await table.LoadAsync(reader);
            return table.Rows.Count - totalRows;
        }

        /// <summary>
        /// Fetches to table.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <param name="table">The table.</param>
        /// <returns></returns>
        public static DataTable FetchToTable(this DbDataReader reader, DataTable table = null)
        {
            if (table != null) table.Clear();
            DataTable newTable = table ?? new DataTable();
            newTable.Load(reader);
            return newTable;
        }

        /// <summary>
        /// Fetches to table asynchronous.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <param name="table">The table.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <returns></returns>
        public static async Task<DataTable> FetchToTableAsync(this DbDataReader reader, DataTable table = null, CancellationToken cancellationToken = default)
        {
            if (table != null) table.Clear();
            DataTable newTable = table ?? new DataTable();
            await newTable.LoadAsync(reader, cancellationToken).ConfigureAwait(false);
            return newTable;
        }
    }
}