public async Task DeleteItem(int itemid, string username)
{
var itemsTableName = ORMQueryHelper.GetTableName <DropshipItemModel>();
var command = new NpgsqlCommand();
command.CommandText = $"DELETE FROM {itemsTableName} WHERE username=@username AND id=@id";
command.Parameters.AddWithValue("username", username);
command.Parameters.AddWithValue("id", itemid);
await db.CommandNonqueryAsync(command);
}
public async Task UpdateListing(DropshipItemModel model)
{
var itemsTableName = ORMQueryHelper.GetTableName <DropshipItemModel>();
var command = new NpgsqlCommand();
command.CommandText = $"UPDATE {itemsTableName} SET listingid=@listingid WHERE id={model.ID} AND username=@username";
command.Parameters.AddWithValue("listingid", model.ListingID);
command.Parameters.AddWithValue("username", model.Username);
await db.CommandNonqueryAsync(command);
}
public async Task UpdateRules(DropshipItemModel model)
{
var itemsTableName = ORMQueryHelper.GetTableName <DropshipItemModel>();
var command = new NpgsqlCommand();
command.CommandText = $"UPDATE {itemsTableName} SET rules=@rules WHERE id={model.ID} AND username=@username";
command.Parameters.AddWithValue("rules", NpgsqlTypes.NpgsqlDbType.Jsonb, JsonConvert.SerializeObject(model.Rules));
command.Parameters.AddWithValue("username", model.Username);
await db.CommandNonqueryAsync(command);
}
public static T ReadModel <T>(this DbDataReader reader, T model)
{
var tableName = ORMQueryHelper.GetTableName <T>();
var props = typeof(T).GetProperties();
var fieldNames = new List <string>();
for (int i = 0; i != reader.FieldCount; i++)
{
fieldNames.Add(reader.GetName(i));
}
foreach (var prop in props)
{
var attribute = prop.GetCustomAttribute <DataColumn>();
if (attribute != null)
{
var index = reader.GetOrdinal($"{tableName}{attribute.Name}");
switch (attribute.DbType)
{
case NpgsqlTypes.NpgsqlDbType.Jsonb:
try
{
if (reader.IsDBNull(index))
{
prop.SetValue(model, HandleNulls(reader.GetFieldType(index)));
}
else
{
prop.SetValue(model, JsonConvert.DeserializeObject((string)reader.GetValue(index), prop.PropertyType));
}
}
catch
{
prop.SetValue(model, HandleNulls(reader.GetFieldType(index)));
}
break;
default:
prop.SetValue(model, !reader.IsDBNull(index) ? reader.GetValue(index) : HandleNulls(reader.GetFieldType(index)));
break;
}
}
}
return(model);
}
public async Task <DropshipAccountItem[]> GetMultipleWithAccount(int take, int skip)
{
var accountTableName = ORMQueryHelper.GetTableName <DropshipAccount>();
var command = new NpgsqlCommand();
command.CommandText = $"SELECT {ORMQueryHelper.GetSelectColumns<DropshipItemModel>()},{ORMQueryHelper.GetSelectColumns<DropshipAccount>()} FROM {tableName} JOIN {accountTableName} ON {tableName}.username={accountTableName}.username LIMIT {take} OFFSET {skip};";
var items = new List <DropshipAccountItem>();
await db.CommandReaderAsync(command, reader =>
{
var item = new DropshipAccountItem();
reader.ReadModel <DropshipItemModel>(item.Item);
reader.ReadModel <DropshipAccount>(item.Account);
items.Add(item);
});
return(items.ToArray());
}
public async Task <DropshipAccountItem[]> GetMultipleWithAccountByUsername(string username)
{
var accountTableName = ORMQueryHelper.GetTableName <DropshipAccount>();
var command = new NpgsqlCommand();
command.CommandText = $"SELECT {ORMQueryHelper.GetSelectColumns<DropshipItemModel>()},{ORMQueryHelper.GetSelectColumns<DropshipAccount>()} FROM {tableName} JOIN {accountTableName} ON {tableName}.username={accountTableName}.username WHERE {tableName}.username=@username;";
command.Parameters.AddWithValue("@username", username);
var items = new List <DropshipAccountItem>();
await db.CommandReaderAsync(command, reader =>
{
var item = new DropshipAccountItem();
reader.ReadModel <DropshipItemModel>(item.Item);
reader.ReadModel <DropshipAccount>(item.Account);
items.Add(item);
});
return(items.ToArray());
}