/// 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; } }
/// 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)); } }