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 async Task <T[]> GetMultiple(T model)
{
var command = new NpgsqlCommand();
var retrieved = new List <T>();
command.CommandText = $"SELECT {ORMQueryHelper.GetSelectColumns<T>()} FROM {tableName} WHERE {String.Join("AND", WhereCriteria(model, command, DataColumnUsage.Retrieve, null))}";
await db.CommandReaderAsync(command, (reader) =>
{
T row = default(T);
LoadModel(reader, row);
retrieved.Add(row);
});
return(retrieved.ToArray());
}
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 <DropshipItemModel> GetOneByID(int id)
{
var command = new NpgsqlCommand();
command.CommandText = $"SELECT {ORMQueryHelper.GetSelectColumns<DropshipItemModel>()} FROM {tableName} WHERE id=@id";
command.Parameters.AddWithValue("@id", id);
var item = new DropshipItemModel();
await db.CommandReaderAsync(command, reader =>
{
item = new DropshipItemModel();
LoadModel(reader, item);
});
return(item);
}
public async Task <T> GetOne(T model)
{
var command = new NpgsqlCommand();
T retrieved = default(T);
command.CommandText = $"SELECT {ORMQueryHelper.GetSelectColumns<T>()} FROM {tableName} WHERE {String.Join("AND", WhereCriteria(model, command, DataColumnUsage.Retrieve, null))}";
await db.CommandReaderAsync(command, (reader) =>
{
while (reader.Read())
{
LoadModel(reader, retrieved);
}
});
return(retrieved);
}
public async Task <DropshipAccount> GetOneByUsername(string username)
{
var command = new NpgsqlCommand();
command.CommandText = $"SELECT {ORMQueryHelper.GetSelectColumns<DropshipAccount>()} FROM {tableName} WHERE username=@username";
command.Parameters.AddWithValue("@username", username);
DropshipAccount account = null;
await db.CommandReaderAsync(command, reader =>
{
account = new DropshipAccount();
LoadModel(reader, account);
});
return(account);
}
public async Task <DropshipItemModel[]> GetMultipleByUsername(string username)
{
var command = new NpgsqlCommand();
command.CommandText = $"SELECT {ORMQueryHelper.GetSelectColumns<DropshipItemModel>()} FROM {tableName} WHERE username=@username";
command.Parameters.AddWithValue("@username", username);
var items = new List <DropshipItemModel>();
await db.CommandReaderAsync(command, reader =>
{
var item = new DropshipItemModel();
LoadModel(reader, item);
items.Add(item);
});
return(items.ToArray());
}
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());
}