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