0

I am using ADODB interop DLL Version :6.0.0.0 to connect any type of database.

CommandTimeout is 3 seconds Opening the recordset with command object with timeout of 3 seconds.

With Oracle database with huge data, when I enter select query, itdoes not time out.

How to timeout the executing query?

I have tried to set command timeout to 1 second as well but still its not getting timed out.

Set connection timeout to be 3 seconds (I know this is different than command time out but still tried)

**In MSSQl database, in stored proc if i insert 10 lack records and have select query after that, it actually gives timeout while executing the proc, but if I have only the select query in proc as well, it will not time out.

public DataTable ExecuteQuery(string commandText, int timeout = 3) {
    if (connection.State.Equals(connected))
    {
        var command = createCommand(commandText, CommandTypeEnum.adCmdText, 3);                     

        var recordset = createRecordset();  recordset.Open(command);

        command = null;     return dataTable; 
    } }

private Command createCommand(string commandText, CommandTypeEnum  commandType, int timeout = 3) {
    var command = new ADODB.Command
    {
        ActiveConnection = connection,
        CommandText = commandText,
        CommandType = commandType,
        CommandTimeout = timeout
    };

    return command; }

private Recordset createRecordset() {
    return new Recordset
    {
        CursorType = CursorTypeEnum.adOpenStatic,
        CursorLocation = CursorLocationEnum.adUseClient,
        LockType = LockTypeEnum.adLockReadOnly,
    }; }

I expect to have error of timed out when query executes more than 3 seconds.

Matthijs
  • 2,483
  • 5
  • 22
  • 33
  • Probably you are not getting timeout because the command has not timed out. Once the command has begun returning data, the timeout no longer applies. – Ben May 29 '19 at 07:06
  • Hi Ben, Thanks for the quick rep. Can you tell me the scenario, how can i create a command timeout when command has not begun the returning the data. Can you tell me the simple query which takes long time to execute ? – user2678302 May 29 '19 at 09:18
  • Use the `DBMS_SESSION.SLEEP` procedure: https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_SESSION.html#GUID-27CCC2F7-6564-41D2-8C42-CFFBF25A69B5 – Ben May 29 '19 at 09:30
  • Ben how can i create a command timeout when command has not begun the returning the data. – user2678302 May 29 '19 at 10:14
  • What do you mean by "create a command timeout"? Do you mean "demonstrate that the timeout has taken effect", or do you mean "set a timeout"? – Ben May 29 '19 at 10:21
  • demonstrate a timeout taken effect – user2678302 May 29 '19 at 11:11
  • Use the `DBMS_SESSION.SLEEP` procedure as I said above. If the timeout is 30 seconds, tell it to sleep for 60 seconds. It should timeout after 30 seconds. – Ben May 29 '19 at 12:46

0 Answers0