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