Beispiel #1
0
        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);
        }
Beispiel #2
0
        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}");
            }
        }