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