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()); }