public void CountAsync(string columnName, SqlCondition condition, BasicAction <long> onCounted, bool distinct)
{
SqlManager.PoolAsyncOperation(() => {
var count = _database.Count <TableKey>(columnName, condition, distinct);
onCounted?.Invoke(count);
});
}
public void AverageAsync(string columnName, SqlCondition condition, BasicAction <long> onAverage)
{
SqlManager.PoolAsyncOperation(() => {
var average = _database.Average <TableKey>(columnName, condition);
onAverage?.Invoke(average);
});
}
public void ExistAsync(SqlCondition condition, BasicAction <bool> onExist, bool distinct)
{
SqlManager.PoolAsyncOperation(() => {
var count = _database.Count <TableKey>(condition, distinct);
onExist?.Invoke(count > 0);
});
}
public void MaxAsync(string columnName, SqlCondition condition, BasicAction <TableKey> onMax)
{
SqlManager.PoolAsyncOperation(() => {
var max = _database.Max <TableKey>(columnName, condition);
onMax?.Invoke(max);
});
}
public void MinAsync(string columnName, SqlCondition condition, BasicAction <TableKey> onMin)
{
SqlManager.PoolAsyncOperation(() => {
var min = _database.Min <TableKey>(columnName, condition);
onMin?.Invoke(min);
});
}
public void SelectAllAsync(SqlCondition condition, BasicAction <TableKey[]> onSelected, bool distinct, params string[] columnsToSelect)
{
SqlManager.PoolAsyncOperation(() => {
var tables = _database.SelectAll <TableKey>(condition, distinct, columnsToSelect);
onSelected?.Invoke(tables);
});
}
public void SumAsync(string columnName, SqlCondition condition, BasicAction <decimal> onSum)
{
SqlManager.PoolAsyncOperation(() => {
var sum = _database.Sum <TableKey>(columnName, condition);
onSum?.Invoke(sum);
});
}
public void DeleteAsync(SqlCondition condition, BasicAction onDeleted)
{
SqlManager.PoolAsyncOperation(() => {
var deleted = _database.Delete <TableKey>(condition);
if (deleted)
{
onDeleted?.Invoke();
}
});
}
/// <summary>
/// Create a new instance of <see cref="SqlCondition"/> and attach to the current instance of <see cref="QueryAccessor{TableKey}"/>
/// </summary>
/// <param name="onCondition"></param>
/// <returns></returns>
public QueryAccessor <TableKey> UseCondition(BasicAction <SqlCondition> onCondition)
{
if (_cd == null)
{
_cd = new SqlCondition();
}
onCondition(_cd);
return(this);
}
internal static void SetQuery(this MySqlCommand command, string query, SqlCondition condition = null)
{
command.CommandText = query;
if (condition != null)
{
condition.ApplyToCmd(command);
}
command.Prepare();
}
/// <summary>
/// Update all elements in the <see cref="Database"/> with current instance's values using the specified <see cref="SqlCondition"/>
/// </summary>
/// <param name="condition"></param>
/// <param name="columnsToUpdate"></param>
/// <returns></returns>
public bool Update(SqlCondition condition, params string[] columnsToUpdate)
{
var updated = false;
Database.ExecuteCommand((cmd) => {
cmd.SetQuery(QueryBuilder.GetUpdateQuery(this, cmd, condition, columnsToUpdate), condition);
updated = cmd.ExecuteNonQuery() > 0;
});
return(updated);
}
private static string UseBuilder(string baseStr, SqlCondition condition, BasicAction <StringBuilder> onBuilder)
{
var sb = new StringBuilder(baseStr);
onBuilder?.Invoke(sb);
if (condition != null)
{
sb.Append($" { condition.GetQuery() }");
}
return(sb.ToString());
}
internal static string GetSelectQuery(Table table, SqlCondition condition, string[] columnsToSelect, bool distinct)
{
return(UseBuilder($"SELECT { (distinct ? "DISTINCT " : string.Empty) }", condition, (sb) => {
if (columnsToSelect.Length > 0)
{
sb.Append(string.Join(",", columnsToSelect));
}
else
{
sb.Append("*");
}
sb.Append($" FROM `{ table.Identifier }`");
}));
}
/// <summary>
/// Execute a COUNT query with the specified parameters and return the result.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="distinct"></param>
/// <param name="columnName"></param>
/// <param name="condition"></param>
/// <returns></returns>
public long Count <T>(string columnName, SqlCondition condition, bool distinct) where T : Table
{
long count = 0;
ExecuteCommand((cmd) => {
if (SqlManager.CreateTableInstance(out T table))
{
cmd.SetQuery(QueryBuilder.GetCountQuery(table, columnName, condition, distinct), condition);
cmd.OnReader((reader) => {
count = reader.GetInt64(0);
});
}
});
return(count);
}
public long Average <T>(string columnName, SqlCondition condition) where T : Table
{
long avg = -1;
ExecuteCommand((cmd) => {
if (SqlManager.CreateTableInstance(out T table))
{
cmd.SetQuery(QueryBuilder.GetAvgQuery(table, columnName, condition), condition);
cmd.OnReader((reader) => {
avg = reader.GetInt64(0);
});
}
});
return(avg);
}
internal static string GetUpdateQuery(Table table, MySqlCommand command, SqlCondition condition, string[] columns)
{
var fields = Table.GetFields(table.GetType());
var iParam = condition == null ? 0 : condition.ParamIndex + 1;
return(UseBuilder($"UPDATE `{ table.Identifier }` SET ", condition, (sb) => {
var cCount = columns.Length;
if (cCount == 0)
{
cCount--;
}
for (var i = 0; i < fields.Length; i++)
{
var field = fields[i];
if (field.GetCustomAttribute <PrimaryKey>() != null)
{
continue;
}
if (cCount > 0)
{
if (!columns.Contains(field.Name))
{
continue;
}
else
{
cCount--;
}
}
var pName = $"@{ iParam++ }";
command.Parameters.AddWithValue(pName, field.GetValue(table));
sb.Append($"{ field.Name }={ pName }");
if (cCount == 0)
{
break;
}
if (i < fields.Length - 1)
{
sb.Append(", ");
}
}
}));
}
/// <summary>
/// Selects all the elements from the specified <see cref="Table"/> with the specified parameters and returns an instance of the <typeparamref name="T"/>.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="columnsToSelect"></param>
/// <param name="condition"></param>
/// <param name="distinct"></param>
/// <returns></returns>
public T[] SelectAll <T>(SqlCondition condition, bool distinct, params string[] columnsToSelect) where T : Table
{
var result = new T[0];
ExecuteCommand((cmd) => {
if (SqlManager.CreateTableInstance(out T table))
{
var records = new List <T>();
var fields = Table.GetFields <T>();
cmd.SetQuery(QueryBuilder.GetSelectQuery(table, condition, columnsToSelect, distinct), condition);
cmd.OnReader((reader) => {
if (SqlManager.CreateTableInstance(out T instance))
{
for (var i = 0; i < reader.FieldCount; i++)
{
var field = fields.FirstOrDefault((f) => f.Name == reader.GetName(i));
if (field != null)
{
object value = null;
if (field.FieldType == typeof(bool))
{
value = reader.GetBoolean(i);
}
else if (!reader.IsDBNull(i))
{
value = reader.GetValue(i);
}
field.SetValue(instance, value);
}
}
records.Add(instance);
}
});
result = records.ToArray();
}
});
return(result);
}
/// <summary>
/// Delete all the elements from the specified <see cref="Table"/> based on the specified <see cref="SqlCondition"/>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition"></param>
/// <returns></returns>
public bool Delete <T>(SqlCondition condition) where T : Table
{
if (condition == null)
{
throw new ArgumentNullException("condition", "A valid SqlCondition is required.");
}
var deleted = false;
ExecuteCommand((cmd) => {
if (SqlManager.CreateTableInstance(out T table))
{
cmd.SetQuery(QueryBuilder.GetDeleteQuery(table, condition), condition);
deleted = cmd.ExecuteNonQuery() > 0;
}
});
return(deleted);
}
public decimal Sum <T>(string columnName, SqlCondition condition) where T : Table
{
decimal sum = -1;
ExecuteCommand((cmd) => {
if (SqlManager.CreateTableInstance(out T table))
{
cmd.SetQuery(QueryBuilder.GetSumQuery(table, columnName, condition), condition);
cmd.OnReader((reader) => {
if (!reader.IsDBNull(0))
{
sum = reader.GetDecimal(0);
}
});
}
});
return(sum);
}
/// <summary>
/// Selects the first element from the specified <see cref="Table"/> with the specified parameters and returns an instance of the <typeparamref name="T"/>.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="columnsToSelect"></param>
/// <param name="condition"></param>
/// <param name="distinct"></param>
/// <returns></returns>
public T Select <T>(SqlCondition condition, bool distinct, params string[] columnsToSelect) where T : Table
{
T table = null;
ExecuteCommand((cmd) => {
if (SqlManager.CreateTableInstance(out table))
{
if (condition == null)
{
condition = new SqlCondition();
}
condition.Limit(1);
var fields = Table.GetFields <T>();
cmd.SetQuery(QueryBuilder.GetSelectQuery(table, condition, columnsToSelect, distinct), condition);
cmd.OnReader((reader) => {
for (var i = 0; i < reader.FieldCount; i++)
{
var field = fields.FirstOrDefault((f) => f.Name == reader.GetName(i));
if (field != null)
{
object value = null;
if (field.FieldType == typeof(bool))
{
value = reader.GetBoolean(i);
}
else if (!reader.IsDBNull(i))
{
value = reader.GetValue(i);
}
field.SetValue(table, value);
}
}
});
}
});
return(table);
}
public T Min <T>(string columnName, SqlCondition condition) where T : Table
{
T table = null;
ExecuteCommand((cmd) => {
if (SqlManager.CreateTableInstance(out table))
{
cmd.SetQuery(QueryBuilder.GetMinQuery(table, columnName, condition), condition);
cmd.OnReader((reader) => {
var field = Table.GetFields <T>().FirstOrDefault((f) => f.Name == columnName);
if (field != null)
{
field.SetValue(table, reader.GetValue(0));
}
});
}
});
return(table);
}
public void CountAsync(string columnName, SqlCondition condition, BasicAction <long> onCounted)
{
CountAsync(columnName, condition, onCounted, false);
}
public void CountAsync(SqlCondition condition, BasicAction <long> onCounted, bool distinct)
{
CountAsync(string.Empty, condition, onCounted, distinct);
}
public long Average(string columnName, SqlCondition condition)
{
return(_database.Average <TableKey>(columnName, condition));
}
/// <summary>
/// If any <see cref="SqlCondition"/> attached, remove it and return the current instance
/// </summary>
/// <returns></returns>
public QueryAccessor <TableKey> ClearCondition()
{
_cd = null;
return(this);
}
public TableKey Min(string columnName, SqlCondition condition)
{
return(_database.Min <TableKey>(columnName, condition));
}
public decimal Sum(string columnName, SqlCondition condition)
{
return(_database.Sum <TableKey>(columnName, condition));
}
public void CountAsync(SqlCondition condition, BasicAction <long> onCounted)
{
CountAsync(string.Empty, condition, onCounted, false);
}
/// <summary>
/// Selects the first element from the specified <see cref="Table"/> with the specified parameters and returns an instance of the <typeparamref name="TableKey"/>.
/// </summary>
/// <param name="columnsToSelect"></param>
/// <param name="condition"></param>
/// <returns></returns>
public TableKey Select(SqlCondition condition, params string[] columnsToSelect)
{
return(Select(condition, false, columnsToSelect));
}
public void SelectAllAsync(SqlCondition condition, BasicAction <TableKey[]> onSelected, params string[] columnsToSelect)
{
SelectAllAsync(condition, onSelected, false, columnsToSelect);
}