private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
DataTable csvData = new DataTable();
try
{
using (CsvTextFieldParser csvReader = new CsvTextFieldParser(csv_file_path))
{
csvReader.SetDelimiter(',');
csvReader.HasFieldsEnclosedInQuotes = true;
string[] colFields = csvReader.ReadFields();
foreach (string column in colFields)
{
DataColumn datecolumn = new DataColumn(column);
datecolumn.AllowDBNull = true;
csvData.Columns.Add(datecolumn);
}
int j = 0;
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
//Making empty value as null
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
j += 1;
csvData.Rows.Add(fieldData);
}
}
}
catch (Exception ex)
{
}
return(csvData);
}
private List <object> GetData(int nrofrows)
{
try
{
if (Entities != null)
{
if (Entities.Count == 0)
{
if (Entities[0].Fields.Count == 0)
{
Getfields();
}
}
}
else
{
Entities = new List <EntityStructure>();
Getfields();
}
Records = new List <object>();
if (Entities != null)
{
fieldParser = new CsvTextFieldParser(Path.Combine(Dataconnection.ConnectionProp.FilePath, Dataconnection.ConnectionProp.FileName));
fieldParser.SetDelimiter(',');
DMTypeBuilder.CreateNewObject(DatasourceName, DatasourceName, Entities[0].Fields);
Dictionary <string, PropertyInfo> properties = new Dictionary <string, PropertyInfo>();
foreach (EntityField item in Entities[0].Fields)
{
properties.Add(item.fieldname, DMTypeBuilder.myType.GetProperty(item.fieldname));
// properties[item.ColumnName].SetValue(x, row[item.ColumnName], null);
}
string[] r = fieldParser.ReadFields();
while ((fieldParser.EndOfData == false))
{
dynamic x = Activator.CreateInstance(DMTypeBuilder.myType);
r = fieldParser.ReadFields();
for (int i = 0; i < Entities[0].Fields.Count; i++)
{
try
{
string st = r[i].ToString();
if (!string.IsNullOrEmpty(st) && !string.IsNullOrWhiteSpace(st))
{
properties[Entities[0].Fields[i].fieldname].SetValue(x, Convert.ChangeType(st, DMEEditor.Utilfunction.GetTypeCode(Type.GetType(Entities[0].Fields[i].fieldtype))), null);
}
}
catch (Exception fieldex)
{
}
}
Records.Add(x);
}
}
fieldParser.Close();
return(Records);
}
catch (Exception ex)
{
fieldParser.Close();
DMEEditor.AddLogMessage("Fail", $"Error : Could not Get Data from File {DatasourceName}- {ex.Message}", DateTime.Now, 0, null, Errors.Failed);
return(null);
}
}
private EntityStructure Getfields()
{
EntityStructure entityData = new EntityStructure();
try
{
string[] flds = null;
if (GetFileState() == ConnectionState.Open)
{
fieldParser = new CsvTextFieldParser(Path.Combine(Dataconnection.ConnectionProp.FilePath, Dataconnection.ConnectionProp.FileName));
fieldParser.SetDelimiter(',');
flds = fieldParser.ReadFields();
int y = 0;
List <EntityField> fl = new List <EntityField>();
string sheetname = Path.GetFileNameWithoutExtension(DatasourceName);
entityData.Viewtype = ViewType.File;
entityData.DatabaseType = DataSourceType.CSV;
entityData.DataSourceID = FileName;
entityData.DatasourceEntityName = sheetname;
entityData.Caption = sheetname;
entityData.EntityName = sheetname;
entityData.OriginalEntityName = sheetname;
entityData.Id = 0;
foreach (string field in flds)
{
EntityField f = new EntityField();
string entspace = Regex.Replace(field, @"\s+", "_");
f.fieldname = entspace;
f.Originalfieldname = field;
f.fieldtype = "System.String";
f.ValueRetrievedFromParent = false;
f.EntityName = sheetname;
f.FieldIndex = y;
f.Checked = false;
f.AllowDBNull = true;
f.IsAutoIncrement = false;
f.IsCheck = false;
f.IsKey = false;
f.IsUnique = false;
y++;
fl.Add(f);
}
entityData.Fields = GetFieldsbyTableScan(DatasourceName, fl, flds);
Entities = new List <EntityStructure>();
EntitiesNames = new List <string>();
Entities.Add(entityData);
EntitiesNames.Add(DatasourceName);
DMEEditor.ConfigEditor.SaveDataSourceEntitiesValues(new ConfigUtil.DatasourceEntities {
datasourcename = DatasourceName, Entities = Entities
});
}
}
catch (Exception ex)
{
DMEEditor.AddLogMessage("Fail", $"Error : Could not Create Entity For File {DatasourceName}- {ex.Message}", DateTime.Now, 0, null, Errors.Failed);
}
fieldParser.Close();
return(entityData);
}
private List <EntityField> GetFieldsbyTableScan(string sheetname, List <EntityField> flds, string[] datac)
{
int y = 0;
string valstring;
decimal dval;
double dblval;
long longval;
bool boolval;
int intval;
short shortval;
float floatval;
DateTime dateval = DateTime.Now;
// setup Fields for Entity
int i = 1;
fieldParser.TrimWhiteSpace = true;
// Scan all rows in Table for types
while ((fieldParser.EndOfData == false))
{
try
{
string[] r = fieldParser.ReadFields();
i += 1;
int j = 0;
// Scan fields in row for Types
foreach (EntityField f in flds)
{
try
{
if (f.fieldname.ToLower().Contains("date") || f.fieldname.ToLower().Contains("_dt"))
{
f.fieldtype = "System.DateTime";
f.Checked = true;
}
else
{
valstring = r[j].ToString();
dateval = DateTime.Now;
if (!f.Checked)
{
if (!string.IsNullOrEmpty(valstring) && !string.IsNullOrWhiteSpace(valstring))
{
if (decimal.TryParse(valstring, out dval))
{
f.fieldtype = "System.Decimal";
// f.Checked = true;
}
else
if (double.TryParse(valstring, out dblval))
{
f.fieldtype = "System.Double";
// f.Checked = true;
}
else
if (long.TryParse(valstring, out longval))
{
f.fieldtype = "System.Long";
// f.Checked = true;
}
else
if (float.TryParse(valstring, out floatval))
{
f.fieldtype = "System.Float";
// f.Checked = true;
}
else
if (int.TryParse(valstring, out intval))
{
f.fieldtype = "System.Int32";
// f.Checked = true;
}
else
if (DateTime.TryParse(valstring, out dateval))
{
f.fieldtype = "System.DateTime";
// f.Checked = true;
}
else
if (bool.TryParse(valstring, out boolval))
{
f.fieldtype = "System.Bool";
// f.Checked = true;
}
else
if (short.TryParse(valstring, out shortval))
{
f.fieldtype = "System.Short";
//f.Checked = true;
}
else
{
f.fieldtype = "System.String";
f.Checked = true;
}
}
}
}
}
catch (Exception Fieldex)
{
}
try
{
if (f.fieldtype.Equals("System.String", StringComparison.OrdinalIgnoreCase))
{
if (!string.IsNullOrEmpty(r[j]) && !string.IsNullOrWhiteSpace(r[j]))
{
if (!string.IsNullOrEmpty(r[j].ToString()))
{
if (r[j].ToString().Length > f.Size1)
{
f.Size1 = r[j].Length;
}
}
}
}
}
catch (Exception stringsizeex)
{
}
try
{
if (f.fieldtype.Equals("System.Decimal", StringComparison.OrdinalIgnoreCase))
{
if (!string.IsNullOrEmpty(r[j]) && !string.IsNullOrWhiteSpace(r[j]))
{
if (!string.IsNullOrEmpty(r[j].ToString()))
{
valstring = r[j].ToString();
if (decimal.TryParse(valstring, out dval))
{
f.fieldtype = "System.Decimal";
f.Size1 = GetDecimalPrecision(dval);
f.Size2 = GetDecimalScale(dval);
}
}
}
}
}
catch (Exception decimalsizeex)
{
}
j++;
}
}
catch (Exception rowex)
{
}
}
// Check for string size
foreach (EntityField fld in flds)
{
if (fld.fieldtype.Equals("System.string", StringComparison.OrdinalIgnoreCase))
{
if (fld.Size1 == 0)
{
fld.Size1 = 150;
}
}
}
return(flds);
}