using System;

using System.Data;

using System.Diagnostics;

using System.Collections.Generic;

using System.Linq;

using System.Text;

​

public class Program

{

    // Configuration for the benchmark

    private const int IterationsPerTrial = 20;   // Number of iterations per trial

    private const int WarmupIterations = 5;     // Number of warmup iterations

    private const int TrialCount = 5;            // Number of trials

    private const int RowCount = 1000;          // Number of rows in test data

    private const int ColumnCount = 10;          // Number of columns in test data

    public static void Main()

    {

        Console.WriteLine($"Benchmark: DataTable Loading Performance");

        Console.WriteLine($"Data size: {RowCount:N0} rows, {ColumnCount} columns (including duplicates)");

        Console.WriteLine($"Configuration: {TrialCount} trials with {IterationsPerTrial} iterations each");

        Console.WriteLine($"Warmup iterations: {WarmupIterations}\n");

        // Run warmup

        Console.WriteLine("Running warmup...");

        RunWarmup();

        // Run benchmarks

        var results = new List<BenchmarkResult>();

        results.Add(BenchmarkMethod("table.Load", () => {

            using (var reader = new MockDataReader(RowCount, ColumnCount, false))

            {

                try

                {

                    DataTable table = new DataTable { TableName = "TestTable" };

                    table.Load(reader);

                    return table;

                }

                catch (Exception ex)

                {

                    Console.WriteLine($"  Error: {ex.Message}");

                    return new DataTable();

                }

            }

        }));

        results.Add(BenchmarkMethod("Manual Loop", () => {

            using (var reader = new MockDataReader(RowCount, ColumnCount, false))

            {

                DataTable table = new DataTable { TableName = "TestTable" };

                // Pre-allocate for field values

                int fieldCount = reader.FieldCount;

                object[] values = new object[fieldCount];

​

                // Add columns efficiently - but will fail on duplicates

                try

                {

                    for (int i = 0; i < fieldCount; i++)

                    {

                        table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));

                    }

                }

                catch (Exception ex)

                {

                    Console.WriteLine($"  Error: {ex.Message}");

                    return new DataTable();

                }

​

                // Process rows

                while (reader.Read())

                {

                    // Get all values at once instead of individually

                    reader.GetValues(values);

​

                    // Add row directly

                    table.Rows.Add(values);

                }

                return table;

            }

        }));

        results.Add(BenchmarkMethod("BeginLoadData", () => {

            using (var reader = new MockDataReader(RowCount, ColumnCount, false))

            {

                try

                {

                    DataTable table = new DataTable { TableName = "TestTable" };

                    // Pre-allocate for field values

                    int fieldCount = reader.FieldCount;

                    object[] values = new object[fieldCount];

​

                    // Add columns efficiently - but will fail on duplicates

                    for (int i = 0; i < fieldCount; i++)

                    {

                        table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));

                    }

​

                    // Use BeginLoadData for better performance

                    table.BeginLoadData();

                    // Process rows

                    while (reader.Read())

                    {

                        // Get all values at once

                        reader.GetValues(values);

                        // Add row directly

                        table.Rows.Add(values);

                    }

                    table.EndLoadData();

                    return table;

                }

                catch (Exception ex)

                {

                    Console.WriteLine($"  Error: {ex.Message}");

                    return new DataTable();

                }

            }

        }));

        results.Add(BenchmarkMethod("HandleDuplicates", () => {

            using (var reader = new MockDataReader(RowCount, ColumnCount, false))

            {

                DataTable table = new DataTable { TableName = "TestTable" };

                // Pre-allocate for field values

                int fieldCount = reader.FieldCount;

                object[] values = new object[fieldCount];

​

                // Add columns with duplicate handling

                var columnNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);

                for (int i = 0; i < fieldCount; i++)

                {

                    string originalName = reader.GetName(i);

                    string uniqueName = originalName;

                    int suffix = 1;

                    // If the column name already exists, add a suffix to make it unique

                    while (columnNames.Contains(uniqueName))

                    {

                        uniqueName = $"{originalName}_{suffix}";

                        suffix++;

                    }

                    columnNames.Add(uniqueName);

                    table.Columns.Add(uniqueName, reader.GetFieldType(i));

                }

                // Process rows

                while (reader.Read())

                {

                    // Get all values at once

                    reader.GetValues(values);

                    // Add row directly

                    table.Rows.Add(values);

                }

                return table;

            }

        }));

        results.Add(BenchmarkMethod("OptimizedDuplicates", () => {

            using (var reader = new MockDataReader(RowCount, ColumnCount, false))

            {

                DataTable table = new DataTable { TableName = "TestTable" };

                // Pre-allocate for field values

                int fieldCount = reader.FieldCount;

                object[] values = new object[fieldCount];

​

                // Use BeginLoadData for better performance

                table.BeginLoadData();

                // Add columns with duplicate handling

                var columnNames = new HashSet<string>(fieldCount, StringComparer.OrdinalIgnoreCase);

                for (int i = 0; i < fieldCount; i++)

                {

                    string originalName = reader.GetName(i);

                    string uniqueName = originalName;

                    int suffix = 1;

                    // If the column name already exists, add a suffix to make it unique

                    while (columnNames.Contains(uniqueName))

                    {

                        uniqueName = $"{originalName}_{suffix}";

                        suffix++;

                    }

                    columnNames.Add(uniqueName);

                    table.Columns.Add(uniqueName, reader.GetFieldType(i));

                }

                // Process rows

                while (reader.Read())

                {

                    // Get all values at once

                    reader.GetValues(values);

                    // Add row directly

                    table.Rows.Add(values);

                }

                table.EndLoadData();

                return table;

            }

        }));

        results.Add(BenchmarkMethod("BufferPoolDuplicates", () => {

            using (var reader = new MockDataReader(RowCount, ColumnCount, false))

            {

                DataTable table = new DataTable { TableName = "TestTable" };

                // Pre-allocate for field values

                int fieldCount = reader.FieldCount;

                // Add columns with duplicate handling

                var columnNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);

                for (int i = 0; i < fieldCount; i++)

                {

                    string originalName = reader.GetName(i);

                    string uniqueName = originalName;

                    int suffix = 1;

                    // If the column name already exists, add a suffix to make it unique

                    while (columnNames.Contains(uniqueName))

                    {

                        uniqueName = $"{originalName}_{suffix}";

                        suffix++;

                    }

                    columnNames.Add(uniqueName);

                    table.Columns.Add(uniqueName, reader.GetFieldType(i));

                }

                // Use BeginLoadData for better performance

                table.BeginLoadData();

                // Use a buffer pool to avoid array allocations

                var bufferPool = new List<object[]>();

                for (int i = 0; i < 10; i++) // Create a small pool of buffers

                {

                    bufferPool.Add(new object[fieldCount]);

                }

                int bufferIndex = 0;

                // Process rows

                while (reader.Read())

                {

                    // Get the next buffer from the pool

                    object[] currentBuffer = bufferPool[bufferIndex];

                    bufferIndex = (bufferIndex + 1) % bufferPool.Count;

                    // Get all values at once

                    reader.GetValues(currentBuffer);

                    // Add row directly

                    table.Rows.Add(currentBuffer);

                }

                table.EndLoadData();

                return table;

            }

        }));

        // Print summary

        PrintSummary(results);

    }

    private static void RunWarmup()

    {

        for (int i = 0; i < WarmupIterations; i++)

        {

            using (var reader = new MockDataReader(500, 5, false))

            {

                // Warmup method 1

                DataTable table1 = new DataTable { TableName = "WarmupTable1" };

                table1.Load(reader);

            }

            using (var reader = new MockDataReader(500, 5, false))

            {

                // Warmup method 2

                DataTable table2 = new DataTable { TableName = "WarmupTable2" };

                int fieldCount = reader.FieldCount;

                object[] values = new object[fieldCount];

​

                for (int j = 0; j < fieldCount; j++)

                {

                    table2.Columns.Add(reader.GetName(j), reader.GetFieldType(j));

                }

​

                while (reader.Read())

                {

                    reader.GetValues(values);

                    table2.Rows.Add(values);

                }

            }

            // Warmup method 3 with duplicate handling

            using (var reader = new MockDataReader(500, 5, true))

            {

                DataTable table3 = new DataTable { TableName = "WarmupTable3" };

                int fieldCount = reader.FieldCount;

                object[] values = new object[fieldCount];

​

                // Add columns with duplicate handling

                var columnNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);

                for (int j = 0; j < fieldCount; j++)

                {

                    string originalName = reader.GetName(j);

                    string uniqueName = originalName;

                    int suffix = 1;

                    // If the column name already exists, add a suffix to make it unique

                    while (columnNames.Contains(uniqueName))

                    {

                        uniqueName = $"{originalName}_{suffix}";

                        suffix++;

                    }

                    columnNames.Add(uniqueName);

                    table3.Columns.Add(uniqueName, reader.GetFieldType(j));

                }

​

                while (reader.Read())

                {

                    reader.GetValues(values);

                    table3.Rows.Add(values);

                }

            }

        }

        // Force GC after warmup

        GC.Collect();

        GC.WaitForPendingFinalizers();

        GC.Collect();

    }

    private static BenchmarkResult BenchmarkMethod(string name, Func<DataTable> method)

    {

        Console.WriteLine($"\nBenchmarking: {name}");

        double[] times = new double[TrialCount];

        long[] memoryUsed = new long[TrialCount];

        int[] rowCounts = new int[TrialCount];

        for (int trial = 0; trial < TrialCount; trial++)

        {

            // Force GC before test

            GC.Collect();

            GC.WaitForPendingFinalizers();

            GC.Collect();

            // Measure memory before test

            long memoryBefore = GC.GetTotalMemory(true);

            // Start timing

            var sw = Stopwatch.StartNew();

            DataTable result = null;

            for (int i = 0; i < IterationsPerTrial; i++)

            {

                result = method();

                if (i < IterationsPerTrial - 1)

                {

                    result.Dispose();

                }

            }

            // Stop timing

            sw.Stop();

            // Measure memory after test

            long memoryAfter = GC.GetTotalMemory(false);

            // Record results

            times[trial] = sw.ElapsedMilliseconds / (double)IterationsPerTrial;

            memoryUsed[trial] = (memoryAfter - memoryBefore) / IterationsPerTrial;

            rowCounts[trial] = result?.Rows.Count ?? 0;

            Console.WriteLine($"  Trial {trial + 1}: {times[trial]:F2}ms per iteration, Memory: {memoryUsed[trial]:N0} bytes, Rows: {rowCounts[trial]:N0}");

            result?.Dispose();

        }

        return new BenchmarkResult

        {

            Name = name,

            AverageTime = times.Average(),

            MinTime = times.Min(),

            MaxTime = times.Max(),

            AverageMemory = memoryUsed.Average(),

            RowCount = rowCounts.Average()

        };

    }

    private static void PrintSummary(List<BenchmarkResult> results)

    {

        Console.WriteLine("\n=== BENCHMARK SUMMARY ===");

        // Filter out failed tests with zero rows

        results = results.Where(r => r.RowCount > 0).ToList();

        if (results.Count == 0)

        {

            Console.WriteLine("\nAll tests failed. No results to display.");

            return;

        }

        // Sort by average time (fastest first)

        var sortedByTime = results.OrderBy(r => r.AverageTime).ToList();

        var fastestTime = sortedByTime.First();

        // Sort by memory usage (lowest first)

        var sortedByMemory = results.OrderBy(r => r.AverageMemory).ToList();

        var lowestMemory = sortedByMemory.First();

        Console.WriteLine("\nResults sorted by execution time (fastest first):");

        Console.WriteLine("┌─────────────────────┬────────────┬──────────────┬─────────────┬───────────┐");

        Console.WriteLine("│ Method              │ Avg Time   │ Memory Usage │ Vs Fastest  │ Memory    │");

        Console.WriteLine("├─────────────────────┼────────────┼──────────────┼─────────────┼───────────┤");

        foreach (var result in sortedByTime)

        {

            string timeCompare = result == fastestTime ? "[FASTEST]" : $"+{(result.AverageTime / fastestTime.AverageTime - 1) * 100:F1}%";

            string memoryIndicator = result == lowestMemory ? "[LOWEST]" : "";

            Console.WriteLine($"│ {result.Name,-19} │ {result.AverageTime,8:F2}ms │ {result.AverageMemory,12:N0} │ {timeCompare,-11} │ {memoryIndicator,-9} │");

        }

        Console.WriteLine("└─────────────────────┴────────────┴──────────────┴─────────────┴───────────┘");

        Console.WriteLine($"\nFastest method: {fastestTime.Name} ({fastestTime.AverageTime:F2}ms)");

        Console.WriteLine($"Lowest memory usage: {lowestMemory.Name} ({lowestMemory.AverageMemory:N0} bytes)");

        // Compare to table.Load

        var tableLoad = results.FirstOrDefault(r => r.Name == "table.Load");

        var manualLoop = results.FirstOrDefault(r => r.Name == "Manual Loop");

        if (tableLoad != null && manualLoop != null)

        {

            var difference = (tableLoad.AverageTime - manualLoop.AverageTime) / tableLoad.AverageTime * 100;

            Console.WriteLine($"\nManual Loop is {difference:F1}% faster than table.Load as claimed in the code comment.");

        }

        // Compare handling duplicates vs standard approaches

        var handleDuplicates = results.FirstOrDefault(r => r.Name == "HandleDuplicates");

        if (handleDuplicates != null && manualLoop != null)

        {

            var difference = (handleDuplicates.AverageTime - manualLoop.AverageTime) / manualLoop.AverageTime * 100;

            if (difference > 0)

                Console.WriteLine($"\nDuplicate column handling adds {difference:F1}% overhead compared to the Manual Loop.");

            else

                Console.WriteLine($"\nDuplicate column handling is {-difference:F1}% faster than the Manual Loop despite the additional checks.");

        }

        // Show fastest duplicate-handling approach

        var duplicateHandlers = results.Where(r => 

            r.Name == "HandleDuplicates" || 

            r.Name == "OptimizedDuplicates" || 

            r.Name == "BufferPoolDuplicates").ToList();

        if (duplicateHandlers.Count > 0)

        {

            var fastestDuplicateHandler = duplicateHandlers.OrderBy(r => r.AverageTime).First();

            Console.WriteLine($"\nFastest duplicate-handling method: {fastestDuplicateHandler.Name} ({fastestDuplicateHandler.AverageTime:F2}ms)");

        }

    }

    public class BenchmarkResult

    {

        public string Name { get; set; }

        public double AverageTime { get; set; }

        public double MinTime { get; set; }

        public double MaxTime { get; set; }

        public double AverageMemory { get; set; }

        public double RowCount { get; set; }

    }

    public class MockDataReader : IDataReader

    {

        private readonly int _rowCount;

        private readonly int _columnCount;

        private int _currentRow = -1;

        private readonly object[][] _data;

        private readonly string[] _columnNames;

        private readonly Type[] _columnTypes;

        private bool _isClosed = false;

        public int FieldCount => _columnCount;

        public bool IsClosed => _isClosed;

        public int Depth => 0;

        public int RecordsAffected => -1;

        public MockDataReader(int rowCount, int columnCount, bool includeDuplicates)

        {

            _rowCount = rowCount;

            _columnCount = columnCount;

            // Generate column names and types

            _columnNames = new string[columnCount];

            _columnTypes = new Type[columnCount];

            for (int i = 0; i < columnCount; i++)

            {

                // If includeDuplicates is true, make some columns have duplicate names

                if (includeDuplicates && i > 0 && i % 5 == 0)

                {

                    // Make every 5th column a duplicate of column 0

                    _columnNames[i] = "Column_0";

                }

                else

                {

                    _columnNames[i] = $"Column_{i}";

                }

                // Every other column is a string, the rest are integers

                _columnTypes[i] = i % 2 == 0 ? typeof(string) : typeof(int);

            }

            // Generate test data

            _data = new object[rowCount][];

            for (int i = 0; i < rowCount; i++)

            {

                _data[i] = new object[columnCount];

                for (int j = 0; j < columnCount; j++)

                {

                    // Every other column is a string, the rest are integers

                    if (j % 2 == 0)

                    {

                        _data[i][j] = $"Value_{i}_{j}";

                    }

                    else

                    {

                        _data[i][j] = i * j;

                    }

                }

            }

        }

        public bool Read()

        {

            _currentRow++;

            return _currentRow < _rowCount;

        }

        public string GetName(int i)

        {

            return _columnNames[i];

        }

        public Type GetFieldType(int i)

        {

            return _columnTypes[i];

        }

        public int GetValues(object[] values)

        {

            int count = Math.Min(values.Length, _columnCount);

            Array.Copy(_data[_currentRow], values, count);

            return count;

        }

        public object GetValue(int i)

        {

            return _data[_currentRow][i];

        }

        public string GetDataTypeName(int i)

        {

            return GetFieldType(i).Name;

        }

        public bool IsDBNull(int i)

        {

            return _data[_currentRow][i] == null || _data[_currentRow][i] == DBNull.Value;

        }

        public object this[string name] => GetValue(GetOrdinal(name));

        public object this[int i] => GetValue(i);

        public int GetOrdinal(string name)

        {

            // Find the first column with matching name

            for (int i = 0; i < _columnCount; i++)

            {

                if (string.Equals(_columnNames[i], name, StringComparison.OrdinalIgnoreCase))

                {

                    return i;

                }

            }

            throw new IndexOutOfRangeException($"Column '{name}' not found");

        }

        public DataTable GetSchemaTable()

        {

            // Create a schema table that matches what a real data reader would return

            DataTable schemaTable = new DataTable();

            // Add standard schema columns

            schemaTable.Columns.Add("ColumnName", typeof(string));

            schemaTable.Columns.Add("ColumnOrdinal", typeof(int));

            schemaTable.Columns.Add("ColumnSize", typeof(int));

            schemaTable.Columns.Add("NumericPrecision", typeof(short));

            schemaTable.Columns.Add("NumericScale", typeof(short));

            schemaTable.Columns.Add("IsUnique", typeof(bool));

            schemaTable.Columns.Add("IsKey", typeof(bool));

            schemaTable.Columns.Add("BaseServerName", typeof(string));

            schemaTable.Columns.Add("BaseCatalogName", typeof(string));

            schemaTable.Columns.Add("BaseColumnName", typeof(string));

            schemaTable.Columns.Add("BaseSchemaName", typeof(string));

            schemaTable.Columns.Add("BaseTableName", typeof(string));

            schemaTable.Columns.Add("DataType", typeof(Type));

            schemaTable.Columns.Add("AllowDBNull", typeof(bool));

            schemaTable.Columns.Add("ProviderType", typeof(int));

            schemaTable.Columns.Add("IsAliased", typeof(bool));

            schemaTable.Columns.Add("IsExpression", typeof(bool));

            schemaTable.Columns.Add("IsIdentity", typeof(bool));

            schemaTable.Columns.Add("IsAutoIncrement", typeof(bool));

            schemaTable.Columns.Add("IsRowVersion", typeof(bool));

            schemaTable.Columns.Add("IsHidden", typeof(bool));

            schemaTable.Columns.Add("IsLong", typeof(bool));

            schemaTable.Columns.Add("IsReadOnly", typeof(bool));

            // Populate schema table

            for (int i = 0; i < _columnCount; i++)

            {

                DataRow row = schemaTable.NewRow();

                // Set required values

                row["ColumnName"] = GetName(i);

                row["ColumnOrdinal"] = i;

                row["ColumnSize"] = i % 2 == 0 ? 255 : 4;  // String or int

                row["DataType"] = GetFieldType(i);

                row["AllowDBNull"] = false;

                row["BaseColumnName"] = GetName(i);

                row["IsKey"] = false;

                row["IsUnique"] = false;

                row["IsAutoIncrement"] = false;

                row["IsReadOnly"] = false;

                schemaTable.Rows.Add(row);

            }

            return schemaTable;

        }

        // Minimal implementations of remaining IDataReader methods

        public bool GetBoolean(int i) => Convert.ToBoolean(GetValue(i));

        public byte GetByte(int i) => Convert.ToByte(GetValue(i));

        public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferOffset, int length) => 0;

        public char GetChar(int i) => Convert.ToChar(GetValue(i));

        public long GetChars(int i, long fieldOffset, char[] buffer, int bufferOffset, int length) => 0;

        public Guid GetGuid(int i) => (Guid)GetValue(i);

        public short GetInt16(int i) => Convert.ToInt16(GetValue(i));

        public int GetInt32(int i) => Convert.ToInt32(GetValue(i));

        public long GetInt64(int i) => Convert.ToInt64(GetValue(i));

        public float GetFloat(int i) => Convert.ToSingle(GetValue(i));

        public double GetDouble(int i) => Convert.ToDouble(GetValue(i));

        public decimal GetDecimal(int i) => Convert.ToDecimal(GetValue(i));

        public DateTime GetDateTime(int i) => Convert.ToDateTime(GetValue(i));

        public IDataReader GetData(int i) => throw new NotImplementedException();

        public string GetString(int i) => Convert.ToString(GetValue(i));

        public bool NextResult() => false;

        public void Close()

        {

            _isClosed = true;

        }

        public void Dispose()

        {

            Close();

        }

    }

}