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 void PersistSessionObject(IDbConnectionAbstraction connectionAbstraction)
{
Dictionary <string, object> .Enumerator parameters = _parameters.GetEnumerator();
using (SqlCommand command = new SqlCommand())
{
IQueryBuilder queryBuilder = null;
if (IsNew)
{
queryBuilder = new InsertQueryBuilder();
}
else if (IsDirty)
{
queryBuilder = new UpdateQueryBuilder();
}
else
{
return;
}
while (parameters.MoveNext())
{
queryBuilder.AddParameter(parameters.Current.Key, parameters.Current.Value);
}
command.Parameters.AddRange(queryBuilder.GenerateSqlParameters());
command.Connection = connectionAbstraction.Connection;
command.CommandText = queryBuilder.GenerateSqlQuery();
connectionAbstraction.Open();
TransactionId = long.Parse(command.ExecuteScalar().ToString());
connectionAbstraction.Close();
}
}
private string BuildUpdate(IDictionary <string, object> parameters, UpdateQueryBuilder query)
{
if (query == null)
{
return(null);
}
if (query.Tables.Count == 0)
{
return(null);
}
if (query.Sets.Count == 0)
{
return(null);
}
var builder = new StringBuilder();
builder.Append("UPDATE ")
.Append(BuildTables(parameters, query.Tables))
.Append(Environment.NewLine).Append("SET ")
.Append(string.Join("," + Environment.NewLine, query.Sets.Select(s => BuildUpdateSet(parameters, s))));
var where = SqlRender.BuildCondition(query.WhereConditions, parameters);
if (!string.IsNullOrWhiteSpace(where))
{
builder.Append(Environment.NewLine).Append($"WHERE ({where})");
}
return(builder.ToString());
}
/// <summary>
/// Updates an Entity in the database, provided that none of the Primary
/// keys were modified.
/// </summary>
/// <remarks>This method utilizes the <see cref="PreparedNonQuery"/> to speed things along.</remarks>
/// <param name="obj">The <see cref="TEntity"/> object to update in the dataset</param>
/// <returns>true if any records in the database were affected; false otherwise.</returns>
public bool Update(TEntity obj)
{
if (UpdateQuery == null)
{
using (var updateQuery = new UpdateQueryBuilder(EntityTable.TableName, Context))
{
// Generate the SQL
foreach (var attribute in EntityTable.Columns)
{
// Keys go in the WHERE statement, not the SET statement
if (EntityTable.PrimaryKeys.Contains(attribute.Key))
{
PropertyInfo info = attribute.Value.Property;
updateQuery.Where(attribute.Key, Comparison.Equals, new SqlLiteral($"@{attribute.Key}"));
}
else
{
updateQuery.Set(attribute.Key, new SqlLiteral($"@{attribute.Key}"));
}
}
UpdateQuery = new PreparedNonQuery(updateQuery.BuildCommand());
}
}
lock (UpdateQuery)
{
// Update parameters and execute the SQL Command
UpdateQuery.SetParameters(obj, EntityTable);
return(UpdateQuery.Execute() == 1);
}
}
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);
}
public void TestUpdateQueryBuilder2()
{
IDictionary <string, object> param = new Dictionary <string, object>();
param.Add(@"COLUMN1", 1);
param.Add(@"COLUMN2", @"ABC");
string tbl = @"TableName";
object[] whereParam = { 2, @"DEF" };
string where = @"COLUMN3=? AND COLUMN4=?";
string exp = @"UPDATE TableName SET COLUMN1=@__param_0,COLUMN2=@__param_1 WHERE COLUMN3=@__param_2 AND COLUMN4=@__param_3";
UpdateQueryBuilder b = new UpdateQueryBuilder(tbl);
b.SetParams(param);
b.SetWhere(where, whereParam);
using (SqlConnection conn = new SqlConnection())
using (DbCommand result = b.Result(conn, UpdateType.UPDATE)) {
Assert.AreEqual(exp, result.CommandText);
Assert.AreEqual(1, result.Parameters[0].Value);
Assert.AreEqual(@"ABC", result.Parameters[1].Value);
Assert.AreEqual(2, result.Parameters[2].Value);
Assert.AreEqual(@"DEF", result.Parameters[3].Value);
}
}
public override UpdateQueryBuilder Apply(UpdateQueryBuilder b) => b
.With("name", Name)
.With("display_name", DisplayName)
.With("description", Description)
.With("icon", Icon)
.With("description_privacy", DescriptionPrivacy)
.With("icon_privacy", IconPrivacy)
.With("list_privacy", ListPrivacy)
.With("visibility", Visibility);
public override UpdateQueryBuilder Apply(UpdateQueryBuilder b) => b
.With("name", Name)
.With("description", Description)
.With("tag", Tag)
.With("avatar_url", AvatarUrl)
.With("token", Token)
.With("ui_tz", UiTz)
.With("description_privacy", DescriptionPrivacy)
.With("member_list_privacy", MemberListPrivacy)
.With("front_privacy", FrontPrivacy)
.With("front_history_privacy", FrontHistoryPrivacy)
.With("pings_enabled", PingsEnabled);
private static void Update <T>(string connectionString, T entity, Dictionary <string, object> fieldsWithValues) where T : new()
{
var(condition, wheres) = KeyAttribute.GetKeysQueryWhere(entity);
var updateBuilder = new UpdateQueryBuilder()
.WithFields(fieldsWithValues)
.WithWhere(condition, wheres.ToArray());
var query = updateBuilder.Build <T>();
DataBaseDataReader.ExecuteNonQuery(connectionString, query);
}
/// <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 void GenerateSimpleQueryTest()
{
// Arrange
const string queryExpected = "UPDATE `books` SET `Title` = 'Essential C#', `Price` = 20.99";
var fields = new Dictionary <string, object>
{
{ GetColumnNameWithQuotes <Book>(nameof(Book.Title)), "Essential C#" },
{ GetColumnNameWithQuotes <Book>(nameof(Book.Price)), 20.99d }
};
var updateQueryBuilder = new UpdateQueryBuilder()
.WithFields(fields);
// Act
var query = updateQueryBuilder.Build <Book>();
// Assert
Assert.AreEqual(queryExpected, query);
}
public void TestUpdateQueryBuilder3()
{
string tbl = @"TableName";
string exp = @"INSERT INTO TableName (COLUMN1,COLUMN2) VALUES (@__param_0,@__param_1)";
UpdateQueryBuilder b = new UpdateQueryBuilder(tbl);
UpdatePabo pabo = new UpdatePabo();
b.SetParams(ObjectQuery.GetParams(pabo));
using (SqlConnection conn = new SqlConnection())
using (DbCommand result = b.Result(conn, UpdateType.INSERT)) {
Assert.AreEqual(exp, result.CommandText);
Assert.AreEqual(pabo.Num, result.Parameters[0].Value);
Assert.AreEqual(pabo.S, result.Parameters[1].Value);
}
}
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 void TestUpdateQueryBuilder1()
{
IDictionary <string, object> param = new Dictionary <string, object>();
param.Add(@"COLUMN1", 1);
param.Add(@"COLUMN2", @"ABC");
string tbl = @"TableName";
string exp = @"INSERT INTO TableName (COLUMN1,COLUMN2) VALUES (@__param_0,@__param_1)";
UpdateQueryBuilder b = new UpdateQueryBuilder(tbl);
b.SetParams(param);
using (SqlConnection conn = new SqlConnection())
using (DbCommand result = b.Result(conn, UpdateType.INSERT)) {
Assert.AreEqual(exp, result.CommandText);
Assert.AreEqual(1, result.Parameters[0].Value);
Assert.AreEqual(@"ABC", result.Parameters[1].Value);
}
}
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);
}
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));
}
public override UpdateQueryBuilder Apply(UpdateQueryBuilder b) => b
.With("display_name", DisplayName)
.With("avatar_url", AvatarUrl);
public override UpdateQueryBuilder Apply(UpdateQueryBuilder b) => b
.With("log_channel", LogChannel)
.With("log_blacklist", LogBlacklist)
.With("blacklist", Blacklist)
.With("log_cleanup_enabled", LogCleanupEnabled);
public static UpdateQueryBuilder Table(this UpdateQueryBuilder @this, string tableName)
{
@this.Tables.Clear();
@this.Tables.Add(new Table(tableName));
return(@this);
}
/// <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;
}
}
public void Run()
{
var _distinctdata = _alldata.SelectMany(e => e.BaseObject.Select(w => w.Key)).Distinct().ToList();
var AllValidfields = _tableInfo.ColumnInfos.Select(e => e.ColumnName).Intersect(_distinctdata);
var ValidKeys = _tableInfo.ColumnInfos.Where(q => q.IsKey).Select(e => e.ColumnName).Intersect(_distinctdata).ToList();
if (ValidKeys.Count != _tableInfo.ColumnInfos.Where(e => e.IsKey).Select(e => e.ColumnName).Distinct().Count())
{
Console.WriteLine($"Key missed -Type {_tableName} ");
throw new Exception();
}
querybulder = new UpdateQueryBuilder(_tableName);
List <SqlParameter> _paramsSql = new List <SqlParameter>();
int _index = 0;
foreach (var item in _alldata)
{
List <string> stringParamForQuery = new List <string>();
List <string> stringKeyParam = new List <string>();
var data = item.ChangedProperties.Intersect(AllValidfields);
if (data.Count() > 0)
{
foreach (var obj in data)
{
string param = $"@p{_index++}";
stringParamForQuery.Add(param);
var dbpar = new SqlParameter(param, shcema[_tableName][obj]);
if (item.BaseObject.ContainsKey(obj))
{
dbpar.Value = QueryBuilderHelper.FormatSQLForDb(_tableName, obj, item.BaseObject[obj]);
}
else
{
dbpar.Value = DBNull.Value;
}
_paramsSql.Add(dbpar);
}
foreach (var singleKey in ValidKeys)
{
string param = $"@p{_index++}";
var dbpar = new SqlParameter(param, shcema[_tableName][singleKey]);
stringKeyParam.Add(param);
dbpar.Value = QueryBuilderHelper.FormatSQLForDb(_tableName, singleKey, item.BaseObject[singleKey]);
_paramsSql.Add(dbpar);
}
querybulder.AppendCommand(data.ToList(), stringParamForQuery, ValidKeys, stringKeyParam);
if (_paramsSql.Count > UpdateCommandParametersCount)
{
Execute(_paramsSql);
_paramsSql = new List <SqlParameter>();
_index = 0;
querybulder.Reset();
stringParamForQuery = new List <string>();
stringKeyParam = new List <string>();
}
}
}
if (_paramsSql.Count > 0)
{
Execute(_paramsSql);
_paramsSql = new List <SqlParameter>();
_index = 0;
}
}
//public static UpdateQueryBuilder Set(this UpdateQueryBuilder @this, Func<FieldBuilder, FieldSet> field)
//{
// var f = field.Invoke(FieldBuilder.Current);
// return @this.Set(f);
//}
//public static UpdateQueryBuilder Set(this UpdateQueryBuilder @this, Func<FieldBuilder, FieldSet[]> fields)
//{
// var fs = fields.Invoke(FieldBuilder.Current);
// return @this.Set(fs);
//}
public static FieldSet <UpdateQueryBuilder> Set(this UpdateQueryBuilder @this, string fieldName)
=> new FieldSet <UpdateQueryBuilder>(@this, fieldName);
public override UpdateQueryBuilder Apply(UpdateQueryBuilder b) => b
.With("allow_autoproxy", AllowAutoproxy);
public void PersistSessionObject(IDbConnectionAbstraction connectionAbstraction)
{
Dictionary<string, object>.Enumerator parameters = _parameters.GetEnumerator();
using (SqlCommand command = new SqlCommand())
{
IQueryBuilder queryBuilder = null;
if (IsNew)
{
queryBuilder = new InsertQueryBuilder();
}
else if (IsDirty)
{
queryBuilder = new UpdateQueryBuilder();
}
else
{
return;
}
while (parameters.MoveNext())
{
queryBuilder.AddParameter(parameters.Current.Key, parameters.Current.Value);
}
command.Parameters.AddRange(queryBuilder.GenerateSqlParameters());
command.Connection = connectionAbstraction.Connection;
command.CommandText = queryBuilder.GenerateSqlQuery();
connectionAbstraction.Open();
TransactionId = long.Parse(command.ExecuteScalar().ToString());
connectionAbstraction.Close();
}
}
public abstract UpdateQueryBuilder Apply(UpdateQueryBuilder b);
public override UpdateQueryBuilder Apply(UpdateQueryBuilder b) => b
.With("proxy_enabled", ProxyEnabled)
.With("autoproxy_mode", AutoproxyMode)
.With("autoproxy_member", AutoproxyMember);