Time out exception occurs after calling a Stored Procedure in C#

Question

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 DataTable.

I am using an OleDbConnection to perform this action. Using a connection string, performing a bulk copy of this DataTable to a table inside the database.

Total no. of records in the Excel file is 3,27,761

Excel bulk export to DataTable:

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();

Bulk copy the DataTable and save it to SQL Server database table SourceTable:

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);
}

Now, the total no. of records in SourceTable is 3,27,761

I am calling a stored procedure SP_InsertToTargetTable.

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();

After execution, application throws the following exception.

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

The stored procedure written inside the DB:

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) > 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) > 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) > 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) > 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) > 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) > 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) > 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) > 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) > 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) > 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) > 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) > 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) > 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) > 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) > 0 or CHARINDEX('`',@ln1) > 0 
        or CHARINDEX('~',@fn1) > 0 or CHARINDEX('~',@ln1) > 0 
        or CHARINDEX('!',@fn1) > 0 or CHARINDEX('!',@ln1) > 0 
        or CHARINDEX('@',@fn1) > 0 or CHARINDEX('@',@ln1) > 0
        or CHARINDEX('#',@fn1) > 0 or CHARINDEX('#',@ln1) > 0
        or CHARINDEX('$',@fn1) > 0 or CHARINDEX('$',@ln1) > 0
        or CHARINDEX('%',@fn1) > 0 or CHARINDEX('%',@ln1) > 0
        or CHARINDEX('^',@fn1) > 0 or CHARINDEX('^',@ln1) > 0
        or CHARINDEX('&',@fn1) > 0 or CHARINDEX('&',@ln1) > 0
        or CHARINDEX('*',@fn1) > 0 or CHARINDEX('*',@ln1) > 0
        or CHARINDEX('(',@fn1) > 0 or CHARINDEX('(',@ln1) > 0
        or CHARINDEX(')',@fn1) > 0 or CHARINDEX(')',@ln1) > 0
        or CHARINDEX('-',@fn1) > 0 or CHARINDEX('-',@ln1) > 0
        or CHARINDEX('_',@fn1) > 0 or CHARINDEX('_',@ln1) > 0
        or CHARINDEX('=',@fn1) > 0 or CHARINDEX('=',@ln1) > 0
        or CHARINDEX('+',@fn1) > 0 or CHARINDEX('+',@ln1) > 0
        or CHARINDEX('[',@fn1) > 0 or CHARINDEX('[',@ln1) > 0
        or CHARINDEX(']',@fn1) > 0 or CHARINDEX(']',@ln1) > 0
        or CHARINDEX('{',@fn1) > 0 or CHARINDEX('{',@ln1) > 0
        or CHARINDEX('}',@fn1) > 0 or CHARINDEX('}',@ln1) > 0
        or CHARINDEX('\',@fn1) > 0 or CHARINDEX('\',@ln1) > 0
        or CHARINDEX('|',@fn1) > 0 or CHARINDEX('|',@ln1) > 0
        or CHARINDEX(';',@fn1) > 0 or CHARINDEX(';',@ln1) > 0
        or CHARINDEX(':',@fn1) > 0 or CHARINDEX(':',@ln1) > 0
        or CHARINDEX('"',@fn1) > 0 or CHARINDEX('"',@ln1) > 0
        or CHARINDEX(',',@fn1) > 0 or CHARINDEX(',',@ln1) > 0
        or CHARINDEX('.',@fn1) > 0 or CHARINDEX('.',@ln1) > 0
        or CHARINDEX('<',@fn1) > 0 or CHARINDEX('<',@ln1) > 0
        or CHARINDEX('>',@fn1) > 0 or CHARINDEX('>',@ln1) > 0
        or CHARINDEX('/',@fn1) > 0 or CHARINDEX('/',@ln1) > 0
        or CHARINDEX('?',@fn1) > 0 or CHARINDEX('?',@ln1) > 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) > 0
        begin
            set @PhNochecked = '0'
            if CHARINDEX('-',@ph1) > 0
            begin
                set @PhoneWOSpace = REPLACE (@ph1, '-', '')
                set @PhNochecked = '1'
            end
            if CHARINDEX(' ',@ph1) > 0
            begin
                set @PhoneWOSpace = REPLACE (@ph1, ' ', '' )
                set @PhNochecked = '1'
            end
            if CHARINDEX('/',@ph1) > 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) > 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) > 12 or LEN(@PhoneWOSpace) < 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

Can anyone provide the solution to overcome the above exception?

Answer 1

The database communication has its own timeout value (the default is 30s) to prevent that the connection would hang, or that you are using resources while a query is stuck in a loop or something. Here below is a statement to define a custom timeout value.

cmd.CommandTimeout = 300;

This statement says that your query should take 5 minutes before it should decline the connection if there is no answer from the database. Please refer to this page for further explanation.

What you can do is to increase the timer, but i don't recommend it. I have noticed that your stored procedure is not only too long, it also contains statements which shouldn't be done on the server itself. The database is not there to filter/extract data. Please do it on client side.

When you are importing data, analyze it, filter/validate it, and prepare it on your local program, not in the database. When you have prepared your data, send it in to your stored procedure program.

Then you will win on two fields;

1. only validated data will be sent in, that you won't waste time on incorrect data.
2. you will spare server resources for other tasks, for example querying it.

I hope that you would not re-use your previous practice, validating data on the database itself, in the future.

Answer 2

This line indicates you are waiting 5 minutes for the table insert to complete

cmd.CommandTimeout = 300;

You can try increasing this value until your insert succeeds

Answer 3

Thank you all for posting your answers here.

I changed my logic in my code. Previously I have done a bulk copy from a C# DataTable to a SQL DB. Total number of rows in the DataTable is 3,27,761. Now i split it to 5000 and inserting each 5000 records in an iteration. Insertion done to 'SourceTable' in DB and converted the above Stored Procedure to trigger for that table.

Answer 4

set this value in your web.config:

<httpRuntime executionTimeout="999999" maxRequestLength="10240" />

if still not working, increase the values further