Esempio n. 1
0
        /// writeData(ReturnData, TableSettings, Tables.Store)
        /// <summary>
        /// Takes the data returned from getData and tries to write it to a file
        /// for each store passed to it
        /// </summary>
        /// <param name="PRM_STORE_NUMBER"></param>
        /// <param name="tableSettings"></param>
        static void writeData(ReturnData returnData, TableSettings tableSettings, Tables.Store store)
        {
            if (showDebug)
            {
                Util.Logging.Log(Util.Logging.Type.Debug, string.Concat("Writing ", tableSettings.table, " data for Store: ", store.PRM_STORE_NUMBER), string.Empty, string.Empty);
            }

            // Check if the pull of data from the database was successful
            if (returnData.isSuccess())
            {
                try
                {
                    /*
                     * Note:
                     * If we can't rely on the SQL script to get proper lengths, we could read in the format file
                     * and resize at the datatable, but it would add some additional processing time for the
                     * larger datasets.  If so, add format_file to tables.json
                     */

                    // Create a StringBuilder object
                    StringBuilder sb       = new StringBuilder();
                    int           rowCount = 0; // Used to return the number of written rows
                    foreach (DataRow row in returnData.getDataTable().Rows)
                    {
                        // Get the fields from the row
                        IEnumerable <string> fields = row.ItemArray.Select(field => field.ToString());
                        // Append the line to the StringBuilder object using the filler character
                        sb.AppendLine(string.Join(fillerChar, fields));
                        rowCount++; // Increment the counter
                    }

                    // Create the full path for the output file
                    // {base folder}\{store number}\{export filename}
                    string output = string.Concat(exportFolder, @"\", store.PRM_STORE_NUMBER, @"\", tableSettings.export_file);

                    // Attempt to write the text to a file
                    File.WriteAllText(output, sb.ToString());

                    if (showDebug)
                    {
                        // Show that the file has been saved (if showing debug messages)
                        Util.Logging.Log(
                            Util.Logging.Type.Debug,
                            string.Concat("Saving ", output),
                            string.Empty,
                            string.Concat("SQL File Used: ", tableSettings.sql_filename, " | Rows written: " + rowCount.ToString())
                            );
                    }
                }
                catch (Exception ex)
                {
                    // Unexpected error
                    Util.Logging.Log(
                        Util.Logging.Type.Error,
                        "Unexpected Error",
                        "Program_writeData_001",
                        ex.Message
                        );
                    return;
                }
            }
            else
            {
                // Database did not return the data as expected
                Util.Logging.Log(
                    Util.Logging.Type.Error,
                    string.Concat("Unexpected Error getting ", "tablenamehere", " data"),
                    "Main_writeData_002",
                    string.Concat("SQL File Used: ", tableSettings.sql_filename, " | ", returnData.getReturnMessage())
                    );
                return;
            }
        }
Esempio n. 2
0
        /// getData(TableSettings, Tables.Store)
        /// <summary>
        ///  Tries to get the data for the table specified in the tableSettings
        ///  object, for the store specified,
        /// </summary>
        /// <param name="tableSettings"></param>
        /// <param name="store"></param>
        static ReturnData getData(TableSettings tableSettings, Tables.Store store)
        {
            if (showDebug)
            {
                Util.Logging.Log(Util.Logging.Type.Debug, string.Concat("Getting ", tableSettings.table, " data"), string.Empty, string.Empty);
                if (store != null)
                {
                    Util.Logging.Log(Util.Logging.Type.Debug, string.Concat("PRM_STORE_NUMBER: ", store.PRM_STORE_NUMBER), string.Empty, string.Empty);
                }
                else
                {
                    Util.Logging.Log(Util.Logging.Type.Debug, "Store not passed", string.Empty, string.Empty);
                }
                Util.Logging.Log(Util.Logging.Type.Debug, string.Concat("Table: ", tableSettings.table), string.Empty, string.Empty);
                Util.Logging.Log(Util.Logging.Type.Debug, string.Concat("Record Type: ", tableSettings.record_type), string.Empty, string.Empty);
                Util.Logging.Log(Util.Logging.Type.Debug, string.Concat("Export File: ", tableSettings.export_file), string.Empty, string.Empty);
                Util.Logging.Log(Util.Logging.Type.Debug, string.Concat("Stored Procedure: ", tableSettings.stored_procedure), string.Empty, string.Empty);
                Util.Logging.Log(Util.Logging.Type.Debug, string.Concat("Use Stored Procedure?: ", tableSettings.use_stored_proc.ToString()), string.Empty, string.Empty);
                Util.Logging.Log(Util.Logging.Type.Debug, string.Concat("SQL Filename: ", tableSettings.sql_filename.ToString()), string.Empty, string.Empty);
                Util.Logging.Log(Util.Logging.Type.Debug, string.Concat("Use Store Number?: ", tableSettings.use_store_number.ToString()), string.Empty, string.Empty);
            }

            // Get the database object
            IDatabase database = new MSSQL();
            // Define returnData as unsuccessful
            ReturnData returnData = new ReturnData(null, false, 0, string.Empty);

            // Check if we're using a stored procedure or a SQL file
            if (tableSettings.use_stored_proc)
            {
                // Use a stored procedure instead of a SQL file
                if (!string.IsNullOrEmpty(tableSettings.stored_procedure))
                {
                    List <SqlParameter> sqlParameters = new List <SqlParameter>();
                    if (tableSettings.use_store_number)
                    {
                        if (store == null)
                        {
                            // Store must be passed if we are using it to generate the query
                            Util.Logging.Log(
                                Util.Logging.Type.Error,
                                "Unable to continue, store is null or blank",
                                "Program_writeData_001",
                                string.Concat("Stored Proc to use: ", tableSettings.stored_procedure)
                                );
                            return(new ReturnData(null, false, 0, "Unable to continue, store is null or blank"));
                        }
                        if (!string.IsNullOrEmpty(store.PRM_STORE_NUMBER))
                        {
                            // Parameterize the PRM_STORE_NUMBER to @store
                            SqlParameter sqlParameter = new SqlParameter();
                            sqlParameter.ParameterName = "@store";
                            sqlParameter.Value         = store.PRM_STORE_NUMBER;
                            sqlParameters.Add(sqlParameter);
                        }
                        else
                        {
                            // PRM_STORE_NUMBER was not passed
                            Util.Logging.Log(
                                Util.Logging.Type.Error,
                                "Unable to use store number, PRM_STORE_NUMBER is null or blank",
                                "Program_writeData_002",
                                string.Concat("SQL File Used: ", tableSettings.sql_filename)
                                );
                            return(new ReturnData(null, false, 0, "PRM_STORE_NUMBER is null or blank"));
                        }
                    }
                    // Get the data from the database using the stored procedure
                    return(database.getDataFromStoredProc(tableSettings.stored_procedure, sqlParameters));
                }
                else
                {
                    Util.Logging.Log(
                        Util.Logging.Type.Error,
                        "Unable to use stored procedure",
                        "Program_writeData_002",
                        "Stored procedure name is null or blank in tables.json"
                        );
                    return(new ReturnData(null, false, 0, "Stored procedure name is null or blank in tables.json"));
                }
            }
            else
            {
                // Use a SQL file (if it exists)
                string sql = string.Empty;
                if (File.Exists(string.Concat(tableSettings.sql_filename)))
                {
                    // Read the SQL file
                    sql = File.ReadAllText(tableSettings.sql_filename);
                }
                else
                {
                    // SQL File does not exist
                    Util.Logging.Log(
                        Util.Logging.Type.Error,
                        "Unable to use SQL File, File does not exist",
                        "Program_writeData_003",
                        string.Concat("SQL File Used: ", tableSettings.sql_filename)
                        );
                    return(new ReturnData(null, false, 0, "Unable to use SQL File, File does not exist"));
                }
                if (tableSettings.use_store_number)
                {
                    if (store == null)
                    {
                        // Store must be passed if we are using it to generate the query
                        Util.Logging.Log(
                            Util.Logging.Type.Error,
                            "Unable to continue, store is null or blank",
                            "Program_writeData_004",
                            string.Concat("SQL File to use: ", tableSettings.sql_filename)
                            );
                        return(new ReturnData(null, false, 0, "Unable to continue, store is null or blank"));
                    }
                    if (!string.IsNullOrEmpty(store.PRM_STORE_NUMBER))
                    {
                        // Replace all instances of @store with the PRM_STORE_NUMBER in quotes
                        sql = sql.Replace("@store", string.Concat("'", store.PRM_STORE_NUMBER, "'"));
                    }
                    else
                    {
                        // PRM_STORE_NUMBER was not passed
                        Util.Logging.Log(
                            Util.Logging.Type.Error,
                            "Unable to use store number, PRM_STORE_NUMBER is null or blank",
                            "Program_writeData_005",
                            string.Concat("SQL File Used: ", tableSettings.sql_filename)
                            );
                        return(new ReturnData(null, false, 0, "PRM_STORE_NUMBER is null or blank"));
                    }
                }
                // Get the data from the database using the SQL script
                return(database.getDataFromSQLString(sql));
            }
        }