使用 ASP 的 Sqlbulkcopy 在 iis6 中可以工作，但在 iis7 中失败

Question

我能够让 sqlbulkcopy 在 iis6（测试服务器）和 iis7（实时服务器）上运行良好，但是在 iis7 中上传数据时，行被切断。在 iis7 中，它只导入了 250,000 个中的~190,000 个。在 iis 6 中，使用相同的代码/页面可以毫无问题地加载所有 250000 条记录。此外，两台服务器都更新相同的 SQL 数据库。

我已经到处寻找解决方案。非常感谢任何帮助。

    Sub UpdateData()

    Dim sSQLTable As String = "Table1"
    Dim sExcelFileName As String = savePath
    Dim sWorkbook() As String = GetExcelSheetNames(sExcelFileName)

    'Create our connection strings
    Dim sExcelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sExcelFileName & ";Extended Properties=""Excel 12.0;HDR=YES;"""

    'Execute a query to erase any previous data from our destination table
    Dim sClearSQL = "DELETE FROM " & sSQLTable
    Dim SqlConn As SqlConnection = New SqlConnection(ConnString)
    Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn)
    SqlConn.Open()
    SqlCmd.ExecuteNonQuery()
    SqlConn.Close()

    'Series of commands to bulk copy data from the excel file into our SQL table
    Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
    OleDbConn.Open()

    Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM [" & sWorkbook(0) & "]"), OleDbConn)
    Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
    Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(ConnString, SqlBulkCopyOptions.UseInternalTransaction)
    bulkCopy.BulkCopyTimeout = 2000
    bulkCopy.DestinationTableName = sSQLTable
    'DEMO bulkCopy.ColumnMappings.Add("Excel", "SQL")
    bulkCopy.ColumnMappings.Add("Material", "Material")
    bulkCopy.ColumnMappings.Add("Plnt", "Plant")
    bulkCopy.ColumnMappings.Add("SLoc", "SLoc")
    bulkCopy.ColumnMappings.Add("S", "S")
    bulkCopy.ColumnMappings.Add("Batch", "Batch")
    bulkCopy.ColumnMappings.Add("Special Stock Number", "SpecialStockNumber")
    bulkCopy.ColumnMappings.Add("Material Description", "MatDesc")
    bulkCopy.ColumnMappings.Add("Typ", "Type")
    bulkCopy.ColumnMappings.Add("StorageBin", "StorageBin")
    bulkCopy.ColumnMappings.Add("Available stock", "AvailStock")
    bulkCopy.ColumnMappings.Add("BUn", "BUn")
    'bulkCopy.ColumnMappings.Add("GR Date", "GRDate")
    bulkCopy.WriteToServer(dr)
    OleDbConn.Close()
End Sub

Answer 1

感谢 Andy 提供的 ExcelDataReader 提示！

让它与 ExcelDataReader 一起使用。看起来这是 OleDB 的内存问题。发布我的代码，以防将来对其他人有帮助。

    Sub updateData()
    Dim sSQLTable As String = "Table1"

    'Execute a query to erase any previous data from our destination table
    Dim sClearSQL = "DELETE FROM " & sSQLTable
    Dim SqlConn As SqlConnection = New SqlConnection(ConnString)
    Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn)
    SqlConn.Open()
    SqlCmd.ExecuteNonQuery()
    SqlConn.Close()


    Dim stream As FileStream = File.Open(savePath, FileMode.Open, FileAccess.Read)

    '1. Reading from a binary Excel file ('97-2003 format; *.xls)
    'Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateBinaryReader(stream)

    '2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
    Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream)

    '3. DataSet - The result of each spreadsheet will be created in the result.Tables
    'Dim ds As DataSet = excelReader.AsDataSet()

     '4. DataSet - Create column names from first row
    excelReader.IsFirstRowAsColumnNames = True
    Dim ds As DataSet = excelReader.AsDataSet()
    Dim sourceData As New DataTable()
    sourceData = ds.Tables(0)

    ''5. Data Reader methods
    'Using destinationConnection As New SqlConnection(ConnString)
    ' open the connection

    'destinationConnection.Open()
    'Using bulkCopy As New SqlBulkCopy(destinationConnection.ConnectionString)

    ' column mappings
    Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(ConnString, SqlBulkCopyOptions.UseInternalTransaction)
    bulkCopy.BulkCopyTimeout = 2000
    bulkCopy.DestinationTableName = sSQLTable

    'DEMO bulkCopy.ColumnMappings.Add("Excel", "SQL")
    bulkCopy.ColumnMappings.Add("Material", "Material")
    bulkCopy.ColumnMappings.Add("Plnt", "Plant")
    bulkCopy.ColumnMappings.Add("SLoc", "SLoc")
    bulkCopy.ColumnMappings.Add("S", "S")
    bulkCopy.ColumnMappings.Add("Batch", "Batch")
    bulkCopy.ColumnMappings.Add("Special Stock Number", "SpecialStockNumber")
    bulkCopy.ColumnMappings.Add("Material Description", "MatDesc")
    bulkCopy.ColumnMappings.Add("Typ", "Type")
    bulkCopy.ColumnMappings.Add("StorageBin", "StorageBin")
    bulkCopy.ColumnMappings.Add("Available stock", "AvailStock")
    bulkCopy.ColumnMappings.Add("BUn", "BUn")
    'bulkCopy.ColumnMappings.Add("GR Date", "GRDate")

    'bulkCopy.DestinationTableName = sSQLTable
    bulkCopy.WriteToServer(sourceData)

    '6. Free resources (IExcelDataReader is IDisposable)
    excelReader.Close()
End Sub