private EntityProperties GetPrimaryKeyProperties(DataEntity dataEntity, OleDbMetadataAccess metadataAccess)
{
var primaryKeyProperties = new EntityProperties();
//Use the data entity name to retrieve a list of indexes
var indexColumns = metadataAccess.GetTableIndexInformation(dataEntity.ObjectDefinitionFullName);
//Add each of the Primary Keys and their values found in the data entity.
foreach (DataRow row in indexColumns.Rows)
{
if (!Convert.ToBoolean(row["PRIMARY_KEY"]))
{
continue;
}
var columnName = row["COLUMN_NAME"].ToString();
// Check if the priamry key column is included in the data entity.
if (dataEntity.Properties.ContainsKey(columnName))
{
// Add the key and its value to the primary key list.
primaryKeyProperties.Add(columnName, dataEntity.Properties[columnName]);
}
else
{
// If the key has not been added set it to null.
primaryKeyProperties.Add(columnName, null);
}
}
return(primaryKeyProperties);
}
/// <summary>
/// This method will attempt to connect to the third-party.
/// </summary>
/// <param name="properties">
/// The collection of information required by this Connector to connect to the third-party.
/// </param>
public void Connect(IDictionary <string, string> properties)
{
// Use LogMethodExecution to add entry and exit tracing to a method.
// When wrapped in a using statement, the exit point
// is written during garbage collection.
using (new LogMethodExecution(ConnectorTypeName, "Connect"))
{
try
{
//parse the incomming properties to ensure the proper parameters are set
BuildConnectionString(properties);
//attempt a connection to the selected datasource
_dataAccess.OleDbConnect(_connectionString);
//open the connection for the metadata access to the server
_metadataAccess = new OleDbMetadataAccess(_dataAccess);
//open the connection to the metadata provider
_metadataProvider = new MetadataProvider(_metadataAccess);
}
catch (OleDbException oleDbException)
{
string msg = ExceptionFormatter.BuildActionableException(
"Unable to Connect to datasource",
string.Format("The following error occured in the {0}:",
Globals.ConnectorName), oleDbException);
//be sure to log any errors that occure while attempting to connect
Logger.Write(Logger.Severity.Error, Globals.ConnectorName, msg);
//throw the InvalidConnectionException found in the ConnectorApi
throw new InvalidConnectionException(msg);
}
catch (InvalidOperationException invalidOperationException)
{
string msg = ExceptionFormatter.BuildActionableException(
"Unable to connect to datasource the provider information is invalid.",
string.Format("The following error occured in the {0}:", Globals.ConnectorName),
invalidOperationException);
//be sure to log an error that is due to an invalid provider
Logger.Write(Logger.Severity.Error, Globals.ConnectorName, msg);
//throw the InvalidConnectionException found in the ConnectorApi
throw new InvalidConnectionException(msg);
}
}
}
private EntityProperties GetPrimaryKeyProperties(DataEntity dataEntity, OleDbMetadataAccess metadataAccess)
{
var primaryKeyProperties = new EntityProperties();
//Use the data entity name to retrieve a list of indexes
var indexColumns = metadataAccess.GetTableIndexInformation(dataEntity.ObjectDefinitionFullName);
//Add each of the Primary Keys and their values found in the data entity.
foreach (DataRow row in indexColumns.Rows)
{
if (!Convert.ToBoolean(row["PRIMARY_KEY"]))
{
continue;
}
var columnName = row["COLUMN_NAME"].ToString();
// Check if the priamry key column is included in the data entity.
if (dataEntity.Properties.ContainsKey(columnName))
{
// Add the key and its value to the primary key list.
primaryKeyProperties.Add(columnName, dataEntity.Properties[columnName]);
}
else
{
// If the key has not been added set it to null.
primaryKeyProperties.Add(columnName, null);
}
}
return primaryKeyProperties;
}
/// <summary>
/// Perform the Upsert operations on the selected table. The connector will first identify if the DataEntity
/// already exists in the data source and then perform either an update or insert operation based on the results.
/// This method will filter updates using the
/// SqlQueryBuilder and the lookup conditions
/// </summary>
/// <param name="operationInput">The operation information being executed.</param>
/// <param name="metadataAccess">Metadata associated with the active connection.</param>
/// <returns>The result of the operation that was processed.</returns>
public OperationResult UpsertOperation(OperationInput operationInput, OleDbMetadataAccess metadataAccess)
{
OperationResult operationResult = new OperationResult();
// Use LogMethodExecution to add entry and exit tracing to a method.
// When wrapped in a using statement, the exit point
// is written during garbage collection.
using (new LogMethodExecution(Globals.ConnectorName, "Upsert"))
{
// Each record processed must return the following 3 pieces of information.
// Each piece of information should be added in the same order it was received.
// The first piece of information would be whether or not the request was successful.
List<bool> successList = new List<bool>();
// The second piece of information is the number of records that have been processed.
List<int> objectList = new List<int>();
// In the event of an error during processing the record, error information should be added here.
// If no error occured a null placeholder for that record is expected.
List<ErrorResult> errors = new List<ErrorResult>();
//Execute each of the inputs individually
// **** Processing inputs individually is done because the
// connector is responsible for error handling on each.
// The connector must return results in the same order in which the
// data entities were received, this allows for reprocessing of failed records.
//Note: If the SupportsBulk flag is not set in the ActionDefinition
// that corresponds to this operation, operationInput.Input
// will always have always have a length of 1.
foreach (DataEntity inputEntity in operationInput.Input)
{
try
{
var primaryKeyPropertes = GetPrimaryKeyProperties(inputEntity, metadataAccess);
//Generate the query to perform the upsert
var upsertQuery =
new SqlQueryBuilder(inputEntity, primaryKeyPropertes, Globals.QueryType.Upsert);
//execute the upsert query
int rowsEffected = _dataAccess.ExecuteNonQuery(upsertQuery.ToString());
//Add the result of the update to the result lists
//set the appropriate success results, If multiple records were returned but the operation did not allow multiples
//then the operation was not successfull.
successList.Add(SetSuccessResult(operationInput.AllowMultipleObject, rowsEffected));
objectList.Add(rowsEffected);
errors.Add(SetErrorResult(rowsEffected));
}
catch (Exception exception)
{
//In the event of an exception do not stop performing all operations
//simple log each individually
successList.Add(false);
objectList.Add(0);
errors.Add(new ErrorResult() { Description = exception.Message, Detail = exception.ToString() });
}
}
//Add the results from the operations to the operation result object
operationResult.Success = successList.ToArray();
operationResult.ObjectsAffected = objectList.ToArray();
operationResult.ErrorInfo = errors.ToArray();
}
return operationResult;
}
/// <summary>
/// Perform the Upsert operations on the selected table. The connector will first identify if the DataEntity
/// already exists in the data source and then perform either an update or insert operation based on the results.
/// This method will filter updates using the
/// SqlQueryBuilder and the lookup conditions
/// </summary>
/// <param name="operationInput">The operation information being executed.</param>
/// <param name="metadataAccess">Metadata associated with the active connection.</param>
/// <returns>The result of the operation that was processed.</returns>
public OperationResult UpsertOperation(OperationInput operationInput, OleDbMetadataAccess metadataAccess)
{
OperationResult operationResult = new OperationResult();
// Use LogMethodExecution to add entry and exit tracing to a method.
// When wrapped in a using statement, the exit point
// is written during garbage collection.
using (new LogMethodExecution(Globals.ConnectorName, "Upsert"))
{
// Each record processed must return the following 3 pieces of information.
// Each piece of information should be added in the same order it was received.
// The first piece of information would be whether or not the request was successful.
List <bool> successList = new List <bool>();
// The second piece of information is the number of records that have been processed.
List <int> objectList = new List <int>();
// In the event of an error during processing the record, error information should be added here.
// If no error occured a null placeholder for that record is expected.
List <ErrorResult> errors = new List <ErrorResult>();
//Execute each of the inputs individually
// **** Processing inputs individually is done because the
// connector is responsible for error handling on each.
// The connector must return results in the same order in which the
// data entities were received, this allows for reprocessing of failed records.
//Note: If the SupportsBulk flag is not set in the ActionDefinition
// that corresponds to this operation, operationInput.Input
// will always have always have a length of 1.
foreach (DataEntity inputEntity in operationInput.Input)
{
try
{
var primaryKeyPropertes = GetPrimaryKeyProperties(inputEntity, metadataAccess);
//Generate the query to perform the upsert
var upsertQuery =
new SqlQueryBuilder(inputEntity, primaryKeyPropertes, Globals.QueryType.Upsert);
//execute the upsert query
int rowsEffected = _dataAccess.ExecuteNonQuery(upsertQuery.ToString());
//Add the result of the update to the result lists
//set the appropriate success results, If multiple records were returned but the operation did not allow multiples
//then the operation was not successfull.
successList.Add(SetSuccessResult(operationInput.AllowMultipleObject, rowsEffected));
objectList.Add(rowsEffected);
errors.Add(SetErrorResult(rowsEffected));
}
catch (Exception exception)
{
//In the event of an exception do not stop performing all operations
//simple log each individually
successList.Add(false);
objectList.Add(0);
errors.Add(new ErrorResult()
{
Description = exception.Message, Detail = exception.ToString()
});
}
}
//Add the results from the operations to the operation result object
operationResult.Success = successList.ToArray();
operationResult.ObjectsAffected = objectList.ToArray();
operationResult.ErrorInfo = errors.ToArray();
}
return(operationResult);
}
public MetadataProvider(OleDbMetadataAccess metadataAccess)
{
_metadataAccess = metadataAccess;
}
public MetadataProvider(OleDbMetadataAccess metadataAccess)
{
_metadataAccess = metadataAccess;
}
/// <summary>
/// This method will attempt to connect to the third-party.
/// </summary>
/// <param name="properties">
/// The collection of information required by this Connector to connect to the third-party.
/// </param>
public void Connect(IDictionary<string, string> properties)
{
// Use LogMethodExecution to add entry and exit tracing to a method.
// When wrapped in a using statement, the exit point
// is written during garbage collection.
using (new LogMethodExecution(ConnectorTypeName, "Connect"))
{
try
{
//parse the incomming properties to ensure the proper parameters are set
BuildConnectionString(properties);
//attempt a connection to the selected datasource
_dataAccess.OleDbConnect(_connectionString);
//open the connection for the metadata access to the server
_metadataAccess = new OleDbMetadataAccess(_dataAccess);
//open the connection to the metadata provider
_metadataProvider = new MetadataProvider(_metadataAccess);
}
catch (OleDbException oleDbException)
{
string msg = ExceptionFormatter.BuildActionableException(
"Unable to Connect to datasource",
string.Format("The following error occured in the {0}:",
Globals.ConnectorName), oleDbException);
//be sure to log any errors that occure while attempting to connect
Logger.Write(Logger.Severity.Error, Globals.ConnectorName, msg);
//throw the InvalidConnectionException found in the ConnectorApi
throw new InvalidConnectionException(msg);
}
catch (InvalidOperationException invalidOperationException)
{
string msg = ExceptionFormatter.BuildActionableException(
"Unable to connect to datasource the provider information is invalid.",
string.Format("The following error occured in the {0}:", Globals.ConnectorName),
invalidOperationException);
//be sure to log an error that is due to an invalid provider
Logger.Write(Logger.Severity.Error, Globals.ConnectorName, msg);
//throw the InvalidConnectionException found in the ConnectorApi
throw new InvalidConnectionException(msg);
}
}
}