how to increase command time for SQL command with datasets and tableAdapter c#

Question

I am trying to set (programmatically or not) the command timeout in a specified dataset (A), right now I have my MVC model, I have created several datasets (A, B, C... N) for different stored procedures, I only require to extend this time for one dataset (A... again -.-), in addition I have seen different questions related but none has solved my problem. I show you my code below:

 private static DAL.CopyCoinDataFromOracle.CoinDataTableAdapters.spCopyCoinDataTableAdapter cdOracle =
        new DAL.CopyCoinDataFromOracle.CoinDataTableAdapters.spCopyCoinDataTableAdapter();

public static bool CopyInformationFromOracle()
    {            
        bool? statusMethod = false;             
        cdOracle.CopyCoinDataFromOracle(ref statusMethod); //This is where I have the problem and the exception is thrown after 30 seconds are reached
        return Convert.ToBoolean(statusMethod);
    }

Just to be clear, "cdOracle" is my table adapter and my stored procedure is linked to "CopyCoinDataFromOracle" method, that query requires for the first time 60seg of execution, that´s why i want to increase that time (by default 30 seg.), the information is copied from a linked service with oracle to SQL Server Any help you can give will be appreciated.

Thanks in advance.

Check this out - http://stackoverflow.com/questions/1192171/how-can-i-change-the-table-adapters-command-timeout — rageit, Mar 22 '17 at 03:28
Thanks @regeit it help me a lot, although it´s a bit confusing, things like correct namespace and where to put that code, but after a while reading and experimenting i finally made it. — Diego Osornio, Mar 22 '17 at 15:23

score 0 · Accepted Answer · edited May 23 '17 at 10:30

I finally solved my problem, it was thanks to this related question in stackoverflow, link was provided by @regeit, and here´s my solution:

I create this class as post says (but don´t gives detailed explanation of where !) with the following methods:

using System;
using System.Data.SqlClient;
using System.Reflection;

namespace DAL.CopyCoinDataFromOracle.CoinDataTableAdapters
{    
    partial class spCopyCoinDataTableAdapter : System.ComponentModel.Component
    {
        public spCopyCoinDataTableAdapter(int Timeout)
        {            
            SetCommandTimeout(Timeout);
        }

        public void SetCommandTimeout(int Timeout)
        {
            foreach (var item in SelectCommand())
            {
                item.CommandTimeout = Timeout;                
            }
        }

        private System.Data.SqlClient.SqlConnection GetConnection()
        {
             return GetProperty("Connection") as      
             System.Data.SqlClient.SqlConnection;
        }

        private SqlCommand[] SelectCommand()
        {
             return GetProperty("CommandCollection") as SqlCommand[];
        }

        private Object GetProperty(String s)
        {
             return this.GetType().GetProperty(s, BindingFlags.NonPublic | BindingFlags.GetProperty | BindingFlags.Instance).GetValue(this, null);
        }
    }
}

"DAL" is my class library, "CopyCoinDataFromOracle" is a folder which contains my dataset and "CoinDataTableAdapter" is the name of namespace, now I put this code in the code of my tableadapter named "CopyCoinData.xsd" (just double clic and erase auto-generated code to create this).

Finally how to use it:

private static DAL.CopyCoinDataFromOracle.CoinDataTableAdapters.spCopyCoinDataTableAdapter cdOracle =
            new DAL.CopyCoinDataFromOracle.CoinDataTableAdapters.spCopyCoinDataTableAdapter(80);

Where 80 represents the timeout that will be set in the constructor of the object, please let´me know for further questions.

Hope to help.

how to increase command time for SQL command with datasets and tableAdapter c#

1 Answers1