public async Task <ExecuteDynamicResultModel> Execute(DatabaseConnection databaseConnection, string formattedString, IEnumerable <ExecuteParamModel> parameters) { var result = new ExecuteDynamicResultModel(); using (var sqlDbConnection = new SqlConnection(databaseConnection.ConnectionString)) { sqlDbConnection.Open(); using var command = new SqlCommand(formattedString, sqlDbConnection); var upperFormat = formattedString.ToUpper().Trim(); var isQuery = upperFormat.StartsWith("SELECT ") && upperFormat.Contains("FROM "); var isInsert = upperFormat.StartsWith("INSERT INTO "); var isUpdate = upperFormat.StartsWith("UPDATE "); var isDelete = upperFormat.StartsWith("DELETE "); var isStoreProcedure = upperFormat.StartsWith("EXEC "); var listParams = new List <SqlParameter>(); if (parameters != null) { foreach (var parameter in parameters) { var fieldParam = StringUtil.GenerateUniqueName(); formattedString = formattedString.Replace("{{" + parameter.Name + "}}", "@" + fieldParam); listParams.Add( new SqlParameter(fieldParam, GetSqlDbType(parameter.Name, parameter.ReplaceValue, out var castObject)) { Value = castObject, Direction = ParameterDirection.Input });
public async Task Execute_Dynamic_Command_In_Mongo_Test() { // Arrange Mock <IExecutionDatabase> mongoExecutionDatabaseMock = new Mock <IExecutionDatabase>(); mongoExecutionDatabaseMock.Setup(a => a.ConnectionType).Returns(Core.Persistences.ConnectionType.MongoDB); mongoExecutionDatabaseMock .Setup(a => a.Execute(It.IsAny <DatabaseConnection>(), It.IsAny <string>(), It.IsAny <IEnumerable <ExecuteParamModel> >())) .Returns(Task.FromResult(new ExecuteDynamicResultModel { IsSuccess = true, Result = "A" })); DatabaseService databaseService = new DatabaseService(new IExecutionDatabase[] { mongoExecutionDatabaseMock.Object }, null); // Act ExecuteDynamicResultModel result = await databaseService.ExecuteDynamic(new LetPortal.Portal.Entities.Databases.DatabaseConnection { ConnectionString = "mongodb://localhost:27017", DatabaseConnectionType = "mongodb", DataSource = "letportal" }, "", new List <ExecuteParamModel>()); // Assert Assert.True(result.IsSuccess); }
public async Task Execute_Dynamic_Command_Not_Supported_DbType_Test() { // Arrange Mock <IExecutionDatabase> mongoExecutionDatabaseMock = new Mock <IExecutionDatabase>(); mongoExecutionDatabaseMock.Setup(a => a.ConnectionType).Returns(Core.Persistences.ConnectionType.MongoDB); mongoExecutionDatabaseMock .Setup(a => a.Execute(It.IsAny <DatabaseConnection>(), It.IsAny <string>(), It.IsAny <IEnumerable <ExecuteParamModel> >())) .Returns(Task.FromResult(new ExecuteDynamicResultModel { IsSuccess = true, Result = "A" })); DatabaseService databaseService = new DatabaseService(new IExecutionDatabase[] { mongoExecutionDatabaseMock.Object }, null); // Act try { ExecuteDynamicResultModel result = await databaseService.ExecuteDynamic(new LetPortal.Portal.Entities.Databases.DatabaseConnection { DatabaseConnectionType = "SQLServer" }, "", new List <ExecuteParamModel>()); Assert.False(true); } catch (Exception ex) { if (ex is DatabaseException databaseException) { Assert.True(databaseException.ErrorCode.Equals(DatabaseErrorCodes.NotSupportedConnectionType)); } else { Assert.False(true); } } }
public async Task <ExecuteDynamicResultModel> ExecuteDynamic( List <DatabaseConnection> databaseConnections, DatabaseExecutionChains executionChains, IEnumerable <ExecuteParamModel> parameters, IEnumerable <LoopDataParamModel> LoopDatas) { var context = new ExecutionDynamicContext { Data = JObject.Parse("{}") }; var result = new ExecuteDynamicResultModel { IsSuccess = true }; var parametersList = parameters.ToList(); for (var i = 1; i <= executionChains?.Steps.Count; i++) { var step = executionChains.Steps[i - 1]; var executingDatabaseConnection = databaseConnections.First(a => a.Id == step.DatabaseConnectionId); var connectionType = executingDatabaseConnection.GetConnectionType(); var executionDatabase = _executionDatabases.First(a => a.ConnectionType == connectionType); if (executionDatabase != null) { step.ExecuteCommand = ReplaceValueWithContext(step.ExecuteCommand, context, ref parametersList, connectionType != Core.Persistences.ConnectionType.MongoDB); StepExecutionResult stepResult; // Single step if (string.IsNullOrEmpty(step.DataLoopKey)) { stepResult = await executionDatabase .ExecuteStep( executingDatabaseConnection, step.ExecuteCommand, parametersList, context); } else { if (LoopDatas != null) { var loopData = LoopDatas.First(a => a.Name == step.DataLoopKey); var stepExecutionResults = new List <StepExecutionResult>(); foreach (var subParameters in loopData.Parameters) { var subStepResult = await executionDatabase .ExecuteStep( executingDatabaseConnection, step.ExecuteCommand, subParameters, context); stepExecutionResults.Add(subStepResult); } stepResult = new StepExecutionResult { IsSuccess = true, Result = stepExecutionResults.Select(a => a.Result).ToArray(), ExecutionType = StepExecutionType.Multiple }; } else { throw new DatabaseException(DatabaseErrorCodes.LoopDataIsNotNull); } } if (stepResult.IsSuccess) { switch (stepResult.ExecutionType) { case StepExecutionType.Query: WriteDataToContext($"step{i.ToString()}", ConvertUtil.SerializeObject(stepResult.Result, true), context); break; case StepExecutionType.Insert: // Due to JSON .NET Serialize problem for dynamic properties isn't working with Camel cast // We need to exchange a anonymous class (or object) before serializing WriteDataToContext( $"step{i.ToString()}", connectionType == Core.Persistences.ConnectionType.MongoDB ? ConvertUtil.SerializeObject(new { stepResult.Result.Id }, true) : ConvertUtil.SerializeObject(stepResult.Result, true), context); break; case StepExecutionType.Update: if (connectionType != Core.Persistences.ConnectionType.MongoDB && stepResult.Result != null) { WriteDataToContext( $"step{i.ToString()}", ConvertUtil.SerializeObject(stepResult.Result, true), context); } break; case StepExecutionType.Delete: if (connectionType != Core.Persistences.ConnectionType.MongoDB && stepResult.Result != null) { WriteDataToContext( $"step{i.ToString()}", ConvertUtil.SerializeObject(stepResult.Result, true), context); } break; case StepExecutionType.Multiple: if (stepResult.Result != null) { WriteDataToContext( $"step{i.ToString()}", ConvertUtil.SerializeObject(stepResult.Result, true), context); } break; } } else { return(ExecuteDynamicResultModel.IsFailed(stepResult.Error)); } } else { throw new DatabaseException(DatabaseErrorCodes.NotSupportedConnectionType); } } result.Result = context.Data.ToObject <dynamic>(); return(result); }
public async Task <ExecuteDynamicResultModel> Execute( DatabaseConnection databaseConnection, string formattedString, IEnumerable <ExecuteParamModel> parameters) { try { formattedString = StringUtil.ReplaceDoubleCurlyBraces(formattedString, parameters.Select(a => new Tuple <string, string, bool>(a.Name, a.ReplaceValue, a.RemoveQuotes))); var result = new ExecuteDynamicResultModel { IsSuccess = true }; var query = _mongoOptions.CurrentValue.EliminateDoubleQuotes(formattedString); var mongoDatabase = new MongoClient(databaseConnection.ConnectionString).GetDatabase(databaseConnection.DataSource); var parsingBson = BsonSerializer.Deserialize <BsonDocument>(query); var executionGroupType = parsingBson.First().Name; switch (executionGroupType) { case Constants.QUERY_KEY: var mongoCollection = GetCollection(mongoDatabase, parsingBson, Constants.QUERY_KEY); var aggregatePipes = parsingBson[Constants.QUERY_KEY][0].AsBsonArray.Select(a => (PipelineStageDefinition <BsonDocument, BsonDocument>)a).ToList(); var aggregateFluent = mongoCollection.Aggregate(); foreach (var pipe in aggregatePipes) { aggregateFluent = aggregateFluent.AppendStage(pipe); } using (var executingCursor = await aggregateFluent.ToCursorAsync()) { while (executingCursor.MoveNext()) { var currentDocument = executingCursor.Current.FirstOrDefault(); if (currentDocument != null) { // Note: Server will decrease the performance for deserializing JSON instead of client var objsList = executingCursor.Current.Select(a => a.ToJson(new MongoDB.Bson.IO.JsonWriterSettings { OutputMode = MongoDB.Bson.IO.JsonOutputMode.Strict })).Select(b => JsonConvert.DeserializeObject <dynamic>(b, new BsonConverter())).ToList(); result.Result = objsList.Count > 1 ? objsList : objsList[0]; result.IsSuccess = true; } } } break; case Constants.INSERT_KEY: var mongoInsertCollection = GetCollection(mongoDatabase, parsingBson, Constants.INSERT_KEY); var collectionCreateBson = parsingBson[Constants.INSERT_KEY][0][Constants.DATA_KEY].AsBsonDocument; var insertId = ObjectId.GenerateNewId(); if (collectionCreateBson.Any(a => a.Name == "_id")) { collectionCreateBson["_id"] = insertId; } else { collectionCreateBson.Add("_id", insertId); } // Ensure any trouble, remove id field collectionCreateBson.Remove("id"); // Getting another fields var anotherFields = parsingBson[Constants.INSERT_KEY][0].AsBsonDocument.Where(a => a.Name != Constants.DATA_KEY); if (anotherFields.Any()) { collectionCreateBson.AddRange(anotherFields); } await mongoInsertCollection.InsertOneAsync(collectionCreateBson); // Only return new id result.Result = new ExpandoObject(); result.Result.Id = insertId.ToString(); break; case Constants.UPDATE_KEY: var mongoUpdateCollection = GetCollection(mongoDatabase, parsingBson, Constants.UPDATE_KEY); var collectionWhereBson = parsingBson[Constants.UPDATE_KEY][0][Constants.WHERE_KEY].AsBsonDocument; var updateData = parsingBson[Constants.UPDATE_KEY][0][Constants.DATA_KEY].AsBsonDocument; // Important hack: because mongodb used '_id' is a primary key so that we need to convert id -> _id when update // Remove id updateData.Remove(" _id"); updateData.Remove("id"); var anotherUpdateFields = parsingBson[Constants.UPDATE_KEY][0].AsBsonDocument.Where(a => a.Name != Constants.DATA_KEY && a.Name != Constants.WHERE_KEY); if (anotherUpdateFields.Any()) { updateData.AddRange(anotherUpdateFields); } ; var set = new BsonDocument("$set", updateData); await mongoUpdateCollection.UpdateOneAsync(collectionWhereBson, set); break; case Constants.DELETE_KEY: var mongoDeleteCollection = GetCollection(mongoDatabase, parsingBson, Constants.DELETE_KEY); var collectionWhereDeleteBson = parsingBson[Constants.DELETE_KEY][0][Constants.WHERE_KEY].AsBsonDocument; var deleteResult = await mongoDeleteCollection.DeleteOneAsync(collectionWhereDeleteBson); result.Result = deleteResult.DeletedCount; break; } return(result); } catch (Exception ex) { return(ExecuteDynamicResultModel.IsFailed(ex.Message)); } }