DatabaseHelper.cs

// =============================================================================
// GPAL - Generally Positive Automation Library
// Copyright © 2026 Software Decisions, Inc. All rights reserved.
//
// This file is part of GPAL.
// Licensed under the Business Source License 1.1
//
// Primary development, architecture, and vision by Michael B. Vederman,
// CEO of Software Decisions, Inc., Texas.
//
// Internal development maintained privately.
// Public releases appear on GitHub: https://github.com/SoftwareDecisionsInc/GPAL.
//
// See LICENSE for full terms, including Additional Use Grant.
// =============================================================================
 
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static GenerallyPositive.Enums;
 
namespace GenerallyPositive
{
    internal class DatabaseHelper
    {
        public static SqlConnection OpenConnection(DatabaseSettings databaseSettings)
        {
            if (null == databaseSettings.Connection)
            {
                try
                {
                    if (null != databaseSettings.ConnectionString)
                        databaseSettings.Connection = new SqlConnection(databaseSettings.ConnectionString);
                    else if (Enums.DatabaseType.SQLServer == databaseSettings.DatabaseType)
                    {
                        StringBuilder sb = new StringBuilder();
                        sb.Append($"Data Source={databaseSettings.ServerName};Initial Catalog={databaseSettings.DatabaseName};");
                        if (null == databaseSettings.UserName)
                            sb.Append("Integrated Security=true;");
                        else
                        {
                            sb.Append($"User id={databaseSettings.UserName};password={databaseSettings.Password};");
                        }
                        databaseSettings.Connection = new SqlConnection(sb.ToString());
 
                    }
 
                    // neither a connection string nor a database type, so there is nothing to open. calling
                    // Open on it reported a null reference, which named nothing the workflow could act on
                    if (null == databaseSettings.Connection)
                        GPAL.PublishSimpleEvent(GPALEventType.ERROR,
                            "Nothing said what to connect to. Set .WithConnectionString, or .WithDatabaseType(DatabaseType.SQLServer) with a server and database name",
                            databaseSettings.Database, GPALObjectType.Database);
                    else
                        databaseSettings.Connection.Open();
                }
                catch (Exception ex)
                {
                    RecordFailure(databaseSettings, ex, $"Unable to open a connection to [{databaseSettings.DatabaseName}] on [{databaseSettings.ServerName}]", null, null, -1);
                }
            }
            return databaseSettings.Connection;
        }
        public static void Close(DatabaseSettings databaseSettings)
        {
            // a connection carrying a transaction stays open. closing it here would roll back between two
            // statements the workflow meant to keep together
            if (null == databaseSettings.SqlTransaction)
            {
                databaseSettings.Connection.Close();
                databaseSettings.Connection = null;
            }
        }

        public static void CloseEverything(DatabaseSettings databaseSettings)
        {
            if (null != databaseSettings.SqlTransaction)
            {
                databaseSettings.SqlTransaction.Rollback();
                databaseSettings.SqlTransaction = null;
            }
 
            if (null != databaseSettings.Connection)
            {
                if (ConnectionState.Closed != databaseSettings.Connection.State)
                    databaseSettings.Connection.Close();
 
                databaseSettings.Connection.Dispose();
                databaseSettings.Connection = null;
            }
        }
        private static void PrepareCommand(DatabaseSettings databaseSettings, SqlCommand command)
        {
            if (null != command)
            {
                if (0 <= databaseSettings.CommandTimeoutSeconds)
                    command.CommandTimeout = databaseSettings.CommandTimeoutSeconds;
 
                if (null != databaseSettings.SqlTransaction)
                    command.Transaction = databaseSettings.SqlTransaction;
            }
        }

        private static void AddParameter(SqlCommand command, DatabaseSettings.DatabaseColumn databaseColumn, string parameterValue)
        {
            SqlParameter sqlParameter = command.Parameters.Add($"@{databaseColumn.ColumnName}", databaseColumn.ColumnType, databaseColumn.ColumnLength);
 
            // a column declared decimal(9,4) is sent as decimal(9,4). left at zero the provider reads the
            // precision and scale off each value instead, so the same column could arrive a different shape
            // on every row
            if (0 < databaseColumn.ColumnPrecision)
                sqlParameter.Precision = (byte)databaseColumn.ColumnPrecision;
 
            if (0 < databaseColumn.ColumnScale)
                sqlParameter.Scale = (byte)databaseColumn.ColumnScale;
 
            sqlParameter.Value = ReturnParameterOfType(parameterValue, databaseColumn.ColumnType);
        }

        private static void AddReadParameters(DatabaseSettings databaseSettings, SqlCommand command)
        {
            List<string> values = 0 < (databaseSettings.ParametersGrid?.Rows ?? 0)
                ? new List<string>(databaseSettings.ParametersGrid[0])
                : databaseSettings.ParametersList;
 
            if (null != command && 0 < (values?.Count ?? 0))
            {
                if (0 < databaseSettings.ColumnList.Count)
                {
                    foreach (DatabaseSettings.DatabaseColumn databaseColumn in databaseSettings.ColumnList)
                    {
                        int columnIndex = databaseSettings.ColumnList.IndexOf(databaseColumn);
 
                        AddParameter(command, databaseColumn, values.Count > columnIndex ? values[columnIndex] : null);
                    }
                }
                else
                {
                    // named rather than typed, which is how a stored procedure is usually called. the server
                    // converts from text, the same as it does for a parameter given no length
                    int valueIndex = 0;
 
                    foreach (string parameterName in databaseSettings.ParameterNamesList)
                    {
                        command.Parameters.Add(new SqlParameter(parameterName,
                            values.Count > valueIndex && null != values[valueIndex] ? (object)values[valueIndex] : DBNull.Value));
 
                        valueIndex++;
                    }
                }
            }
        }

        private static SqlCommand BuildReadCommand(DatabaseSettings databaseSettings, SqlConnection sqlConnection)
        {
            SqlCommand cmd = null;
 
            // .WithReadAs on the chain the verb named wins, the way it did when reading .Read copied that sql
            // over everything else. It is chosen here now instead of being copied, so a stored procedure the
            // caller also set is still there for a later call rather than replaced
            if (false == string.IsNullOrWhiteSpace(databaseSettings.VerbSQL))
            {
                cmd = new SqlCommand(databaseSettings.VerbSQL, sqlConnection)
                {
                    CommandType = CommandType.Text
                };
            }
            // a stored procedure and a sql command are kept in the same place, so which one it is has to be
            // read off the command type. asking whether SqlCommand was set answered yes for a procedure as
            // well, so the branch that gives a procedure its parameters could never be reached
            else if (CommandType.StoredProcedure == databaseSettings.SqlCommandType && null != databaseSettings.StoredProcedure)
            {
                cmd = new SqlCommand(databaseSettings.StoredProcedure, sqlConnection)
                {
                    CommandType = CommandType.StoredProcedure
                };
            }
            else if (null != databaseSettings.SqlCommand)
            {
                cmd = new SqlCommand(databaseSettings.SqlCommand, sqlConnection)
                {
                    CommandType = databaseSettings.SqlCommandType
                };
            }
            else if (null != databaseSettings.ReadSQL)
            {
                cmd = new SqlCommand(databaseSettings.ReadSQL, sqlConnection)
                {
                    CommandType = databaseSettings.SqlCommandType
                };
            }
            else if (null != databaseSettings.TableName)
            {
                string sqlCommand = "Select " + ((-1 != databaseSettings.RowCount) ? $"TOP({databaseSettings.RowCount}) " : "") + $"* from {databaseSettings.TableName}";
 
                cmd = new SqlCommand(sqlCommand, sqlConnection)
                {
                    CommandType = CommandType.Text
                };
            }
 
            AddReadParameters(databaseSettings, cmd);
 
            PrepareCommand(databaseSettings, cmd);
 
            return cmd;
        }

        private static SqlCommand ReadCommandOrNothing(DatabaseSettings databaseSettings, SqlConnection sqlConnection)
        {
            SqlCommand retVal = null;
 
            if (null == sqlConnection || ConnectionState.Open != sqlConnection.State)
                GPAL.PublishSimpleEvent(GPALEventType.WARNING,
                    $"No open connection to [{databaseSettings.DatabaseName}] on [{databaseSettings.ServerName}], so nothing was read",
                    databaseSettings.Database, GPALObjectType.Database);
            else
            {
                retVal = BuildReadCommand(databaseSettings, sqlConnection);
 
                if (null == retVal)
                    GPAL.PublishSimpleEvent(GPALEventType.ERROR,
                        "Nothing said what to read. Set one of .WithSQLCommand, .WithReadAs, .WithTableName or .WithStoredProcedure",
                        databaseSettings.Database, GPALObjectType.Database);
            }
 
            return retVal;
        }

        public static void GetScalar(DatabaseSettings databaseSettings, out string value)
        {
            string retVal = null;
 
            // cleared before the connection is asked for, so it describes this call and a failure to open
            // still stands afterwards
            databaseSettings.LastError = null;
 
            SqlConnection sqlConnection = OpenConnection(databaseSettings);
            SqlCommand cmd = ReadCommandOrNothing(databaseSettings, sqlConnection);
 
            if (null != cmd)
            {
                try
                {
                    object answer = cmd.ExecuteScalar();
 
                    retVal = DBNull.Value == answer ? null : answer?.ToString();
 
                    Close(databaseSettings);
                }
                catch (Exception ex)
                {
                    RecordFailure(databaseSettings, ex, $"Failed reading with [{cmd.CommandText}]", cmd.CommandText, databaseSettings.ParametersGrid, -1);
                }
            }
 
            value = retVal;
        }

        public static int GetDataSet(DatabaseSettings databaseSettings, out DataSet dataSet)
        {
            DataSet returnDataSet = new DataSet();
 
            // cleared before the connection is asked for, so it describes this call and a failure to open
            // still stands afterwards
            databaseSettings.LastError = null;
 
            SqlConnection sqlConnection = OpenConnection(databaseSettings);
            SqlCommand cmd = ReadCommandOrNothing(databaseSettings, sqlConnection);
 
            if (null != cmd)
            {
                try
                {
                    using var adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(returnDataSet);
                    Close(databaseSettings);
                }
                catch (Exception ex)
                {
                    RecordFailure(databaseSettings, ex, $"Failed reading with [{cmd.CommandText}]", cmd.CommandText, databaseSettings.ParametersGrid, -1);
                }
            }
 
            dataSet = returnDataSet;
            return dataSet.Tables.Count;
        }
        public static int GetGrid(DatabaseSettings databaseSettings, out IGPALGrid<string> dataGrid)
        {
            IGPALGrid<string> returnDataGrid = GPAL.Grid.ToGPALObject();
            GetDataSet(databaseSettings, out DataSet dataSet);
 
            var rowCountsPerTable = new List<int>();
            int rowsAdded = 0;
            foreach (DataTable table in dataSet.Tables)
            {
                if (databaseSettings.ColumnList.Count == 0)
                {
                    foreach (DataColumn column in table.Columns)
                    {
                        SqlDbType sqlDbType = GetSqlDbTypeFromClrType(column.DataType);
                        databaseSettings.ColumnList.Add(new DatabaseSettings.DatabaseColumn(column.ColumnName, sqlDbType));
                    }
                }
 
                int rowsAddedForTable = 0;
                foreach (DataRow row in table.Rows)
                {
                    if (0 < databaseSettings.RowCount && rowsAdded >= databaseSettings.RowCount)
                        break;
                    returnDataGrid.AddRow(row.ItemArray.Select(v => v.ToString()).ToList());
                    rowsAdded++;
                    rowsAddedForTable++;
                }
                rowCountsPerTable.Add(rowsAddedForTable);
            }
            databaseSettings.LastGridRowCountsPerTable = rowCountsPerTable;
 
            dataGrid = returnDataGrid;
            return dataGrid.Count();
        }
        public static int GetDataTable(DatabaseSettings databaseSettings, out DataTable dataTable)
        {
            GetDataSet(databaseSettings, out DataSet dataSet);
 
            if (dataSet.Tables.Count > 1)
            {
                GPAL.PublishSimpleEvent(GPALEventType.WARNING,
                    $"Query returned [{dataSet.Tables.Count}] result sets; SaveTo(out DataTable) only keeps the first. Use SaveTo(out DataSet) to get all of them.",
                    databaseSettings.Database, GPALObjectType.Database);
            }
 
            dataTable = dataSet.Tables.Count > 0 ? dataSet.Tables[0] : new DataTable();
            return dataTable.Rows.Count;
        }
        internal static string BuildInsertStatement(DatabaseSettings databaseSettings)
        {
            string retVal = null;
            bool hasTable = false == string.IsNullOrWhiteSpace(databaseSettings.TableName);
            bool hasColumns = 0 < databaseSettings.ColumnList.Count;
 
            // half of what an insert needs is a caller who meant one and is missing a call, so say which. neither
            // half is a caller who has not said what to run at all, and the write reports that once for itself
            // rather than having this speak first about an insert nobody asked for
            if (false == hasTable || false == hasColumns)
            {
                if (true == hasTable || true == hasColumns)
                    GPAL.PublishSimpleEvent(GPALEventType.ERROR,
                        "An insert needs a table name and at least one parameter to write. Set .WithTableName and one of the parameter calls",
                        databaseSettings.Database, GPALObjectType.Database);
            }
            else
            {
                // joined rather than appended with a separator on each one, which left the last separator on
                // the end. only the space was taken off again, so every insert this built asked for a column
                // after the last one and the server rejected the statement
                string columns = string.Join(", ", databaseSettings.ColumnList.Select(databaseColumn => databaseColumn.ColumnName));
                string parameters = string.Join(", ", databaseSettings.ColumnList.Select(databaseColumn => $"@{databaseColumn.ColumnName}"));
 
                retVal = $"INSERT INTO {databaseSettings.TableName} ({columns}) VALUES ({parameters})";
            }
 
            return retVal;
        }

        internal static string BuildWriteStatement(DatabaseSettings databaseSettings)
        {
            string retVal = false == string.IsNullOrWhiteSpace(databaseSettings.VerbSQL)
                ? databaseSettings.VerbSQL
                : databaseSettings.SqlCommand;
 
            if (string.IsNullOrWhiteSpace(retVal) && DatabaseSettings.CrudVerb.Create == databaseSettings.Verb)
                retVal = BuildInsertStatement(databaseSettings);
 
            return retVal;
        }
        internal static int ExecuteSQL(DatabaseSettings databaseSettings, string sqlToRun, IGPALGrid<string> parameterTokens = null)
        {
            return ExecuteSQL(databaseSettings, sqlToRun, parameterTokens, out _);
        }
        internal static int ExecuteSQL(DatabaseSettings databaseSettings, string sqlToRun, IGPALGrid<string> parameterTokens, out List<int> rowCountsPerStatement)
        {
            // cleared before the connection is asked for, so it describes this call and a failure to open
            // still stands afterwards
            databaseSettings.LastError = null;
 
            // the command type belongs to whatever SqlCommand/StoredProcedure is holding. A statement built for
            // the verb on the chain, or built from the table name, is text however the caller also set that, so
            // a stored procedure sitting on the settings does not send the server looking for one named by the
            // whole insert
            CommandType commandType = sqlToRun == databaseSettings.SqlCommand
                ? databaseSettings.SqlCommandType
                : CommandType.Text;
 
            using var sqlCommand = new SqlCommand(sqlToRun, databaseSettings.Connection ?? OpenConnection(databaseSettings))
            {
                CommandType = commandType
            };
 
            PrepareCommand(databaseSettings, sqlCommand);
 
            int rowCount = 0;
            int rowBeingWritten = -1;
            bool ranPerRow = false;
            var statementCounts = new List<int>();
 
            sqlCommand.StatementCompleted += (s, e) => statementCounts.Add(e.RecordCount);
 
            // a connection that never opened, or a command with no sql, ran anyway and reported the
            // provider complaining about that rather than the reason the workflow needs to see
            if (null == sqlCommand.Connection || ConnectionState.Open != sqlCommand.Connection.State)
                GPAL.PublishSimpleEvent(GPALEventType.WARNING,
                    $"No open connection to [{databaseSettings.DatabaseName}] on [{databaseSettings.ServerName}], so nothing was run",
                    databaseSettings.Database, GPALObjectType.Database);
            else if (string.IsNullOrWhiteSpace(sqlToRun))
                GPAL.PublishSimpleEvent(GPALEventType.ERROR,
                    "Nothing said what to run. Set one of .WithSQLCommand, .WithCreateAs, .WithUpdateAs, .WithDeleteAs or .WithStoredProcedure, or give .Create a .WithTableName and its parameters to write",
                    databaseSettings.Database, GPALObjectType.Database);
            else
            {
                try
                {
                    if (parameterTokens != null && parameterTokens.Rows > 0)
                    {
                        var structuredColumn = databaseSettings.ColumnList.FirstOrDefault(c => c.ColumnType == SqlDbType.Structured);
                        if (structuredColumn != null)
                        {
                            if (string.IsNullOrEmpty(structuredColumn.TvTableTypeName))
                                throw new InvalidOperationException($"UDTT Type Name required for Table Value column @[{structuredColumn.ColumnName}]. Use .WithUDDTName()");
 
                            var dataTable = new DataTable();
                            int columnCount = parameterTokens.Columns;
 
                            for (int i = 0; i < columnCount; i++)
                            {
                                dataTable.Columns.Add($"Column{i}", typeof(string));
                            }
 
                            foreach (var row in parameterTokens)
                            {
                                if (row.Count != columnCount)
                                {
                                    GPAL.PublishSimpleEvent(GPALEventType.WARNING,
                                        $"Row [{row}] has [{row.Count}] values, but expected [{columnCount}]. Skipping row.",
                                        parameterTokens, GPALObjectType.Other);
                                    continue;
                                }
                                dataTable.Rows.Add(row.ToArray());
                            }
 
                            var param = sqlCommand.Parameters.AddWithValue($"@{structuredColumn.ColumnName}", dataTable);
                            param.SqlDbType = SqlDbType.Structured;
                            param.TypeName = structuredColumn.TvTableTypeName;
                        }
                        else
                        {
                            // a grid is rows of parameters, so the command runs once against each of them. one row is
                            // the ordinary case and runs once, which is what a caller adding parameters one at a time
                            // gets. taking only the first row meant a grid of fifty rows wrote one and warned about
                            // the other forty nine
                            foreach (var parameters in parameterTokens)
                            {
                                rowBeingWritten++;
                                sqlCommand.Parameters.Clear();
 
                                foreach (var databaseColumn in databaseSettings.ColumnList)
                                {
                                    int columnIndex = databaseSettings.ColumnList.IndexOf(databaseColumn);
                                    string paramValue = parameters.Count > columnIndex ? parameters[columnIndex] : null;
 
                                    AddParameter(sqlCommand, databaseColumn, paramValue);
                                }
 
                                rowCount += sqlCommand.ExecuteNonQuery();
                            }
 
                            ranPerRow = true;
                        }
                    }
 
                    // the table valued path hands the whole grid over as one command, and a command with no
                    // parameters at all still has to run
                    if (false == ranPerRow)
                        rowCount = sqlCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
  
                    RecordFailure(databaseSettings, ex, $"Failed running [{sqlToRun}]", sqlToRun, parameterTokens, rowBeingWritten);
                }
            }
 
            rowCountsPerStatement = statementCounts;
            return rowCount;
        }
        private static void RecordFailure(DatabaseSettings databaseSettings, Exception ex, string message, string sql, IGPALGrid<string> parameterTokens, int row)
        {
            databaseSettings.LastError = new GPALDatabase.DatabaseError
            {
                Number = ex is SqlException sqlException ? sqlException.Number : 0,
                Message = ex.Message,
                Sql = sql,
                Parameters = parameterTokens,
                Row = row,
                RowValues = 0 <= row && row < (parameterTokens?.Rows ?? 0) ? new List<string>(parameterTokens[row]) : null,
                Occurred = DateTime.Now,
                Exception = ex
            };
 
            // the server rejecting something is an error and says so with a number and a message. anything else
            // arriving here was not expected at all, so it stays an exception.
            // published rather than handed straight to the exception handler: this reaches the console, the debug
            // window and a logger as well, the handler still fires because an exception came with it, and the
            // screenshot is taken by the publish rather than here
            GPAL.PublishSimpleEvent(ex is SqlException ? GPALEventType.ERROR : GPALEventType.EXCEPTION,
                message, databaseSettings.Database, GPALObjectType.Database, ex);
 
            CallOnErrorHandler(databaseSettings);
        }
 
        private static void CallOnErrorHandler(DatabaseSettings databaseSettings)
        {
            if (null != databaseSettings.CallOnErrorHandler)
                databaseSettings.CallOnErrorHandler(databaseSettings.Database);
 
            if (null != databaseSettings.SqlTransaction)
            {
                databaseSettings.SqlTransaction.Rollback();
                databaseSettings.SqlTransaction = null;
            }
 
            if (null != databaseSettings.Connection && ConnectionState.Open == databaseSettings.Connection.State)
            {
                databaseSettings.Connection.Close();
                databaseSettings.Connection = null;
            }
        }
        static SqlDbType GetSqlDbTypeFromName(string dataTypeName)
        {
            switch (dataTypeName.ToLower())
            {
                case "char":
                    return SqlDbType.Char;
                case "nchar":
                    return SqlDbType.NChar;
                case "ntext":
                    return SqlDbType.NText;
                case "nvarchar":
                    return SqlDbType.NVarChar;
                case "text":
                    return SqlDbType.Text;
                case "varchar":
                    return SqlDbType.VarChar;
                case "xml":
                    return SqlDbType.Xml;
                case "date":
                    return SqlDbType.Date;
                case "datetime":
                    return SqlDbType.DateTime;
                case "datetime2":
                    return SqlDbType.DateTime2;
                case "smalldatetime":
                    return SqlDbType.SmallDateTime;
                case "time":
                    return SqlDbType.Time;
                case "timestamp":
                    return SqlDbType.Timestamp;
                case "decimal":
                    return SqlDbType.Decimal;
                case "money":
                    return SqlDbType.Money;
                case "smallmoney":
                    return SqlDbType.SmallMoney;
                case "float":
                    return SqlDbType.Float;
                case "real":
                    return SqlDbType.Real;
                case "image":
                    return SqlDbType.Image;
                case "bit":
                    return SqlDbType.Bit;
                case "int":
                    return SqlDbType.Int;
                case "smallint":
                    return SqlDbType.SmallInt;
                // Add cases for other types as needed
                default:
                    throw new ArgumentException("Unknown SqlDbType: " + dataTypeName);
            }
        }
        static SqlDbType GetSqlDbTypeFromClrType(Type clrType)
        {
            switch (Type.GetTypeCode(clrType))
            {
                case TypeCode.String: return SqlDbType.NVarChar;
                case TypeCode.Boolean: return SqlDbType.Bit;
                case TypeCode.Byte: return SqlDbType.TinyInt;
                case TypeCode.Int16: return SqlDbType.SmallInt;
                case TypeCode.Int32: return SqlDbType.Int;
                case TypeCode.Int64: return SqlDbType.BigInt;
                case TypeCode.Single: return SqlDbType.Real;
                case TypeCode.Double: return SqlDbType.Float;
                case TypeCode.Decimal: return SqlDbType.Decimal;
                case TypeCode.DateTime: return SqlDbType.DateTime;
                default:
                    if (clrType == typeof(Guid)) return SqlDbType.UniqueIdentifier;
                    if (clrType == typeof(byte[])) return SqlDbType.VarBinary;
                    if (clrType == typeof(TimeSpan)) return SqlDbType.Time;
                    if (clrType == typeof(DateTimeOffset)) return SqlDbType.DateTimeOffset;
                    return SqlDbType.NVarChar;
            }
        }
        private static object ReturnParameterOfType(string parmValue, SqlDbType sqlDbType)
        {
            if (string.IsNullOrEmpty(parmValue))
            {
                GPAL.PublishSimpleEvent(GPALEventType.WARNING,
                    $"Parameter value is null or empty for SqlDbType [{sqlDbType}]. Returning DBNull.Value.",
                    null, GPALObjectType.Other);
                return DBNull.Value;
            }
 
            try
            {
                switch (sqlDbType)
                {
                    case SqlDbType.Char:
                    case SqlDbType.NChar:
                    case SqlDbType.NText:
                    case SqlDbType.NVarChar:
                    case SqlDbType.Text:
                    case SqlDbType.VarChar:
                    case SqlDbType.Xml:
                        return parmValue;
 
                    case SqlDbType.Date:
                    case SqlDbType.DateTime:
                    case SqlDbType.DateTime2:
                    case SqlDbType.SmallDateTime:
                        return DateTime.TryParse(parmValue, out DateTime dateResult) ? (object)dateResult : DBNull.Value;
 
                    // a sql server time column is a TimeSpan on this side. handing it a DateTime is refused by
                    // the provider before the command leaves, so a whole date and time is accepted here and the
                    // time of day taken out of it: "14:30:00" and "2026-09-03 14:30:00" both write 14:30
                    case SqlDbType.Time:
                        if (TimeSpan.TryParse(parmValue, out TimeSpan timeResult))
                            return timeResult;
 
                        return DateTime.TryParse(parmValue, out DateTime timeOfDayResult) ? (object)timeOfDayResult.TimeOfDay : DBNull.Value;
 
                    case SqlDbType.DateTimeOffset:
                        return DateTimeOffset.TryParse(parmValue, out DateTimeOffset offsetResult) ? (object)offsetResult : DBNull.Value;
 
                    case SqlDbType.Decimal:
                    case SqlDbType.Money:
                    case SqlDbType.SmallMoney:
                        return Decimal.TryParse(parmValue, out decimal decimalResult) ? (object)decimalResult : DBNull.Value;
 
                    // sql server's float holds 8 bytes and real holds 4. reading both as Single rounded away
                    // everything a float column was chosen for
                    case SqlDbType.Float:
                        return double.TryParse(parmValue, out double doubleResult) ? (object)doubleResult : DBNull.Value;
 
                    case SqlDbType.Real:
                        return float.TryParse(parmValue, out float floatResult) ? (object)floatResult : DBNull.Value;
 
                    case SqlDbType.Bit:
                        return bool.TryParse(parmValue, out bool boolResult) ? (object)boolResult : (int.TryParse(parmValue, out int intBitResult) ? (object)(intBitResult != 0) : DBNull.Value);
 
                    case SqlDbType.Int:
                    case SqlDbType.SmallInt:
                        return int.TryParse(parmValue, out int intResult) ? (object)intResult : DBNull.Value;
 
                    case SqlDbType.BigInt:
                        return long.TryParse(parmValue, out long longResult) ? (object)longResult : DBNull.Value;
 
                    case SqlDbType.UniqueIdentifier:
                        return Guid.TryParse(parmValue, out Guid guidResult) ? (object)guidResult : DBNull.Value;
 
                    case SqlDbType.Image:
                    case SqlDbType.VarBinary:
                    case SqlDbType.Binary:
                        try
                        {
                            return Convert.FromBase64String(parmValue);
                        }
                        catch (FormatException ex)
                        {
                            GPAL.PublishSimpleEvent(GPALEventType.WARNING,
                                $"Failed to decode Base64 string [{parmValue}] for SqlDbType [{sqlDbType}]. Returning DBNull.Value.",
                                null, GPALObjectType.Other, ex);
                            return DBNull.Value;
                        }
 
                    case SqlDbType.Timestamp:
                        try
                        {
                            if (ulong.TryParse(parmValue, out ulong ulongResult))
                            {
                                return BitConverter.GetBytes(ulongResult).Reverse().ToArray();
                            }
                            throw new FormatException("Invalid numeric string for Timestamp");
                        }
                        catch (Exception ex)
                        {
                            GPAL.PublishSimpleEvent(GPALEventType.WARNING,
                                $"Failed to parse [{parmValue}] as Timestamp. Returning DBNull.Value.",
                                null, GPALObjectType.Other, ex);
                            return DBNull.Value;
                        }
 
                    case SqlDbType.Variant:
                        if (int.TryParse(parmValue, out int variantIntResult))
                            return variantIntResult;
                        if (decimal.TryParse(parmValue, out decimal variantDecResult))
                            return variantDecResult;
                        if (DateTime.TryParse(parmValue, out DateTime variantDateResult))
                            return variantDateResult;
                        if (Guid.TryParse(parmValue, out Guid variantGuidResult))
                            return variantGuidResult;
                        return parmValue;
 
                    case SqlDbType.Structured:
                        GPAL.PublishSimpleEvent(GPALEventType.WARNING,
                            $"SqlDbType.Structured is not supported for individual parameter values. Use a multi-row GPALGrid<string> in ExecuteSQL. Returning DBNull.Value.",
                            null, GPALObjectType.Other);
                        return DBNull.Value;
 
                    default:
                        GPAL.PublishSimpleEvent(GPALEventType.WARNING,
                            $"Unsupported SqlDbType [{sqlDbType}] for parameter value [{parmValue}]. Returning DBNull.Value.",
                            null, GPALObjectType.Other);
                        return DBNull.Value;
                }
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(GPALEventType.WARNING,
                    $"Failed to convert parameter value [{parmValue}] to SqlDbType [{sqlDbType}]. Returning DBNull.Value.",
                    null, GPALObjectType.Other, ex);
                return DBNull.Value;
            }
        }
        internal static void TokenizeDatabase(UnitOfWork currentUOW, IGPALDatabase inputDatabase)
        {
            IGPALGrid<string> tokens;
            if (null != currentUOW)
                currentUOW.InputDatabase = inputDatabase;
            int rowCount = DatabaseHelper.GetGrid(((GPALDatabase)inputDatabase).DatabaseSettings, out tokens);
            ((GPALDatabase)inputDatabase).Tokens = tokens;
        }
        public static IGPALGrid<string> DataTableToGrid(DataTable dataTable, out List<DatabaseSettings.DatabaseColumn> columnsList)
        {
            IGPALGrid<string> retVal = GPAL.Grid.ToGPALObject();
 
            columnsList = new List<DatabaseSettings.DatabaseColumn>();
 
            if (null != dataTable)
            {
                foreach (DataColumn column in dataTable.Columns)
                    columnsList.Add(new DatabaseSettings.DatabaseColumn(column.ColumnName, GetSqlDbTypeFromClrType(column.DataType)));
 
                foreach (DataRow row in dataTable.Rows)
                    retVal.AddRow(row.ItemArray.Select(value => DBNull.Value == value ? null : value?.ToString()).ToList());
            }
 
            return retVal;
        }
        public static void TokenizeDatabase(GPALDatabase inputDatabase)
        {
            IGPALGrid<string> tokens;
            int rowCount = DatabaseHelper.GetGrid(inputDatabase.DatabaseSettings, out tokens);
            inputDatabase.Tokens = tokens;
        }
    }

    public static class DataTableExtensions
    {
        public static void SetColumnsOrder(this DataTable table, params String[] columnNames)
        {
            int columnIndex = 0;
            foreach (var columnName in columnNames)
            {
                if (table.Columns.Contains(columnName))
                {
                    table.Columns[columnName].SetOrdinal(columnIndex);
                    columnIndex++;
                }
            }
        }
    }
}