Self Servicing - Oracle CommandTimeOut Per Call

Posts   
 
    
sapul
User
Posts: 49
Joined: 11-Jan-2019
# Posted on: 21-Jan-2019 13:37:55   

Hello there!

I need to set custom timeout for the stored procedures calls ( call specific) . I do not want to use the global setting CommonDaoBase.CommandTimeOut because it is not a safe solution.

so, my question is, how would you implement this requirement. I'd really appreciate that if you tell me some keywords to explain an appropriate approach in order to move on.

thanks Serkan

ActionProcedure.DoSomeThing();

ActionProcedure.DoSomeThingComplexIknowItTakesTime(120);

ActionProcedure.DoVeryEasyOne(10);

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 21-Jan-2019 16:58:22   

This is available in the Adapter templateSet. You will need to use it instead of the SelfServicing templateSet.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Jan-2019 09:44:45   

as a workaround you could set it to a higher value regardless. Normally regular queries will complete before the timeout, and if you need a higher timeout for procs, you got that covered in that case. It's not ideal, but selfservicing doesn't offer 'per call' features so it's an alternative over migrating to adapter.

Frans Bouma | Lead developer LLBLGen Pro
sapul
User
Posts: 49
Joined: 11-Jan-2019
# Posted on: 22-Jan-2019 14:58:17   

Hi Frans,

I've modified CommonDaoBase.template and actionProcedures.template so that i can pass time out parameter and call oracle command manually.

the Generated custom code is below. Maybe you want to just take a short look at the code. I Hope there is no certain issue that jeopardy the project's health or security simple_smile

I just want to share my workaround. Thanks for the support Serkan

CommonDaoBase.cs

public partial class CommonDaoBase : DaoBase
    {
        /// <summary>The connection string to use in the SelfServicing code. This is a global setting and is automatically set with the value read from the config file.</summary>
        public static string ActualConnectionString = string.Empty;
        private const string connectionKeyString = "Main.ConnectionString.Oracle (ODP.NET)";
        
        #region Custom Code #V5

        public int CallActionStoredProcedure2(string storedProcedureToCall, DbParameter[] parameters, ITransaction transaction)
        {
            return CallActionStoredProcedure2(storedProcedureToCall, parameters, transaction, 0);
        }

        public DbCommand CreateStoredProcedureCallCommand2(string storedProcedureToCall, DbParameter[] parameters, ITransaction transaction)
        {
            DynamicQueryEngine dqe = new DynamicQueryEngine();
            DbConnection connectionToUse = this.DetermineConnectionToUse(transaction);
            DbCommand dbCommand = dqe.Creator.CreateCommand(connectionToUse);
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandText = dqe.GetNewStoredProcedureName(storedProcedureToCall);
            dbCommand.Connection = connectionToUse;
            if (transaction != null)
            {
                dbCommand.Transaction = transaction.PhysicalTransaction;
            }
            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.Parameters.AddRange(parameters);
            return dbCommand;
        }

        public int CallActionStoredProcedure2(string storedProcedureToCall, DbParameter[] parameters, ITransaction transaction, int timeOut)
        {
            int toReturn;
            DbCommand command = this.CreateStoredProcedureCallCommand2(storedProcedureToCall, parameters, transaction);
         if (timeOut>0)
            command.CommandTimeout = timeOut;
            bool connectionOpenedLocally = false;
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                    connectionOpenedLocally = true;
                }
                toReturn = command.ExecuteNonQuery();
            }
            finally
            {
                if (connectionOpenedLocally)
                {
                    command.Connection.Close();
                    if (transaction == null)
                    {
                        command.Connection.Dispose();
                    }
                }
                command.Dispose();
            }
            return toReturn;
        }

        #endregion 

ActionProcedures.cs

public delegate int AktarmaGoreGelisTrhDondurTxnHelperCmdTimeOutCallBack(System.Decimal pBookingId, ref System.DateTime returnValue, TransactionHelper transactionHelper, int commandTimeOut);


        public int AktarmaGoreGelisTrhDondurTxnHelperCmdTimeOut(System.Decimal pBookingId, ref System.DateTime returnValue, TransactionHelper transactionHelper, int commandTimeOut)
        {
            // create parameters
            OracleParameter[] parameters = new OracleParameter[2];
            parameters[0] = new OracleParameter("RETURN_VALUE", OracleDbType.Date, 0, ParameterDirection.ReturnValue, true, 0, 0, "", DataRowVersion.Current, returnValue);
            parameters[1] = new OracleParameter("P_BOOKING_ID", OracleDbType.Decimal, 0, ParameterDirection.Input, true, 38, 38, "", DataRowVersion.Current, pBookingId);
            // Call the stored proc.
            CommonDaoBase commonDaoBase = new CommonDaoBase();
            int toReturn = commonDaoBase.CallActionStoredProcedure2("YNA.AKTARMA_GORE_GELIS_TRH_DONDUR", parameters, transactionHelper.GetTransaction(), commandTimeOut);
            if (parameters[0].Value != System.DBNull.Value)
            {
                returnValue = (System.DateTime)ValueConverter.Convert(parameters[0]);
            }
            return toReturn;
        }


internal class ParameterCreator
        {
            /// <summary>Creates the specified parameter name.</summary>
            /// <param name="parameterName">Name of the parameter.</param>
            /// <param name="dbType">parameterType.</param>
            /// <param name="direction">The direction.</param>
            /// <param name="size">The max size</param>
            /// <param name="precision">The precision.</param>
            /// <param name="scale">The scale.</param>
            /// <param name="value">The value.</param>
            /// <returns>ready to use parameter</returns>
            internal static OracleParameter Create(string parameterName, OracleDbType dbType, ParameterDirection direction, int size, byte precision, byte scale, object value)
            {
                OracleParameter toReturn = new OracleParameter();
                toReturn.ParameterName = parameterName;
                toReturn.Direction = direction;
                toReturn.IsNullable = true;
                if (dbType != OracleDbType.Decimal)
                {
                    toReturn.Precision = precision;
                    toReturn.Scale = scale;
                }
                toReturn.Size = size;
                toReturn.OracleDbType = dbType;
                toReturn.Value = value;
                return toReturn;
            }
        }

        /// <summary>
        /// Converts parameter values from specific Oracle types to .NET types.
        /// </summary>
        internal class ValueConverter
        {
            internal static object Convert<T>(OracleParameter parameter)
            {
                object toReturn = Convert(parameter);
                if (toReturn != System.DBNull.Value)
                {
                    toReturn = System.Convert.ChangeType(toReturn, typeof(T));
                }
                return toReturn;
            }

            internal static object Convert(OracleParameter parameter)
            {
                object toReturn = DBNull.Value;
                if (parameter.Value != null)
                {
                    Type typeOfParameterValue = parameter.Value.GetType();
                    switch (typeOfParameterValue.FullName)
                    {
                        case "Oracle.DataAccess.Types.OracleBinary":
                            OracleBinary valueAsBinary = (OracleBinary)parameter.Value;
                            if (!valueAsBinary.IsNull)
                            {
                                toReturn = valueAsBinary.Value;
                            }
                            break;
                        case "Oracle.DataAccess.Types.OracleBlob":
                            toReturn = ((OracleBlob)parameter.Value).Value;
                            break;
                        case "Oracle.DataAccess.Types.OracleClob":
                            toReturn = ((OracleClob)parameter.Value).Value;
                            break;
                        case "Oracle.DataAccess.Types.OracleDate":
                            OracleDate valueAsDate = (OracleDate)parameter.Value;
                            if (!valueAsDate.IsNull)
                            {
                                toReturn = valueAsDate.Value;
                            }
                            break;
                        case "Oracle.DataAccess.Types.OracleDecimal":
                            OracleDecimal valueAsDecimal = (OracleDecimal)parameter.Value;
                            if (!valueAsDecimal.IsNull)
                            {
                                decimal value = valueAsDecimal.Value;
                                int actualPrecision = parameter.Precision;
                                if (parameter.Precision == 0)
                                {
                                    actualPrecision = 38;
                                }
                                toReturn = value;
                                // Following code is based on ODP.NET's conversion routine, as it is pretty obscure what ODP.NET's doing with values read. The if statements are from ODP.NET. 
                                if (parameter.Scale == 0)
                                {
                                    if (actualPrecision < 5)
                                    {
                                        toReturn = System.Convert.ToInt16(value);
                                    }
                                    else
                                    {
                                        if (actualPrecision < 10)
                                        {
                                            toReturn = System.Convert.ToInt32(value);
                                        }
                                        else
                                        {
                                            if (actualPrecision < 19)
                                            {
                                                toReturn = System.Convert.ToInt64(value);
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    if (actualPrecision < 8)
                                    {
                                        toReturn = System.Convert.ToSingle(value);
                                    }
                                    else
                                    {
                                        if (actualPrecision < 16)
                                        {
                                            toReturn = System.Convert.ToDouble(value);
                                        }
                                    }
                                }
                            }
                            break;
                        case "Oracle.DataAccess.Types.OracleIntervalDS":
                            OracleIntervalDS valueAsIntervalDS = (OracleIntervalDS)parameter.Value;
                            if (!valueAsIntervalDS.IsNull)
                            {
                                toReturn = valueAsIntervalDS.Value;
                            }
                            break;
                        case "Oracle.DataAccess.Types.OracleIntervalYM":
                            OracleIntervalYM valueAsIntervalYM = (OracleIntervalYM)parameter.Value;
                            if (!valueAsIntervalYM.IsNull)
                            {
                                toReturn = valueAsIntervalYM.Value;
                            }
                            break;
                        case "Oracle.DataAccess.Types.OracleString":
                            OracleString valueAsString = (OracleString)parameter.Value;
                            if (!valueAsString.IsNull)
                            {
                                toReturn = valueAsString.Value;
                            }
                            break;
                        case "Oracle.DataAccess.Types.OracleTimeStamp":
                            OracleTimeStamp valueAsTimeStamp = (OracleTimeStamp)parameter.Value;
                            if (!valueAsTimeStamp.IsNull)
                            {
                                toReturn = valueAsTimeStamp.Value;
                            }
                            break;
                        case "Oracle.DataAccess.Types.OracleTimeStampLTZ":
                            OracleTimeStampLTZ valueAsTimeStampLTZ = (OracleTimeStampLTZ)parameter.Value;
                            if (!valueAsTimeStampLTZ.IsNull)
                            {
                                toReturn = valueAsTimeStampLTZ.Value;
                            }
                            break;
                        case "Oracle.DataAccess.Types.OracleTimeStampTZ":
                            OracleTimeStampTZ valueAsTimeStampTZ = (OracleTimeStampTZ)parameter.Value;
                            if (!valueAsTimeStampTZ.IsNull)
                            {
                                toReturn = valueAsTimeStampTZ.Value;
                            }
                            break;
                        default:
                            toReturn = parameter.Value;
                            break;
                    }
                }
                if (toReturn == null)
                {
                    toReturn = System.DBNull.Value;
                }
                return toReturn;
            }
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 24-Jan-2019 10:52:57   

Looks good. the amount of code is sadly indeed a bit massive, in v5.5 we have wrapped things up in classes inside the framework which indeed don't give much extension points so you have to go this route.

Frans Bouma | Lead developer LLBLGen Pro