title: Time out exception occurs after calling a Stored Procedure in C#
body: <p>I have developed a Windows Forms application using C#. Once running this, allows us to browse an Excel file and fills the records from that file into a <code>DataTable</code>.</p>
  
  <p>I am using an <code>OleDbConnection</code> to perform this action. Using a connection string, performing a bulk copy of this <code>DataTable</code> to a table inside the database.</p>
  
  <p>Total no. of records in the Excel file is 3,27,761</p>
  
  <p>Excel bulk export to <code>DataTable</code>:</p>
  
  <pre><code>DataTable dtAll = new DataTable();
  string connString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                      "Data Source=" + FilePath1 + ";Extended Properties=Excel 12.0;";
  OleDbConnection conn = new OleDbConnection(connString);
  conn.Open();
  OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [Raw Data$]", conn);
  sheetAdapter.Fill(dtAll);
  conn.Close();
  </code></pre>
  
  <p>Bulk copy the <code>DataTable</code> and save it to SQL Server database table <code>SourceTable</code>:</p>
  
  <pre><code>con.Open();
  using (SqlBulkCopy copy = new SqlBulkCopy(con))
  {
    copy.ColumnMappings.Add(0, 0);
    copy.ColumnMappings.Add(1, 1);
    copy.ColumnMappings.Add(2, 2);
    copy.ColumnMappings.Add(3, 3);
    copy.ColumnMappings.Add(4, 4);
    copy.ColumnMappings.Add(5, 5);
    copy.DestinationTableName = "SourceTable";
    copy.WriteToServer(dtAll);
  }
  </code></pre>
  
  <p>Now, the total no. of records in <code>SourceTable</code> is 3,27,761 </p>
  
  <p>I am calling a stored procedure <code>SP_InsertToTargetTable</code>.</p>
  
  <pre><code>SqlConnection con = new SqlConnection("server=(local);database=CN-DataCleansing;integrated security=true; Connect Timeout=300");
  SqlCommand cmd = new SqlCommand("SP_InsertToSourceTable1", con);
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.CommandTimeout = 300;
  cmd.ExecuteNonQuery();
  </code></pre>
  
  <p>After execution, application throws the following exception.</p>
  
  <blockquote>
    <p>Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.</p>
  </blockquote>
  
  <p>The stored procedure written inside the DB:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[SP_InsertToTargetTable]
  as
  begin
     declare @e1 nvarchar(50)
     declare @fn1 nvarchar(50)
     declare @ln1 nvarchar(50)
     declare @ln1Lenght int
     declare @ph1 nvarchar(50)
     declare @t1 nvarchar(50)
     declare @c1 nvarchar(50)
     declare @lnfn1 nvarchar(max)
  
     declare @ContactStatus nvarchar(50)
     set @ContactStatus = null
  
     declare @PhoneStatus nvarchar(50)
     set @PhoneStatus = null
  
     declare @Notes nvarchar(50)
     set @Notes = null
  
     declare @splitLN1 nvarchar(1)
     declare @splitLN2 nvarchar(1)
     declare @splitLN3 nvarchar(1)
     declare @splitLN4 nvarchar(1)
     declare @splitLN5 nvarchar(1)
  
     declare @PhNochecked nvarchar(1)
     declare @CountryCode nvarchar(3) = '+86'
     declare @CityCode nvarchar(3)
     declare @Phone1 nvarchar(4)
     declare @Phone2 nvarchar(4)
     declare @PhoneWOSpace nvarchar(50)
     declare @finalPhNo nvarchar(18)
  
     declare @EMailNew nvarchar(50)                 
  
     DECLARE @EMail CURSOR                          
  
    SET @EMail = CURSOR FOR 
        SELECT [Email Address] FROM SourceTable1                 
  
    OPEN @EMail 
  
    FETCH NEXT FROM @EMail INTO @EMailNew                          
  
    WHILE @@FETCH_STATUS = 0                          
    BEGIN                          
          set @ContactStatus = null
          set @PhoneStatus = null
          set @Notes = null
  
          -- Select each row --     
          SELECT @e1 = [Email Address], @fn1 = [First Name], @ln1 = [Last Name], @ln1Lenght = len([Last Name]), 
          @ph1 = [Business Phone], @t1 = Title, @c1 = City, @lnfn1 = ([Last Name] + ' ' + [First Name])
          FROM SourceTable1 where [Email Address]=@EMailNew
          -- End Select each row --
  
          -- First name is null and Last name length = 3 --
          if @ln1Lenght = 3 and LEN(@fn1) = 0
          begin
              set @splitLN1=substring(@ln1,1,1)
              set @splitLN2=substring(@ln1,2,1)
              set @splitLN3=substring(@ln1,3,1)
  
              set @fn1=@splitLN1
              set @ln1=@splitLN2 + @splitLN3
  
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
          -- END criteria-1 --
  
          -- First name is null and Last name length = 4 --
          else if @ln1Lenght = 4 and LEN(@fn1) = 0
          begin
              --set @Criteria123 = 2
              set @splitLN1=substring(@ln1,1,1)
              set @splitLN2=substring(@ln1,2,1)
              set @splitLN3=substring(@ln1,3,1)
              set @splitLN4=substring(@ln1,4,1)
  
              set @fn1=@splitLN1 + @splitLN2
              set @ln1=@splitLN3 + @splitLN4
  
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
          -- END criteria-2 --
  
          -- First name is null and Last name length = 5 --
          else if @ln1Lenght = 5 and LEN(@fn1) = 0
          begin
              --set @Criteria123 = 3
              set @splitLN1=substring(@ln1,1,1)
              set @splitLN2=substring(@ln1,2,1)
              set @splitLN3=substring(@ln1,3,1)
              set @splitLN4=substring(@ln1,4,1)
              set @splitLN5=substring(@ln1,5,1)
  
              set @fn1=@splitLN1 + @splitLN2
              set @ln1=@splitLN3 + @splitLN4 + @splitLN5
  
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
          -- END criteria-3 --
  
          -- Last name is null and First name length = 3 --
          else if @ln1Lenght = 0 and LEN(@fn1) = 3
          begin
              --set @Criteria123 = 1
              set @splitLN1=substring(@fn1,1,1)
              set @splitLN2=substring(@fn1,2,1)
              set @splitLN3=substring(@fn1,3,1)
  
              set @fn1=@splitLN1
              set @ln1=@splitLN2 + @splitLN3
  
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
          -- END criteria-4 --
  
          -- Last name is null and First name length = 4 --
          else if @ln1Lenght = 0 and LEN(@fn1) = 4
          begin
              --set @Criteria123 = 2
              set @splitLN1=substring(@fn1,1,1)
              set @splitLN2=substring(@fn1,2,1)
              set @splitLN3=substring(@fn1,3,1)
              set @splitLN4=substring(@fn1,4,1)
  
              set @fn1=@splitLN1 + @splitLN2
              set @ln1=@splitLN3 + @splitLN4
  
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
          -- END criteria-5 --
  
          -- Last name is null and First name length = 5 --
          else if @ln1Lenght = 0 and LEN(@fn1) = 5
          begin
              --set @Criteria123 = 3
              set @splitLN1=substring(@fn1,1,1)
              set @splitLN2=substring(@fn1,2,1)
              set @splitLN3=substring(@fn1,3,1)
              set @splitLN4=substring(@fn1,4,1)
              set @splitLN5=substring(@fn1,5,1)
  
              set @fn1=@splitLN1 + @splitLN2
              set @ln1=@splitLN3 + @splitLN4 + @splitLN5
  
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
          -- END criteria-6 --
  
          -- First Name or Last name containing 小姐 --
  
          declare @testvar nvarchar(50)
          if CHARINDEX(N'小姐',@fn1) &gt; 0
          begin
              set @testvar=@fn1
              SELECT @fn1 = REPLACE(@testvar,N'小姐','');
              set @t1=N'小姐'
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
          else if CHARINDEX(N'小姐',@ln1) &gt; 0
          begin
              set @testvar=@ln1
              SELECT @ln1 = REPLACE(@testvar,N'小姐','');
              set @t1=N'小姐'
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
  
          -- END First Name or Last name containing 小姐 --
  
          -- First Name or Last name containing 先生 --
  
          else if CHARINDEX(N'先生',@fn1) &gt; 0
          begin
              set @testvar=@fn1
              SELECT @fn1 = REPLACE(@testvar,N'先生','');
              set @t1=N'先生'
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
          else if CHARINDEX(N'先生',@ln1) &gt; 0
          begin
              set @testvar=@ln1
              SELECT @ln1 = REPLACE(@testvar,N'先生','')
              set @t1=N'先生'
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
  
          -- END First Name or Last name containing 先生 --
  
          -- First Name or Last name containing 经理 --
  
          else if CHARINDEX(N'经理',@fn1) &gt; 0
          begin
              set @testvar=@fn1
              SELECT @fn1 = REPLACE(@testvar,N'经理','')
              set @t1=N'经理'
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
          else if CHARINDEX(N'经理',@ln1) &gt; 0
          begin
              set @testvar=@ln1
              SELECT @ln1 = REPLACE(@testvar,N'经理','')
              set @t1=N'经理'
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
  
          -- END First Name or Last name containing 经理 --
  
          -- First Name or Last name containing 女士 --
  
          else if CHARINDEX(N'女士',@fn1) &gt; 0
          begin
              set @testvar=@fn1
              SELECT @fn1 = REPLACE(@testvar,N'女士','')
              set @t1=N'女士'
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
          else if CHARINDEX(N'女士',@ln1) &gt; 0
          begin
              set @testvar=@ln1
              SELECT @ln1 = REPLACE(@testvar,N'女士','')
              set @t1=N'女士'
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
  
          -- END First Name or Last name containing 经理 --
  
          -- First Name or Last name containing 老师 --
  
          else if CHARINDEX(N'老师',@fn1) &gt; 0
          begin
              set @testvar=@fn1
              SELECT @fn1 = REPLACE(@testvar,N'老师','')
              set @t1=N'老师'
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
          else if CHARINDEX(N'老师',@ln1) &gt; 0
          begin
              set @testvar=@ln1
              SELECT @ln1 = REPLACE(@testvar,N'老师','')
              set @t1=N'老师'
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
  
          -- END First Name or Last name containing 老师 --
  
          -- First Name or Last name containing 老师 --
  
          else if CHARINDEX(N'主任',@fn1) &gt; 0
          begin
              set @testvar=@fn1
              SELECT @fn1 = REPLACE(@testvar,N'主任','')
              set @t1=N'主任'
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
          else if CHARINDEX(N'主任',@ln1) &gt; 0
          begin
              set @testvar=@ln1
              SELECT @ln1 = REPLACE(@testvar,N'主任','')
              set @t1=N'主任'
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
  
          -- END First Name or Last name containing 主任 --
  
          -- First Name or Last name containing 部长 --
  
          else if CHARINDEX(N'部长',@fn1) &gt; 0
          begin
              set @testvar=@fn1
              SELECT @fn1 = REPLACE(@testvar,N'部长','')
              set @t1=N'部长'
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
          else if CHARINDEX(N'部长',@ln1) &gt; 0
          begin
              set @testvar=@ln1
              SELECT @ln1 = REPLACE(@testvar,N'部长','')
              set @t1=N'部长'
              set @ContactStatus = null
              set @PhoneStatus = null
              set @Notes = null
          end
  
          -- END First Name or Last name containing 部长 --
  
          -- First name and Last name are same --
  
          else if @fn1 = @ln1
          begin
              set @ContactStatus = 'First name and Last name are same'
              set @PhoneStatus = null
              set @Notes = null
          end
  
          -- END First name and Last name are same --
  
          -- First Name or Last name containing #$$%123 --
  
          else if CHARINDEX('`',@fn1) &gt; 0 or CHARINDEX('`',@ln1) &gt; 0 
          or CHARINDEX('~',@fn1) &gt; 0 or CHARINDEX('~',@ln1) &gt; 0 
          or CHARINDEX('!',@fn1) &gt; 0 or CHARINDEX('!',@ln1) &gt; 0 
          or CHARINDEX('@',@fn1) &gt; 0 or CHARINDEX('@',@ln1) &gt; 0
          or CHARINDEX('#',@fn1) &gt; 0 or CHARINDEX('#',@ln1) &gt; 0
          or CHARINDEX('$',@fn1) &gt; 0 or CHARINDEX('$',@ln1) &gt; 0
          or CHARINDEX('%',@fn1) &gt; 0 or CHARINDEX('%',@ln1) &gt; 0
          or CHARINDEX('^',@fn1) &gt; 0 or CHARINDEX('^',@ln1) &gt; 0
          or CHARINDEX('&amp;',@fn1) &gt; 0 or CHARINDEX('&amp;',@ln1) &gt; 0
          or CHARINDEX('*',@fn1) &gt; 0 or CHARINDEX('*',@ln1) &gt; 0
          or CHARINDEX('(',@fn1) &gt; 0 or CHARINDEX('(',@ln1) &gt; 0
          or CHARINDEX(')',@fn1) &gt; 0 or CHARINDEX(')',@ln1) &gt; 0
          or CHARINDEX('-',@fn1) &gt; 0 or CHARINDEX('-',@ln1) &gt; 0
          or CHARINDEX('_',@fn1) &gt; 0 or CHARINDEX('_',@ln1) &gt; 0
          or CHARINDEX('=',@fn1) &gt; 0 or CHARINDEX('=',@ln1) &gt; 0
          or CHARINDEX('+',@fn1) &gt; 0 or CHARINDEX('+',@ln1) &gt; 0
          or CHARINDEX('[',@fn1) &gt; 0 or CHARINDEX('[',@ln1) &gt; 0
          or CHARINDEX(']',@fn1) &gt; 0 or CHARINDEX(']',@ln1) &gt; 0
          or CHARINDEX('{',@fn1) &gt; 0 or CHARINDEX('{',@ln1) &gt; 0
          or CHARINDEX('}',@fn1) &gt; 0 or CHARINDEX('}',@ln1) &gt; 0
          or CHARINDEX('\',@fn1) &gt; 0 or CHARINDEX('\',@ln1) &gt; 0
          or CHARINDEX('|',@fn1) &gt; 0 or CHARINDEX('|',@ln1) &gt; 0
          or CHARINDEX(';',@fn1) &gt; 0 or CHARINDEX(';',@ln1) &gt; 0
          or CHARINDEX(':',@fn1) &gt; 0 or CHARINDEX(':',@ln1) &gt; 0
          or CHARINDEX('"',@fn1) &gt; 0 or CHARINDEX('"',@ln1) &gt; 0
          or CHARINDEX(',',@fn1) &gt; 0 or CHARINDEX(',',@ln1) &gt; 0
          or CHARINDEX('.',@fn1) &gt; 0 or CHARINDEX('.',@ln1) &gt; 0
          or CHARINDEX('&lt;',@fn1) &gt; 0 or CHARINDEX('&lt;',@ln1) &gt; 0
          or CHARINDEX('&gt;',@fn1) &gt; 0 or CHARINDEX('&gt;',@ln1) &gt; 0
          or CHARINDEX('/',@fn1) &gt; 0 or CHARINDEX('/',@ln1) &gt; 0
          or CHARINDEX('?',@fn1) &gt; 0 or CHARINDEX('?',@ln1) &gt; 0
          begin
              set @ContactStatus = 'Contains junk'
              set @PhoneStatus = null
              set @Notes = null
          end
  
          -- END First Name or Last name containing #$$%123 --
  
          -- Change the phone number format --
  
          if LEN(@ph1) &gt; 0
          begin
              set @PhNochecked = '0'
              if CHARINDEX('-',@ph1) &gt; 0
              begin
                  set @PhoneWOSpace = REPLACE (@ph1, '-', '')
                  set @PhNochecked = '1'
              end
              if CHARINDEX(' ',@ph1) &gt; 0
              begin
                  set @PhoneWOSpace = REPLACE (@ph1, ' ', '' )
                  set @PhNochecked = '1'
              end
              if CHARINDEX('/',@ph1) &gt; 0
              begin
                  set @ph1 = SUBSTRING(@ph1, 1, CHARINDEX('/', @ph1) - 1)
                  set @ph1 = REPLACE (@ph1, '/', '' )
                  set @PhoneWOSpace = REPLACE (@ph1, ' ', '' )
                  set @PhNochecked = '1'
              end
              if CHARINDEX('.',@ph1) &gt; 0
              begin
                  set @ph1 = SUBSTRING(@ph1, 1, CHARINDEX('.', @ph1) - 1)
                  set @ph1 = REPLACE (@ph1, '.', '' )
                  set @PhoneWOSpace = REPLACE (@ph1, ' ', '' )
                  set @PhNochecked = '1'
              end
              if @PhNochecked = '0'
                  set @PhoneWOSpace = @ph1
  
              if (@PhNochecked = '1' or @PhNochecked = '0') and LEN(@PhoneWOSpace) = 11  
              begin
                  set @CityCode=substring(@PhoneWOSpace,1,1)+substring(@PhoneWOSpace,2,1)+substring(@PhoneWOSpace,3,1)
                  set @Phone1=substring(@PhoneWOSpace,4,1)+substring(@PhoneWOSpace,5,1)+substring(@PhoneWOSpace,6,1)
                  +substring(@PhoneWOSpace,7,1)
                  set @Phone2=substring(@PhoneWOSpace,8,1)+substring(@PhoneWOSpace,9,1)+substring(@PhoneWOSpace,10,1)
                  +substring(@PhoneWOSpace,11,1)
                  set @finalPhNo='+86' + ' ' + @CityCode + ' ' + @Phone1 + ' ' + @Phone2
  
                  set @ph1 = @finalPhNo
                  set @PhoneStatus = null
              end
              else if @PhNochecked = '1' and LEN(@PhoneWOSpace) = 12
              begin
                  set @CityCode=substring(@PhoneWOSpace,1,1)+substring(@PhoneWOSpace,2,1)+substring(@PhoneWOSpace,3,1)
                  +substring(@PhoneWOSpace,4,1)
                  set @Phone1=substring(@PhoneWOSpace,5,1)+substring(@PhoneWOSpace,6,1)+substring(@PhoneWOSpace,7,1)
                  +substring(@PhoneWOSpace,8,1)
                  set @Phone2=substring(@PhoneWOSpace,9,1)+substring(@PhoneWOSpace,10,1)+substring(@PhoneWOSpace,11,1)
                  +substring(@PhoneWOSpace,12,1)
                  set @finalPhNo='+86' + ' ' + @CityCode + ' ' + @Phone1 + ' ' + @Phone2
  
                  set @ph1 = @finalPhNo
                  set @PhoneStatus = null
              end
              else if @PhNochecked = '0' and (LEN(@PhoneWOSpace) &gt; 12 or LEN(@PhoneWOSpace) &lt; 11)
                  set @PhoneStatus = 'Business phone cannot identified'
          end
          else
              set @PhoneStatus = 'Business phone is null' 
          -- END phone number format --
  
          -- Finally do insert --
          insert into TargetTable ([Email Address], [First Name],[Last Name],[Business Phone],Title,City,
          LastNameFirstName,Contact_status,Phone_status,Notes)
          values (rtrim(ltrim(@e1)), rtrim(ltrim(@fn1)), rtrim(ltrim(@ln1)), rtrim(ltrim(@ph1)), rtrim(ltrim(@t1)), 
          rtrim(ltrim(@c1)), rtrim(ltrim((@ln1 + ' ' + @fn1))),rtrim(ltrim(@ContactStatus)),rtrim(ltrim(@PhoneStatus)),
          rtrim(ltrim(@Notes)))
          -- END insert --
  
      FETCH NEXT FROM @EMail INTO @EMailNew                          
      END                    
  CLOSE @EMail                          
  DEALLOCATE @EMail                    
  end
  </code></pre>
  
  <p>Can anyone provide the solution to overcome the above exception?</p>