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);
}
public List <ValidateError> ValidateSql(Action action)
{
List <ValidateError> errors = new List <ValidateError>();
if (action.SQL.Contains("sp_executesql"))
{
ValidateError error = new ValidateError();
error.Name = $"{action.Namespace}/{action.Name}";
error.Description = Constants.ValidateErrors.CannotUseSpExecuteSql;
errors.Add(error);
}
return(errors);
}
public List <ValidateError> ValidateParameters(Action action)
{
List <ValidateError> errors = new List <ValidateError>();
foreach (Parameter param in action.Parameters)
{
if (action.SQL.IndexOf($"@{param.Name}", StringComparison.OrdinalIgnoreCase) < 0)
{
ValidateError error = new ValidateError();
error.Name = $"{action.Namespace}/{action.Name}";
error.Description = String.Format(Constants.ValidateErrors.ParameterNotFoundInSql, param.Name);
errors.Add(error);
}
}
return(errors);
}
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
}
