public async Task<UserMessage> AddMessage(string userLogin, string text)
{
var res = new UserMessage() {Text = text, UserLogin = userLogin, Created = DateTime.UtcNow};
using (var conn = new SQLiteConnection(_connectionString))
{
await conn.OpenAsync().ConfigureAwait(false);
int count = await conn.ExecuteAsync(
"insert or ignore into [Message] (Text, Created, UserId) select @text as Text, @created as Created, UserId from [User] where [Login] = @userLogin",
new {text, created = res.Created, userLogin}).ConfigureAwait(false);
if (count == 0)
{
int userId =
await
conn.ExecuteScalarAsync<int>(
"insert into [User] (Login, Created) values(@userLogin, @created); SELECT last_insert_rowid() FROM [User]",
new {userLogin, created = res.Created}).ConfigureAwait(false);
await conn.ExecuteAsync("insert into [Message] (Text, Created, UserId) values(@text, @created, @userId)", new { text, created = res.Created, userId}).ConfigureAwait(false);
}
}
return res;
}
/// <summary>
/// Execute scalar
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public static Task <object> ExecuteScalarAsync(this SQLiteConnection conn, string cmdText, SQLiteParameter[] parameters, CommandType commandType)
{
conn.CheckNull(nameof(conn));
return(conn.ExecuteScalarAsync(cmdText, parameters, commandType, null));
}
/// <summary>
/// Execute scalar
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdText"></param>
/// <param name="commandType"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static Task <object> ExecuteScalarAsync(this SQLiteConnection conn, string cmdText, CommandType commandType, SQLiteTransaction transaction)
{
conn.CheckNull(nameof(conn));
return(conn.ExecuteScalarAsync(cmdText, null, commandType, transaction));
}
public async Task<DataPage<UserMessage>> GetUserMessages(string userLogin, int pageNumber, int pageSize)
{
ValidatePagination(pageNumber, pageSize);
using (var conn = new SQLiteConnection(_connectionString))
{
await conn.OpenAsync().ConfigureAwait(false);
if (await conn.ExecuteScalarAsync<int>("select count(*) from [User] where Login = @userLogin", new { userLogin }).ConfigureAwait(false) == 0)
return new DataPage<UserMessage>(pageNumber, pageSize, 0, null); //not found
int count = await conn.ExecuteScalarAsync<int>("select count(*) from [Message] m join [User] u on m.UserId=u.UserId where u.Login = @userLogin", new {userLogin}).ConfigureAwait(false);
string q = "select u.Login as UserLogin, m.Text, m.Created from [Message] m join [User] u on m.UserId=u.UserId where u.Login = @userLogin order by m.Created";
object prms;
if (pageSize == -1)
prms = new { userLogin };
else
{
q += " limit @size offset @skip";
prms = new { userLogin, size = pageSize, skip = (pageNumber - 1) * pageSize };
}
return
new DataPage<UserMessage>(pageNumber, pageSize, count,
(await conn.QueryAsync<UserMessage>(q, prms)).ToList());
}
}
public async Task<DataPage<UserInfo>> GetUsers(int pageNumber, int pageSize)
{
ValidatePagination(pageNumber, pageSize);
using (var conn = new SQLiteConnection(_connectionString))
{
await conn.OpenAsync().ConfigureAwait(false);
int count = await conn.ExecuteScalarAsync<int>("select count(*) from [User]").ConfigureAwait(false);
string q = "select [Login] as UserLogin, Display as DisplayName, Created from [User] order by [Login]";
object prms;
if (pageSize == -1)
prms = null;
else
{
q += " limit @size offset @skip";
prms = new { size = pageSize, skip = (pageNumber - 1) * pageSize };
}
return
new DataPage<UserInfo>(pageNumber, pageSize, count,
(await conn.QueryAsync<UserInfo>(q, prms).ConfigureAwait(false)).ToList());
}
}