/// <summary>
/// Perform the Create operations on the selected table.
/// This method will filter creations using the
/// SqlQueryBuilder and the lookup conditions
/// </summary>
/// <param name="operationInput">The operation information being executed.</param>
/// <returns>The result of the operation that was processed.</returns>
public OperationResult CreateOperation(OperationInput operationInput)
{
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, "Create"))
{
// 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.
// If the requested record that has a key that already exists this should not result in a failure.
List<bool> successList = new List<bool>();
// The second piece of information is the number of records that have been processed.
// If a duplicate key is detected when performing an insert then 0 rows should be added for the request.
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
{
//Use the query builder to parse input conditions
SqlQueryBuilder queryBuilder = new SqlQueryBuilder(
inputEntity, Globals.QueryType.Insert);
//execute the create query
int rowsEffected = _dataAccess.ExecuteNonQuery(queryBuilder.ToString());
//Add the result of the create to the result lists
successList.Add(SetSuccessResult(operationInput.AllowMultipleObject, rowsEffected));
objectList.Add(rowsEffected);
errors.Add(SetErrorResult(rowsEffected));
}
catch (OleDbException oleDbException)
{
//Create a new error result for ole db specific exeptions
ErrorResult errorResult = new ErrorResult();
var oleDbError = oleDbException.ErrorCode;
//Look for a specific error code that occurs when attempting to duplicate a record.
//This will tell ScribeOnline that an update is required rather than an Insert.
if (oleDbError == -2147217873)
{
//this is the error code for a 'Violation in unique index'
errorResult.Number = ErrorNumber.DuplicateUniqueKey;
if (oleDbException.Errors[0] != null && oleDbException.Errors[0] != null)
{
var dbError = oleDbException.Errors[0];
errorResult.Description = dbError != null ? dbError.Message : oleDbException.Message;
var error = oleDbException.Errors[1];
errorResult.Detail = error != null ? error.Message : oleDbException.StackTrace;
}
}
else
{
errorResult.Description = oleDbException.Message;
errorResult.Detail = oleDbException.StackTrace;
errorResult.Number = oleDbError;
}
successList.Add(false);
objectList.Add(0);
errors.Add(errorResult);
}
catch (Exception exception)
{
//In the event of an exception do not stop performing
//all operations simply 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>
/// Retrieves the number of rows based on the entity and lookup condition provided.
/// This method will also throw an ArgumentException if multiple rows are found in the datasource
/// based on the lookup condition when the allowMultiples flag was not set.
/// </summary>
/// <param name="dataEntity">Data entity used to retrieve the row count.</param>
/// <param name="lookupCondition">LookupCondition that is equivlent to a sql 'where' clause.</param>
/// <param name="allowMultiples">Flag that identifies if multple rows may be effected by a single query.</param>
/// <returns></returns>
private void ValidateRowCount(DataEntity dataEntity, Expression lookupCondition, bool allowMultiples)
{
//create the select count (*) query
var selectQuery = new SqlQueryBuilder(dataEntity, lookupCondition, Globals.QueryType.Count);
//execute the the count query
var queryResults = _dataAccess.Execute(selectQuery.ToString());
//retrieve the row count from the query
var rowCount = Convert.ToInt32(queryResults.Rows[0][0]);
//validate whether or not more than one row will be effected
if (allowMultiples == false && rowCount > 1)
{
throw new ArgumentOutOfRangeException("allowMultiples", string.Format(ErrorCodes.TooManyRowsReturned.Description, rowCount));
}
}
/// <summary>
/// Perform the Delete operations on the selected table.
/// This method will filter deletes using the SqlQueryBuilder and the lookup conditions
/// </summary>
/// <param name="operationInput">The operation information being executed.</param>
/// <returns>The result of the operation that was processed.</returns>
public OperationResult DeleteOperation(OperationInput operationInput)
{
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, "Delete"))
{
// 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.
// If the requested record does not exist it should not result in a failure.
List<bool> successList = new List<bool>();
// The second piece of information is the number of records that have been processed.
// If a delete is attempted on a record that does not exist then 0 rows should be reported here.
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>();
int index = 0;
// 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
{
// Process the number of rows that will be deleted.
ValidateRowCount(inputEntity, operationInput.LookupCondition[index],
operationInput.AllowMultipleObject);
// Use the query builder to parse input conditions.
var query = new SqlQueryBuilder(inputEntity, operationInput.LookupCondition[index], Globals.QueryType.Delete);
// Execute the query generated from the operation input.
int rowCount = _dataAccess.ExecuteNonQuery(query.ToString());
// Add a the result to the result list.
successList.Add(SetSuccessResult(operationInput.AllowMultipleObject, rowCount));
objectList.Add(rowCount);
errors.Add(SetErrorResult(rowCount));
index++;
}
catch (ArgumentException argumentException)
{
// This will catch a filter that returns multiple rows
// when only one is expected.
var errorResult = new ErrorResult()
{
Description = argumentException.Message,
Number = ErrorCodes.TooManyRowsReturned.Number
};
errors.Add(errorResult);
successList.Add(false);
objectList.Add(0);
}
catch (Exception exception)
{
// In the event of an exception do not stop performing
// all operations simply 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;
}
/// <summary>
/// Perform the Delete operations on the selected table.
/// This method will filter deletes using the SqlQueryBuilder and the lookup conditions
/// </summary>
/// <param name="operationInput">The operation information being executed.</param>
/// <returns>The result of the operation that was processed.</returns>
public OperationResult DeleteOperation(OperationInput operationInput)
{
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, "Delete"))
{
// 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.
// If the requested record does not exist it should not result in a failure.
List <bool> successList = new List <bool>();
// The second piece of information is the number of records that have been processed.
// If a delete is attempted on a record that does not exist then 0 rows should be reported here.
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>();
int index = 0;
// 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
{
// Process the number of rows that will be deleted.
ValidateRowCount(inputEntity, operationInput.LookupCondition[index],
operationInput.AllowMultipleObject);
// Use the query builder to parse input conditions.
var query = new SqlQueryBuilder(inputEntity, operationInput.LookupCondition[index], Globals.QueryType.Delete);
// Execute the query generated from the operation input.
int rowCount = _dataAccess.ExecuteNonQuery(query.ToString());
// Add a the result to the result list.
successList.Add(SetSuccessResult(operationInput.AllowMultipleObject, rowCount));
objectList.Add(rowCount);
errors.Add(SetErrorResult(rowCount));
index++;
}
catch (ArgumentException argumentException)
{
// This will catch a filter that returns multiple rows
// when only one is expected.
var errorResult = new ErrorResult()
{
Description = argumentException.Message,
Number = ErrorCodes.TooManyRowsReturned.Number
};
errors.Add(errorResult);
successList.Add(false);
objectList.Add(0);
}
catch (Exception exception)
{
// In the event of an exception do not stop performing
// all operations simply 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);
}
/// <summary>
/// Perform the Create operations on the selected table.
/// This method will filter creations using the
/// SqlQueryBuilder and the lookup conditions
/// </summary>
/// <param name="operationInput">The operation information being executed.</param>
/// <returns>The result of the operation that was processed.</returns>
public OperationResult CreateOperation(OperationInput operationInput)
{
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, "Create"))
{
// 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.
// If the requested record that has a key that already exists this should not result in a failure.
List <bool> successList = new List <bool>();
// The second piece of information is the number of records that have been processed.
// If a duplicate key is detected when performing an insert then 0 rows should be added for the request.
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
{
//Use the query builder to parse input conditions
SqlQueryBuilder queryBuilder = new SqlQueryBuilder(
inputEntity, Globals.QueryType.Insert);
//execute the create query
int rowsEffected = _dataAccess.ExecuteNonQuery(queryBuilder.ToString());
//Add the result of the create to the result lists
successList.Add(SetSuccessResult(operationInput.AllowMultipleObject, rowsEffected));
objectList.Add(rowsEffected);
errors.Add(SetErrorResult(rowsEffected));
}
catch (OleDbException oleDbException)
{
//Create a new error result for ole db specific exeptions
ErrorResult errorResult = new ErrorResult();
var oleDbError = oleDbException.ErrorCode;
//Look for a specific error code that occurs when attempting to duplicate a record.
//This will tell ScribeOnline that an update is required rather than an Insert.
if (oleDbError == -2147217873)
{
//this is the error code for a 'Violation in unique index'
errorResult.Number = ErrorNumber.DuplicateUniqueKey;
if (oleDbException.Errors[0] != null && oleDbException.Errors[0] != null)
{
var dbError = oleDbException.Errors[0];
errorResult.Description = dbError != null ? dbError.Message : oleDbException.Message;
var error = oleDbException.Errors[1];
errorResult.Detail = error != null ? error.Message : oleDbException.StackTrace;
}
}
else
{
errorResult.Description = oleDbException.Message;
errorResult.Detail = oleDbException.StackTrace;
errorResult.Number = oleDbError;
}
successList.Add(false);
objectList.Add(0);
errors.Add(errorResult);
}
catch (Exception exception)
{
//In the event of an exception do not stop performing
//all operations simply 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>
/// The Connector will perform the query and pass the results back in an
/// enumerable set of ResultEntities. Each of which could be a set of objects
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public IEnumerable <DataEntity> ExecuteQuery(Query query)
{
// 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, "ExecuteQuery"))
{
//set the enumerated list of data entities to null
IEnumerable <DataEntity> dataEntities = null;
try
{
//Verify that a root entity is decalred and that return data is requested in the property list.
if (string.IsNullOrWhiteSpace(query.RootEntity.ObjectDefinitionFullName))
{
//this message can be anything that is meaningfull to the user
string message = string.Format(ErrorCodes.InvalidQueryObject.Description,
query.RootEntity.PropertyList.Count);
//Log that no query has been filled out
Logger.Write(Logger.Severity.Error, "Execute Query Failed", message);
throw new ArgumentException(message);
}
//retrieve the name of the root entity
string tableName = query.RootEntity.ObjectDefinitionFullName;
//retrieve the list of column definitions for proper data type convertion
DataTable columnDefinitions = _metadataAccess.GetColumnDefinitions(tableName);
columnDefinitions.TableName = tableName;
//Create a new instance of the query builder and send the query information along
SqlQueryBuilder queryBuilder = new SqlQueryBuilder(query, columnDefinitions);
//Convert the query builder to a string value
string queryString = queryBuilder.ToString();
//Execute the query and retrieve the enumerated results
dataEntities = _dataAccess.Execute(query.RootEntity.ObjectDefinitionFullName, queryString, queryBuilder.RelatedForeignKeys);
if (dataEntities != null)
{
//Since the dataEntities is an enumerated list it will need to be forced to fire the execute method
foreach (var entity in dataEntities)
{
break;
}
}
}
catch (FatalErrorException)
{
throw;
}
catch (Exception exception)
{
//this message may be anything that is meaningful to the user
string message = string.Format("{0} {1}", ErrorCodes.GenericConnectorError, exception.Message);
//Log the exception
Logger.Write(Logger.Severity.Error, message, exception.StackTrace);
//All exeptions that occur in the query execution must be returned as an InvalidQueryException
throw new InvalidExecuteQueryException(message);
}
return(dataEntities);
}
}
/// <summary>
/// The Connector will perform the query and pass the results back in an
/// enumerable set of ResultEntities. Each of which could be a set of objects
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public IEnumerable<DataEntity> ExecuteQuery(Query query)
{
// 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, "ExecuteQuery"))
{
//set the enumerated list of data entities to null
IEnumerable<DataEntity> dataEntities = null;
try
{
//Verify that a root entity is decalred and that return data is requested in the property list.
if (string.IsNullOrWhiteSpace(query.RootEntity.ObjectDefinitionFullName))
{
//this message can be anything that is meaningfull to the user
string message = string.Format(ErrorCodes.InvalidQueryObject.Description,
query.RootEntity.PropertyList.Count);
//Log that no query has been filled out
Logger.Write(Logger.Severity.Error, "Execute Query Failed", message);
throw new ArgumentException(message);
}
//retrieve the name of the root entity
string tableName = query.RootEntity.ObjectDefinitionFullName;
//retrieve the list of column definitions for proper data type convertion
DataTable columnDefinitions = _metadataAccess.GetColumnDefinitions(tableName);
columnDefinitions.TableName = tableName;
//Create a new instance of the query builder and send the query information along
SqlQueryBuilder queryBuilder = new SqlQueryBuilder(query, columnDefinitions);
//Convert the query builder to a string value
string queryString = queryBuilder.ToString();
//Execute the query and retrieve the enumerated results
dataEntities = _dataAccess.Execute(query.RootEntity.ObjectDefinitionFullName, queryString, queryBuilder.RelatedForeignKeys);
if (dataEntities != null)
{
//Since the dataEntities is an enumerated list it will need to be forced to fire the execute method
foreach (var entity in dataEntities)
{
break;
}
}
}
catch (FatalErrorException)
{
throw;
}
catch (Exception exception)
{
//this message may be anything that is meaningful to the user
string message = string.Format("{0} {1}", ErrorCodes.GenericConnectorError, exception.Message);
//Log the exception
Logger.Write(Logger.Severity.Error, message, exception.StackTrace);
//All exeptions that occur in the query execution must be returned as an InvalidQueryException
throw new InvalidExecuteQueryException(message);
}
return dataEntities;
}
}
