GPALDatabase.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.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GenerallyPositive.Browser;
using System.Data;
using static GenerallyPositive.Enums;
 
namespace GenerallyPositive
{
    public class GPALDatabase : IGPALDatabase, IAllowDatabaseGridSettings, IDisposable
    {
        private DatabaseSettings.DatabaseColumn lastDatabaseColumn { get; set; }
        internal DatabaseSettings DatabaseSettings { get; set; }
 
        public delegate int CallOnError(GPALDatabase database);

        public class DatabaseError
        {
            public int Number { get; internal set; }
            public string Message { get; internal set; }
            public string Sql { get; internal set; }
            public IGPALGrid<string> Parameters { get; internal set; }
            public int Row { get; internal set; } = -1;
            public List<string> RowValues { get; internal set; }
            public DateTime Occurred { get; internal set; }
            public Exception Exception { get; internal set; }
        }

        public DatabaseError LastError
        {
            get
            {
                return DatabaseSettings.LastError;
            }
            internal set
            {
                DatabaseSettings.LastError = value;
            }
        }

        public IGPALDatabase ToGPALObject()
        {
            return this;
        }
 
        internal GPALDatabase()
        {
            DatabaseSettings = new DatabaseSettings(this);
        }
 
        #region <Getters/Setters>
        // each verb says which one it is and nothing more. copying its sql into SqlCommand left that sql behind
        // for the next verb and replaced a stored procedure with text on the way; the statement is chosen from
        // the verb when the command is built. See DatabaseSettings.Verb
        public IAllowParametersAndUsing Create
        {
            get
            {
                DatabaseSettings.Verb = DatabaseSettings.CrudVerb.Create;
 
                return this;
            }
        }
        public IAllowParametersAndInto Read
        {
            get
            {
                DatabaseSettings.Verb = DatabaseSettings.CrudVerb.Read;
 
                return this;
            }
        }
        public IAllowParametersAndUsing Update
        {
            get
            {
                DatabaseSettings.Verb = DatabaseSettings.CrudVerb.Update;
 
                return this;
            }
        }
        public IAllowParametersAndUsing Delete
        {
            get
            {
                DatabaseSettings.Verb = DatabaseSettings.CrudVerb.Delete;
 
                return this;
            }
        }
        public IAllowParameters ClearParameters
        { 
            get
            {
                DatabaseSettings.ParametersList?.Clear();
                DatabaseSettings.ParametersGrid?.Clear();
 
                return this;
            }
        }

        public IGPALGrid<string> Tokens
        {
            get
            {
                return DatabaseSettings.Tokens;
            }
            internal set
            {
                DatabaseSettings.Tokens = value;
            }
        }
 
        public IAllowParametersAndActions BeginTransaction
        {
            get
            {
                if (null == DatabaseSettings.SqlTransaction)
                {
                    try
                    {
                        DatabaseHelper.OpenConnection(DatabaseSettings);
                        DatabaseSettings.SqlTransaction = DatabaseSettings.Connection.BeginTransaction();
                    }
                    catch (Exception ex)
                    {
                        GPAL.PublishSimpleEvent(GPALEventType.EXCEPTION, "ERROR beginning transaction", this, Enums.GPALObjectType.Database, ex);
                    }
                }
                else
                {
                    GPAL.PublishSimpleEvent(GPALEventType.WARNING, "Already in a transaction. You cannot start a new transaction while in a transaction. Doing nothing.", this, Enums.GPALObjectType.Database);
                }
                return this;
            }
        }
 
        public IAllowDatabaseActions Rollback
        {
            get
            {
                DatabaseSettings.SqlTransaction?.Rollback();
                DatabaseSettings.SqlTransaction = null;
                DatabaseHelper.CloseEverything(DatabaseSettings);
                return this;
            }
        }
 
        public IAllowDatabaseActions Commit
        {
            get
            {
                DatabaseSettings.SqlTransaction?.Commit();
                DatabaseSettings.SqlTransaction = null;
                DatabaseHelper.CloseEverything(DatabaseSettings);
                return this;
            }
        }
 
        #endregion <Getters/Setters>
        #region <Fluent Methods>
        public IAllowDatabaseSettings WithConnectionString(string connectionString)
        {
            DatabaseSettings.ConnectionString = connectionString;
            return this;
        }

        public IAllowDatabaseSettings WithUsername(string username)
        {
            DatabaseSettings.UserName = username;
            return this;
        }

        public IAllowDatabaseSettings WithPassword(string password)
        {
            DatabaseSettings.Password = password;
            return this;
        }

        public IAllowDatabaseSettings WithSQLCommand(string sqlCommand)
        {
            DatabaseSettings.SqlCommand = sqlCommand;
            return this;
        }

        public IAllowDatabaseSettings WithStoredProcedure(string storedProcedure)
        {
            DatabaseSettings.StoredProcedure = storedProcedure;
            return this;
        }

        public IAllowDatabaseSettings WithParameterName(string parameterName)
        {
            DatabaseSettings.ParameterNamesList.Add(parameterName);
            return this;
        }

        public IAllowParametersAndActions WithParameter(string parameter)
        {
            // TODO: if datbase action already called, clear the parms
            DatabaseSettings.ParametersList.Add(parameter);
            return this;
        }

        public IAllowParametersAndActions WithParameterGrid(IGPALGrid<string> parametersGrid)
        {
            DatabaseSettings.ParametersGrid = parametersGrid;
            return this;
        }
        // TODO: figure out fluent syntax for using the underlying sql
        public IAllowDatabaseSettings WithCreateAs(string sqlCommand)
        {
            DatabaseSettings.CreateSQL = sqlCommand;
            return this;
        }
        // TODO: figure out fluent syntax for using the underlying sql
        public IAllowDatabaseSettings WithReadAs(string sqlCommand)
        {
            DatabaseSettings.ReadSQL = sqlCommand;
            return this;
        }
        // TODO: figure out fluent syntax for using the underlying sql
        public IAllowDatabaseSettings WithUpdateAs(string sqlCommand)
        {
            DatabaseSettings.UpdateSQL = sqlCommand;
            return this;
        }
        // TODO: figure out fluent syntax for using the underlying sql
        public IAllowDatabaseSettings WithDeleteAs(string sqlCommand)
        {
            DatabaseSettings.DeleteSQL = sqlCommand;
            return this;
        }

        public IAllowDatabaseSettings WithDatabaseType(Enums.DatabaseType databaseType)
        {
            DatabaseSettings.DatabaseType = databaseType;
            return this;
        }

        public IAllowDatabaseSettings WithServerName(string serverName)
        {
            DatabaseSettings.ServerName = serverName;
            return this;
        }

        public IAllowDatabaseSettings WithDatabaseName(string databaseName)
        {
            DatabaseSettings.DatabaseName = databaseName;
            return this;
        }

        public IAllowDatabaseSettings WithTableName(string tableName)
        {
            DatabaseSettings.TableName = tableName;
            return this;
        }

        public IAllowDatabaseSettings WithCommandTimeout(int timeoutInSeconds)
        {
            DatabaseSettings.CommandTimeoutSeconds = timeoutInSeconds;
            return this;
        }

        public IAllowDatabaseSettings Close()
        {
            DatabaseHelper.CloseEverything(DatabaseSettings);
            return this;
        }

        public void Dispose()
        {
            DatabaseHelper.CloseEverything(DatabaseSettings);
        }
 
        public IAllowDatabaseSettings WithRowCount(int rowCount)
        {
            DatabaseSettings.RowCount = rowCount;
            return this;
        }
 
        #region <Output Column Settings [not yet implemented]>
        public IAllowDatabaseSettings WithDatetimeParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, System.Data.SqlDbType.DateTime);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowDatabaseSettings WithTimeParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, System.Data.SqlDbType.Time);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowDatabaseSettings WithTimestampParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, System.Data.SqlDbType.Timestamp);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowPrecsionScaleAndSettings WithDecimalParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, System.Data.SqlDbType.Decimal);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowDatabaseSettings WithFloatParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, System.Data.SqlDbType.Float);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowDatabaseSettings WithImageParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, System.Data.SqlDbType.Image);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowDatabaseSettings WithIntegerParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, System.Data.SqlDbType.Int);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowDatabaseSettings WithMoneyParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, System.Data.SqlDbType.Money);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowParameterLength WithCharParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, System.Data.SqlDbType.Char);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowParameterLength WithNCharParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, System.Data.SqlDbType.NChar);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowParameterLength WithVarCharParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, System.Data.SqlDbType.VarChar);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowParameterLength WithNVarCharParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, System.Data.SqlDbType.NVarChar);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowParameterLength WithTextParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, System.Data.SqlDbType.Text);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowParameterLength WithNTextParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, System.Data.SqlDbType.NText);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowDatabaseSettings WithParameterLength(int length)
        {
            if (true == NamedAColumn("length"))
                lastDatabaseColumn.ColumnLength = length;
 
            return this;
        }
 
        public IAllowPrecsionScaleAndSettings WithParameterPrecision(int columnPrecision)
        {
            if (true == NamedAColumn("precision"))
                lastDatabaseColumn.ColumnPrecision = columnPrecision;
 
            return this;
        }
 
        public IAllowPrecsionScaleAndSettings WithParameterScale(int columnScale)
        {
            if (true == NamedAColumn("scale"))
                lastDatabaseColumn.ColumnScale = columnScale;
 
            return this;
        }

        private bool NamedAColumn(string setting)
        {
            bool retVal = null != lastDatabaseColumn;
 
            if (false == retVal)
                GPAL.PublishSimpleEvent(GPALEventType.ERROR,
                    $"No parameter to set a {setting} on. Define one first, with .WithVarCharParameter, .WithDecimalParameter or another of the parameter calls",
                    this, GPALObjectType.Database);
 
            return retVal;
        }
 
        public IAllowDatabaseSettings WithTableParameter(string parameterName)
        {
            DatabaseSettings.DatabaseColumn databaseColumn = new DatabaseSettings.DatabaseColumn(parameterName, SqlDbType.Structured);
            lastDatabaseColumn = databaseColumn;
            DatabaseSettings.ColumnList.Add(databaseColumn);
            return this;
        }
        public IAllowDatabaseSettings WithUDTTName(string typeName)
        {
            if (lastDatabaseColumn == null)
            {
                GPAL.PublishSimpleEvent(GPALEventType.WARNING,
                    "No column defined before setting UDTT name. Call .WithTableParameter() first.",
                    this, GPALObjectType.Database);
                return this;
            }
            if (lastDatabaseColumn.ColumnType != SqlDbType.Structured)
            {
                GPAL.PublishSimpleEvent(GPALEventType.WARNING,
                    $"UDTT name specified for non-Structured column [{lastDatabaseColumn.ColumnName}]. Ignoring.",
                    this, GPALObjectType.Database);
            }
            lastDatabaseColumn.TvTableTypeName = typeName;
            return this;
        }
        public IAllowDatabaseExecute Using(IGPALGrid<string> parametersGrid)
        {
            DatabaseSettings.ParametersGrid = parametersGrid;
            return this;
        }
 
        public IAllowDatabaseExecute Using(DataTable dataTable)
        {
            IGPALGrid<string> parametersGrid = DatabaseHelper.DataTableToGrid(dataTable, out List<DatabaseSettings.DatabaseColumn> columns);
            DatabaseSettings.ParametersGrid = parametersGrid;
            DatabaseSettings.ColumnList = columns;
            return this;
        }
 
        public IAllowDatabaseGridSettings SaveTo(out IGPALGrid<string> dataGrid)
        {
            DatabaseHelper.GetGrid(DatabaseSettings, out dataGrid);
            return this;
        }
 
        public IAllowDatabaseSettings GetRowCount(out List<int> rowCountsPerTable)
        {
            rowCountsPerTable = DatabaseSettings.LastGridRowCountsPerTable ?? new List<int>();
            return this;
        }
 
        public IAllowDatabaseSettings SaveTo(out DataTable dataTable)
        {
            DatabaseHelper.GetDataTable(DatabaseSettings, out dataTable);
            return this;
        }
 
        public IAllowDatabaseSettings SaveTo(out DataSet dataSet)
        {
            DatabaseHelper.GetDataSet(DatabaseSettings, out dataSet);
            return this;
        }

        public IAllowDatabaseSettings SaveTo(out string value)
        {
            DatabaseHelper.GetScalar(DatabaseSettings, out value);
            return this;
        }
 
        public IAllowParametersAndActions Execute(out int rowCount)
        {
            rowCount = DatabaseHelper.ExecuteSQL(DatabaseSettings, DatabaseHelper.BuildWriteStatement(DatabaseSettings), ParameterTokens());
            return this;
        }
 
        public IAllowParametersAndActions Execute(out List<int> rowCountsPerStatement)
        {
            DatabaseHelper.ExecuteSQL(DatabaseSettings, DatabaseHelper.BuildWriteStatement(DatabaseSettings), ParameterTokens(), out rowCountsPerStatement);
            return this;
        }

        private IGPALGrid<string> ParameterTokens()
        {
            IGPALGrid<string> retVal = DatabaseSettings.ParametersGrid;
 
            if (null == retVal || 0 == retVal.Rows)
            {
                Tokens.Clear();
                Tokens.AddRow(DatabaseSettings.ParametersList);
 
                retVal = Tokens;
            }
 
            return retVal;
        }
 
        public IAllowDatabaseSettings WithErrorCallback(GPALDatabase.CallOnError callOnError)
        {
            DatabaseSettings.CallOnErrorHandler = callOnError;
            return this;
        }
        #endregion <Output Column Settings [saving not yet implemented]>
        #endregion <Fluent Methods>
        #region <Private>
        #endregion <Private>
    }
}