public async Task <LagoVista.Core.Models.UIMetaData.ListResponse <DataStreamResult> > GetItemsAsync(string deviceId, LagoVista.Core.Models.UIMetaData.ListRequest request)
{
var sql = new StringBuilder("select ");
if (request.PageSize == 0)
{
request.PageSize = 50;
}
sql.Append($"[{_stream.TimeStampFieldName}]");
foreach (var fld in _stream.Fields)
{
sql.Append($", [{fld.FieldName}]");
}
sql.AppendLine();
sql.AppendLine($" from [{_stream.DBTableName}]");
sql.AppendLine($" where [{_stream.DeviceIdFieldName}] = @deviceId");
if (!String.IsNullOrEmpty(request.NextRowKey))
{
sql.AppendLine($" and {_stream.TimeStampFieldName} < @lastDateStamp");
}
if (!String.IsNullOrEmpty(request.StartDate))
{
sql.AppendLine($" and {_stream.TimeStampFieldName} >= @startDateStamp");
}
if (!String.IsNullOrEmpty(request.EndDate))
{
sql.AppendLine($" and {_stream.TimeStampFieldName} <= @endDateStamp");
}
sql.AppendLine($" order by [{_stream.TimeStampFieldName}] desc");
sql.AppendLine(" OFFSET @PageSize * @PageIndex ROWS");
sql.AppendLine(" FETCH NEXT @PageSize ROWS ONLY ");
Console.WriteLine(sql.ToString());
var responseItems = new List <DataStreamResult>();
using (var cn = new System.Data.SqlClient.SqlConnection(_connectionString))
using (var cmd = new System.Data.SqlClient.SqlCommand(sql.ToString(), cn))
{
cmd.Parameters.AddWithValue("@deviceId", deviceId);
cmd.Parameters.AddWithValue("@PageSize", request.PageSize);
cmd.Parameters.AddWithValue("@PageIndex", request.PageIndex);
if (!String.IsNullOrEmpty(request.NextRowKey))
{
cmd.Parameters.AddWithValue($"@lastDateStamp", request.NextRowKey.ToDateTime());
}
if (!String.IsNullOrEmpty(request.StartDate))
{
cmd.Parameters.AddWithValue($"@startDateStamp", request.StartDate.ToDateTime());
}
if (!String.IsNullOrEmpty(request.EndDate))
{
cmd.Parameters.AddWithValue($"@endDateStamp", request.EndDate.ToDateTime());
}
cmd.CommandType = System.Data.CommandType.Text;
await cmd.Connection.OpenAsync();
using (var rdr = await cmd.ExecuteReaderAsync())
{
while (rdr.Read())
{
var resultItem = new DataStreamResult();
resultItem.Timestamp = Convert.ToDateTime(rdr[_stream.TimeStampFieldName]).ToJSONString();
foreach (var fld in _stream.Fields)
{
resultItem.Fields.Add(fld.FieldName, rdr[fld.FieldName]);
}
responseItems.Add(resultItem);
}
}
}
var response = new Core.Models.UIMetaData.ListResponse <DataStreamResult>();
response.Model = responseItems;
response.PageSize = responseItems.Count;
response.PageIndex = request.PageIndex;
response.HasMoreRecords = responseItems.Count == request.PageSize;
if (response.HasMoreRecords)
{
response.NextRowKey = responseItems.Last().Timestamp;
}
return(response);
}
public Task <LagoVista.Core.Models.UIMetaData.ListResponse <DataStreamResult> > GetItemsAsync(string deviceId, LagoVista.Core.Models.UIMetaData.ListRequest request)
{
throw new NotSupportedException("Reading a list of items from amazon S3 is not supported.");
}
public async Task <LagoVista.Core.Models.UIMetaData.ListResponse <DataStreamResult> > GetItemsAsync(string deviceId, LagoVista.Core.Models.UIMetaData.ListRequest request)
{
throw new NotImplementedException();
}
