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());
 
                    }
                    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 SqlCommand BuildReadCommand(DatabaseSettings databaseSettings, SqlConnection sqlConnection)
        {
            SqlCommand cmd = null;
 
            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
                };
            }
            else if (null != databaseSettings.StoredProcedure)
            {
                int parmIdx = 0;
                cmd = sqlConnection.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = databaseSettings.StoredProcedure;
                if (null != databaseSettings.ParametersList)
                {
                    foreach (string parameter in databaseSettings.ParameterNamesList)
                        cmd.Parameters.Add(new SqlParameter(parameter, databaseSettings.ParametersList[parmIdx++]));
                }
            }
 
            PrepareCommand(databaseSettings, cmd);
 
            return cmd;
        }

        public static void GetScalar(DatabaseSettings databaseSettings, out string value)
        {
            string retVal = null;
            SqlConnection sqlConnection = OpenConnection(databaseSettings);
            SqlCommand cmd = BuildReadCommand(databaseSettings, sqlConnection);
 
            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();
            SqlConnection sqlConnection = OpenConnection(databaseSettings);
            SqlCommand cmd = BuildReadCommand(databaseSettings, sqlConnection);
 
            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;
        }
        public static int Insert(DatabaseSettings databaseSettings, IGPALGrid<string> tokens)
        {
            StringBuilder insertCommand = new StringBuilder();
            StringBuilder insertParams = new StringBuilder();
 
            insertCommand.Append($"INSERT INTO {databaseSettings.TableName} (");
 
            foreach (DatabaseSettings.DatabaseColumn databaseColumn in databaseSettings.ColumnList)
            {
                insertCommand.Append($"{databaseColumn.ColumName}, ");
                insertParams.Append($"@{databaseColumn.ColumName}, ");
            }
            insertCommand.Remove(insertCommand.Length - 1, 1);  // remove trailing comma
            insertCommand.Append(") Values (");
            insertParams.Remove(insertParams.Length - 1, 1);  // remove trailing comma
            insertParams.Append(")");
            insertCommand.Append(insertParams);
 
            return ExecuteSQL(databaseSettings, insertCommand.ToString(), tokens);
        }
        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)
        {
            using var sqlCommand = new SqlCommand(sqlToRun, databaseSettings.Connection ?? OpenConnection(databaseSettings))
            {
                CommandType = databaseSettings.SqlCommandType
            };
 
            PrepareCommand(databaseSettings, sqlCommand);
 
            int rowCount = 0;
            int rowBeingWritten = -1;
            bool ranPerRow = false;
            var statementCounts = new List<int>();
 
            // cleared here so it always describes this call rather than an older one
            databaseSettings.LastError = null;
            sqlCommand.StatementCompleted += (s, e) => statementCounts.Add(e.RecordCount);
 
            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.ColumName}]. 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.ColumName}", 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;
                                var convertedValue = ReturnParameterOfType(paramValue, databaseColumn.ColumnType);
                                sqlCommand.Parameters.Add($"@{databaseColumn.ColumName}", databaseColumn.ColumnType, databaseColumn.ColumnLength).Value = convertedValue;
                            }
 
                            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:
                    case SqlDbType.Time:
                        return DateTime.TryParse(parmValue, out DateTime dateResult) ? (object)dateResult : 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;
 
                    case SqlDbType.Float:
                    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++;
                }
            }
        }
    }
}