public void Test1() { var columns = new List <string> { "PointOfSale", "Product", "Date", "Stock" }; CsvPocDataReader reader = new CsvPocDataReader(@"[PATH_TO_FILE]", ';', columns.ToArray()); System.Data.SqlClient.SqlBulkCopy bcp = new SqlBulkCopy([CONNECTION_STRING], SqlBulkCopyOptions.UseInternalTransaction);
public DatabaseMigrationDataProcessorResult MigrateCsvFilesToDatabaseTable(IEnumerable <CsvFile> csvFiles, string separator) { DatabaseMigrationDataProcessorResult databaseMigrationDataProcessorResult = new DatabaseMigrationDataProcessorResult(); if (csvFiles == null || !csvFiles.Any()) { throw new ArgumentNullException("Csv files parameter is null or empty"); } if (string.IsNullOrWhiteSpace(separator)) { throw new ArgumentNullException("separator parameter is null or empty"); } this.CleanTableBeforeInsert(databaseCsvSettings.DataTable); var totalCsvFiles = csvFiles.Count(); var currentProcessingFile = default(int); foreach (var csvFile in csvFiles) { currentProcessingFile++; Console.WriteLine("Procesando en base de datos {0} de {1}", currentProcessingFile, totalCsvFiles); var columns = new List <string> { "PointOfSale", "Product", "Date", "Stock" }; CsvPocDataReader csvPocDataReader = new CsvPocDataReader(csvFile.Path, Convert.ToChar(csvFile.Separator), columns.ToArray()); databaseMigrationDataProcessorResult = this.ProcessDatabaseInBatchesDatatable(csvPocDataReader); } return(databaseMigrationDataProcessorResult); }
private DatabaseMigrationDataProcessorResult ProcessDatabaseInBatchesDatatable(CsvPocDataReader csvDataReader) { var databaseMigrationDataProcessorResult = new DatabaseMigrationDataProcessorResult(); try { using (var sqlConnection = new SqlConnection(databaseCsvSettings.ConnectionString)) { sqlConnection.Open(); using (SqlTransaction transaction = sqlConnection.BeginTransaction()) { try { using (var sqlBulk = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.TableLock, transaction)) { sqlBulk.DestinationTableName = databaseCsvSettings.DataTable; sqlBulk.WriteToServer(csvDataReader); sqlBulk.BatchSize = databaseCsvSettings.BulkBatchSize; transaction.Commit(); } } catch (Exception ex) { databaseMigrationDataProcessorResult.CreateMessage(ex.ToString()); Console.WriteLine(ex.Message); transaction.Rollback(); } } } } catch (Exception ex) { databaseMigrationDataProcessorResult.CreateMessage(ex.ToString()); throw; } return(databaseMigrationDataProcessorResult); }