在C#中调用存储过程后发生超时异常

Question

我已经使用C#开发了一个Windows应用程序。运行此操作后，我们可以浏览Excel文件，并将该文件中的记录填充到DataTable中。

我正在使用一个OleDbConnection来执行这个操作。使用连接字符串，将此DataTable大容量复制到数据库中的表中。

总共没有。Excel文件中的记录为3,27,761

Excel批量导出到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();

批量复制DataTable并将其保存到Server数据库表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);
}

现在，完全没有。SourceTable中的记录为3,27,761

我正在调用一个存储过程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();

执行后，应用程序将引发以下异常。

超时过期了。在操作完成或服务器没有响应之前经过的超时时间。

在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

有人能提供克服上述例外的解决方案吗？

Answer 1

数据库通信有自己的超时值(默认值为30)，以防止连接挂起，或者在查询停留在循环或其他地方时使用资源。下面是定义自定义超时值的语句。

cmd.CommandTimeout = 300;

这条语句说，如果数据库中没有答案，查询应该需要5分钟才能拒绝连接。请参考此页进一步的解释。

您可以做的是增加计时器，但是--我不推荐它为。我注意到您的存储过程不仅太长，还包含不应该在服务器上执行的语句。数据库不是用来过滤/提取数据的。请在客户端上做。

当您导入数据时，对其进行分析、筛选/验证，并在您的本地程序(数据库中的 而不是)上准备它。准备好数据后，将其发送到存储过程程序中。

然后你将在两个领域获胜；

1. 只有经过验证的数据才会被发送进来，这样就不会在不正确的数据上浪费时间。
2. 您将腾出服务器资源用于其他任务，例如查询它。

我希望您以后不要重复使用以前的实践，验证数据库本身的数据。

Answer 2

这一行表示要等待表插入完成5分钟。

cmd.CommandTimeout = 300;

您可以尝试增加此值，直到插入成功为止。

Answer 3

谢谢大家在这里张贴你们的答案。

我在代码中改变了我的逻辑。以前，我已经完成了从C# DataTable到SQL的大容量复制。DataTable中的行总数为3,27,761行。现在，我将其拆分为5000条，并在一次迭代中插入每个5000条记录。对DB中的“SourceTable”进行插入，并将上述存储过程转换为该表的触发器。