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