问题描述:

I'm using SqlBulkCopy to import around 66k rows from a CSV. The CSV is imported into a DataTable and then passed into the following method below.

It seems to replicate the same rows (or reset once it reaches the end) indefinitely into the database. Debug ran for around 30mins and created close to 350k records duplicating each row in the DataTable around 5 times.

I can confirm that the amount of rows in the DataTable is correct so I must be missing something, any ideas?

Note: I added the s.BatchSize with the hopes that it will only import that amount which is not the case.

 /*

* StoreImportedData()

* This method will store all of the imported data from the datatable to the SQL server database.

*

*

*/

private Boolean StoreImportedData(DataTable csvFileData)

{

try

{

using (SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))

{

dbConnection.Open();

using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))

{

s.DestinationTableName = "Staging";

// Amount of rows to issue in the batch: (currently value is around 60k)

s.BatchSize = csvFileData.Rows.Count;

s.ColumnMappings.Add("remark", "ReportDate");

s.ColumnMappings.Add("ipAddress", "IPAddress");

s.ColumnMappings.Add("hostName", "HostName");

s.ColumnMappings.Add("macAddress", "MacAddress");

s.ColumnMappings.Add("deviceName", "PrinterName");

s.ColumnMappings.Add("comment", "CenterCode");

s.ColumnMappings.Add("userName", "CustomerCode");

s.ColumnMappings.Add("userDisplayName", "CustomerName");

s.ColumnMappings.Add("printerBlackTotal", "TPrintBw");

s.ColumnMappings.Add("printerColorTotal", "TPrintCol");

s.ColumnMappings.Add("copyBlackTotal", "TCopyBw");

s.ColumnMappings.Add("copyColorTotal", "TCopyCol");

s.ColumnMappings.Add("scanTotal", "TScan");

s.ColumnMappings.Add("faxBlackTotal", "TFaxBw");

s.WriteToServer(csvFileData);

}

}

}

catch (Exception e)

{

throw new Exception("An error has occured with the import SQL operation: " + e.Message);

}

return true;

}

}

网友答案:

Try this:

s.WriteToServer(csvFileData.CreateDataReader());
相关阅读:
Top