Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Custom Templates> Self Servicing - Oracle CommandTimeOut Per Call
 

Pages: 1
Custom Templates
Self Servicing - Oracle CommandTimeOut Per Call
Page:1/1 

  Print all messages in this thread  
Poster Message
sapul
User



Location:
Turkey
Joined on:
11-Jan-2019 15:10:43
Posted:
42 posts
# 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);
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14498 posts
# 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.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37561 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
sapul
User



Location:
Turkey
Joined on:
11-Jan-2019 15:10:43
Posted:
42 posts
# 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 Regular Smiley

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

CommonDaoBase.cs
Code:
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
Code:
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;
            }





  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37561 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.