public override async Task <ActionResponse> ExecuteActionAsync(ActionRequest request)
{
// Provided by the json
string configTable = request.DataStore.GetValue("SqlConfigTable");
// Provided by the user including the messages below
string connectionString = request.DataStore.GetValueAtIndex("SqlConnectionString", "SqlServerIndex");
// Get list of settings to deploy;
var listGroup = request.DataStore.GetAllJson("SqlGroup");
var listSubgroup = request.DataStore.GetAllJson("SqlSubGroup");
var listConfigEntryName = request.DataStore.GetAllJson("SqlEntryName");
var listConfigEntryValue = request.DataStore.GetAllJson("SqlEntryValue");
if (listGroup == null || listSubgroup == null || listConfigEntryName == null || listConfigEntryValue == null)
{
return(new ActionResponse(ActionStatus.Success, JsonUtility.GetEmptyJObject(), null, DefaultErrorCodes.DefaultErrorCode,
"Configuration value properties not found"));
}
for (int i = 0; i < listGroup.Count(); i++)
{
string group = request.DataStore.GetAllJson("SqlGroup")[i].ToString();
string subgroup = request.DataStore.GetAllJson("SqlSubGroup")[i].ToString();
string configEntryName = request.DataStore.GetAllJson("SqlEntryName")[i].ToString();
string configEntryValue = request.DataStore.GetAllJson("SqlEntryValue")[i].ToString();
string query = string.Format(queryTemplate, configTable, group, subgroup, configEntryName,
configEntryValue);
SqlUtility.InvokeSqlCommand(connectionString, query, null);
}
return(new ActionResponse(ActionStatus.Success, JsonUtility.GetEmptyJObject()));
}
public void DeleteConfigurationValues(string connString, string group, string subgroup, string name)
{
var configValues = new Dictionary <string, string> {
{ "arg1", @group }, { "arg2", subgroup }, { "arg3", name }
};
SqlUtility.InvokeSqlCommand(connString, SqlConfigQueries.deleteConfigQuery, configValues);
}
public void CreateStoredProcedure(List <ADFField> fields, string sprocName, string schemaName, string tableTypeName, string targetTableName, string connString)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(string.Format("CREATE procedure [{0}].[{1}] @{2} [{0}].[{3}] READONLY as BEGIN", schemaName, sprocName, targetTableName, tableTypeName));
sb.AppendLine(string.Format("MERGE [{0}].[{1}] AS TARGET \r\nUSING\r\n(SELECT", schemaName, targetTableName));
foreach (var field in fields)
{
sb.AppendLine(string.Format("[{0}],", field.name));
}
sb.Remove(sb.Length - 3, 1);
sb.AppendLine(string.Format("FROM @{0}\r\n) AS SOURCE\r\n ON SOURCE.ID = TARGET.ID \r\n WHEN MATCHED AND source.[LastModifiedDate] > target.[LastModifiedDate] THEN", targetTableName));
sb.AppendLine("UPDATE \r\n SET");
foreach (var field in fields)
{
sb.AppendLine(string.Format("TARGET.[{0}] = SOURCE.[{0}],", field.name));
}
sb.Remove(sb.Length - 3, 1);
sb.AppendLine("WHEN NOT MATCHED BY TARGET THEN \r\nINSERT(");
foreach (var field in fields)
{
sb.AppendLine(string.Format("[{0}],", field.name));
}
sb.Remove(sb.Length - 3, 1);
sb.Append(")\r\n VALUES (");
foreach (var field in fields)
{
sb.AppendLine(string.Format("SOURCE.[{0}],", field.name));
}
sb.Remove(sb.Length - 3, 1);
sb.Append(");");
var containsDelete = fields.Select(p => p.name == "IsDeleted");
if (containsDelete.Contains(true))
{
sb.AppendLine($"DELETE FROM [{schemaName}].[{targetTableName}]");
sb.AppendLine("WHERE IsDeleted = 1");
}
sb.AppendLine(@"END");
SqlUtility.InvokeSqlCommand(connString, sb.ToString(), null);
}
public void CreateSqlTableAndTableType(List <ADFField> fields, SalesforceSOAP.Field[] sfFields, string schemaName, string tableName, string connString)
{
StringBuilder sbTable = new StringBuilder();
StringBuilder sbTableType = new StringBuilder();
string existingColumnsCmd = $"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableName}' AND TABLE_SCHEMA = '{schemaName}'";
var queryResult = SqlUtility.RunCommand(connString, existingColumnsCmd, SqlCommandType.ExecuteWithData);
var existingColumns = from DataRow dr in queryResult.Rows
select dr["COLUMN_NAME"];
string createTable;
if (coreObjects.Contains(tableName))
{
createTable = string.Format("ALTER TABLE [{0}] ADD", tableName.ToLowerInvariant());
}
else
{
createTable = string.Format("CREATE TABLE [{0}] (", tableName.ToLowerInvariant());
}
string createTableType = string.Format("CREATE TYPE [{0}].[{1}Type] AS TABLE(", schemaName, tableName.ToLowerInvariant());
sbTable.AppendLine(createTable);
sbTableType.AppendLine(createTableType);
foreach (var field in fields)
{
if (!existingColumns.ToList().Contains(field.name))
{
createTable = CreatePayload(sfFields, sbTable, field);
}
createTableType = CreatePayload(sfFields, sbTableType, field);
}
string tableTypeCmd = createTableType.Remove(createTableType.Length - 3, 1);
tableTypeCmd = tableTypeCmd + ")";
SqlUtility.InvokeSqlCommand(connString, tableTypeCmd, null);
string createTableCmd = string.Empty;
if (coreObjects.Contains(tableName))
{
createTableCmd = createTable + ($"CONSTRAINT [PK_{tableName}] PRIMARY KEY CLUSTERED ([Id])");
}
else
{
createTableCmd = createTable + ($"CONSTRAINT [PK_{tableName}] PRIMARY KEY CLUSTERED ([Id]))");
}
SqlUtility.InvokeSqlCommand(connString, createTableCmd, null);
}
public override async Task <ActionResponse> ExecuteActionAsync(ActionRequest request)
{
var sqlIndex = int.Parse(request.DataStore.GetValue("SqlServerIndex"));
var sqlScriptsFolder = request.DataStore.GetValue("SqlScriptsFolder");
string connectionString = request.DataStore.GetAllValues("SqlConnectionString")[sqlIndex];
var files = Directory.EnumerateFiles(Path.Combine(request.Info.App.AppFilePath, sqlScriptsFolder)).ToList();
files.ForEach(f => SqlUtility.InvokeSqlCommand(connectionString, File.ReadAllText(f), new Dictionary <string, string>()));
return(new ActionResponse(ActionStatus.Success, JsonUtility.GetEmptyJObject()));
}
public async Task <HttpResponseMessage> PostDeploymentId()
{
try
{
var resp = new HttpResponseMessage();
var body = this.Request.Content.ReadAsStringAsync().Result;
if (string.IsNullOrEmpty(body))
{
resp = new HttpResponseMessage(HttpStatusCode.Forbidden);
resp.ReasonPhrase = "Content is null";
return(resp);
}
var content = JsonUtility.GetJsonObjectFromJsonString(body);
string refreshToken = content["tokens"]?["refresh"].ToString();
string accessToken = content["tokens"]?["access"].ToString();
string deploymentId = content["deploymentId"].ToString();
if (string.IsNullOrEmpty(refreshToken) || string.IsNullOrEmpty(accessToken) || string.IsNullOrEmpty(deploymentId))
{
resp = new HttpResponseMessage(HttpStatusCode.Forbidden);
resp.ReasonPhrase = "Refresh token, access token or deployment id is null.";
return(resp);
}
string tokenUrl = string.Format(Constants.AzureTokenUri, "common");
var refreshResponse = await GetToken(refreshToken, tokenUrl, Constants.MicrosoftClientId);
if (!refreshResponse.IsSuccessStatusCode)
{
resp = new HttpResponseMessage(HttpStatusCode.Forbidden);
resp.ReasonPhrase = "Access token could not be refreshed.";
return(resp);
}
string deploymentIdsConnection = Constants.BpstDeploymentIdDatabase;
var cmd = $"INSERT INTO deploymentids VALUES('{deploymentId}','{DateTime.UtcNow.ToString("o")}')";
SqlUtility.InvokeSqlCommand(deploymentIdsConnection, cmd, new Dictionary <string, string>());
resp = new HttpResponseMessage(HttpStatusCode.OK);
return(resp);
}
catch
{
var resp = new HttpResponseMessage(HttpStatusCode.InternalServerError);
resp.ReasonPhrase = "An internal error has occurred ";
return(resp);
}
}
public void AddConfigurationValues(string connString, string group, string subgroup,
string name, string value, bool visible = true)
{
var configValues = new Dictionary <string, string>();
configValues.Add("arg1", group);
configValues.Add("arg2", subgroup);
configValues.Add("arg3", name);
configValues.Add("arg4", value);
configValues.Add("arg5", Convert.ToInt32(visible).ToString(CultureInfo.InvariantCulture));
SqlUtility.InvokeSqlCommand(connString, SqlConfigQueries.configQuery, configValues);
}
public override async Task <ActionResponse> ExecuteActionAsync(ActionRequest request)
{
string connectionString = request.DataStore.GetValueAtIndex("SqlConnectionString", "SqlServerIndex");
string sqlScriptsFolder = request.DataStore.GetValue("SqlScriptsFolder");
List <string> files = Directory.EnumerateFiles(Path.Combine(request.Info.App.AppFilePath, sqlScriptsFolder)).ToList();
foreach (string file in files)
{
SqlUtility.InvokeSqlCommand(connectionString, File.ReadAllText(file), new Dictionary <string, string>());
}
return(new ActionResponse(ActionStatus.Success));
}
public async override Task <ActionResponse> ExecuteActionAsync(ActionRequest request)
{
var connectionString = request.DataStore.GetValue("SqlConnectionString");
var counts = request.DataStore.GetValue("InitialCounts");
var schema = request.DataStore.GetValue("SqlSchema");
var countsObj = JsonConvert.DeserializeObject <Dictionary <string, int> >(counts);
foreach (var entry in countsObj)
{
string cmd = $"INSERT INTO [{schema}].[entityinitialcount] VALUES ('{entry.Key}','{entry.Value}','0','{DateTime.UtcNow.ToString("o")}')";
SqlUtility.InvokeSqlCommand(connectionString, cmd, new Dictionary <string, string>());
}
return(new ActionResponse(ActionStatus.Success));
}
private void CreateIndexes(List <ADFField> fields, string schemaName, string tableName, string connString)
{
string commandFormat = string.Empty;
if (fields.Exists(f => f.name.ToLowerInvariant() == "isdeleted"))
{
commandFormat = $"CREATE INDEX idx_{tableName}_isDeleted ON [{schemaName}].[{tableName}] (isDeleted)";
SqlUtility.InvokeSqlCommand(connString, commandFormat, null);
}
if (fields.Exists(f => f.name.ToLowerInvariant() == "lastmodifieddate"))
{
commandFormat = $"CREATE INDEX idx_lmd_{tableName} ON [{schemaName}].[{tableName}] (LastModifiedDate) include(id)";
SqlUtility.InvokeSqlCommand(connString, commandFormat, null);
}
}
public override async Task <ActionResponse> ExecuteActionAsync(ActionRequest request)
{
// Provided by the json
string configTable = request.DataStore.GetValue("SqlConfigTable");
// Provided by the user including the messages below
string connectionString = request.DataStore.GetValueAtIndex("SqlConnectionString", "SqlServerIndex");
// Get list of settings to deploy;
var listGroup = request.DataStore.GetAllJson("SqlGroup");
var listSubgroup = request.DataStore.GetAllJson("SqlSubGroup");
var listConfigEntryName = request.DataStore.GetAllJson("SqlEntryName");
var listConfigEntryValue = request.DataStore.GetAllJson("SqlEntryValue");
// This action should not be called with incomplete entries - most likely an init.json error
if (listGroup == null || listSubgroup == null || listConfigEntryName == null || listConfigEntryValue == null)
{
return(new ActionResponse(ActionStatus.Failure, JsonUtility.GetEmptyJObject(), "SQL_MissingConfigValues"));
}
// Counts must be consistent
if (!(listGroup.Count == listSubgroup.Count && listSubgroup.Count == listConfigEntryName.Count && listConfigEntryName.Count == listConfigEntryValue.Count))
{
return(new ActionResponse(ActionStatus.Failure, JsonUtility.GetEmptyJObject(), "SQL_MalformedConfigValues"));
}
for (int i = 0; i < listGroup.Count; i++)
{
string group = listGroup[i].ToString();
string subgroup = listSubgroup[i].ToString();
string configEntryName = listConfigEntryName[i].ToString();
string configEntryValue = listConfigEntryValue[i].ToString();
string query = string.Format(queryTemplate, configTable, group, subgroup, configEntryName, configEntryValue);
SqlUtility.InvokeSqlCommand(connectionString, query, null);
}
return(new ActionResponse(ActionStatus.Success));
}
public override async Task <ActionResponse> ExecuteActionAsync(ActionRequest request)
{
string searchQuery = request.DataStore.GetValue("SearchQuery") ?? string.Empty;
string sqlConnectionString = request.DataStore.GetValueAtIndex("SqlConnectionString", "SqlServerIndex");
searchQuery = searchQuery.Replace("'", "''");
bool isAdvanced = IsAdvancedTwitterQuery(searchQuery);
SqlUtility.InvokeSqlCommand(sqlConnectionString, string.Format(INSERT_INTO_TWITTER_QUERY, 1, isAdvanced ? 1 : 0, searchQuery), null);
if (!isAdvanced)
{
List <string> conditions = SplitQueryByOr(searchQuery);
int detailId = 1;
for (int i = 0; i < conditions.Count; i++)
{
ReadableQueryPart readableQueryPart = GetReadableQueryPart(conditions[i]);
SqlUtility.InvokeSqlCommand(sqlConnectionString, string.Format(INSERT_INTO_TWITTER_QUERY_READABLE, i + 1, 1, readableQueryPart.Query, conditions[i]), null);
for (int j = 0; j < readableQueryPart.Details.Count; j++)
{
string detail = readableQueryPart.Details[j];
if (detail[0] == 'C')
{
SqlUtility.InvokeSqlCommand(sqlConnectionString, string.Format(INSERT_INTO_TWITTER_QUERY_DETAILS, detailId, i + 1, TWITTER_OPERATOR_CONTAINS, detail.Substring(TWITTER_OPERATOR_CONTAINS.Length + 2).TrimEnd('"')), null);
}
else
{
SqlUtility.InvokeSqlCommand(sqlConnectionString, string.Format(INSERT_INTO_TWITTER_QUERY_DETAILS, detailId, i + 1, TWITTER_OPERATOR_DOES_NOT_CONTAIN, detail.Substring(TWITTER_OPERATOR_DOES_NOT_CONTAIN.Length + 2).TrimEnd('"')), null);
}
detailId++;
}
}
}
return(new ActionResponse(ActionStatus.Success));
}
public override async Task <ActionResponse> ExecuteActionAsync(ActionRequest request)
{
string connectionString = request.DataStore.GetValueAtIndex("SqlConnectionString", "SqlServerIndex");
string sqlScriptsFolder = request.DataStore.GetValue("SqlScriptsFolder");
// For database level management scripts, such as enable change tracking will throw exception if transaction is enabled
var enableTransactionString = request.DataStore.GetValue("enableTransaction");
bool enableTransaction = true;
if (!string.IsNullOrWhiteSpace(enableTransactionString))
{
bool.TryParse(enableTransactionString, out enableTransaction);
}
List <string> files = Directory.EnumerateFiles(Path.Combine(request.Info.App.AppFilePath, sqlScriptsFolder)).ToList();
foreach (string file in files)
{
SqlUtility.InvokeSqlCommand(connectionString, File.ReadAllText(file), new Dictionary <string, string>(), enableTransaction: enableTransaction);
}
return(new ActionResponse(ActionStatus.Success));
}
public async Task <HttpResponseMessage> PostDeploymentId()
{
try
{
var body = this.Request.Content.ReadAsStringAsync().Result;
if (string.IsNullOrEmpty(body))
{
var resp = new HttpResponseMessage(HttpStatusCode.Forbidden);
resp.ReasonPhrase = "Content is null";
return(resp);
}
var content = JsonUtility.GetJsonObjectFromJsonString(body);
string refreshToken = content["tokens"]?["refresh"].ToString();
string accessToken = content["tokens"]?["access"].ToString();
string deploymentId = content["deploymentId"].ToString();
var originalClaim = new JwtSecurityToken(accessToken).Claims.First(e => e.Type == "_claim_sources").Value;
string tokenUrl = string.Format(Constants.AzureTokenUri, "common");
var primaryResponse = await GetToken(refreshToken, tokenUrl, Constants.MicrosoftClientId);
var access = new JwtSecurityToken(primaryResponse["access_token"].ToString());
var newClaim = access.Claims.First(e => e.Type == "_claim_sources").Value;
if (originalClaim == newClaim)
{
string deploymentIdsConnection = Constants.BpstDeploymentIdDatabase;
var cmd = $"INSERT INTO deploymentids VALUES('{deploymentId}','{DateTime.UtcNow.ToString("o")}')";
SqlUtility.InvokeSqlCommand(deploymentIdsConnection, cmd, new Dictionary <string, string>());
}
return(new HttpResponseMessage(HttpStatusCode.OK));
}
catch {
return(new HttpResponseMessage(HttpStatusCode.InternalServerError));
}
}
public override async Task <ActionResponse> ExecuteActionAsync(ActionRequest request)
{
string azureToken = request.DataStore.GetJson("AzureToken", "access_token");
string refreshToken = request.DataStore.GetJson("AzureToken", "refresh_token");
string subscription = request.DataStore.GetJson("SelectedSubscription", "SubscriptionId");
string resourceGroup = request.DataStore.GetValue("SelectedResourceGroup");
string triggerUrl = request.DataStore.GetValue("NotifierTriggerUrl");
string connectionString = request.DataStore.GetValue("SqlConnectionString");
string deploymentIdsConnection = Constants.BpstDeploymentIdDatabase;
string deploymentId = Guid.NewGuid().ToString();
string dataPullCompleteThreshold = "80";
var asDisabled = request.DataStore.GetValue("ssasDisabled");
Dictionary <string, string> configValues = new Dictionary <string, string>()
{
{ "NotifierUrl", Constants.BpstNotifierUrl },
{ "NotificationEmails", request.DataStore.GetValue("EmailAddress") },
{ "DeploymentId", deploymentId },
{ "TemplateName", request.Info.AppName },
{ "DeploymentTimestamp", DateTime.UtcNow.ToString("o") },
{ "ASDeployment", string.IsNullOrEmpty(asDisabled) ? "False" : (!Convert.ToBoolean(asDisabled)).ToString() },
{ "DataPullCompleteThreshold", dataPullCompleteThreshold },
{ "DataPullStatus", "-1" }
};
for (int i = 0; i < configValues.Count; i++)
{
dynamic payload = new ExpandoObject();
payload.SqlGroup = "SolutionTemplate";
payload.SqlSubGroup = "Notifier";
payload.SqlEntryName = configValues.ElementAt(i).Key;
payload.SqlEntryValue = configValues.ElementAt(i).Value;
request.DataStore.AddToDataStore("NotifierValues" + i, "SqlGroup", "SolutionTemplate");
request.DataStore.AddToDataStore("NotifierValues" + i, "SqlSubGroup", "Notifier");
request.DataStore.AddToDataStore("NotifierValues" + i, "SqlEntryName", configValues.ElementAt(i).Key);
request.DataStore.AddToDataStore("NotifierValues" + i, "SqlEntryValue", configValues.ElementAt(i).Value);
}
var configResponse = await RequestUtility.CallAction(request, "Microsoft-SetConfigValueInSql");
if (!configResponse.IsSuccess)
{
return(configResponse);
}
//OnPrem scenario
if (request.Info.WebsiteRootUrl.Contains("https://msi"))
{
var post = PostDeploymentId(deploymentId, azureToken, refreshToken);
if (!post)
{
request.Logger.LogEvent("ConfigureNotifier failed for on prem scenario - couldn't reach service.", new Dictionary <string, string>());
}
}
else
{
//Website scenario
var cmd = $"INSERT INTO deploymentids VALUES('{deploymentId}','{DateTime.UtcNow.ToString("o")}')";
SqlUtility.InvokeSqlCommand(deploymentIdsConnection, cmd, new Dictionary <string, string>());
}
AzureHttpClient azureClient = new AzureHttpClient(azureToken, subscription, resourceGroup);
var response = await azureClient.ExecuteGenericRequestNoHeaderAsync(HttpMethod.Post, triggerUrl, string.Empty);
return(new ActionResponse(ActionStatus.Success));
}
public void CreateSqlTableAndTableType(List <ADFField> fields, SalesforceSOAP.Field[] sfFields, string schemaName, string tableName, string connString)
{
StringBuilder sb = new StringBuilder();
string createTable = string.Format("CREATE TABLE [{0}].[{1}](", schemaName, tableName);
string createTableType = string.Format("CREATE TYPE [{0}].[{1}Type] AS TABLE(", schemaName, tableName);
sb.AppendLine(createTable);
foreach (var field in fields)
{
var sqlType = TypeMapper.DotNetToSql.Where(p => p.Key == field.type).First();
if (sqlType.Key != null && sqlType.Value == "nvarchar")
{
int nvarcharSize = sfFields.First(e => e.name == field.name).length;
string size = string.Empty;
if (nvarcharSize > 4000)
{
size = "max";
}
if (nvarcharSize == 0)
{
size = "255";
}
string commandFormat = string.Empty;
if (field.name == "Id")
{
commandFormat = "[{0}] [{1}]({2}),";
}
else
{
commandFormat = "[{0}] [{1}]({2}) NULL,";
}
sb.AppendLine(string.Format(commandFormat,
field.name,
string.IsNullOrEmpty(sqlType.Value) ? field.type.ToString() : sqlType.Value,
!string.IsNullOrEmpty(size) ? size : nvarcharSize.ToString()));
}
else
{
sb.AppendLine(string.Format("[{0}] [{1}] NULL,", field.name, string.IsNullOrEmpty(sqlType.Value) ? field.type.ToString() : sqlType.Value));
}
}
var tableType = sb.ToString();
tableType = tableType.Remove(sb.Length - 3, 1);
tableType = tableType + ")";
tableType = tableType.Replace(createTable, createTableType);
SqlUtility.InvokeSqlCommand(connString, tableType, null);
sb.AppendLine($"CONSTRAINT [PK_{tableName}] PRIMARY KEY CLUSTERED ([Id]))");
var createTableCmd = sb.ToString();
SqlUtility.InvokeSqlCommand(connString, sb.ToString(), null);
}
