private bool UpdateValueInForeignTable(Guid tenantId, string tableName, string entityPrefix, string primaryKey, List <ColumnAndField> matchingColumns, string primaryValue) { var queryBuilder = new InsertQueryBuilder(); var insertColumns = new Dictionary <string, string> (); foreach (var match in matchingColumns) { if (match.TableName.Equals(tableName) && match.EntityPrefix.Equals(entityPrefix)) { if (match.Value != null) { insertColumns.Add(match.ColumnName, match.Value.ToString()); } } } if (!insertColumns.Any()) { return(true); } UpdateQueryBuilder query = new UpdateQueryBuilder(); query.AddTable(tableName, insertColumns); query.AddWhere(primaryKey, Comparison.Equals, primaryValue.ToString(), 1); var updateQuery = query.BuildQuery(); IQueryAdmin admin = new QueryAdmin(); var status = admin.UpdateResult(tenantId, "inverse", updateQuery); return(status); }
public static bool updateOrNewConfig(string key, string value) { MysqlDatas data = getValue(key); if (!data.hasRows) { InsertQueryBuilder query = new InsertQueryBuilder(); query.Table = _table; query.addValue("config_key", key); query.addValue("config_value", value); query.addValue("created_at", Helper.GetDatabaseDateFormat()); string cmd = query.BuildQuery(); return(Utils.Mysql.execute(cmd)); } else { UpdateQueryBuilder query = new UpdateQueryBuilder(); query.Table = _table; query.addSet("config_value", value); query.AddWhere("config_key", Comparison.Equals, key); string cmd = query.BuildQuery(); return(Utils.Mysql.execute(cmd)); } return(false); }
/// <summary> /// Adds a server's posted snapshot into the Snapshot Processing Queue, which /// will process the snapshot as soon as possible. This method is Non-Blocking. /// </summary> /// <remarks> /// Any errors that occur during the actual import of the data will be /// logged inside the StatsDebug log /// </remarks> /// <param name="Data">The snapshot data provided by the server.</param> /// <param name="ServerAddress">The IP address of the server.</param> /// <exception cref="UnauthorizedAccessException"> /// Thrown if the Server IP is not authorized to post game data to this server /// </exception> /// <exception cref="InvalidDataException"> /// Thrown if the provided Snapshot data is not valid, and cannot be processed /// </exception> public static void QueueServerSnapshot(string Data, IPAddress ServerAddress) { // Make sure the server is authorized if (!IsAuthorizedGameServer(ServerAddress)) { throw new UnauthorizedAccessException("Un-Authorised Gameserver (Ip: " + ServerAddress + ")"); } // Create the Snapshot Object Snapshot Snap = new Snapshot(Data, ServerAddress); // Update this server in the Database using (StatsDatabase Database = new StatsDatabase()) { // Try and grab the ID of this server int id = Database.ExecuteScalar <int>( "SELECT COALESCE(id, -1), COUNT(id) FROM servers WHERE ip=@P0 AND port=@P1", ServerAddress, Snap.ServerPort ); // New server? if (id < 0) { InsertQueryBuilder builder = new InsertQueryBuilder(Database); builder.SetTable("servers"); builder.SetField("ip", ServerAddress); builder.SetField("port", Snap.ServerPort); builder.SetField("prefix", Snap.ServerPrefix); builder.SetField("name", Snap.ServerName); builder.SetField("queryport", Snap.QueryPort); builder.SetField("lastupdate", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")); builder.Execute(); } else // existing { UpdateQueryBuilder builder = new UpdateQueryBuilder(Database); builder.SetTable("servers"); builder.SetField("prefix", Snap.ServerPrefix); builder.SetField("name", Snap.ServerName); builder.SetField("queryport", Snap.QueryPort); builder.SetField("lastupdate", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")); builder.AddWhere("id", Comparison.Equals, id); builder.Execute(); } } // Add snapshot to Queue SnapshotQueue.Enqueue(Snap); }
/// <summary> /// Updates an Account's information by ID /// </summary> /// <param name="Id">The Current Account ID</param> /// <param name="NewPid">New Account ID</param> /// <param name="NewNick">New Account Name</param> /// <param name="NewPassword">New Account Password, UN HASHED. Leave empty to not set a new password</param> /// <param name="NewEmail">New Account Email Address</param> public void UpdateUser(int Id, int NewPid, string NewNick, string NewPassword, string NewEmail) { UpdateQueryBuilder Query = new UpdateQueryBuilder("accounts", this); Query.SetField("id", NewPid); Query.SetField("name", NewNick); Query.SetField("email", NewEmail.ToLowerInvariant()); Query.AddWhere("id", Comparison.Equals, Id); // Set new password if not empty if (!String.IsNullOrWhiteSpace(NewPassword)) { Query.SetField("password", NewPassword.GetMD5Hash(false)); } Query.Execute(); }
public TriggerExecutionMessage ExecuteTrigger(TriggerExecutionPayload payload) { var updateQuery = new UpdateQueryBuilder(); var columnWithValue = new Dictionary <string, string>(); var value = payload.PayloadObj.Where(item => !string.IsNullOrEmpty(item.Value)).Aggregate(string.Empty, (current, item) => current + (item.Value + " ")); columnWithValue.Add("[Name]", value.TrimEnd()); updateQuery.AddTable("[dbo].[Item]", columnWithValue); updateQuery.AddWhere("[Id]", Comparison.Equals, payload.ConditionalValue); var queryRes = updateQuery.BuildQuery(); var message = new TriggerExecutionMessage { Message = queryRes }; return(message); }
public static bool deleteValue(int id) { DeleteQueryBuilder query = new DeleteQueryBuilder(); query.Table = _table; query.AddWhere("id", Comparison.Equals, id); string cmd = query.BuildQuery(); bool result = Utils.Mysql.execute(cmd); if (result) { UpdateQueryBuilder update = new UpdateQueryBuilder(); update.Table = "users"; update.addSet("faculty", "0"); update.AddWhere("faculty", Comparison.Equals, id); cmd = update.BuildQuery(); Utils.Mysql.execute(cmd); } return(result); }
public static bool blockedUser(int id) { /* Hard Delete */ /* * DeleteQueryBuilder query = new DeleteQueryBuilder(); * query.Table = _table; * query.AddWhere("id", Comparison.Equals, id); * string cmd = query.BuildQuery(); * bool result = Mysql.execute(cmd); * return result; */ /*Soft Delete*/ UpdateQueryBuilder query = new UpdateQueryBuilder(); query.Table = _table; query.addSet("status", "blocked"); query.AddWhere("id", Comparison.Equals, id); string cmd = query.BuildQuery(); bool result = Mysql.execute(cmd); return(result); }
/// <summary> /// Save Button Click Event /// </summary> private void SaveBtn_Click(object sender, EventArgs e) { try { using (StatsDatabase Driver = new StatsDatabase()) { bool Changes = false; UpdateQueryBuilder Query = new UpdateQueryBuilder("player", Driver); int Rank = Int32.Parse(Player["rank"].ToString()); // Update clantag if (Player["clantag"].ToString() != ClanTagBox.Text.Trim()) { Player["clantag"] = ClanTagBox.Text.Trim(); Query.SetField("clantag", ClanTagBox.Text.Trim()); Changes = true; } // Update Rank if (Rank != RankSelect.SelectedIndex) { if (Rank > RankSelect.SelectedIndex) { Query.SetField("decr", 1); Query.SetField("chng", 0); } else { Query.SetField("decr", 0); Query.SetField("chng", 1); } Player["rank"] = RankSelect.SelectedIndex; Query.SetField("rank", RankSelect.SelectedIndex); Changes = true; } // update perm ban status if (Int32.Parse(Player["permban"].ToString()) != PermBanSelect.SelectedIndex) { Player["permban"] = PermBanSelect.SelectedIndex; Query.SetField("permban", PermBanSelect.SelectedIndex); Changes = true; } // If no changes made, just return if (!Changes) { MessageBox.Show("Unable to save player because no changes were made.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } // Preform Query Query.AddWhere("id", Comparison.Equals, Pid); Query.Execute(); this.Close(); } } catch (DbConnectException Ex) { HttpServer.Stop(); ExceptionForm.ShowDbConnectError(Ex); return; } }
private string BuildInsertQueryV1(Guid itemId, string entityName, List <GroupedColumns> matchingColumns) { //create lower tables... Dictionary <string, string> executedTables = new Dictionary <string, string>(); var query = ""; var tablesWhoHasForeignKeyReference = matchingColumns.Where(t => t.NeedToUpdateColumn != null).ToList(); if (tablesWhoHasForeignKeyReference.Any()) { foreach (var table in tablesWhoHasForeignKeyReference) { query += GetQueryStr(table); StoreExecutedTables(executedTables, table); } } //create item table.. var itemTable = matchingColumns.FirstOrDefault(t => t.EntityFullName.ToLower().Equals(ItemHelper.ItemClassName.ToLower())); if (itemTable != null) { query += GetQueryStr(itemTable); StoreExecutedTables(executedTables, itemTable); } //create other tables.. foreach (var item in matchingColumns) { var clientName = (string.IsNullOrEmpty(item.ClientName)) ? item.EntityFullName : item.ClientName; var isExecuted = executedTables.Where(t => t.Key.Equals(clientName)).ToList(); if (isExecuted.Any()) { continue; } query += GetQueryStr(item); } // add relateions foreach (var table in tablesWhoHasForeignKeyReference) { if (table.NeedToUpdateColumn == null) { continue; } var columnWithValue = new Dictionary <string, string>(); UpdateQueryBuilder updateQuery = new UpdateQueryBuilder(); columnWithValue.Add(table.NeedToUpdateColumn.ColumnName, table.Id.ToString()); updateQuery.AddTable(table.NeedToUpdateColumn.TableName, columnWithValue); var targetRow = matchingColumns.FirstOrDefault(t => t.EntityFullName.ToLower().Equals(table.NeedToUpdateColumn.EntityFullName.ToLower())); if (targetRow == null) { continue; } updateQuery.AddWhere(table.NeedToUpdateColumn.PrimaryKey, Comparison.Equals, targetRow.Id.ToString(), 1); query += updateQuery.BuildQuery(); } IMetadataManager iMetadataManager = new MetadataManager.Contracts.MetadataManager(); var triggers = iMetadataManager.GetTriggerProperties(entityName); if (triggers.Any()) { var singletonTrigger = triggers[0]; var bodyProp = singletonTrigger.GetBody(); var search = matchingColumns.FirstOrDefault(t => t.EntityFullName.ToLower().Equals(entityName.ToLower())); if (search == null) { return(TransactionHelper.BuildQuery(query)); } { var payload = bodyProp.Select(item => search.Columns.FirstOrDefault(t => t.FieldName.ToLower().Equals(item.ToLower()))).Where(matching => matching != null).ToDictionary <ColumnAndField, string, string>(matching => matching.ColumnName, matching => matching.Value); if (!payload.Any()) { return(TransactionHelper.BuildQuery(query)); } var triggerEngine = new TriggerEngine(); var triggerExecutionPayload = new TriggerExecutionPayload { PayloadObj = payload, ConditionalValue = itemId.ToString() }; var triggerQuery = triggerEngine.GetQuery(triggers, triggerExecutionPayload); if (!string.IsNullOrEmpty(triggerQuery)) { query += triggerQuery; } } } //------------ return(TransactionHelper.BuildQuery(query)); }