-4

I'm new to C# and SQL Server; I wrote a simple stored procedure in T-SQL, and in C# with under code call that:

da = new SqlDataAdapter("select_equals_Cycle", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
ds = new DataSet(); 
da.Fill(ds, "select_equals_Cycle");

but on this line:

da.Fill(ds, "select_equals_Cycle");

I get this error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

My connection string this:

string conn = "Data Source=.;Initial Catalog=ClubEatc;Integrated Security=True;Connect Timeout=30;";

How can I solve that? thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
behzad razzaqi
  • 11
  • 6
  • 6
    You need to find the root cause of the timeout. Is it because you're trying to load a gazillion gigabytes from the database? Are you waiting for a lock? Do you have a really slow network? Do you *have* a network? You can always just increase the timeout but unless you have a grasp on the why it won't really be a fix, more like a bandaid. – Lasse V. Karlsen Sep 21 '16 at 09:27
  • Well if all else fails you can increase the timeout in the connection string - if the procedure runs that long - however if a stored procedure runs more than 30 seconds to fill a data set, either the procedure is rather inefficient or the server isn't set up for the load you need. – Adwaenyth Sep 21 '16 at 09:30
  • Try the steps in this link http://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation – Prasanth V J Sep 21 '16 at 09:38

2 Answers2

0

Use CommandTimeout or optimize the StoredProcedure 

da.SelectCommand.CommandTimeout = 180; // default is 30 seconds
BrunoMartinsPro
  • 1,646
  • 1
  • 24
  • 48
0

Don't set the timeout value, if you don't know how to configure it.

Microsoft make the optional has well defined value.

the Timeout question has two items you have to check out.

Database: e.g. MSSQL

--the code below ran in Query of Sql Server 
--this code snippet will show you all the advance options
Exec sp_configure 'show advanced options',1
recogfigure
-- this code snippet configure the seconds of the query wait 
-- this configuration means that if the  memory couldn't used to query -- the big query, sql server will wait for some seconds
-- this option usually use the default value made by Microsoft
-- the default value about how many seconds of formula is the estimated query -- -- time multiply by 25, and if the memory still couldn't used for querying.
-- then throw TimeOut Exception
Exec sp_configure 'query wait', 200;
ReCONFIGURE
-- the end

// the timeout configuration about C#
SqlConnection.ConnectionTimeout = 200
// here is the Document:
// get the time to wait while trying to establish a connection before 
// terminating the attempt and generating  and error 
// the Unit of the property is second
// 0  is no limit

after you scan the code snippet, you will find two reason can cause the exception.

  1. your code couldn't establish the connection.

  2. your memory in the machine install sql server couldn't used to run the big query

Potato
  • 491
  • 1
  • 4
  • 13