public async Task <DataTable> GetCSVTable(ConnectParameters source, ConnectParameters target, string indexDataType)
{
DataTable result = new DataTable();
if (dt.Rows.Count == 0)
{
DateTime timeStart = DateTime.Now;
DateTime timeEnd;
TimeSpan diff;
connectionString = target.ConnectionString;
dt = new DataTable();
string accessJson = await fileStorageService.ReadFile("connectdefinition", "PPDMDataAccess.json");
_dataDef = JsonConvert.DeserializeObject <List <DataAccessDef> >(accessJson);
string referenceJson = await fileStorageService.ReadFile("connectdefinition", "PPDMReferenceTables.json");
_references = JsonConvert.DeserializeObject <List <ReferenceTable> >(referenceJson);
string csvJson = await fileStorageService.ReadFile("connectdefinition", "CSVDataAccess.json");
_csvDef = JsonConvert.DeserializeObject <List <CSVAccessDef> >(csvJson);
//Console.WriteLine("start reading csv file");
string csvText = await fileStorageService.ReadFile(source.Catalog, source.FileName);
timeEnd = DateTime.Now;
diff = timeEnd - timeStart;
//Console.WriteLine($"Time span, read all definitions files: {diff}");
string dataType = source.DataType.Remove(source.DataType.Length - 1, 1);
dataAccess = _dataDef.First(x => x.DataType == dataType);
dt = NewDataTable(dataType);
tableDataType = dataType;
int headerLines = 1;
int wellCounter = 0;
CSVAccessDef csvAccess = _csvDef.First(x => x.DataType == dataType);
Dictionary <string, int> attributes = csvAccess.Mappings.ToDictionary();
Dictionary <string, string> constants = csvAccess.Constants.ToStringDictionary();
byte[] byteArray = Encoding.ASCII.GetBytes(csvText);
MemoryStream csvStream = new MemoryStream(byteArray);
using (TextFieldParser csvParser = new TextFieldParser(csvStream))
{
csvParser.CommentTokens = new string[] { "#" };
csvParser.SetDelimiters(new string[] { "," });
csvParser.HasFieldsEnclosedInQuotes = true;
for (int i = 0; i < headerLines; i++)
{
string line = csvParser.ReadLine();
}
while (!csvParser.EndOfData)
{
wellCounter++;
if ((wellCounter % 1000) == 0)
{
var tmp = wellCounter;
}
string[] fields = csvParser.ReadFields();
string key = GetDataKey(fields, attributes);
string duplicateKey = GetDuplicateKey(fields, attributes);
if (string.IsNullOrEmpty(key))
{
//Console.WriteLine($"Well {wellCounter} has an empty key");
}
else
{
if (duplicates.ContainsKey(duplicateKey))
{
DataRow[] rows = dt.Select(key);
rows[0] = InsertCSVRow(rows[0], attributes, fields, constants);
rows[0]["ROW_CHANGED_DATE"] = DateTime.Now.ToString("yyyy-MM-dd");
}
else
{
DataRow newRow = dt.NewRow();
newRow = InsertCSVRow(newRow, attributes, fields, constants);
newRow["ROW_CREATED_DATE"] = DateTime.Now.ToString("yyyy-MM-dd");
newRow["ROW_CHANGED_DATE"] = DateTime.Now.ToString("yyyy-MM-dd");
dt.Rows.Add(newRow);
duplicates.Add(duplicateKey, "");
}
}
}
}
timeEnd = DateTime.Now;
diff = timeEnd - timeStart;
//Console.WriteLine($"Time span, completion: {diff}");
}
if (tableDataType == indexDataType)
{
result = dt;
}
else
{
result = GetParentDataTable(indexDataType);
}
return(result);
}
public async Task LoadCSVFile(ConnectParameters source, ConnectParameters target, string fileName)
{
DateTime timeStart = DateTime.Now;
connectionString = target.ConnectionString;
string accessJson = await fileStorageService.ReadFile("connectdefinition", "PPDMDataAccess.json");
_dataDef = JsonConvert.DeserializeObject <List <DataAccessDef> >(accessJson);
string referenceJson = await fileStorageService.ReadFile("connectdefinition", "PPDMReferenceTables.json");
_references = JsonConvert.DeserializeObject <List <ReferenceTable> >(referenceJson);
string csvJson = await fileStorageService.ReadFile("connectdefinition", "CSVDataAccess.json");
_csvDef = JsonConvert.DeserializeObject <List <CSVAccessDef> >(csvJson);
//Console.WriteLine("start reading csv file");
string csvText = await fileStorageService.ReadFile(source.Catalog, fileName);
DateTime timeEnd = DateTime.Now;
TimeSpan diff = timeEnd - timeStart;
//Console.WriteLine($"Time span, read all definitions files: {diff}");
string dataType = source.DataType.Remove(source.DataType.Length - 1, 1);
dataAccess = _dataDef.First(x => x.DataType == dataType);
CSVAccessDef csvAccess = _csvDef.First(x => x.DataType == dataType);
Dictionary <string, int> attributes = csvAccess.Mappings.ToDictionary();
Dictionary <string, string> constants = csvAccess.Constants.ToStringDictionary();
Dictionary <string, string> columnTypes = dt.GetColumnTypes();
DbUtilities dbConn = new DbUtilities();
dbConn.OpenWithConnectionString(connectionString);
string dataTypeSql = dataAccess.Select;
attributeProperties = CommonDbUtilities.GetColumnSchema(dbConn, dataTypeSql);
dbConn.CloseConnection();
//Console.WriteLine("Start parsing csv file");
using (TextReader csvStream = new StringReader(csvText))
{
var conf = new CsvConfiguration(CultureInfo.InvariantCulture)
{
//BadDataFound = null
};
using (var csv = new CsvReader(csvStream, conf))
{
csv.Read();
csv.ReadHeader();
string[] headerRow = csv.HeaderRecord;
var attributeMappings = new Dictionary <string, string>();
foreach (var item in attributes)
{
int colNumber = item.Value;
attributeMappings.Add(headerRow[colNumber], item.Key);
}
List <dynamic> csvRecords = csv.GetRecords <dynamic>().ToList();
timeEnd = DateTime.Now;
diff = timeEnd - timeStart;
//Console.WriteLine($"Time span, parsed cvs file into dynamic objects: {diff}");
foreach (var row in csvRecords)
{
DynamicObject newCsvRecord = new DynamicObject();
foreach (var item in row)
{
if (attributeMappings.ContainsKey(item.Key))
{
string dbAttribute = attributeMappings[item.Key];
string value = item.Value;
string dataProperty = attributeProperties[dbAttribute];
if (dataProperty.Contains("varchar"))
{
string numberString = Regex.Match(dataProperty, @"\d+").Value;
int maxCharacters = Int32.Parse(numberString);
if (value.Length > maxCharacters)
{
value = value.Substring(0, maxCharacters);
}
}
newCsvRecord.AddProperty(dbAttribute, value);
}
}
FixKey(newCsvRecord);
}
timeEnd = DateTime.Now;
diff = timeEnd - timeStart;
//Console.WriteLine($"Time span, fixed dynamic objects: {diff}");
dt = DynamicToDT(newCsvRecords);
timeEnd = DateTime.Now;
diff = timeEnd - timeStart;
//Console.WriteLine($"Time span, transfer from csv to datatable: {diff}");
InsertTableToDatabase(attributes, dataType, target, constants);
}
}
timeEnd = DateTime.Now;
diff = timeEnd - timeStart;
//Console.WriteLine($"Time span, completion: {diff}");
}
