public async Task InsertMessage(StoreMessage message)
{
using (var con = new SqlConnection())
{
con.ConnectionString = _connectionString;
await con.OpenAsync();
var command = con.CreateCommand();
command.CommandText =
"INSERT INTO Messages (Guid,Sender,Recipient,SendDateTime,StoreDateTime,DataLength,DataType,Text)" +
"VALUES (@Guid,@Sender,@Recipient,@SendDateTime,@StoreDateTime,@DataLength,@DataType,@Text)";
command.Parameters.AddRange(new[]
{
new SqlParameter("@Guid", message.Guid),
new SqlParameter("@Sender", message.Sender),
new SqlParameter("@Recipient", message.Recipient),
new SqlParameter("@SendDateTime", message.SendDateTime),
new SqlParameter("@StoreDateTime", message.StoreDateTime),
new SqlParameter("@DataLength", message.DataLength),
new SqlParameter("@DataType", message.DataType),
new SqlParameter("@Text", message.Text)
});
var count = await command.ExecuteNonQueryAsync();
if (count < 1)
{
throw new DataException($"Cannot insert message in database: {command}");
}
}
}
public async Task <StoreMessage[]> GetMessages(string recipient, int id, int count)
{
var messages = new List <StoreMessage>();
if (count == 0)
{
return(messages.ToArray());
}
using (var con = new SqlConnection())
{
con.ConnectionString = _connectionString;
await con.OpenAsync();
var command = con.CreateCommand();
command.CommandText =
"SELECT TOP (@count) Id,Guid,Sender,Recipient,SendDateTime,StoreDateTime,DataLength,DataType,Text " +
"FROM Messages WHERE Recipient = @Recipient AND Id > @Id " +
"ORDER BY Id";
command.Parameters.AddRange(new[]
{
new SqlParameter("@count", count),
new SqlParameter("@Recipient", recipient),
new SqlParameter("@Id", id)
});
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
var m = new StoreMessage
{
Id = reader.GetInt32("Id"),
Guid = reader.GetString("Guid"),
Sender = reader.GetString("Sender"),
Recipient = reader.GetString("Recipient"),
SendDateTime = reader.GetDateTime("SendDateTime"),
StoreDateTime = reader.GetDateTime("StoreDateTime"),
DataType = reader.GetString("DataType"),
DataLength = reader.GetInt32("DataLength"),
Text = reader.GetString("Text")
};
messages.Add(m);
}
reader.Close();
}
return(messages.ToArray());
}
