public Link Add(Link link)
{
using (var connection = connectionFactory.CreateConnection())
{
helperService.ExequtePlain(connection,
"INSERT INTO links(UserId, Url, Redirects, CreationDate)" +
"VALUES($userId, $url, 0, CURRENT_TIMESTAMP)",
new Parameter("userId", link.UserId),
new Parameter("url", link.Url));
link.Id = helperService.GetScalar <long>(connection, "SELECT last_insert_rowid()");
return(link);
}
}
public void Seed()
{
using (var connection = dataConnectionFactory.CreateConnection())
{
connection.Open();
if (!helperService.IsTableExists(connection, "links"))
{
helperService.ExequtePlain(connection,
"CREATE TABLE links (" +
"UserId INT," +
"Url TEXT," +
"Redirects INT," +
"CreationDate INT" +
")");
}
if (!helperService.IsTableExists(connection, "users"))
{
helperService.ExequtePlain(connection,
"CREATE TABLE users (" +
"Username TEXT UNIQUE," +
"Password TEXT," +
"Salt TEXT," +
"CreationDate INT" +
")");
}
}
}
public async Task <List <TEntity> > Process <TEntity>(RequestModel requestModel) where TEntity : class
{
var entityType = typeof(TEntity);
List <PropertyInfo> fields = entityType.GetProperties().Where(e => Attribute.IsDefined(e, typeof(PrimaryKeyAttribute))).ToList();
if (fields.Count == 0)
{
throw new ArgumentException("POCO class should have PrimaryKey attribute");
}
if (fields.Count > 1)
{
throw new ArgumentException("The library doesn't support composite key. Use only 1 PrimaryKey attribute in POCO class");
}
if (requestModel.Paging == null)
{
throw new ArgumentException("Paging mustn't be null");
}
if (requestModel.Paging.PageSize > _pageSizeLimit || requestModel.Paging.PageSize < 1)
{
throw new ArgumentException("Incorrect page size");
}
PropertyInfo primaryKey = fields.First();
var keys = await GetKeys <TEntity>(requestModel, primaryKey);
int skipCount = requestModel.Paging.PageNumber * requestModel.Paging.PageSize;
var dynamicIds = keys.Skip(skipCount).Take(requestModel.Paging.PageSize).ToList();
Type type = primaryKey.PropertyType;
Type listType = typeof(List <>).MakeGenericType(type);
IList resultIds = (IList)Activator.CreateInstance(listType);
foreach (var id in dynamicIds)
{
resultIds.Add(id);
}
using (DataConnection connection = _dataConnectionFactory.CreateConnection()) {
var table = connection.GetTable <TEntity>();
IQueryable <TEntity> resultQuery = table.Where($"@0.Contains({primaryKey.Name})", resultIds);
return(await resultQuery.ToListAsync());
}
}
public User Register(string username, string password)
{
using (var connection = connectionFactory.CreateConnection())
{
var salt = CreateSalt();
var passwordHash = Sha256(Sha256(password) + salt);
helperService.ExequtePlain(connection,
"INSERT INTO users(Username, Password, Salt, CreationDate)" +
"VALUES($username, $password, $salt, CURRENT_TIMESTAMP)",
new Parameter("username", username),
new Parameter("password", passwordHash),
new Parameter("salt", salt));
return(new User
{
Id = helperService.GetScalar <long>(connection, "SELECT last_insert_rowid()"),
Username = username
});
}
}