sql-server - 抛出异常：system.data.dll 中的 system.data.sqlclient.sqlexception

Question

我正在使用 vb.net 处理 sql server 中的大型数据表。我正在从文本文件中提取数据到 sql server 中的数据表，并通过多个 sql 更新查询更新数据表。我正在迭代整个提取和更新过程的次数，例如 100 次以满足我的算法的要求。在每次迭代中，都会从文本文件中提取新数据，并相应地更新数据表。这个过程对我来说很好，因为迭代次数有限，比如 20 次。之后，我发现在 Visual Studio 2015 的即时窗口中写入错误“抛出异常：system.data.dll 中的 system.data.sqlclient.sqlexception”。但是模块并没有中断或停止，它只是挂了更长的时间。之后几次迭代以正常速度运行。再次经过几次迭代，同样的错误和同样缓慢的执行速度。随后每次迭代变得太慢，需要一些关注和补救措施。我真的被困在这里了。这是我的代码：

Public connectionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Main Data.mdf;Integrated Security=True"
Public con As SqlConnection


Private Sub btnOk_Click(sender As Object, e As EventArgs) Handles btnOk.Click

Dim i as Integer
con = New SqlConnection(connectionString)
con.Open()

For i = 1 to 100
    SimVehRecord(Main_DataDataSet, Sim_VehRecordTableAdapter)
    'Other function
Next

con.Close()
con.Dispose()

End Sub


Function SimVehRecord(ByVal ds As Main_DataDataSet, ByVal ta As Main_DataDataSetTableAdapters.Sim_VehRecordTableAdapter)

        Dim tblReadCSV As New DataTable()

        tblReadCSV.Columns.Add("Sim_Time")
        tblReadCSV.Columns.Add("Veh_No")
        tblReadCSV.Columns.Add("Link_No")
        tblReadCSV.Columns.Add("Lane_No")
        tblReadCSV.Columns.Add("Position")

        Dim csvParser As New FileIO.TextFieldParser("C:\Users\user\Desktop\Aus\Result Output\Rom001.fzp")
        csvParser.TextFieldType = FileIO.FieldType.Delimited
        csvParser.SetDelimiters(";")
        csvParser.TrimWhiteSpace = True

        Try
            While Not (csvParser.EndOfData = True)
                tblReadCSV.Rows.Add(csvParser.ReadFields())
            End While
            csvParser.Close()
            csvParser.Dispose()

            Dim cmdText As String = "TRUNCATE TABLE [Sim VehRecord]"
            Using cmd = New SqlCommand(cmdText, con)
                cmd.CommandTimeout = 3600
                cmd.ExecuteNonQuery()
            End Using

            Using bulkcopy As New SqlBulkCopy(con)
                bulkcopy.DestinationTableName = "[Sim VehRecord]"
                bulkcopy.ColumnMappings.Add("Sim_Time", "Sim Time (sec)")
                bulkcopy.ColumnMappings.Add("Veh_No", "Veh No")
                bulkcopy.ColumnMappings.Add("Link_No", "Link No")
                bulkcopy.ColumnMappings.Add("Lane_No", "Lane No")
                bulkcopy.ColumnMappings.Add("Position", "Position (m)")
                bulkcopy.BatchSize = 50000
                bulkcopy.WriteToServer(tblReadCSV)
            End Using

            tblReadCSV.Dispose()

            GetDensity()

        Catch ex As SqlException
            MessageBox.Show(ex.Message)
        End Try

        Return ds

    End Function

 Private Sub GetDensity()

Try
        Dim cmdText As String = "WITH SimVehRecord AS 
                                (SELECT [Record ID], [Sim Time (sec)], [Position (m)],
                                LAG ([Position (m)]) OVER (PARTITION BY [Veh No] ORDER By [Sim Time (sec)] ASC) as prev_posn,
                                LAG ([Sim Time (sec)]) OVER (PARTITION BY [Veh No] ORDER By [Sim Time (sec)] ASC) as prev_time
                                FROM [Sim VehRecord])
                                
                            UPDATE [Sim VehRecord]
                            SET [Speed (km/hr)] = IIF((3.6* ISNULL ((SimVehRecord.[Position (m)] - SimVehRecord.prev_posn)/(SimVehRecord.[Sim Time (sec)] - SimVehRecord.prev_time), 0))>0,(3.6* ISNULL ((SimVehRecord.[Position (m)] - SimVehRecord.prev_posn)/(SimVehRecord.[Sim Time (sec)] - SimVehRecord.prev_time), 0)),0) 
                            FROM [Sim VehRecord]
                            INNER JOIN SimVehRecord
                            ON [Sim VehRecord].[Record ID] = SimVehRecord.[Record ID]"

        Using cmd = New SqlCommand(cmdText, con)
            cmd.CommandTimeout = 3600
            cmd.ExecuteNonQuery()
        End Using

        Dim cmdText1 As String = "UPDATE [Sim VehRecord]
                            SET [Veh no of Leader] =
                            (SELECT TOP 1 [Veh No] FROM [Sim VehRecord] sv 
                            WHERE sv.[Position (m)] > [Sim VehRecord].[Position (m)] 
                            AND 
                            sv.[Sim Time (sec)]= [Sim VehRecord].[Sim Time (sec)]
                            AND
                            sv.[Lane No]= [Sim VehRecord].[Lane No]
                            ORDER BY sv.[Position (m)] ASC)"

        Using cmd1 = New SqlCommand(cmdText1, con)
            cmd1.CommandTimeout = 3600
            cmd1.ExecuteNonQuery()
        End Using

        Dim cmdText2 As String = "UPDATE [Sim VehRecord]
                            SET [Veh no of Follower] =
                            (SELECT TOP 1 [Veh No] FROM [Sim VehRecord] sv 
                            WHERE sv.[Position (m)] < [Sim VehRecord].[Position (m)] 
                            AND 
                            sv.[Sim Time (sec)]= [Sim VehRecord].[Sim Time (sec)]
                            AND
                            sv.[Lane No]= [Sim VehRecord].[Lane No]
                            ORDER BY sv.[Position (m)] DESC)"

        Using cmd2 = New SqlCommand(cmdText2, con)
            cmd2.CommandTimeout = 3600
            cmd2.ExecuteNonQuery()
        End Using

        Dim cmdText3 As String = "UPDATE [Sim VehRecord]
                            SET [Headway (m)] = 
                            (SELECT MIN(sv.[Position (m)])
                            FROM [Sim VehRecord] sv 
                            WHERE sv.[Position (m)] > [Sim VehRecord].[Position (m)]
                            AND
                            sv.[Lane No] = [Sim VehRecord].[Lane No]
                            AND
                            sv.[Sim Time (sec)] = [Sim VehRecord].[Sim Time (sec)]) - [Sim VehRecord].[Position (m)]"

        Using cmd3 = New SqlCommand(cmdText3, con)
            cmd3.CommandTimeout = 3600
            cmd3.ExecuteNonQuery()
        End Using

        Dim cmdText4 As String = "UPDATE [Sim VehRecord]
                            SET[Density (veh/km)] =
                            (SELECT CAST(ROUND(1000* COALESCE ((COUNT(DISTINCT [Veh No]))/NULLIF((MAX(sv.[Position (m)]) - MIN (sv.[Position (m)])),0),0),0) as int)
                            FROM [Sim VehRecord] sv
                            WHERE
                            sv.[Sim Time (sec)] = [Sim VehRecord].[Sim Time (sec)])"

        Using cmd4 = New SqlCommand(cmdText4, con)
            cmd4.CommandTimeout = 3600
            cmd4.ExecuteNonQuery()
        End Using

       Catch ex As SqlException
          MessageBox.Show(ex.Message)
       End Try

    End Sub

标签： sql-servervb.net