public dynamic ExecuteSql(IDBContext dbContext, Action action, DapiJson dapiJson)
{
List <SqlParameter> sqlParameters = new List <SqlParameter>();
foreach (Parameter param in action.Parameters)
{
SqlParameter sqlParam = new SqlParameter(param.Name, Mapper.Map <SqlDbType>(param.DataType));
if (param.Length > 0)
{
sqlParam.Size = param.Length ?? sqlParam.Size;
}
var dParam = dapiJson.Parameters.Find(d => d.name.Equals(param.Name, StringComparison.OrdinalIgnoreCase));
sqlParam.Value = dParam?.value;
sqlParameters.Add(sqlParam);
}
dynamic result;
try
{
switch (action.Outputs.OutputMode)
{
case OutputMode.NoReturnValue:
result = dbContext.ExecuteNonQuerySQLStatement(action.SQL, sqlParameters);
result = "";
break;
case OutputMode.Scalar:
result = dbContext.ExecuteSqlStatementAsScalar <dynamic>(action.SQL, sqlParameters);
break;
case OutputMode.Rowset:
result = dbContext.ExecuteSqlStatementAsDataTable(action.SQL, sqlParameters);
break;
default:
result = "";
break;
}
DidActionExecute = true;
}
catch (Exception ex)
{
DidActionExecute = false;
result = ex.Message;
throw;
}
return(result);
}
/// <summary>
/// Update specific JSON Parameters, which should be used prior to running DynamicAPI.
/// Use a dictionary with Key = param index and Value = new value for param
/// JSON parameter 0 is always WorkspaceId to connect to
/// </summary>
/// <param name="Namespace"></param>
/// <param name="actionName"></param>
/// <param name="parametersToUpdate"></param>
public void UpdateJsonParameters(string Namespace, string actionName, IDictionary <int, string> parametersToUpdate)
{
DapiJson dapiJsonModel = Newtonsoft.Json.JsonConvert.DeserializeObject <DapiJson>(GetJson(Namespace, actionName));
foreach (KeyValuePair <int, string> param in parametersToUpdate)
{
dapiJsonModel.Parameters[param.Key].value = param.Value;
}
string dapiJson = Newtonsoft.Json.JsonConvert.SerializeObject(dapiJsonModel);
_sqlList.Find(x =>
x.Namespace.Equals(Namespace, StringComparison.OrdinalIgnoreCase) &&
x.Name.Equals(actionName, StringComparison.OrdinalIgnoreCase)).Json = dapiJson;
}
public void ValidJson(string fileName, bool expectedValidity)
{
//Arrange
string executableLocation = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
string yamlLocation = Path.Combine(executableLocation, fileName);
string json = "";
FileStream file = new FileStream(yamlLocation, FileMode.Open, FileAccess.Read);
using (StreamReader reader = new StreamReader(file))
{
json = reader.ReadToEnd();
}
//Act
DapiJson dapiJson = JsonConvert.DeserializeObject <DapiJson>(json);
file.Close();
//Assert
Assert.AreEqual(expectedValidity, dapiJson.IsJsonValid());
}
public async Task <IHttpActionResult> DynamicAction(string dapiNamespace, string dapiAction, DapiJson dapiJson)
{
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Start Function for route {dapiNamespace}/{dapiAction}");
if (HttpContext.Current.Request.Headers["X-CSRF-Header"] == null)
{
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - X-CSRF-Header is missing");
return(BadRequest("Please add X-CSRF-Header with an empty value."));
}
if (!dapiJson.IsJsonValid())
{
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - JSON is invalid. Please make sure YamlFileName and Parameters exist, and that WorkspaceId is passed within Parameters.");
return(BadRequest("JSON is invalid. Please make sure YamlFileName and Parameters exist. Also WorkspaceId is required within Parameters."));
}
IServicesMgr serviceManager = ConnectionHelper.Helper().GetServicesManager();
using (Library library = new Library())
{
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Connected to RSAPI Client");
List <Tuple <int, DateTime?> > yamlArtifactIdList = new List <Tuple <int, DateTime?> >();
List <Tuple <int, DateTime?> > removedYamlArtifactIdList = new List <Tuple <int, DateTime?> >();
var userId = ConnectionHelper.Helper().GetAuthenticationManager().UserInfo.ArtifactID;
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - UserId: {userId}");
bool doesUserHavePermissions = await DoesUserHavePermission(serviceManager, userId, false, nameof(DynamicAction));
if (doesUserHavePermissions)
{
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Querying for YAML File ({dapiJson.YamlFileName}) in Resource Files");
yamlArtifactIdList = await QueryYamlFileArtifactIDsByNameAsync(serviceManager, dapiJson.YamlFileName);
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Found ({yamlArtifactIdList.Count}) YAML File(s)");
removedYamlArtifactIdList = library.GetListOfRemovedYamlFiles(yamlArtifactIdList);
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Found ({removedYamlArtifactIdList.Count}) removed YAML File(s)");
if (removedYamlArtifactIdList.Count > 0)
{
library.RemoveListOfYamlFiles(removedYamlArtifactIdList);
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Cleared routes for removed YAML File(s)");
}
yamlArtifactIdList = library.GetListOfNewOrUpdatedYamlFiles(yamlArtifactIdList);
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Found ({yamlArtifactIdList.Count}) new or updated YAML File(s)");
if (yamlArtifactIdList.Count > 0)
{
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Requesting Auth Token");
var auth = await RequestAuthTokenAsync(serviceManager);
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Received Auth Token");
foreach (var artifact in yamlArtifactIdList)
{
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Getting YAML file string data for Artifact ID: {artifact} - {dapiJson.YamlFileName}");
var yamlData = await DownloadYamlData(serviceManager, artifact.Item1, auth);
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Got YAML file string data for Artifact ID: {artifact} - {dapiJson.YamlFileName}");
LibraryStreamParser libraryStreamParser = new LibraryStreamParser();
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Attempting to parse YAML for Artifact ID: {artifact} - {dapiJson.YamlFileName}");
ParseResult result = libraryStreamParser.Parse(yamlData);
LibraryValidator libraryValidator = new LibraryValidator();
libraryValidator.IsValid(result);
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Parsed YAML for Artifact ID: {artifact} - {dapiJson.YamlFileName}");
if (result.Success)
{
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Attempting to Add or Update YAML actions within Artifact ID: {artifact} - {dapiJson.YamlFileName}");
library.AddNewOrUpdatedAction(result.Library.Actions, artifact);
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Added/Updated YAML actions within Artifact ID: {artifact} - {dapiJson.YamlFileName}");
}
else
{
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Errors during YAML Parsing on {artifact} - {dapiJson.YamlFileName}");
StringBuilder errors = new StringBuilder(500);
foreach (ParseError error in result.Errors)
{
errors.AppendLine($"Error Name: {error.Name}");
errors.AppendLine($"Error Description: {error.Description}");
}
foreach (ValidateError error in result.ValidateErrors)
{
errors.AppendLine($"Error Name: {error.Name}");
errors.AppendLine($"Error Description: {error.Description}");
}
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Error Info: {errors.ToString()}");
return(BadRequest(errors.ToString()));
}
} // end foreach on yamlfiles
}
} // end if user exists in group
else
{
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - User is not authorized to use DynamicApi");
return(BadRequest("User is not authorized to use DynamicApi"));
}
// Now attempt to execute the SQL
if (library.DoesNamespaceExist(dapiNamespace))
{
if (library.DoesActionNameExist(dapiAction))
{
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Found route {dapiNamespace}/{dapiAction}");
Action action = library.GetAction(dapiNamespace, dapiAction);
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Attempting to get WorkspaceId from JSON Parameters");
int workspaceId = dapiJson.GetWorkspaceId();
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Got WorkspaceId ({workspaceId}) from JSON Parameters");
IDBContext dbContext;
try
{
dbContext = ConnectionHelper.Helper().GetDBContext(workspaceId);
}
catch (Exception ex)
{
_logHelper.LogError(ex, $"DAPI - {nameof(DynamicAction)} - Failed to connect to Workspace ({workspaceId})");
return(BadRequest($"Failed to connect to Workspace ({workspaceId})"));
}
LibraryExecutor libraryExecutor = new LibraryExecutor();
object result;
try
{
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Attempting to Execute SQL: {action.SQL}");
result = libraryExecutor.ExecuteSql(dbContext, action, dapiJson);
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Executed SQL");
return(Json(result));
}
catch (Exception ex)
{
_logHelper.LogError(ex, $"DAPI - {nameof(DynamicAction)} - Failed to Execute SQL: {((ex.InnerException != null) ? ex.InnerException.Message : string.Empty)}");
result = $"Error in executing SQL. Please verify the SQL works. {((ex.InnerException != null) ? ex.InnerException.Message : string.Empty)}";
return(Json(result));
}
}
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Bad Request for route {dapiNamespace}/{dapiAction}");
return(BadRequest($"Namespace ({dapiNamespace}) exists, but Action ({dapiAction}) does not"));
} // end if on checking route (namespace/name)
_logHelper.LogInformation($"DAPI - {nameof(DynamicAction)} - Bad Request for route {dapiNamespace}/{dapiAction}");
return(BadRequest($"Namespace ({dapiNamespace}) does not exist!"));
} // end USING library
}
public void ExecuteAction(string fileName, OutputMode mode, bool expectedSuccess, int expectedTotalActions)
{
//Arrange
var executableLocation = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
var yamlLocation = Path.Combine(executableLocation, fileName);
var dapiJson = new DapiJson();
dapiJson.Parameters = new List <DapiParameter>();
var param01 = new DapiParameter {
name = "workspaceid", value = "-1"
};
var param02 = new DapiParameter {
name = "name", value = "DynamicApi"
};
var param03 = new DapiParameter {
name = "StatusCodeArtifactID", value = "675"
};
dapiJson.Parameters.Add(param01);
dapiJson.Parameters.Add(param02);
dapiJson.Parameters.Add(param03);
var table = new DataTable("Case", "EDDS");
table.Columns.Add("ArtifactId");
table.Columns.Add("WorkspaceName");
var row = table.NewRow();
row["ArtifactId"] = "10101";
row["WorkspaceName"] = "DAPI Test";
table.Rows.Add(row);
var file = new FileStream(yamlLocation, FileMode.Open, FileAccess.Read);
switch (mode)
{
case OutputMode.NoReturnValue:
MockDBContext
.Setup(x => x.ExecuteNonQuerySQLStatement(It.IsAny <string>(), It.IsAny <List <SqlParameter> >()))
.Returns(0);
break;
case OutputMode.Scalar:
MockDBContext
.Setup(x => x.ExecuteSqlStatementAsScalar <dynamic>(It.IsAny <string>(), It.IsAny <List <SqlParameter> >()))
.Returns("Fake-Guid-010101");
break;
case OutputMode.Rowset:
MockDBContext
.Setup(x => x.ExecuteSqlStatementAsDataTable(It.IsAny <string>(), It.IsAny <List <SqlParameter> >()))
.Returns(table);
break;
}
//Act
ParseResult result = libraryStreamParser.Parse(file);
file.Close();
var isValid = libraryValidator.IsValid(result);
//Assert
Assert.AreEqual(expectedSuccess, result.ValidateSuccess);
if (expectedSuccess)
{
var output = libraryExecutor.ExecuteSql(MockDBContext.Object, result.Library.Actions.Where(a => a.Outputs.OutputMode == mode).First(), dapiJson);
Console.WriteLine((mode == OutputMode.Rowset) ? JsonConvert.SerializeObject(output, Formatting.Indented) : output);
Assert.AreEqual(expectedTotalActions, result.Library.Actions.Count);
}
if (!expectedSuccess)
{
foreach (ParseError error in result.Errors)
{
Console.WriteLine($"{error.Name} - {error.Description}");
}
}
}