public void Upload(HttpPostedFileBase file)
{
var csvconf = new CsvHelper.Configuration.CsvConfiguration()
{
IsStrictMode = false,
SkipEmptyRecords = true,
IsCaseSensitive = false
};
csvconf.SkipEmptyRecords = true;
csvconf.ClassMapping <AppImportRowModel.CsvMap>();
var csvReader = new CsvReader(new System.IO.StreamReader(file.InputStream), csvconf);
var csvRows = csvReader.GetRecords <AppsImport>().Select(f =>
{
f.Id = this.Id;
f.InterlineTransfer = f.InterlineTransfer ?? false;
f.EndOfYearValidationOnly = f.EndOfYearValidationOnly ?? false;
return(f);
});
var idatareader = CC.Web.Helpers.idrf.GetReader(csvRows);
var sqlBulkCopy = GetSqlBulkCopy();
sqlBulkCopy.WriteToServer(idatareader);
}
public static IEnumerable <string> GetCsvFileHeaders <TMap>() where TMap : CsvHelper.Configuration.CsvClassMap
{
var conf = new CsvHelper.Configuration.CsvConfiguration();
conf.ClassMapping <TMap>();
return(conf.Properties.Select(f => f.NameValue));
}
public static Import Upload(HttpPostedFileWrapper file, int userId)
{
var newImport = NewMethod(userId);
var csvConf = new CsvHelper.Configuration.CsvConfiguration
{
SkipEmptyRecords = true,
IsCaseSensitive = false,
IsStrictMode = true
};
csvConf.ClassMapping <MasterIdImportCsvMap>();
var streamReader = new System.IO.StreamReader(file.InputStream);
using (var csvReader = new CsvHelper.CsvReader(streamReader, csvConf))
{
var data = csvReader.GetRecords <ImportClient>().Select((record, i) =>
new ImportClient
{
RowIndex = i,
ImportId = newImport.Id,
ClientId = record.ClientId,
MasterId = record.MasterId,
UpdatedAt = newImport.StartedAt,
UpdatedById = newImport.UserId,
});
string connectionString = System.Data.SqlClient.ConnectionStringHelper.GetProviderConnectionString();
var sqlBulkCopy = new System.Data.SqlClient.SqlBulkCopy(connectionString);
sqlBulkCopy.DestinationTableName = "ImportClients";
sqlBulkCopy.ColumnMappings.Add("ImportId", "ImportId");
sqlBulkCopy.ColumnMappings.Add("RowIndex", "RowIndex");
sqlBulkCopy.ColumnMappings.Add("ClientId", "ClientId");
sqlBulkCopy.ColumnMappings.Add("MasterId", "MasterId");
sqlBulkCopy.ColumnMappings.Add("UpdatedAt", "UpdatedAt");
sqlBulkCopy.ColumnMappings.Add("UpdatedById", "UpdatedById");
var dataReader = new IEnumerableDataReader <ImportClient>(data);
sqlBulkCopy.WriteToServer(dataReader);
sqlBulkCopy.Close();
}
return(newImport);
}
public void Import()
{
var csvConf = new CsvHelper.Configuration.CsvConfiguration()
{
IsStrictMode = true,
IsCaseSensitive = false,
SkipEmptyRecords = true,
};
csvConf.ClassMapping <CsvMap>();
using (var csvReader = new CsvReader(new System.IO.StreamReader(file.InputStream), csvConf))
{
var records = csvReader.GetRecords <GovHcHour>();
var data = records.Select((f, i) => new ImportGovHcHour
{
RowIndex = i + 2,
ImportId = this.id,
ClientId = f.ClientId,
StartDate = f.StartDate,
Value = f.Value
});
string connectionString = ConnectionStringHelper.GetProviderConnectionString();
using (var sqlBulkCopy = new System.Data.SqlClient.SqlBulkCopy(connectionString))
{
sqlBulkCopy.DestinationTableName = "dbo.ImportGovHcHours";
sqlBulkCopy.ColumnMappings.Add("ImportId", "ImportId");
sqlBulkCopy.ColumnMappings.Add("RowIndex", "RowIndex");
sqlBulkCopy.ColumnMappings.Add("ClientId", "ClientId");
sqlBulkCopy.ColumnMappings.Add("StartDate", "StartDate");
sqlBulkCopy.ColumnMappings.Add("Value", "Value");
try
{
sqlBulkCopy.WriteToServer(idrf.GetReader(data));
}
catch
{
throw;
}
}
}
}
protected override void SetMapping(CsvHelper.Configuration.CsvConfiguration conf)
{
conf.ClassMapping <CriHcWeeklyCsvMap>();
}
protected override void SetMapping(CsvHelper.Configuration.CsvConfiguration conf)
{
conf.ClassMapping <CriQuantityAmountCsvMap>();
}
public ActionResult Upload(HttpPostedFileWrapper file)
{
if (file == null)
{
ModelState.AddModelError(string.Empty, "Please select a file");
var csvColumnNames = CsvHelper.CsvHelperExtenstions.ColumnHeaderNames <ImportHcepCsvMap>();
return(View(csvColumnNames));
}
var ImportId = Guid.NewGuid();
string fileName = System.IO.Path.Combine(System.IO.Path.GetTempPath(), ImportId.ToString());
var csvConf = new CsvHelper.Configuration.CsvConfiguration()
{
IsStrictMode = false,
IsCaseSensitive = false,
SkipEmptyRecords = true,
};
csvConf.ClassMapping <ImportHcepCsvMap>();
var updatedAt = DateTime.Now;
var updatedBy = this.Permissions.User.Id;
using (var db = new ccEntities())
{
db.Imports.AddObject(new Import()
{
Id = ImportId,
StartedAt = updatedAt,
UserId = this.Permissions.User.Id
});
db.SaveChanges();
}
using (var csvReader = new CsvHelper.CsvReader(new System.IO.StreamReader(file.InputStream), csvConf))
{
var csvChunkSize = 10000;
var recordIndex = 1;
foreach (var csvChunk in csvReader.GetRecords <ImportHcep>().Split(csvChunkSize))
{
string connectionString = ConnectionStringHelper.GetProviderConnectionString();
using (var sqlBulk = new System.Data.SqlClient.SqlBulkCopy(connectionString))
{
foreach (var record in csvChunk)
{
record.RowIndex = recordIndex++;
record.ImportId = ImportId;
record.UpdatedAt = updatedAt;
record.UpdatedBy = updatedBy;
}
sqlBulk.DestinationTableName = "ImportHcep";
sqlBulk.ColumnMappings.Add("ImportId", "ImportId");
sqlBulk.ColumnMappings.Add("RowIndex", "RowIndex");
sqlBulk.ColumnMappings.Add("Id", "Id");
sqlBulk.ColumnMappings.Add("ClientId", "ClientId");
sqlBulk.ColumnMappings.Add("StartDate", "StartDate");
sqlBulk.ColumnMappings.Add("EndDate", "EndDate");
sqlBulk.ColumnMappings.Add("UpdatedAt", "UpdatedAt");
sqlBulk.ColumnMappings.Add("UpdatedBy", "UpdatedBy");
var reader = new CC.Web.Helpers.IEnumerableDataReader <ImportHcep>(csvChunk);
sqlBulk.WriteToServer(reader);
}
}
}
return(this.RedirectToAction(f => f.Preview(ImportId)));
}
protected override void SetMapping(CsvHelper.Configuration.CsvConfiguration conf)
{
conf.ClassMapping <CriClientNamesAndCostsCsvMap>();
}
internal void ProcessFile(bool insert)
{
var ImportId = this.Id;
string fileName = System.IO.Path.Combine(System.IO.Path.GetTempPath(), ImportId.ToString());
var csvConf = new CsvHelper.Configuration.CsvConfiguration()
{
IsStrictMode = false,
IsCaseSensitive = false,
SkipEmptyRecords = true
};
if (insert)
{
csvConf.ClassMapping <ClientCsvMap>();
}
else
{
csvConf.ClassMapping <ExistingClientCsvMap>();
}
var updatedAt = DateTime.Now;
var updatedBy = this.Permissions.User.Id;
using (var csvReader = new CsvHelper.CsvReader(new System.IO.StreamReader(File.InputStream), csvConf))
{
var csvChunkSize = 10000;
var recordIndex = 1;
using (var db = new ccEntities())
{
db.Imports.AddObject(new CC.Data.Import()
{
Id = ImportId,
StartedAt = DateTime.Now,
UserId = this.Permissions.User.Id
});
db.SaveChanges();
}
foreach (var csvChunk in csvReader.GetRecords <ImportClient>().Split(csvChunkSize))
{
string connectionString = ConnectionStringHelper.GetProviderConnectionString();
using (var sqlBulk = new System.Data.SqlClient.SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepNulls))
{
foreach (var record in csvChunk)
{
record.RowIndex = recordIndex++;
record.ImportId = ImportId;
record.UpdatedAt = updatedAt;
record.UpdatedById = updatedBy;
if (this.Permissions.User.RoleId != (int)FixedRoles.Admin)
{
using (var db = new ccEntities())
{
var regionId = (from a in db.Agencies
where a.Id == record.AgencyId
select a.AgencyGroup.Country.RegionId).SingleOrDefault();
if (regionId == 2) //Israel
{
record.CountryId = 344;
record.NationalIdTypeId = 1;
}
}
}
if (!string.IsNullOrEmpty(record.NationalId) && record.NationalId.Length < 9 && record.NationalIdTypeId == 1)
{
record.NationalId = record.NationalId.PadLeft(9, '0');
}
if (insert)
{
record.ApprovalStatusId = (int)ApprovalStatusEnum.New;
}
}
var dataTable = csvChunk.ToDataTable();
var q = dataTable.Columns.OfType <System.Data.DataColumn>().Where(f => f.DataType == typeof(Int32)).Select(f => new
{
c = f.ColumnName,
values = dataTable.Rows.OfType <System.Data.DataRow>().Select((r, i) => r[f.ColumnName])
});
sqlBulk.DestinationTableName = "ImportClients";
sqlBulk.NotifyAfter = 1000;
MapColumns(sqlBulk);
sqlBulk.SqlRowsCopied += (s, e) =>
{
System.Diagnostics.Debug.Write(e.RowsCopied);
};
sqlBulk.WriteToServer(dataTable);
}
}
}
}