/// <summary> /// Insert a row into the T table /// </summary> /// <typeparam name="T"></typeparam> /// <param name="newRow">The data</param> /// <param name="tablePrefix">a table prefix if exists</param> /// <returns></returns> public async Task <IMySQLResponse> InsertAsync <T>(T newRow, string tablePrefix = null) where T : class { IMySQLResponse result = null; Type t = newRow.GetType(); string name = tablePrefix + t.Name; string queryBefore = $"INSERT INTO {name} ("; string queryAfter = ") VALUES ("; ICollection <DbParameter> parameters = new List <DbParameter>(); foreach (var item in t.GetRuntimeProperties()) { IEnumerable <Attribute> attributes = item.GetCustomAttributes(); if (attributes.FirstOrDefault(x => x.GetType() == typeof(IgnoreInsert)) == default(Attribute) && attributes.FirstOrDefault(x => x.GetType() == typeof(NoColumn)) == default(Attribute)) { queryBefore += item.Name + ","; queryAfter += $"@{item.Name.ToLower()},"; var val = item.GetValue(newRow); if (attributes.FirstOrDefault(x => x.GetType() == typeof(Abstract.Database.Nullable)) != default(Attribute)) { if (val.Equals(GetDefaultValue(val.GetType()))) { val = null; } } parameters.Add(new MySqlParameter() { ParameterName = $"@{item.Name.ToLower()}", Value = val }); } } queryBefore = queryBefore.Substring(0, queryBefore.Length - 1); queryAfter = queryAfter.Substring(0, queryAfter.Length - 1) + ")"; string query = queryBefore + queryAfter; try { if (newRow is IDatabaseTable) { var temp = newRow as IDatabaseTable; if (!temp.CheckConstarints()) { result = await QueryAsync(query, parameters); } else { result = new MySQLResponse(DatabaseError.ErrorConst, DatabaseError.ErrorConst, DatabaseError.ErrorConst, new DatabaseError("Class constraint failed", 0)); } } else { result = await QueryAsync(query, parameters); } } catch (Exception e) { result = new MySQLResponse(DatabaseError.ErrorConst, DatabaseError.ErrorConst, DatabaseError.ErrorConst, new DatabaseError(e)); } return(result); }
/// <summary> /// Delete a row into the T table. Works only with "=" assertions /// </summary> /// <typeparam name="T"></typeparam> /// <param name="toDelete">The data</param> /// <param name="tablePrefix">a table prefix if exists</param> /// <returns></returns> public async Task <IMySQLResponse> DeleteAsync <T>(T toDelete, string tablePrefix = null) where T : class { IMySQLResponse result = null; Type t = toDelete.GetType(); string name = tablePrefix + t.Name; string query = $"DELETE FROM {name} WHERE "; ICollection <DbParameter> parameters = new List <DbParameter>(); foreach (var item in t.GetRuntimeProperties()) { IEnumerable <Attribute> attributes = item.GetCustomAttributes(); if (attributes.FirstOrDefault(x => x.GetType() == typeof(CheckDelete)) != default(Attribute)) { query += item.Name + $"=@{item.Name.ToLower()} AND "; parameters.Add(new MySqlParameter() { ParameterName = $"@{item.Name.ToLower()}", Value = item.GetValue(toDelete) }); } } query = query.Substring(0, query.Length - 6); try { if (toDelete is IDatabaseTable) { var temp = toDelete as IDatabaseTable; if (!temp.CheckConstarints()) { result = await QueryAsync(query, parameters); } else { result = new MySQLResponse(DatabaseError.ErrorConst, DatabaseError.ErrorConst, DatabaseError.ErrorConst, new DatabaseError("Class constraint failed", 0)); } } else { result = await QueryAsync(query, parameters); } } catch (Exception e) { result = new MySQLResponse(DatabaseError.ErrorConst, DatabaseError.ErrorConst, DatabaseError.ErrorConst, new DatabaseError(e)); } return(result); }
/// <summary> /// Update a row into the T table. /// </summary> /// <typeparam name="T1"></typeparam> /// <param name="toFind">The searched row</param> /// <param name="toUpdate">the row</param> /// <param name="tablePrefix">a table prefix if exists</param> /// <returns></returns> public async Task <IMySQLResponse> UpdateAsync <T1>(T1 toFind, T1 toUpdate, string tablePrefix = null) where T1 : class { IMySQLResponse result = null; Type t = toUpdate.GetType(); string name = tablePrefix + t.Name; string query = $"UPDATE {name} SET "; ICollection <DbParameter> parameters = new List <DbParameter>(); foreach (var item in t.GetRuntimeProperties()) { IEnumerable <Attribute> attributes = item.GetCustomAttributes(); if (attributes.FirstOrDefault(x => x.GetType() == typeof(IgnoreUpdate)) == default(Attribute) && attributes.FirstOrDefault(x => x.GetType() == typeof(NoColumn)) == default(Attribute)) { query += item.Name + $"=@{item.Name.ToLower()},"; var val = item.GetValue(toUpdate); if (attributes.FirstOrDefault(x => x.GetType() == typeof(Abstract.Database.Nullable)) != default(Attribute)) { if (val.Equals(GetDefaultValue(val.GetType()))) { val = null; } } parameters.Add(new MySqlParameter() { ParameterName = $"@{item.Name.ToLower()}", Value = val }); } } query = query.Substring(0, query.Length - 1) + " WHERE "; foreach (var item in t.GetRuntimeProperties()) { IEnumerable <Attribute> attributes = item.GetCustomAttributes(); if (attributes.FirstOrDefault(x => x.GetType() == typeof(CheckUpdate)) != default(Attribute)) { query += item.Name + $"=@u{item.Name.ToLower()} AND "; parameters.Add(new MySqlParameter() { ParameterName = $"@u{item.Name.ToLower()}", Value = item.GetValue(toFind) }); } } query = query.Substring(0, query.Length - 6); try { if (toUpdate is IDatabaseTable) { var temp = toUpdate as IDatabaseTable; if (!temp.CheckConstarints()) { result = await QueryAsync(query, parameters); } else { result = new MySQLResponse(DatabaseError.ErrorConst, DatabaseError.ErrorConst, DatabaseError.ErrorConst, new DatabaseError("Class constraint failed", 0)); } } else { result = await QueryAsync(query, parameters); } } catch (Exception e) { result = new MySQLResponse(DatabaseError.ErrorConst, DatabaseError.ErrorConst, DatabaseError.ErrorConst, new DatabaseError(e)); } return(result); }