?
Solved

How to set TimeOut of ObjectDataSource Command - c#

Posted on 2009-04-18
8
Medium Priority
?
3,077 Views
Last Modified: 2013-11-12
In Visual Studio 2008, C# running with an ObjectDataSource, how can I set the Command TimeOut as I have an SQL query that is taking a long time and causing errors with the default timeout.
I have tried the connection timeout in the sql connection string but he command is still timing out.
0
Comment
Question by:andrewmilner
  • 4
  • 3
8 Comments
 
LVL 14

Expert Comment

by:amar31282
ID: 24177555
you have to change timeout in SQL server and in C# part.

in C#

Con.timeOut=value;

and for sql refer to this link

http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_24214585.html
0
 
LVL 12

Accepted Solution

by:
wht1986 earned 2000 total points
ID: 24177573
if your object data source is the autogenerated Table adapter, create a partial class of the table adapter and add a public property to set/get the command timeout value
namespace DataSet1TableAdapters
{
    public partial class TestTableTableAdapter
    {
        public int CommandTimeout
        {
            get { return this._commandCollection[0].CommandTimeout; }
            set
            {
                for (int i = 0; i < this._commandCollection.Length; i++)
                    this._commandCollection[i].CommandTimeout = value;
            }
        }
    }
}
 
Then can be called like:
DataSet1TableAdapters.TestTableTableAdapter adpt = new DataSet1TableAdapters.TestTableTableAdapter();
adpt.CommandTimeout = 200;

Open in new window

0
 
LVL 12

Expert Comment

by:wht1986
ID: 24177582
bah hit submit too soon, the command timeout can not be set on the generated dataset adapters because it makes the adapters as private.  If you wrote your own ObjectDataSource its even easier, something like the following  (sorry for the VB
<DataObjectMethod(DataObjectMethodType.Delete)> _
public bool DeleteEmployee(int EID)
{
SqlConnection conn = New SqlConnection(_connectionString);
SqlCommand cmd = New SqlCommand("my sql syntax", conn)
cmd.CommandTimeout = 20
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EID
...
}

Open in new window

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:andrewmilner
ID: 24178609
How/where would I enter this partial class for the table adaptor?
I have an xsd file containing the autogenerated tableadaptors and XML behind that.
0
 
LVL 12

Expert Comment

by:wht1986
ID: 24180060
You create a new class file for it. Just add the correct namespace and class name for your table adapter.
0
 

Author Comment

by:andrewmilner
ID: 24180190
I'm pulling my hair out with this now.  It just won't work.

I've tried the above code suggestion but couldnt get it to work.  It compiled fine but just makes no difference.

I now have the code below which is kind of the same anyway.
I have this in a new Class1.cs file with the same namespace etc as my TableAdaptor.

Then i'm calling / setting the timeout from my presentation aspx.cs page. by running the code below on the ObjectDataSource1_ObjectCreated call.
I know it's calling because I put some response.write in there and it spat it out.

It seems to me that the data is being fetched before the Timeout has been set.

Any ideas?
I can't beleive how much trouble this is causing for something that should be so simple.


//Class1.cs
namespace DataSet1TableAdapters
{
    public partial class ReplenReportsTableAdapter
    {
        public void SetCommandTimeout(int timeout)
        {
            for (int i = 0; i < this.CommandCollection.Length; i++)
                if (this.CommandCollection[i] != null)
                    this.CommandCollection[i].CommandTimeout = timeout;
        }
    }
}
 
//Pres ASPX CS page
 
protected void ObjectDataSource1_ObjectCreated(object sender, ObjectDataSourceEventArgs e)
    {
        if (e.ObjectInstance != null)
        {
            Response.Write("not null");
            DataSet1TableAdapters.ReplenReportsTableAdapter adapter = new DataSet1TableAdapters.ReplenReportsTableAdapter();
            adapter.SetCommandTimeout(0);
        }
    }

Open in new window

0
 
LVL 12

Expert Comment

by:wht1986
ID: 24181420
Dont create a new instance of the table adapter. the arguement of the method contains the instance it will be using

    protected void ObjectDataSource1_ObjectCreated(object sender, ObjectDataSourceEventArgs e)
    {
        DataSet1TableAdapters.TestTableTableAdapter adpt = (DataSet1TableAdapters.TestTableTableAdapter)e.ObjectInstance;
        adpt.CommandTimeout = 0;
    }

Open in new window

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe how to setup a Cisco WLC 5508 to work with Apple's Bonjour protocol across VLANs.  I will also discuss using screen mirroring and Airplay on an AppleTV v3.  This article covers the wireless network only and requires m…
Working settings for French ISP Orange "Prêt à Surfer" SIM cards for data connections only. Can't be found anywhere else !
This Micro Tutorial will show you how to maximize your wireless card to its maximum capability. This will be demonstrated using Intel(R) Centrino(R) Wireless-N 2230 wireless card on Windows 8 operating system.
Viewers will learn how to connect to a wireless network using the network security key. They will also learn how to access the IP address and DNS server for connections that must be done manually. After setting up a router, find the network security…
Suggested Courses
Course of the Month17 days, 12 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question