title: Time out expired probleme sometime
body: <p>I use C#, .NET Framework 4.0 and SQL Server 2008 R2 Express, and I wrote a Winforms program with it.</p>
  
  <p>I got this message:</p>
  
  <blockquote>
    <p>The timeout period elapsed prior to completion of the operation or the server is not responding.</p>
  </blockquote>
  
  <p>I searched it and found some hint, like</p>
  
  <p><a href="https://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation">hint 1</a></p>
  
  <p>Facts:</p>
  
  <ul>
  <li>I did close my database connection</li>
  <li>I have set the timeout = 0</li>
  <li>I did apply the corrective framework <a href="https://support.microsoft.com/fr-fr/kb/2605597" rel="nofollow noreferrer">microsoft correctif framework</a></li>
  </ul>
  
  <p>Here is my connection class (Data Layer):</p>
  
  <pre><code>class Connexion : IDisposable 
  {
          public SqlConnection conn;
  
          public SqlConnection GetConnected()
          {
              try
              {
                  //String strConnectionString = ConfigurationManager.ConnectionStrings["Soft8Exp_ClientConnStr"].ConnectionString;
                  string strConnectionString = Properties.Settings.Default.Soft8Exp_ClientConnStr;
                  conn = new SqlConnection(strConnectionString);
              }
              catch (Exception excThrown)
              {
                  conn = null;
                  throw new Exception(excThrown.InnerException.Message, excThrown);
              }
  
              // Ouverture et restitution de la connexion en cours
              if (conn.State == ConnectionState.Closed) conn.Open();
              return conn;
          }
  
          public Boolean IsConnected
          {
              get { return (conn != null) &amp;&amp; (conn.State != ConnectionState.Closed) &amp;&amp; (conn.State != ConnectionState.Broken); }
          }
  
          public void CloseConnection()
          {
              // Libération de la connexion si elle existe
              if (IsConnected)
              {
                  conn.Close();
                  conn = null;
              }
          }
  
          public void Dispose()
          {
              CloseConnection();
          }
  }
  </code></pre>
  
  <p>and I use it like this (Business Layer):</p>
  
  <pre><code>public string Cloturer_Ordre_Unitaire_Agence(string CHAUFFEUR, string ClotList, DateTime Enlev, DateTime ClotReel, string sql_Syntax, string Unitaire, string NomAgence)
  {
              try
              {
                  using (var connectionWrapper = new Connexion())
                  {
                      var connectedConnection = connectionWrapper.GetConnected();
  
                      SqlCommand comm_Command = new SqlCommand(sql_Syntax, connectionWrapper.conn);
                      comm_Command.CommandTimeout = 0;
                      comm_Command.Parameters.AddWithValue("@CHAUFFEUR", CHAUFFEUR);
                      comm_Command.Parameters.AddWithValue("@Trans", ClotList);
                      comm_Command.Parameters.AddWithValue("@DATE_CLOTUR_REEL", ClotReel);
                      comm_Command.Parameters.AddWithValue("@DateEnlev", Enlev);
                      comm_Command.Parameters.AddWithValue("@Unitaire", Unitaire);
                      comm_Command.Parameters.AddWithValue("@AGENCE", NomAgence);
                      string IdOrdre = "";
                      SqlDataReader readerOne = comm_Command.ExecuteReader();
  
                      while (readerOne.Read())
                      {
                          if (IdOrdre != "") IdOrdre += ",";
                          IdOrdre += readerOne["NO_ORDRE"].ToString();
                      }
  
                      return IdOrdre;
                  }
              }
              catch (Exception excThrown)
              {
                  throw new Exception(excThrown.Message);
              }
  }
  </code></pre>
  
  <p>but after all I did, why do I still SOMETIMES get a timeout problem?</p>
  
  <p>Do I have to:</p>
  
  <ul>
  <li>delete the <code>.ldf</code> file because it to big</li>
  <li>index my table</li>
  <li>tune my SQL syntax</li>
  </ul>
  
  <p>should i tune this sql:</p>
  
  <pre><code>DECLARE @AfterUpdate table (
      NO_ORDRE_Temp int NOT NULL, 
      CODE_DEST_Temp varchar(50) NOT NULL,
      DATE_CLOTUR_Temp datetime NOT NULL, 
      FA_CLIENT_Temp varchar(20) NOT NULL,
      CODE_CLIENT_Temp varchar(20) NOT NULL,
      MODAL_MODE_Temp char(1) NOT NULL,
      MODAL_PORT_Temp bit NOT NULL,
      LIVRS_EXPRS_Temp bit NOT NULL,
      ENLEV_UNITE_Temp int NOT NULL,
      NBR_COLIS_Temp int NOT NULL,
      POID_Temp decimal(10, 2) NOT NULL,
      ENLEV_CREMB_Temp decimal(10, 2)  NULL,
      ENLEV_DECL_Temp decimal(10, 2)  NULL,
      TRANSPORTEUR_Temp varchar(5) NOT NULL,
      CODE_MAG_Temp varchar(50) NOT NULL,
      FACTURATION_Temp int NOT NULL,
      LIVRS_SIGN_Temp bit NOT NULL,
      LIVRS_SAMD_Temp bit NOT NULL,
      OBS_Temp varchar(max) NOT NULL,
      AGENCE_Temp varchar(max) NOT NULL,
      LIB_TOURNE_Temp varchar(50) NOT NULL,
      DATE_CLOTUR_REEL_Temp datetime NOT NULL,
      ADRESSE_Temp varchar(500) NOT NULL,
      REF_EXPED_Temp varchar(20) NOT NULL
    )  
  
  UPDATE ORDRE 
  SET STATUT_ORDRE = 2,  CHAUFFEUR = @CHAUFFEUR, FACTURATION= @CHAUFFEUR, DATE_CLOTUR_REEL = @DATE_CLOTUR_REEL
  OUTPUT inserted.NO_ORDRE, inserted.CODE_DEST, inserted.DATE_CLOTUR, inserted.FA_CLIENT, inserted.CODE_CLIENT, inserted.MODAL_MODE, inserted.MODAL_PORT,inserted.LIVRS_EXPRS,inserted.ENLEV_UNITE,  inserted.NBR_COLIS,inserted.POID,inserted.ENLEV_CREMB,inserted.ENLEV_DECL, inserted.TRANSPORTEUR, inserted.CODE_MAG, inserted.FACTURATION, inserted.LIVRS_SIGN, inserted.LIVRS_SAMD, inserted.OBS, inserted.AGENCE, inserted.LIB_TOURNE, inserted.DATE_CLOTUR_REEL, inserted.ADRESSE, inserted.REF_EXPED INTO @AfterUpdate
  WHERE STATUT_ORDRE = 1 AND (TRANSPORTEUR IN (SELECT ParsedString From dbo.ParseStringList(@Trans))) 
  
  INSERT INTO LETTRE_VOIT 
  
  OUTPUT inserted.NO_ORDRE
  
  select rsVoit.NOID, NO_ORDRE_Temp, rsOrdre.CODE_DEST_Temp,rsOrdre.DATE_CLOTUR_Temp,rsOrdre.FA_CLIENT_Temp, rsOrdre.CODE_CLIENT_Temp, rsOrdre.MODAL_MODE_Temp, rsOrdre.MODAL_PORT_Temp,
  CASE rsOrdre.MODAL_PORT_Temp
              WHEN 'false' THEN N'Pay'+CHAR(233)
              ELSE 'Du'
          END, 
  rsOrdre.LIVRS_EXPRS_Temp,
  CASE rsOrdre.LIVRS_EXPRS_Temp
              WHEN 'false' THEN 'Express'
              ELSE 'Long'
          END, 
    rsOrdre.ENLEV_UNITE_Temp, LIBELLE, NBR_COLIS_Temp,POID_Temp,ENLEV_CREMB_Temp,ENLEV_DECL_Temp, TRANSPORTEUR_Temp, CODE_MAG_Temp, FACTURATION_Temp, LIVRS_SIGN_Temp, LIVRS_SAMD_Temp, OBS_Temp, AGENCE_Temp,LIB_TOURNE_Temp,DATE_CLOTUR_REEL_Temp,REF_EXPED_Temp
  from @AfterUpdate rsOrdre
  inner join
  (
    select CODE_DEST,MODAL_MODE, MODAL_PORT, LIVRS_EXPRS,ENLEV_UNITE,AGENCE,CODE_CLIENT,ADRESSE, ROW_NUMBER() over (order by CODE_DEST) as NOID
    from ORDRE
    group by CODE_DEST,MODAL_MODE,MODAL_PORT,LIVRS_EXPRS,ENLEV_UNITE,AGENCE,CODE_CLIENT,ADRESSE
  ) rsVoit on rsVoit.CODE_DEST = rsOrdre.CODE_DEST_Temp and rsVoit.MODAL_MODE = rsOrdre.MODAL_MODE_Temp
  and rsVoit.MODAL_PORT = rsOrdre.MODAL_PORT_Temp and rsVoit.LIVRS_EXPRS = rsOrdre.LIVRS_EXPRS_Temp
  and rsVoit.ENLEV_UNITE = rsOrdre.ENLEV_UNITE_Temp and rsVoit.AGENCE = rsOrdre.AGENCE_Temp
   and rsVoit.CODE_CLIENT = rsOrdre.CODE_CLIENT_Temp and rsVoit.ADRESSE = rsOrdre.ADRESSE_Temp
  
  LEFT JOIN T_UNITE ON rsOrdre.ENLEV_UNITE_Temp = T_UNITE.NOID
  
  
  order by rsVoit.NOID, NO_ORDRE_Temp
  </code></pre>
  
  <p>another SQL:</p>
  
  <pre><code>SELECT *, 
  --FAWEB_CLIENT.RS_NOM AS RS_NOM_EXP,
  --FAWEB_CLIENT.ADRESSE AS ADDR_EXP,
  --FAWEB_CLIENT.CP AS CP_EXP,
  --FAWEB_CLIENT.VILLE AS VILLE_EXP,
  --FAWEB_CLIENT.TEL AS TEL_EXP,
  --FAWEB_CLIENT.FAX AS FAX_EXP,
  
  AGENCE.NOM AS RS_NOM_EXP,
  AGENCE.ADRESSE AS ADDR_EXP,
  AGENCE.CP AS CP_EXP,
  AGENCE.VILLE AS VILLE_EXP,
  AGENCE.TELP AS TEL_EXP,
  '' AS FAX_EXP,
  
  
  ORDRE.RS_NOM AS RS_NOM_DEST,
  ORDRE.ADRESSE AS ADDR_DEST,
  ORDRE.CP AS CP_DEST,
  ORDRE.INSEE AS INSEE_DEST,
  ORDRE.VILLE AS VILLE_DEST,
  ORDRE.TEL AS TEL_DEST,
  ORDRE.FAX AS FAX_DEST
  
  FROM LETTRE_VOIT 
  LEFT JOIN AGENCE ON AGENCE.NOM = LETTRE_VOIT.LIB_AGENCE AND AGENCE.NO_CLIENT = LETTRE_VOIT.CODE_CLIENT
  --LEFT JOIN FAWEB_CLIENT ON FAWEB_CLIENT.CODE_CLIENT = LETTRE_VOIT.CODE_CLIENT
  LEFT JOIN ORDRE ON ORDRE.CODE_DEST = LETTRE_VOIT.CODE_DEST AND ORDRE.CODE_CLIENT = LETTRE_VOIT.CODE_CLIENT
             AND ORDRE.NO_ORDRE = LETTRE_VOIT.NO_ORDRE
  WHERE  (LETTRE_VOIT.NO_ORDRE IN (SELECT ParsedString From dbo.ParseStringList(@Trans)))
  
  --AND ORDRE.DATE_CLOTUR = LETTRE_VOIT.DATE_CLOTURE
  </code></pre>