// ----------------------------------------------------------------------------------------
/// <!-- ElementExists -->
/// <summary>
/// Looks for element exists on direct id transfer transformations (instructions)
/// </summary>
/// <param name="module"></param>
/// <param name="field"></param>
/// <param name="tableName"></param>
/// <param name="aspect"></param>
/// <returns></returns>
protected bool ElementExists(string tableName, DataRow field, InfoAspect aspect)
{
bool exists = false;
try
{
if (tableName == "Level_1") // "V"
{
TransformField transform = Transform["Level_1_ID"];
SqlInt32 extractPkValue = InData.GetSqlInt32(transform.XrefLookup);
string loadPkColumn = ColumnName_LoadId(tableName, aspect.Enter()); aspect--;
exists = CheckElementExists(tableName, loadPkColumn, extractPkValue, aspect.Enter()); aspect--;
}
else
{
// ------------------------------------------------------------------
// The normal direct 1 to 1 conversion case
// ------------------------------------------------------------------
string extractPkColumn = ColumnName_ExtractPkFromLoad(tableName, aspect.Enter()); aspect--;
SqlInt32 extractPkValue = InData.GetSqlInt32(field, extractPkColumn);
string loadPkColumn = ColumnName_LoadId(tableName, aspect.Enter()); aspect--;
exists = CheckElementExists(tableName, loadPkColumn, extractPkValue, aspect.Enter()); aspect--;
}
}
catch (Exception ex) { Pause(); aspect.Rethrow(ex); }
return(exists);
}
// ----------------------------------------------------------------------------------------
/// <!-- Update -->
/// <summary>
/// Updates a row in one of the location related tables
/// </summary>
/// <param name="updateQuery"></param>
/// <param name="field"></param>
/// <param name="importedData"></param>
/// <param name="aspect"></param>
private SqlInt32 Update(string tableName, DataRow field, DataTable importedData, InfoAspect aspect)
{
if (tableName == "Instructions")
{
string updateQuery = "";
object obj = null;
using (SqlCommand cmd = new SqlCommand(updateQuery, aspect.SecondaryConnection))
{
try
{
// --------------------------------------------------------------
// Fill the command
// --------------------------------------------------------------
//UpdateQueryFill(cmd, tableName, field, aspect.Enter(Name, "QueryFill")); aspect--;
foreach (string transformKey in Transform.Keys)
{
AddParameterToLoad(cmd, tableName, field, transformKey, aspect.Enter()); aspect--;
}
// --------------------------------------------------------------
// Perform the insert
// --------------------------------------------------------------
obj = ExecuteScalar(cmd, tableName, aspect.Enter()); aspect--;
string report = InData.AsciiNewQuery(cmd);
if (obj != null)
{
SqlInt32 id = (SqlInt32)obj;
// ------------------------------------------------------------------
// Record the insert
// ------------------------------------------------------------------
if (id > 0)
{
int row = importedData.Rows.Count;
importedData.ImportRow(field);
importedData.Rows[row][tableName + "DbId"] = id;
field[RealIdColumn] = id;
}
}
else
{
string report2 = InData.AsciiNewQuery(cmd);
}
}
catch (SqlException ex) { Pause(); string report = InData.AsciiNewQuery(cmd); aspect.Rethrow(ex); }
catch (Exception ex) { Pause(); string report = InData.AsciiNewQuery(cmd); aspect.Rethrow(ex); }
}
}
return(0);
}
// ----------------------------------------------------------------------------------------
/// <!-- Import -->
/// <summary>
/// Imports a DataTable of location elements into one of the location tables
/// </summary>
/// <param name="importData"></param>
/// <param name="aspect"></param>
/// <returns></returns>
public DataTable Import(DataTable importData, string tableName, InfoAspect aspect)
{
// --------------------------------------------------------------------------
// Initialize variables
// --------------------------------------------------------------------------
DataTable importedData = ImportDalCommon.InitializeImportedDataTable(importData, tableName + "DbId", aspect.Enter()); aspect--; // the output table
string insertQuery = InsertQuery(tableName, aspect.Enter()); aspect--;
string updateQuery = UpdateQuery(tableName, aspect.Enter()); aspect--;
for (int row = 0; row < importData.Rows.Count; ++row)
{
DataRow field = importData.Rows[row];
try
{
// ------------------------------------------------------------------
// Insert or Update a valid location record in one of four location tables
// ------------------------------------------------------------------
bool isValid = (bool)field["IsValid"];
SqlInt32 id = SqlInt32.Null;
if (isValid)
{
bool isUpdate = (bool)field["IsUpdate"];
if (!isUpdate)
{
isUpdate = ElementExists(tableName, field, aspect.Enter()); aspect--;
}
if (isUpdate)
{
id = Update(tableName, field, importedData, aspect.Enter(tableName, "Update")); aspect--;
}
else
{
id = Insert(insertQuery, tableName, field, importedData, aspect.Enter(tableName, "Insert")); aspect--;
}
}
}
catch (Exception ex) { Pause(); aspect.Rethrow(ex); }
}
return(importedData);
}
// ----------------------------------------------------------------------------------------
/// <!-- Insert -->
/// <summary>
/// If the location element does not exist then insert into one of the four location tables
/// </summary>
/// <param name="insertQuery"></param>
/// <param name="field"></param>
/// <param name="importedData">adds row to this table if the location element is actually saved</param>
/// <param name="aspect"></param>
/// <returns></returns>
private SqlInt32 Insert(string insertQuery, string tableName, DataRow field, DataTable importedData, InfoAspect aspect)
{
SqlInt32 id = SqlInt32.Null;
object obj = null;
using (SqlCommand cmd = new SqlCommand(insertQuery, aspect.SecondaryConnection))
{
try
{
// ------------------------------------------------------------------
// Fill the command
// ------------------------------------------------------------------
//InsertQueryFill(cmd, tableName, field, aspect.Enter(Name,"QueryFill")); aspect--;
foreach (string transformKey in Transform.Keys)
{
AddParameterToLoad(cmd, tableName, field, transformKey, aspect.Enter()); aspect--;
}
// ------------------------------------------------------------------
// Sanity check
// ------------------------------------------------------------------
string err = ParameterSanityCheck(cmd);
if (!string.IsNullOrEmpty(err.Trim()))
{
throw new Exception(err);
}
// ------------------------------------------------------------------
// Perform the insert
// ------------------------------------------------------------------
obj = ExecuteScalar(cmd, tableName, aspect.Enter()); aspect--;
string report = InData.AsciiNewQuery(cmd);
if (obj != null)
{
id = InData.GetSqlInt32(obj);
// ------------------------------------------------------------------
// Record the insert
// ------------------------------------------------------------------
if (id > 0)
{
int row = importedData.Rows.Count;
importedData.ImportRow(field);
importedData.Rows[row][tableName + "DbId"] = id;
field[RealIdColumn] = id;
}
}
else
{
string report2 = InData.AsciiNewQuery(cmd);
}
}
catch (SqlException ex) { Pause(); string report = InData.AsciiNewQuery(cmd); aspect.Rethrow(ex); }
catch (Exception ex) { Pause(); string report = InData.AsciiNewQuery(cmd); aspect.Rethrow(ex); }
finally
{
// ------------------------------------------------------------------
// Make sure the database identity structure is set properly
// ------------------------------------------------------------------
string query = " SET IDENTITY_INSERT [" + tableName + "] OFF;";
SqlCommand reset = new SqlCommand(query, aspect.SecondaryConnection);
reset.ExecuteNonQuery();
}
}
return(id);
}
// ----------------------------------------------------------------------------------------
/// <!-- ImportErrorsInsert -->
/// <summary>
/// Inserts all module import errors into the database
/// </summary>
/// <param name="statusErrorLog"></param>
/// <param name="itemIdByLineNum"></param>
/// <param name="connection"></param>
/// <returns></returns>
public static int ImportErrorsInsert(DataTable statusErrorLog, Dictionary <int, int> itemIdByLineNum
, string moduleName, InfoAspect aspect)
{
int errorCount = 0;
try
{
string query = ImportDalCommon.ImportErrorQuery(moduleName);
for (int row = 0; row < statusErrorLog.Rows.Count; ++row)
{
// ------------------------------------------------------------------
// Process the row
// ------------------------------------------------------------------
string statusErrorCode = statusErrorLog.Rows[row]["Status_Error_Code"].ToString();
if (!Regex.IsMatch(statusErrorCode, "0[01]$"))
{
ImportDalCommon.ImportErrorInsert(query, statusErrorLog, row, statusErrorCode
, itemIdByLineNum, moduleName, aspect.Enter(moduleName, "ImportErrorInsert")); aspect--;
errorCount++;
}
}
}
catch (Exception ex) { Pause(); aspect.Rethrow(ex); }
return(errorCount);
}