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);

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

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.

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

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;
            }

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.