public List <string> tt; //ordered type from csv
public bool CheckFields(ReadCsv r)
{
rcsv = r;
var csvColumns = new List <string>();
int csvFieldCount = r.parser.FieldCount;
using (IDbConnection dbConnection = ConnectionID)
{
//string sql = $"SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('{config.Desto}') ";
try
{
tt = new List <string>();
string sql = "SELECT QUOTENAME(SCHEMA_NAME(tb.[schema_id])) AS 'Schema' " +
",QUOTENAME(OBJECT_NAME(tb.[OBJECT_ID])) AS 'Table' " +
",C.NAME as 'Column'" +
",T.name AS 'Type',C.max_length ,C.is_nullable , C.is_identity as [identity] " +
"FROM SYS.COLUMNS C INNER JOIN SYS.TABLES tb ON tb.[object_id] = C.[object_id] " +
"INNER JOIN SYS.TYPES T ON C.system_type_id = T.user_type_id " +
$"WHERE tb.[is_ms_shipped] = 0 and tb.[object_id] = OBJECT_ID('{config.Desto}')";
dbConnection.Open();
int tc = 0;
string sTempRC;
var data = dbConnection.Query(sql).ToList();
cil = new List <ColumnInfo>();
foreach (dynamic row in data)
{
tc++;
if (row.identity)
{
continue;
}
if (row.Column.IndexOf(" ") > 0)
{
sTempRC = row.Column; //.Replace("\"","");
}
else
{
sTempRC = row.Column;
}
cil.Add(new ColumnInfo {
name = sTempRC, type = row.Type
});
}
rr = r.parser.RawRecord.Split(',');
for (int icnt = 0; icnt < rr.Length; icnt++)
{
tc++;
//Clean up carraig return in file here on column names (last one may have it).
if (rr[icnt].IndexOf("\n") > 0)
{
rr[icnt] = rr[icnt].Replace("\n", "");
}
if (rr[icnt].IndexOf("\r") > 0)
{
rr[icnt] = rr[icnt].Replace("\r", "");
}
while (rr[icnt].IndexOf("\"") >= 0)
{
rr[icnt] = rr[icnt].Replace("\"", "");
}
var lu = lookup(rr[icnt].ToLower());
if (lu == null)
{
var le = new LogError();
le.InsertError($"error field {rr[icnt]} not exist in schema {config.Name}", r.filename, config.Email,
false);
Program.Log($"error field {rr[icnt]} not exist in schema {config.Name} filename - {r.filename}");
return(false);
}
Debug.WriteLine(" column num:" + tc);
tt.Add(lu.type);
}
foreach (string cs in rr)
{
var rcomp = cs;
csvColumns.Add(rcomp.ToLower());
}
foreach (string col in csvColumns)
{
var test = checkColumn(col.ToLower());
if (!test) //checks column exist in db schema
{
var le = new LogError();
le.InsertError($"error field {col} not exist in schema " + config.Name, r.filename, config.Email,
false);
return(false);
}
}
return(true);
}
catch (Exception ex)
{
var le = new LogError();
le.InsertError(ex.Message, r.filename, config.Email,
false);
Program.Log(ex.Message + $" filename {r.filename} time:{DateTime.Now:hh:mm:ss}");
}
}
return(true);
}
static void ImportRun(string[] args)
{
//This project moves data from csv files into the import tables listed in the ImportSource table.
lasttimeRun = DateTime.Now;
// Set time to 7:30 so if it's run in the afternoon, it will run again the next morning.
Log($"Main: {lasttimeRun:hh:mm:ss}");
//Read ImportSource table for parameters
try
{
if (args.Length > 0 && args[0] == "testemail")
{
var te = new LogError();
te.InsertError("test email", "na", emailAddress, true);
return;
}
var cd = new ConfigData();
cd.PopulateList();
foreach (var config in cd.ImportListConfig)
{
var rcsv = new ReadCsv(config);
while (rcsv.Read())
{
try
{
config.Email = emailAddress;
var import = new Import(config);
if (import.CheckFields(rcsv))
{
import.ImportData(config);
}
import.Dispose();
}
catch (Exception ex)
{
var n = new LogError();
n.InsertError(ex.Message, rcsv.filename, emailAddress);
}
try
{
if (config.archiveLocation != null && config.archiveLocation.Length > 2)
{
//archive already processed found files
string target = config.archiveLocation + "\\" + Path.GetFileName(rcsv.filename);
if (!File.Exists(target))
{
File.Copy(rcsv.filename, config.archiveLocation + "\\" + Path.GetFileName(rcsv.filename));
}
ReadCsv.FileDelete(rcsv.filename);
}
}
catch (Exception ex)
{
var n = new LogError();
try
{
//we have to delete to prevent endless loop.
ReadCsv.FileDelete(rcsv.filename);
Log($"delete file {rcsv.filename} time: {DateTime.Now:hh:mm:ss}");
}
catch (Exception ex2)
{
n.InsertError("Delete file failed.", rcsv.filename, emailAddress);
Log($"delete failed {rcsv.filename} exception: {ex2.Message} time: {DateTime.Now:hh:mm:ss}");
throw new Exception("ERROR! Get out of loop");
}
n.InsertError("Copy to archive failed.", rcsv.filename, emailAddress);
Log($"copy failed {rcsv.filename} exception: {ex.Message} time: {DateTime.Now:hh:mm:ss}");
}
}
}
}
catch (Exception ex)
{
var n = new LogError();
n.InsertError(ex.Message, "not set yet", emailAddress);
Log(ex.Message + $" time: {DateTime.Now:hh:mm:ss}");
}
}