/// <summary>
///
/// This method will initialize the Insert prepared statement.
///
/// <param name="poBucketConfiguration">The configuration for the logical bucket (i.e., a table and a subset of its columns)</param>
/// <returns>Prepared statement that inserts a row to the logical Bucket</returns>
/// </summary>
private SqlCommand InitInsertCommand(AceAPIBucket poBucketConfiguration)
{
StringBuilder InsertStatement = new StringBuilder();
StringBuilder ValuesClause = new StringBuilder();
foreach (string sTmpColumn in poBucketConfiguration.SoughtColumns.Keys)
{
if (InsertStatement.Length > 0)
{
InsertStatement.Append(", ");
ValuesClause.Append(",");
}
else
{
InsertStatement.Append("INSERT INTO " + poBucketConfiguration.TableName + "(");
ValuesClause.Append(" VALUES(");
}
InsertStatement.Append(sTmpColumn);
ValuesClause.Append("@" + sTmpColumn);
}
ValuesClause.Append(")");
InsertStatement.Append(")" + ValuesClause.ToString());
SqlCommand NewInsertCommand = new SqlCommand(InsertStatement.ToString(), DbConn);
PrepareParemeters(NewInsertCommand, poBucketConfiguration);
return(NewInsertCommand);
}
} // method()
/// <summary>
///
/// Using the metadata for the configured Process, this method will determinen whether or not any values
/// for a designated Bucket are present in the current record parsed from the raw data payload.
///
/// <param name="poProcess">The structure that represents the Process being currently run</param>
/// <param name="psBucketName">The logical Bucket currently being targeted by the running instance of the Process</param>
/// <param name="poRecord">The current record parsed from the raw data payload and which will be applied to the Bucket's target table/columns</param>
/// <returns>A boolean indicating whether or not the record has any relevant values that belong to the logical Bucket</returns>
/// </summary>
private bool AreBucketValuesPresent(AceProcess poProcess, string psBucketName, Hashtable poRecord)
{
bool bValuesPresent = false;
List <string> oKeys = null;
AceAPIBucket oBucket = null;
if (!poProcess.DataAPIConfiguration.ApplyBuckets.ContainsKey(psBucketName))
{
return(false);
}
oBucket = poProcess.DataAPIConfiguration.ApplyBuckets[psBucketName];
foreach (string sTmpAttrName in oBucket.SoughtColumns.Keys)
{
if (!oBucket.ColKeys.Contains(sTmpAttrName))
{
string sTmpVal = (string)poRecord[sTmpAttrName];
if (!String.IsNullOrEmpty(sTmpVal))
{
bValuesPresent = true;
break;
}
}
}
return(bValuesPresent);
} // method()
/// <summary>
///
/// This method will extract the detailed information (like the payload's tags mapping to which
/// columns of a staging table) needed in order to drive the retrieval of data through the API.
///
/// <param name="poProcessDetailsReader">The reader that pulls metadata from the configuration tables</param>
/// <param name="poTmpConfig">The structure that will hold all of the extract config metadata</param>
/// <returns>None.</returns>
/// </summary>
private void SetAPIDetails(SqlDataReader poAPIDetailsReader, AceAPIConfiguration poTmpConfig)
{
while (poAPIDetailsReader.Read())
{
string sBucketName = poAPIDetailsReader["bucket_nm"].ToString();
string sTableName = poAPIDetailsReader["target_table_nm"].ToString();
if (!poTmpConfig.ApplyBuckets.Keys.Contains(sBucketName))
{
poTmpConfig.ApplyBuckets[sBucketName] = new AceAPIBucket(sBucketName, sTableName);
}
AceAPIBucket oTmpBucket = poTmpConfig.ApplyBuckets[sBucketName];
if (String.IsNullOrEmpty(oTmpBucket.BucketName))
{
oTmpBucket.BucketName = sBucketName;
oTmpBucket.TableName = sTableName;
}
// Add new row to bucket here
if (!poAPIDetailsReader.IsDBNull(2))
{
string sAttrName = poAPIDetailsReader["attr_nm"].ToString();
string sAttrType = poAPIDetailsReader["attr_ora_type"].ToString();
string sAttrLen = poAPIDetailsReader["attr_ora_type_len"].ToString();
string sAttrIsKey = poAPIDetailsReader["attr_is_key"].ToString();
string sAttrXPath = poAPIDetailsReader["attr_xpath"].ToString();
string sAttrIsXmlBody = poAPIDetailsReader["attr_is_xml_body"].ToString();
int nAttrLen = -1;
if (!String.IsNullOrEmpty(sAttrLen))
{
nAttrLen = Convert.ToInt32(sAttrLen);
}
SqlDbType oOraDbType = SqlDbType.VarChar;
if (!String.IsNullOrEmpty(sAttrType))
{
oOraDbType = GetMappedOraDbType(sAttrType);
}
bool bIsKey = false;
if (!String.IsNullOrEmpty(sAttrIsKey))
{
bIsKey = (sAttrIsKey == "Y") ? true : false;
}
bool bIsXmlBody = false;
if (!String.IsNullOrEmpty(sAttrIsXmlBody))
{
bIsXmlBody = (sAttrIsXmlBody == "Y") ? true : false;
}
oTmpBucket.AddTargetColumn(sAttrName, oOraDbType, bIsKey, nAttrLen, sAttrXPath, bIsXmlBody);
}
}
}
/// <summary>
///
/// This method will populate a provided Hashtable with data retrieved through the provided SqlDataReader.
/// After obtaining the data payload of the target record, it will use the metadata's XPath values to
/// parse the payload and pull out values of interest.
///
/// <param name="poNewProductReader">The Reader that is enumerating through the dataset of payloads (which were pulled via the REST API)</param>
/// <param name="poTmpConfig">The configuration for the currently running Process</param>
/// <param name="poNewProductRecord">The container that will hold the values parsed from the raw data payload for our record (i.e. product)</param>
/// <returns>None</returns>
/// </summary>
static public void PopulateProductData(SqlDataReader poNewProductReader, AceAPIConfiguration poTmpConfig, Hashtable poNewProductRecord)
{
string sDataRecord = poNewProductReader[0].ToString();
string sXPath = null;
XDocument oDataDoc = null;
if (!sDataRecord.Contains("<errors>") && !sDataRecord.Contains("<error>"))
{
using (StringReader oDataReader = new StringReader(sDataRecord))
{
oDataDoc = XDocument.Load(oDataReader, LoadOptions.PreserveWhitespace);
}
foreach (string sTmpBucketName in poTmpConfig.ApplyBuckets.Keys)
{
AceAPIBucket oTempBucket = poTmpConfig.ApplyBuckets[sTmpBucketName];
foreach (string sTmpAttrName in oTempBucket.SoughtColXPaths.Keys)
{
sXPath = oTempBucket.SoughtColXPaths[sTmpAttrName];
try
{
if (oTempBucket.SoughtColXmlBodies.Keys.Contains(sTmpAttrName) && oTempBucket.SoughtColXmlBodies[sTmpAttrName])
{
if (oDataDoc.XPathSelectElement(sXPath) != null)
{
poNewProductRecord[sTmpAttrName] = oDataDoc.XPathSelectElement(sXPath).ToString();
}
}
else if (oDataDoc.XPathSelectElement(sXPath) != null)
{
poNewProductRecord[sTmpAttrName] = oDataDoc.XPathSelectElement(sXPath).Value;
}
}
catch (Exception ex)
{
// Any logging should occur here
throw ex;
}
}
}
}
else
{
// Any logging should occur here
poNewProductRecord["error"] = sDataRecord;
}
}
} // method()
/// <summary>
///
/// Using the metadata of the designated process, this method will create a mapping between the logical buckets
/// of a raw data payload (like a composite XML body of a document) to a table and its various columns.
///
/// <param name="poProcess">The structure that represents the Process being currently run</param>
/// <returns>None</returns>
/// </summary>
private Dictionary <string, IApplicable> CreateApplyManagers(AceProcess poProcess)
{
Dictionary <string, IApplicable> ApplyManagers = new Dictionary <string, IApplicable>();
foreach (string sBucketName in poProcess.DataAPIConfiguration.ApplyBuckets.Keys)
{
AceAPIBucket TempBucket = poProcess.DataAPIConfiguration.ApplyBuckets[sBucketName];
AceApplyManager TempApplyManager = new AceApplyManager(moStgConnectionMetadata, TempBucket);
ApplyManagers[sBucketName] = TempApplyManager;
}
return(ApplyManagers);
}
/// <summary>
///
/// This method will create the parameters for the prepared statement.
///
/// <param name="poCommand">The preparement statement to which we will add the needed parameters</param>
/// <param name="poBucketConfiguration">The configuration for the logical bucket (i.e., a table and a subset of its columns)</param>
/// <returns>None</returns>
/// </summary>
private void PrepareParemeters(SqlCommand poCommand, AceAPIBucket poBucketConfiguration)
{
foreach (string sTmpColumn in poBucketConfiguration.SoughtColumns.Keys)
{
SqlDbType TargetType = poBucketConfiguration.SoughtColumns[sTmpColumn];
if (poBucketConfiguration.SoughtColLengths.Keys.Contains(sTmpColumn))
{
int TargetLen = poBucketConfiguration.SoughtColLengths[sTmpColumn];
poCommand.Parameters.Add(new SqlParameter(sTmpColumn, TargetType, TargetLen));
}
else
{
poCommand.Parameters.Add(new SqlParameter(sTmpColumn, TargetType));
}
}
}
/// <summary>
///
/// This method will set the parameters' values for the prepared statement.
///
/// <param name="poCommand">The preparement statement to which we will set the needed parameters</param>
/// <param name="poBucketConfiguration">The configuration for the logical bucket (i.e., a table and a subset of its columns)</param>
/// <param name="poRecord">The record whose values will be used to set the prepared statement</param>
/// <returns>None</returns>
/// </summary>
private void SetParemeters(SqlCommand poCommand, AceAPIBucket poBucketConfiguration, Hashtable poRecord)
{
foreach (string sTmpColumn in poBucketConfiguration.SoughtColumns.Keys)
{
if (poRecord.ContainsKey(sTmpColumn))
{
string sValue = (string)poRecord[sTmpColumn];
if (!String.IsNullOrEmpty(sValue))
{
poCommand.Parameters[sTmpColumn].Value = sValue;
}
else
{
poCommand.Parameters[sTmpColumn].Value = DBNull.Value;
}
}
}
}
public AceApplyManager(AceConnectionMetadata poConnMetadata, AceAPIBucket poBucketConfiguration)
{
DbConn = new SqlConnection(poConnMetadata.DBConnectionString);
DbConn.Open();
CountCommand = InsertCommand = UpdateCommand = RetrieveCommand = null;
BucketConfiguration = poBucketConfiguration;
InitPreparedStatements();
lock (ListsLock)
{
if (DateTypeList == null)
{
DateTypeList =
new List <SqlDbType> {
SqlDbType.Date, SqlDbType.DateTime, SqlDbType.DateTime2,
SqlDbType.SmallDateTime, SqlDbType.Time, SqlDbType.Time
};
}
if (NumericTypeList == null)
{
NumericTypeList =
new List <SqlDbType> {
SqlDbType.BigInt, SqlDbType.Bit, SqlDbType.Int,
SqlDbType.SmallInt, SqlDbType.TinyInt
};
}
if (DoubleTypeList == null)
{
DoubleTypeList =
new List <SqlDbType> {
SqlDbType.Decimal, SqlDbType.Float, SqlDbType.Money,
SqlDbType.Real, SqlDbType.SmallMoney
};
}
}
}
/// <summary>
///
/// This method will initialize the Select prepared statement.
///
/// <param name="poBucketConfiguration">The configuration for the logical bucket (i.e., a table and a subset of its columns)</param>
/// <returns>Prepared statement that returns a particular row based on the provided key</returns>
/// </summary>
private SqlCommand InitSelectCommand(AceAPIBucket poBucketConfiguration)
{
StringBuilder SelectStatement = new StringBuilder("SELECT ");
StringBuilder ClauseStatement = new StringBuilder();
foreach (string sTmpColumn in poBucketConfiguration.SoughtColumns.Keys)
{
if (SelectStatement.Length > 0)
{
SelectStatement.Append(", ");
}
SelectStatement.Append(sTmpColumn);
if (poBucketConfiguration.ColKeys.Contains(sTmpColumn))
{
if (ClauseStatement.Length > 0)
{
ClauseStatement.Append(" AND ");
}
else
{
ClauseStatement.Append(" WHERE ");
}
ClauseStatement.Append(sTmpColumn + " = @" + sTmpColumn);
}
}
SelectStatement.Append(" FROM " + poBucketConfiguration.TableName);
SelectStatement.Append(ClauseStatement.ToString());
SqlCommand RetrieveCommand = new SqlCommand(SelectStatement.ToString(), DbConn);
PrepareParemeters(RetrieveCommand, poBucketConfiguration);
return(RetrieveCommand);
}
/// <summary>
///
/// This method will initialize the Insert prepared statement.
///
/// <param name="poBucketConfiguration">The configuration for the logical bucket (i.e., a table and a subset of its columns)</param>
/// <returns>Prepared statement that inserts a row to the logical Bucket</returns>
/// </summary>
private SqlCommand InitUpdateCommand(AceAPIBucket poBucketConfiguration)
{
StringBuilder UpdateStatement = new StringBuilder();
StringBuilder UpdateClause = new StringBuilder();
foreach (string sTmpColumn in poBucketConfiguration.SoughtColumns.Keys)
{
if (UpdateStatement.Length > 0)
{
UpdateStatement.Append(", ");
UpdateClause.Append(" AND ");
}
else
{
UpdateStatement.Append("UPDATE " + poBucketConfiguration.TableName + " SET ");
UpdateClause.Append(" WHERE ");
}
if (!poBucketConfiguration.ColKeys.Contains(sTmpColumn))
{
UpdateStatement.Append(sTmpColumn + " = @" + sTmpColumn);
}
else
{
UpdateClause.Append(sTmpColumn + " = @" + sTmpColumn);
}
}
UpdateStatement.Append(")" + UpdateClause.ToString());
SqlCommand NewUpdateCommand = new SqlCommand(UpdateStatement.ToString(), DbConn);
PrepareParemeters(NewUpdateCommand, poBucketConfiguration);
return(new SqlCommand(UpdateStatement.ToString(), DbConn));
}
/// <summary>
///
/// This method will initialize the Count prepared statement.
///
/// <param name="poBucketConfiguration">The configuration for the logical bucket (i.e., a table and a subset of its columns)</param>
/// <returns>Prepared statement that returns the Count of rows in the target table</returns>
/// </summary>
private SqlCommand InitCountCommand(AceAPIBucket poBucketConfiguration)
{
StringBuilder CountStatement = new StringBuilder("SELECT COUNT(*) FROM " + poBucketConfiguration.TableName);
return(new SqlCommand(CountStatement.ToString(), DbConn));
}
