private Dictionary <string, object> GetMltRowValues(string recordString, fr_record record, int recordLine)
{
List <frd_record_definition> recordDefinitions = record.frd_record_definition.Where(r => r.Line == recordLine).OrderBy(r => r.Sequence).ToList();
int pos = 0;
Dictionary <string, object> colValues = new Dictionary <string, object>();
foreach (frd_record_definition recordDefinition in recordDefinitions)
{
if (pos + recordDefinition.Length <= recordString.Length)
{
string columnValue = recordString.Substring(pos, recordDefinition.Length).Trim();
colValues.Add(recordDefinition.Name, createObject(columnValue, recordDefinition.ed_element_definition.DataType, recordDefinition.ed_element_definition.SourceFormatString, recordDefinition.DecimalPlaces));
}
pos += recordDefinition.Length;
}
return(colValues);
}
private string GetColumnSeparator(fr_record record)
{
string columnSeparator = null;
if (record != null)
{
switch (record.Template.ToLower())
{
case "template":
{
columnSeparator = null;
break;
}
case "f_csv":
case "csv":
{
columnSeparator = ",";
break;
}
case "ssv": // semi-colon separated
{
columnSeparator = ";";
break;
}
default:
{
columnSeparator = null;
break;
}
}
}
return(columnSeparator);
}
private bool WriteMltRecordType(string absolutePath, string distribution, ft_transaction transaction)
{
bool result = false;
string tableName = transaction.Description;
List <frd_record_definition> columns = new List <frd_record_definition>();
List <ftd_transaction_definition> transactionDefinitions = transaction.ftd_transaction_definition.OrderBy(t => t.Sequence).ToList();
foreach (ftd_transaction_definition transactionDefinition in transactionDefinitions)
{
List <frd_record_definition> recordDefinitions = transactionDefinition.fr_record.frd_record_definition.OrderBy(r => r.Sequence).ToList();
recordDefinitions.ForEach(r => columns.Add(r));
}
//version and/or create target table if required
//validateTargetTable(tableName, false, DateTime.Now, columns);
List <Dictionary <string, object> > rows = new List <Dictionary <string, object> >();
Dictionary <string, object> row = new Dictionary <string, object>();
//start SQL Transaction
SqlConnection connection = dataAccess.GetConnection();
connection.Open();
SqlTransaction tran = connection.BeginTransaction();
//blank or non-numeric distribution implies '01'
if (distribution == string.Empty | !int.TryParse(distribution, out int d))
{
distribution = "01";
}
//distribution is defined as char(2)
distribution = distribution.PadRight(2, ' ');
//get all identifiers for this transaction
List <string> identifiers = new List <string>();
foreach (ftd_transaction_definition transactionDefinition in transactionDefinitions)
{
identifiers.Add(transactionDefinition.fr_record.Identifier);
}
List <Tuple <string, string, int, int?> > colDefs = new List <Tuple <string, string, int, int?> >();
foreach (frd_record_definition column in columns)
{
colDefs.Add(new Tuple <string, string, int, int?>(column.Name, column.ed_element_definition.DataType, column.Length, column.DecimalPlaces));
}
dataAccess.CreateTable(tableName, colDefs, false, string.Empty);
try
{
int size = (int)transaction.ftd_transaction_definition.FirstOrDefault().fr_record.RecordLength;
using (StreamReader sr = new StreamReader(File.OpenRead(absolutePath), Encoding.UTF8, false, size))
{
//calculate number of records to read
//int recordCount = 0;
//long fileLength = sr.BaseStream.Length;
//int totalHeaderSize = (transaction.HeaderLength ?? 0) * (transaction.HeaderCount ?? 1);
//int totalTrailerSize = (transaction.TrailerLength ?? 0) * (transaction.TrailerCount ?? 1);
//long recordBytes = fileLength - (totalHeaderSize + totalTrailerSize);
//if (recordBytes > 0)
//{
// recordCount = (int)Math.Ceiling((double)recordBytes / size);
//}
//bool hasTrailer = totalTrailerSize != 0;
int recordLine = 1;
string rowIdentifier = string.Empty;
//skip header
//readNext(sr, totalHeaderSize);
//for (int x = 0; x < recordCount; x++)
//{
string line = string.Empty;
while ((line = sr.ReadLine()) != null)
{
//size = (x == recordCount-1) ? size-2 : size;
//string recordString = readNext(sr, size);
//if (recordString.Length != size)
//{
// break;
//}
//string lineIdentifier = GetMltIdentifier(recordString, identifiers);
string lineIdentifier = GetMltIdentifier(line, identifiers);
if (string.IsNullOrEmpty(lineIdentifier))
{
recordLine++;
}
else
{
rowIdentifier = lineIdentifier;
recordLine = 1;
}
ftd_transaction_definition transDef = transactionDefinitions.Where(t => t.fr_record.Identifier == rowIdentifier).FirstOrDefault();
if (transDef != null)
{
fr_record record = transDef.fr_record;
//check for new transaction
if (rowIdentifier == identifiers[0] && row.Keys.Count > 0)
{
rows.Add(row);
row = new Dictionary <string, object>();
}
//Dictionary<string, object> rowValues = GetMltRowValues(recordString, record, recordLine);
Dictionary <string, object> rowValues = GetMltRowValues(line, record, recordLine);
foreach (KeyValuePair <string, object> rowValue in rowValues)
{
row.Add(rowValue.Key, rowValue.Value);
}
}
if (rows.Count == 20)
{
dataAccess.InsertDynamicWithColumns(connection, tran, tableName, 0, distribution, rows, columns.Select(c => c.Name).ToList());
rows.Clear();
}
}
}
if (row.Keys.Count > 0)
{
rows.Add(row);
}
if (rows.Count > 0)
{
dataAccess.InsertDynamicWithColumns(connection, tran, tableName, 0, distribution, rows, columns.Select(c => c.Name).ToList());
}
tran.Commit();
result = true;
}
catch (Exception ex)
{
tran.Rollback();
}
finally
{
connection.Close();
}
return(result);
}