public async Task <HttpResponseMessage> Post(JObject value)
{
dynamic data = value;
IEnumerable <int> result;
using (var connection = new SqlCeConnection(_connString))
{
await connection.OpenAsync();
connection.Execute(
"insert into Tasks (Title, Description, CreatedDate) values (@title, @description, @createdDate);",
new
{
title = (string)data.title,
description = (string)data.description,
createdDate = DateTime.Parse((string)data.createdDate)
}
);
result = await connection.QueryAsync <int>("select max(Id) as id from Tasks;");
}
int id = result.First();
data.id = id;
var response = Request.CreateResponse(HttpStatusCode.Created, (JObject)data);
response.Headers.Location = new Uri(Url.Link("DefaultApi", new { controller = "Tasks", id = id }));
return(response);
}
public async Task<IEnumerable<dynamic>> GetAll()
{
using (var connection = new SqlCeConnection(_connString))
{
await connection.OpenAsync();
IEnumerable<dynamic> tasks = await connection.QueryAsync<dynamic>("select Id as id, Title as title, Description as description, CreatedDate as createdDate from Tasks;");
return tasks;
}
}
public async Task <List <Voice> > GetAllByService(int serviceId)
{
using (var cnn = new SqlCeConnection(ConnectionString))
{
var obj = new { ServiceId = serviceId };
var results = await cnn.QueryAsync <Voice>("select * from voice where ServiceId = @ServiceId", obj);
return(results.ToList());
}
}
public async Task <List <Voice> > GetAll(int serviceId, string language)
{
using (var cnn = new SqlCeConnection(ConnectionString))
{
var obj = new { ServiceId = serviceId, Language = language };
var results = await cnn.QueryAsync <Voice>("select * from voice where ServiceId = @ServiceId && Language = @Language", obj);
return(results.ToList());
}
}
public async Task <IEnumerable <dynamic> > GetAll()
{
using (var connection = new SqlCeConnection(_connString))
{
await connection.OpenAsync();
IEnumerable <dynamic> tasks = await connection.QueryAsync <dynamic>("select Id as id, Title as title, Description as description, CreatedDate as createdDate from Tasks;");
return(tasks);
}
}
public async Task <dynamic> Get(int id)
{
using (var connection = new SqlCeConnection(_connString))
{
await connection.OpenAsync();
IEnumerable <dynamic> tasks = await connection.QueryAsync <dynamic>("select Id as id, Title as title, Description as description, CreatedDate as createdDate from Tasks where Id = @id;", new { id = id });
if (!tasks.Any())
{
throw new HttpResponseException(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Task not found"));
}
return(tasks.First());
}
}
public async Task <List <TextToSpeech> > GetPage(int page, int amount)
{
using (var cnn = new SqlCeConnection(ConnectionString))
{
var startingRow = (page - 1) * amount + 1;
var endingRow = startingRow + amount;
var results = await cnn.QueryAsync <TextToSpeech>($@"
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY Id ) AS RowNum, *
FROM Orders
) AS RowConstrainedResult
WHERE RowNum >= @RowStart
AND RowNum < @RowEnd
ORDER BY RowNum", new { RowStart = startingRow, RowEnd = endingRow });
return(results.ToList());
}
}
public async Task<HttpResponseMessage> Post(JObject value)
{
dynamic data = value;
IEnumerable<int> result;
using (var connection = new SqlCeConnection(_connString))
{
await connection.OpenAsync();
connection.Execute(
"insert into Tasks (Title, Description, CreatedDate) values (@title, @description, @createdDate);",
new
{
title = (string)data.title,
description = (string)data.description,
createdDate = DateTime.Parse((string)data.createdDate)
}
);
result = await connection.QueryAsync<int>("select max(Id) as id from Tasks;");
}
int id = result.First();
data.id = id;
var response = Request.CreateResponse(HttpStatusCode.Created, (JObject)data);
response.Headers.Location = new Uri(Url.Link("DefaultApi", new { controller = "Tasks", id = id }));
return response;
}
public async Task<dynamic> Get(int id)
{
using (var connection = new SqlCeConnection(_connString))
{
await connection.OpenAsync();
IEnumerable<dynamic> tasks = await connection.QueryAsync<dynamic>("select Id as id, Title as title, Description as description, CreatedDate as createdDate from Tasks where Id = @id;", new { id = id });
if (!tasks.Any())
throw new HttpResponseException(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Task not found"));
return tasks.First();
}
}
private async Task <IEnumerable <Entry> > GetEntriesAsync(SqlCeConnection connection)
{
return(await connection.QueryAsync <Entry>("SELECT * FROM [Entry]"));
}