Exemple #1
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="CSVRootPath"></param>
        /// <returns></returns>
        public static List <CSVFileEntry> ReadCSVReadyFile(string CSVRootPath)
        {
            // 1. Initialize output list of file entries
            List <CSVFileEntry> CSVFileEntryList = new List <CSVFileEntry>();

            // 2. Open input stream to read from CSV Ready File
            string       CSVReadyFilePath = Path.Combine(CSVRootPath, "csv_ready.csv");
            StreamReader ReadyFileStream  = new StreamReader(CSVReadyFilePath);

            // 3. Read each line in CSV Ready file and split the lines into
            //    individual columns separated by commas
            string ReadyFileLine;

            while ((ReadyFileLine = ReadyFileStream.ReadLine()) != null)
            {
                // 3.a. Expect each line in the CSV ready file to be of the format:
                // <FileName>,<NumRows>,<TargetTable>,<MD5Checksum>
                string[] ReadyFileLineTokens = ReadyFileLine.Split(',');

                // 3.b. Create an empty FileEntry and populate it with the columns
                CSVFileEntry FileEntry = new CSVFileEntry();
                FileEntry.FilePath    = Path.Combine(CSVRootPath, ReadyFileLineTokens[0].Trim()); // column 1
                FileEntry.HeaderPath  = FileEntry.FilePath + ".hdr";
                FileEntry.RowCount    = int.Parse(ReadyFileLineTokens[1].Trim());                 // column 2
                FileEntry.TargetTable = ReadyFileLineTokens[2].Trim();                            // column 3
                FileEntry.Checksum    = ReadyFileLineTokens[3].Trim();                            // column 4

                // 3.c. Add file entry to output list
                CSVFileEntryList.Add(FileEntry);
            }

            // 4. Close input stream and return output file entry list
            ReadyFileStream.Close();
            return(CSVFileEntryList);
        }
Exemple #2
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="DBEntry"></param>
        /// <param name="FileEntry"></param>
        public static bool UpdateComputedColumns(DatabaseEntry DBEntry, CSVFileEntry FileEntry)
        {
            try {
                using (SqlConnection SqlConn = new SqlConnection(DBEntry.ConnectionString)) {
                    switch (FileEntry.TargetTable.ToUpper())
                    {
                    case "P2DETECTION":
                        // Update ZoneID
                        SqlConn.Open();
                        SqlCommand SqlCmd =
                            new SqlCommand(@"UPDATE P2Detection SET zoneID = (""dec""+(90.0))/(0.0083333)", SqlConn);
                        SqlCmd.ExecuteNonQuery();
                        SqlConn.Close();

                        // Update cx
                        SqlConn.Open();
                        SqlCmd =
                            new SqlCommand(@"UPDATE P2Detection SET cx = (COS(RADIANS(""dec""))*COS(RADIANS(ra)))", SqlConn);
                        SqlCmd.ExecuteNonQuery();
                        SqlConn.Close();

                        // Update cy
                        SqlConn.Open();
                        SqlCmd =
                            new SqlCommand(@"UPDATE P2Detection SET cy = COS(RADIANS(""dec""))*SIN(RADIANS(ra))", SqlConn);
                        SqlCmd.ExecuteNonQuery();
                        SqlConn.Close();

                        // Update cz
                        SqlConn.Open();
                        SqlCmd =
                            new SqlCommand(@"UPDATE P2Detection SET cz = (SIN(RADIANS(""dec"")))", SqlConn);
                        SqlCmd.ExecuteNonQuery();
                        SqlConn.Close();

                        break;

                    case "P2FRAMEMETA":
                        // No columns to be updated for FrameMeta
                        break;

                    case "P2IMAGEMETA":
                        // No columns to be updated for ImageMeta
                        break;

                    default:
                        // none of the table types matches...invalid
                        return(false);
                    }
                }
            } catch (Exception ex) {
                // update column failed
                return(false);
            }
            // update column success
            return(true);
        }
Exemple #3
0
        /// <summary>
        /// Test if a CSV File defined in the CSV Ready list actually exists on disk.
        /// </summary>
        /// <param name="FileEntry">FileEntry for CSVFile to test</param>
        /// <returns>True if the FilePath in the given FileEntry exists on disk. False otherwise.</returns>
        public static bool IsExistsCSVFile(CSVFileEntry FileEntry)
        {
            FileInfo CSVFileInfo = new FileInfo(FileEntry.FilePath);

            if (!CSVFileInfo.Exists)
            {
                return(false);
            }

            FileInfo CSVFileHeaderInfo = new FileInfo(FileEntry.HeaderPath);

            return(CSVFileHeaderInfo.Exists);
        }
Exemple #4
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="DBEntry"></param>
        /// <param name="FileEntry"></param>
        /// <returns></returns>
        public static bool IsMatchTableColumnRanges(DatabaseEntry DBEntry, CSVFileEntry FileEntry)
        {
            // determine expected column ranges
            List <LoadConstants.ColumnRange> ExpectedColumnRanges = null;

            switch (FileEntry.TargetTable.ToUpper())
            {
            case "P2DETECTION":
                ExpectedColumnRanges = LoadConstants.EXPECTED_DETECTION_COL_RANGES;
                break;

            case "P2FRAMEMETA":
                // No columns range values available for FrameMeta
                ExpectedColumnRanges = new List <LoadConstants.ColumnRange>();
                break;

            case "P2IMAGEMETA":
                // No columns range values available for ImageMeta
                ExpectedColumnRanges = new List <LoadConstants.ColumnRange>();
                break;

            default:
                // none of the table types matches...invalid
                return(false);
            }

            // connect to database instance
            using (SqlConnection SqlConn = new SqlConnection(DBEntry.ConnectionString)) {
                // For each column in available list, test if rows in table fall outside expected range
                foreach (LoadConstants.ColumnRange Column in ExpectedColumnRanges)
                {
                    // build SQL command for error count
                    string SqlStr =
                        string.Format(@"SELECT COUNT(*) FROM {0} WHERE ({1} < {2} OR {1} > {3}) AND {1} != -999",
                                      FileEntry.TargetTable, Column.ColumnName,
                                      Column.MinValue, Column.MaxValue);

                    // execute range error count command
                    SqlConn.Open();
                    SqlCommand SqlCmd     = new SqlCommand(SqlStr, SqlConn);
                    int        ErrorCount = (int)SqlCmd.ExecuteScalar();
                    SqlConn.Close();
                    if (ErrorCount > 0)
                    {
                        return(false);      // found a range error
                    }
                }
            }

            return(true); // no range errors found
        }
Exemple #5
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="DBEntry"></param>
 /// <param name="FileEntry"></param>
 /// <returns></returns>
 public static bool IsMatchTableRowCount(DatabaseEntry DBEntry, CSVFileEntry FileEntry)
 {
     // does the number of rows expected match the number of rows loaded
     using (SqlConnection SqlConn = new SqlConnection(DBEntry.ConnectionString)) {
         // build row count command
         string SqlStr = "SELECT COUNT(*) FROM " + FileEntry.TargetTable;
         // execute row count command
         SqlConn.Open();
         SqlCommand SqlCmd   = new SqlCommand(SqlStr, SqlConn);
         int        RowCount = (int)SqlCmd.ExecuteScalar();
         SqlConn.Close();
         // check if row count matches expected row count
         return(RowCount == FileEntry.RowCount);
     }
 }
Exemple #6
0
        /// <summary>
        /// Checks if the correct list of column headers is present for the CSV file
        /// to match the table
        /// </summary>
        /// <param name="FileEntry">FileEntry for CSV File whose column headers to test</param>
        /// <returns>True if the column headers present in the CSV File are the same as the
        /// expected table columns. False otherwise.</returns>
        public static bool IsMatchCSVFileColumnNames(CSVFileEntry FileEntry)
        {
            // determine expected columns
            List <string> ExpectedColumns = null;

            switch (FileEntry.TargetTable.ToUpper())
            {
            case "P2DETECTION":
                ExpectedColumns = LoadConstants.EXPECTED_DETECTION_COLS;
                break;

            case "P2FRAMEMETA":
                ExpectedColumns = LoadConstants.EXPECTED_FRAME_META_COLS;
                break;

            case "P2IMAGEMETA":
                ExpectedColumns = LoadConstants.EXPECTED_IMAGE_META_COLS;
                break;

            default:
                // none of the table types match...invalid
                return(false);
            }

            // test if the expected and present column name counts are the same
            if (ExpectedColumns.Count != FileEntry.ColumnNames.Count)
            {
                return(false);
            }

            // test of all expected names exist in the columns present
            foreach (string ColumnName in ExpectedColumns)
            {
                if (!FileEntry.ColumnNames.Contains(ColumnName))
                {
                    return(false); // mismatch
                }
            }

            // all columns match
            return(true);
        }
Exemple #7
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="FileEntry"></param>
        /// <param name="FileEntry"></param>
        /// <returns></returns>
        public static CSVFileEntry ReadCSVFileColumnNames(CSVFileEntry FileEntry)
        {
            // 2. Read the header line of the CSV File.
            StreamReader CSVFileReader = new StreamReader(FileEntry.HeaderPath);
            string       HeaderRow     = CSVFileReader.ReadLine();

            // 3. Extract the comma-separated columns names of the CSV File from its header line.
            // Strip empty spaces around column names.
            List <string> ColumnNames = new List <string>(HeaderRow.Split(','));

            FileEntry.ColumnNames = new List <string>();
            foreach (string ColumnName in ColumnNames)
            {
                FileEntry.ColumnNames.Add(ColumnName.Trim());
            }

            CSVFileReader.Close();

            return(FileEntry);
        }
Exemple #8
0
 // derby bulk load: SYSCS_UTIL.SYSCS_IMPORT_TABLE
 /// <summary>
 /// Loads a CSV File into an existing table using Sql BULK INSERT command
 /// </summary>
 /// <param name="DBEntry">Database into which to load the CSV file</param>
 /// <param name="FileEntry">File to be bulk loaded into database table</param>
 /// <returns>True if the bulk load ran without exceptions. False otherwise.</returns>
 public static bool LoadCSVFileIntoTable(DatabaseEntry DBEntry, CSVFileEntry FileEntry)
 {
     try {
         // connect to database instance
         using (SqlConnection SqlConn = new SqlConnection(DBEntry.ConnectionString)) {
             // build bulk insert SQL command
             string SqlStr = string.Format(
                 @"BULK INSERT {0} FROM '{1}'
       WITH(BATCHSIZE=10000, CODEPAGE='raw', FIELDTERMINATOR=',',
       FIRSTROW=1, MAXERRORS=0, ROWTERMINATOR='0x0A', TABLOCK )",
                 FileEntry.TargetTable, FileEntry.FilePath);
             // execute bulk insert command
             SqlConn.Open();
             SqlCommand SqlCmd = new SqlCommand(SqlStr, SqlConn);
             SqlCmd.ExecuteNonQuery();
             SqlConn.Close();
         }
     } catch (Exception ex) {
         // bulk insert failed
         return(false);
     }
     // bulk insert success
     return(true);
 }