Solved

SQL Connection Timeout

Posted on 2004-04-07
3
6,223 Views
Last Modified: 2008-03-03
I am setting the timeout in a connection string to be 420 seconds but for some reason it still times out at 30, I've run the query in analyzer and it's all good, in the example below i set the timeout to 0 which should be infinite
Here's the code

            public DataAccess(string psUser, string psPassword, string psServer, string psDB)
            {
                  mstrConnection = "Network Library=DBMSSOCN;Data Source=" + psServer + ";Initial Catalog=" + psDB + ";User ID=" + psUser + ";Password=" + psPassword + ";Connection Timeout = 0";
                  
            }

            public DataTable ExportShorts()
            {
                  System.Diagnostics.Debug.WriteLine(mstrConnection);
                  
                  SqlConnection conn = new SqlConnection(mstrConnection);
                  System.Diagnostics.Debug.WriteLine(conn.ConnectionTimeout);
                  DataSet ds = new DataSet();
                  DataTable dt = new DataTable();
                  ds=SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "ShortPositionAges");
                  dt=ds.Tables[0];
                  return dt;
            }

Thanks
0
Comment
Question by:painlessprod
  • 2
3 Comments
 

Author Comment

by:painlessprod
ID: 10778893
Sorry I'm also using sql helper if that has anything to do with it, but I don't see why it should.
0
 
LVL 3

Accepted Solution

by:
gillit earned 125 total points
ID: 10779015
I have had trouble with this before, although I am not very familiar with SqlHelper. But the following is what I used.

SqlCommand myCommand = new SqlCommand();
myCommand.Connection = new SqlConnection(strConnection);
myCommand.Connection.Open();
myCommand.CommandText = strSql;         // *******************1
myCommand.CommandType = CommandType.Text;  //***************2
myCommand.CommandTimeout = 120;

SqlDataAdapter objDataAdapter = new SqlDataAdapter(myCommand);

objDataAdapter.Fill(dt);


*****************

You probably would need to change 1 and 2 with the following:
myCommand.CommandText = "ShortPositionAges";
myCommand.CommandType = CommandType.StoredProcedure;


**************

Hope that helps.
0
 

Author Comment

by:painlessprod
ID: 10779246
Well that worked.

Upon doing further research I found that SQLHelper doesn't allow a command timeout, it has a connection timeout but not a command timeout.  So SQLHelper is useless for large stored procedures.  Otherwise SQLHelper is a very useful tool.

Thanks gillit
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now