public async Task DeleteAsync(string tableName, RestDbExpression filterExpression)
{
using (var connection = new SqliteConnection(_connectionString))
{
await connection.OpenAsync();
var query = new StringBuilder();
query.Append($"DELETE FROM [{tableName}]");
var parameters = new Dictionary <string, object>();
if (filterExpression != null)
{
var whereClause = filterExpression.ToSql(parameters);
if (!string.IsNullOrEmpty(whereClause))
{
query.Append($" WHERE {whereClause}");
}
}
using (var command = connection.CreateCommand())
{
command.CommandText = query.ToString();
foreach (var parameter in parameters)
{
command.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
await command.ExecuteNonQueryAsync();
}
}
}
public Task DeleteAsync(string tableName, RestDbExpression filterExpression)
{
throw new System.NotImplementedException();
}
public Task UpdateAsync(string tableName, Dictionary <string, object> values, RestDbExpression filterExpression)
{
throw new System.NotImplementedException();
}
public Task <List <Dictionary <string, object> > > SelectAsync(string tableName, Dictionary <string, ColumnType> selectedFields, RestDbExpression filterExpression)
{
throw new System.NotImplementedException();
}
public async Task <List <Dictionary <string, object> > > SelectAsync(string tableName, Dictionary <string, ColumnType> selectedFields, RestDbExpression filterExpression)
{
using (var connection = new SqliteConnection(_connectionString))
{
await connection.OpenAsync();
var query = new StringBuilder();
query.Append($"SELECT * FROM [{tableName}]");
var parameters = new Dictionary <string, object>();
if (filterExpression != null)
{
var whereClause = filterExpression.ToSql(parameters);
if (!string.IsNullOrEmpty(whereClause))
{
query.Append($" WHERE {whereClause}");
}
}
using (var command = connection.CreateCommand())
{
command.CommandText = query.ToString();
foreach (var kvp in parameters)
{
command.Parameters.AddWithValue(kvp.Key, kvp.Value);
}
var result = new List <Dictionary <string, object> >();
using (var reader = await command.ExecuteReaderAsync())
{
while (reader.Read())
{
result.Add(reader.ToDictionaryValues(selectedFields));
}
}
return(result);
}
}
}
public async Task UpdateAsync(string tableName, Dictionary <string, object> values, RestDbExpression filterExpression)
{
using (var connection = new SqliteConnection(_connectionString))
{
await connection.OpenAsync();
var assigns = string.Join(",", values.Keys.Select(k => $"{k}=@{k}"));
var query = new StringBuilder();
query.Append($"UPDATE [{tableName}] SET {string.Join(',', assigns)}");
var parameters = new Dictionary <string, object>();
if (filterExpression != null)
{
var whereClause = filterExpression.ToSql(parameters);
if (!string.IsNullOrEmpty(whereClause))
{
query.Append($" WHERE {whereClause}");
}
}
using (var command = connection.CreateCommand())
{
command.CommandText = query.ToString();
foreach (var kvp in values)
{
command.Parameters.AddWithValue(kvp.Key, kvp.Value);
}
await command.ExecuteNonQueryAsync();
}
}
}
