public async Task <InvokeResult <List <DataStreamResult> > > ExecSQLAsync(string query, List <SQLParameter> filter)
        {
            try
            {
                var responseItems = new List <DataStreamResult>();
                using (var cn = OpenConnection(_stream.DbName))
                    using (var cmd = new NpgsqlCommand())
                    {
                        cmd.Connection  = cn;
                        cmd.CommandText = query.ToString();

                        foreach (var filterItem in filter)
                        {
                            cmd.Parameters.AddWithValue(filterItem.Name, filterItem.Value);
                        }

                        using (var rdr = await cmd.ExecuteReaderAsync())
                        {
                            while (rdr.Read())
                            {
                                var resultItem = new DataStreamResult();
                                for (var idx = 0; idx < rdr.FieldCount; ++idx)
                                {
                                    resultItem.Add(rdr.GetColumnSchema()[idx].ColumnName, rdr[idx]);
                                }

                                responseItems.Add(resultItem);
                            }
                        }

                        return(InvokeResult <List <DataStreamResult> > .Create(responseItems));
                    }
            }
            catch (Exception ex)
            {
                _logger.AddException("PostgresqlConnector_ExecSQLAsync", ex, query.ToKVP("query"));
                return(InvokeResult <List <DataStreamResult> > .FromException("PostgresqlConnector_ExecSQLAsync", ex));
            }
        }
Ejemplo n.º 2
0
        public DataStreamResult ToDataStreamResult(DataStream stream)
        {
            var result = new DataStreamResult();

            foreach (var item in Data)
            {
                result.Add(item.Key, item.Value);
            }

            switch (stream.DateStorageFormat.Value)
            {
            case DateStorageFormats.Epoch:
                long epoch = Convert.ToInt64(Data[stream.TimestampFieldName]);
                result.Timestamp = DateTimeOffset.FromUnixTimeSeconds(epoch).DateTime.ToJSONString();
                break;

            case DateStorageFormats.ISO8601:
                result.Timestamp = Data[stream.TimestampFieldName].ToString();
                break;
            }

            return(result);
        }
        public async Task <ListResponse <DataStreamResult> > GetTimeSeriesAnalyticsAsync(string query, Dictionary <string, object> filter, ListRequest request)
        {
            var sql = new StringBuilder(query);

            sql.AppendLine();
            sql.AppendLine($"  from  {_stream.DbSchema}.{_stream.DbTableName}");
            sql.AppendLine($"  where 1 = 1"); /* just used to establish a where clause we can use by appending "and x = y" */

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

            foreach (var filterItem in filter)
            {
                sql.AppendLine($"  and {filterItem.Key} = @parm{filterItem.Key}");
            }

            sql.AppendLine($"  group by period");
            sql.AppendLine($"  order by period desc");
            sql.AppendLine($"   LIMIT {request.PageSize} OFFSET {request.PageSize * Math.Max(request.PageIndex - 1, 0)} ");

            var responseItems = new List <DataStreamResult>();

            using (var cn = OpenConnection(_stream.DbName))
                using (var cmd = new NpgsqlCommand())
                {
                    cmd.Connection  = cn;
                    cmd.CommandText = sql.ToString();
                    Console.WriteLine(cmd.CommandText);

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

                    foreach (var filterItem in filter)
                    {
                        cmd.Parameters.AddWithValue($"@parm{filterItem.Key}", filterItem.Value);
                        _logger.AddCustomEvent(LogLevel.Message, "ProcessStreamAnalyticsAsync", $"{filterItem.Key} - {filterItem.Value}");
                    }

                    cmd.CommandType = System.Data.CommandType.Text;

                    using (var rdr = await cmd.ExecuteReaderAsync())
                    {
                        while (rdr.Read())
                        {
                            var resultItem = new DataStreamResult();
                            for (var idx = 0; idx < rdr.FieldCount; ++idx)
                            {
                                resultItem.Add($"col{idx}", rdr[idx]);
                            }

                            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 && request.PageSize > 0;
            if (response.HasMoreRecords)
            {
                response.NextRowKey = responseItems.Last().Timestamp;
            }

            return(response);
        }
        public async Task <ListResponse <DataStreamResult> > GetTimeSeriesAnalyticsAsync(TimeSeriesAnalyticsRequest request, ListRequest listRequest)
        {
            var fields     = new List <string>();
            var windowType = "";

            switch (request.Window)
            {
            case Windows.Seconds: windowType = "seconds"; break;

            case Windows.Minutes: windowType = "minutes"; break;

            case Windows.Hours: windowType = "hours"; break;

            case Windows.Days: windowType = "days"; break;

            case Windows.Months: windowType = "months"; break;

            case Windows.Years: windowType = "years"; break;
            }

            var sql = new StringBuilder($"select time_bucket('{request.WindowSize} {windowType}', {_stream.TimestampFieldName}) as period");

            fields.Add("period");
            sql.Append($", {_stream.DeviceIdFieldName}");
            fields.Add(_stream.DeviceIdFieldName);

            foreach (var method in request.Fields)
            {
                var operationType = "";
                switch (method.Operation)
                {
                case Operations.Average: operationType = "avg"; break;

                case Operations.Minimum: operationType = "min"; break;

                case Operations.Maximum: operationType = "max"; break;

                case Operations.Count: operationType = "count"; break;

                case Operations.Sum: operationType = "sum"; break;

                case Operations.Interpolate: operationType = "interpolate"; break;
                }

                if (String.IsNullOrEmpty(operationType))
                {
                    sql.Append($", {method.Name}");
                    fields.Add(method.Name);
                }
                else
                {
                    var fieldName = $"{operationType}_{method.Name}";
                    sql.Append($", {operationType}({method.Name}) as {fieldName}");
                    fields.Add(fieldName);
                }
            }

            sql.AppendLine();

            sql.AppendLine($"  from  {_stream.DbSchema}.{_stream.DbTableName}");
            sql.AppendLine($"  where 1 = 1"); /* just used to establish a where clause we can use by appending "and x = y" */

            foreach (var filterItem in request.Filter)
            {
                sql.AppendLine($"  and {filterItem.Key} = @parm{filterItem.Key}");
            }

            sql.AppendLine($"  group by period, {_stream.DeviceIdFieldName}");
            sql.AppendLine($"  order by period desc");
            sql.AppendLine($"   LIMIT {listRequest.PageSize} OFFSET {listRequest.PageSize * Math.Max(listRequest.PageIndex - 1, 0)} ");

            var responseItems = new List <DataStreamResult>();

            using (var cn = OpenConnection(_stream.DbName))
                using (var cmd = new NpgsqlCommand())
                {
                    cmd.Connection  = cn;
                    cmd.CommandText = sql.ToString();
                    Console.WriteLine(cmd.CommandText);

                    if (!String.IsNullOrEmpty(listRequest.NextRowKey))
                    {
                        cmd.Parameters.AddWithValue($"@lastDateStamp", listRequest.NextRowKey.ToDateTime());
                    }

                    if (!String.IsNullOrEmpty(listRequest.StartDate))
                    {
                        cmd.Parameters.AddWithValue($"@startDateStamp", listRequest.StartDate.ToDateTime());
                    }

                    if (!String.IsNullOrEmpty(listRequest.EndDate))
                    {
                        cmd.Parameters.AddWithValue($"@endDateStamp", listRequest.EndDate.ToDateTime());
                    }

                    foreach (var filterItem in request.Filter)
                    {
                        cmd.Parameters.AddWithValue($"@parm{filterItem.Key}", filterItem.Value);
                    }

                    cmd.CommandType = System.Data.CommandType.Text;

                    using (var rdr = await cmd.ExecuteReaderAsync())
                    {
                        while (rdr.Read())
                        {
                            var resultItem = new DataStreamResult();
                            for (var idx = 0; idx < rdr.FieldCount; ++idx)
                            {
                                resultItem.Add(fields[idx], rdr[idx]);
                            }

                            responseItems.Add(resultItem);
                        }
                    }
                }

            var response = new Core.Models.UIMetaData.ListResponse <DataStreamResult>();

            response.Model          = responseItems;
            response.PageSize       = responseItems.Count;
            response.PageIndex      = listRequest.PageIndex;
            response.HasMoreRecords = responseItems.Count == listRequest.PageSize && listRequest.PageSize > 0;
            if (response.HasMoreRecords)
            {
                response.NextRowKey = responseItems.Last().Timestamp;
            }

            return(response);
        }
        public async Task <ListResponse <DataStreamResult> > GetItemsAsync(Dictionary <string, object> filter, ListRequest request)
        {
            var sql = new StringBuilder("select ");

            if (request.PageSize == 0)
            {
                request.PageSize = 50;
            }

            sql.Append($"{_stream.TimestampFieldName}");
            sql.Append($", {_stream.DeviceIdFieldName}");

            foreach (var fld in _stream.Fields)
            {
                switch (fld.FieldType.Value)
                {
                case DeviceAdmin.Models.ParameterTypes.GeoLocation:
                    sql.Append($", ST_AsText({fld.FieldName}) out_{fld.FieldName}");
                    break;

                default:
                    sql.Append($", {fld.FieldName}");
                    break;
                }
            }

            sql.AppendLine();
            sql.AppendLine($"  from  {_stream.DbSchema}.{_stream.DbTableName}");
            sql.AppendLine($"  where 1 = 1"); /* just used to establish a where clause we can use by appending "and x = y" */

            _logger.AddCustomEvent(LogLevel.Message, "GetItemsAsync", sql.ToString());

            var responseItems = new List <DataStreamResult>();

            using (var cn = OpenConnection(_stream.DbName))
                using (var cmd = new NpgsqlCommand())
                {
                    Console.WriteLine(cmd.CommandText);

                    if (!String.IsNullOrEmpty(request.NextRowKey))
                    {
                        sql.AppendLine($"  and {_stream.TimestampFieldName} < @lastDateStamp");
                        cmd.Parameters.AddWithValue($"@lastDateStamp", request.NextRowKey.ToDateTime());
                    }

                    if (!String.IsNullOrEmpty(request.StartDate))
                    {
                        sql.AppendLine($"  and {_stream.TimestampFieldName} >= @startDateStamp");
                        cmd.Parameters.AddWithValue($"@startDateStamp", request.StartDate.ToDateTime());
                    }

                    if (!String.IsNullOrEmpty(request.EndDate))
                    {
                        sql.AppendLine($"  and {_stream.TimestampFieldName} <= @endDateStamp");
                        cmd.Parameters.AddWithValue($"@endDateStamp", request.EndDate.ToDateTime());
                    }

                    if (!String.IsNullOrEmpty(request.GroupBy))
                    {
                        sql.AppendLine($"  and period = @groupBy");
                        cmd.Parameters.AddWithValue($"@groupBy", request.GroupBy);
                    }

                    foreach (var filterItem in filter)
                    {
                        sql.AppendLine($"  and {filterItem.Key} = @parm{filterItem.Key}");
                        cmd.Parameters.AddWithValue($"@parm{filterItem.Key}", filterItem.Value);
                        _logger.AddCustomEvent(LogLevel.Message, "GetItemsAsync", $"{filterItem.Key} - {filterItem.Value}");
                    }

                    sql.AppendLine($"  order by {_stream.TimestampFieldName} desc");
                    sql.AppendLine($"   LIMIT {request.PageSize} OFFSET {request.PageSize * Math.Max(request.PageIndex - 1, 0)} ");

                    cmd.Connection  = cn;
                    cmd.CommandText = sql.ToString();
                    cmd.CommandType = System.Data.CommandType.Text;

                    using (var rdr = await cmd.ExecuteReaderAsync())
                    {
                        while (rdr.Read())
                        {
                            var resultItem = new DataStreamResult();
                            var timeStamp  = Convert.ToDateTime(rdr[_stream.TimestampFieldName]);
                            timeStamp            = DateTime.SpecifyKind(timeStamp, DateTimeKind.Utc);
                            resultItem.Timestamp = timeStamp.ToJSONString();

                            resultItem.Add(_stream.TimestampFieldName, resultItem.Timestamp);
                            resultItem.Add(_stream.DeviceIdFieldName, rdr[_stream.DeviceIdFieldName]);

                            foreach (var fld in _stream.Fields)
                            {
                                switch (fld.FieldType.Value)
                                {
                                case DeviceAdmin.Models.ParameterTypes.GeoLocation:
                                    var result = rdr[$"out_{fld.FieldName}"] as String;
                                    if (!String.IsNullOrEmpty(result))
                                    {
                                        var reg      = new Regex(@"^POINT\((?'lat'[\d\.\-]{2,14}) (?'lon'[\d\.\-]{2,14})\)$");
                                        var regMatch = reg.Match(result);
                                        if (regMatch.Success && regMatch.Groups.Count == 3)
                                        {
                                            var strLat = regMatch.Groups[1];
                                            var strLon = regMatch.Groups[2];
                                            if (double.TryParse(strLat.Value, out double lat) &&
                                                double.TryParse(strLat.Value, out double lon))
                                            {
                                                resultItem.Add(fld.FieldName, $"{lat:0.0000000},{lon:0.0000000}");
                                            }
                                        }
                                    }


                                    if (!resultItem.Keys.Contains(fld.FieldName))
                                    {
                                        resultItem.Add(fld.FieldName, null);
                                    }

                                    break;

                                case DeviceAdmin.Models.ParameterTypes.DateTime:
                                {
                                    var dtValue = rdr[fld.FieldName] as DateTime?;
                                    if (dtValue.HasValue)
                                    {
                                        resultItem.Add(fld.FieldName, dtValue.Value.ToJSONString());
                                    }
                                }

                                break;

                                default:
                                    resultItem.Add(fld.FieldName, rdr[fld.FieldName]);
                                    break;
                                }
                            }

                            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);
        }
Ejemplo n.º 6
0
        public async Task <LagoVista.Core.Models.UIMetaData.ListResponse <DataStreamResult> > GetItemsAsync(string deviceId, LagoVista.Core.Models.UIMetaData.ListRequest request)
        {
            request.PageIndex--;
            request.PageIndex = Math.Max(0, request.PageIndex);

            //TODO: Next chunk of code sux, likely much better way but will probably want to build a more robust filtering system at some point.
            ISearchResponse <Dictionary <string, object> > result = null;

            if (String.IsNullOrEmpty(request.StartDate) && String.IsNullOrEmpty(request.EndDate))
            {
                result = await _client.SearchAsync <Dictionary <string, object> >(src => src.From(0)
                                                                                  .Index(_stream.ElasticSearchIndexName)
                                                                                  //.Type(_stream.ElasticSearchTypeName)
                                                                                  .From(request.PageIndex *request.PageSize)
                                                                                  .Size(request.PageSize)
                                                                                  .Sort(srt => srt.Descending(new Field("sortOrder"))));
            }
            else if (String.IsNullOrEmpty(request.StartDate))
            {
                var endTicks = request.EndDate.ToDateTime().Ticks;
                result = await _client.SearchAsync <Dictionary <string, object> >(src => src.From(0)
                                                                                  .Index(_stream.ElasticSearchIndexName)
                                                                                  //.Type(_stream.ElasticSearchTypeName)
                                                                                  .Query(qry =>
                                                                                         qry.Range(rng => rng
                                                                                                   .Field("sortOrder")
                                                                                                   .LessThanOrEquals(endTicks)
                                                                                                   ))
                                                                                  .From(request.PageIndex *request.PageSize)
                                                                                  .Size(request.PageSize)
                                                                                  .Sort(srt => srt.Descending(new Field("sortOrder"))));
            }
            else if (String.IsNullOrEmpty(request.EndDate))
            {
                var startTicks = request.StartDate.ToDateTime().Ticks;

                result = await _client.SearchAsync <Dictionary <string, object> >(src => src.From(0)
                                                                                  .Index(_stream.ElasticSearchIndexName)
                                                                                  //.Type(_stream.ElasticSearchTypeName)
                                                                                  .Query(qry =>
                                                                                         qry.Range(rng => rng
                                                                                                   .Field("sortOrder")
                                                                                                   .GreaterThanOrEquals(startTicks)
                                                                                                   ))
                                                                                  .From(request.PageIndex *request.PageSize)
                                                                                  .Size(request.PageSize)
                                                                                  .Sort(srt => srt.Descending(new Field("sortOrder"))));
            }
            else
            {
                var startTicks = request.StartDate.ToDateTime().Ticks;
                var endTicks   = request.EndDate.ToDateTime().Ticks;

                result = await _client.SearchAsync <Dictionary <string, object> >(src => src.From(0)
                                                                                  .Index(_stream.ElasticSearchIndexName)
                                                                                  //.Type(_stream.ElasticSearchTypeName)
                                                                                  .Query(qry =>
                                                                                         qry.Range(rng => rng
                                                                                                   .Field("sortOrder")
                                                                                                   .GreaterThanOrEquals(startTicks)
                                                                                                   .LessThanOrEquals(endTicks)
                                                                                                   ))
                                                                                  .From(request.PageIndex *request.PageSize)
                                                                                  .Size(request.PageSize)
                                                                                  .Sort(srt => srt.Descending(new Field("sortOrder"))));
            }

            if (result.IsValid)
            {
                var records = new List <DataStreamResult>();
                foreach (var record in result.Documents)
                {
                    var streamResult = new DataStreamResult()
                    {
                        /* Newtonsoft assumes the value is date time for something that looks like a date, time this dual conversion gets our standard ISO8601 date string */
                        Timestamp = record[_stream.TimestampFieldName].ToString().ToDateTime().ToJSONString(),
                    };

                    foreach (var key in record.Keys)
                    {
                        streamResult.Add(key, record[key]);
                    }

                    records.Add(streamResult);
                }

                var response = Core.Models.UIMetaData.ListResponse <DataStreamResult> .Create(records);

                response.PageIndex      = request.PageIndex;
                response.PageSize       = records.Count;
                response.HasMoreRecords = response.PageSize == request.PageSize;

                return(response);
            }
            else
            {
                if (result.OriginalException != null)
                {
                    return(ListResponse <DataStreamResult> .FromError(result.OriginalException.Message));
                }
                else
                {
                    return(ListResponse <DataStreamResult> .FromError(result.DebugInformation));
                }
            }
        }
Ejemplo n.º 7
0
        public async Task <ListResponse <DataStreamResult> > GetItemsAsync(string deviceId, LagoVista.Core.Models.UIMetaData.ListRequest request)
        {
            var filter = TableQuery.GenerateFilterCondition(nameof(Models.DataStreamTSEntity.PartitionKey), QueryComparisons.Equal, deviceId);

            var dateFilter = String.Empty;

            /* FYI - less than and greater than are reversed because the data is inserted wiht row keys in descending order */
            if (!String.IsNullOrEmpty(request.StartDate) && !String.IsNullOrEmpty(request.EndDate))
            {
                var startRowKey = request.StartDate.ToDateTime().ToInverseTicksRowKey();
                var endRowKey   = request.EndDate.ToDateTime().ToInverseTicksRowKey();

                dateFilter = TableQuery.CombineFilters(
                    TableQuery.GenerateFilterCondition(nameof(Models.DataStreamTSEntity.RowKey), QueryComparisons.LessThanOrEqual, startRowKey.ToString()),
                    TableOperators.And,
                    TableQuery.GenerateFilterCondition(nameof(Models.DataStreamTSEntity.RowKey), QueryComparisons.GreaterThanOrEqual, endRowKey.ToString())
                    );
            }
            else if (String.IsNullOrEmpty(request.StartDate) && !String.IsNullOrEmpty(request.EndDate))
            {
                var endRowKey = request.EndDate.ToDateTime().ToInverseTicksRowKey();
                dateFilter = TableQuery.GenerateFilterCondition(nameof(Models.DataStreamTSEntity.RowKey), QueryComparisons.GreaterThanOrEqual, endRowKey.ToString());
            }
            else if (String.IsNullOrEmpty(request.EndDate) && !String.IsNullOrEmpty(request.StartDate))
            {
                var startRowKey = request.StartDate.ToDateTime().ToInverseTicksRowKey();
                dateFilter = TableQuery.GenerateFilterCondition(nameof(Models.DataStreamTSEntity.RowKey), QueryComparisons.LessThanOrEqual, startRowKey.ToString());
            }

            if (!String.IsNullOrEmpty(dateFilter))
            {
                filter = TableQuery.CombineFilters(filter, TableOperators.And, dateFilter);
            }

            var query = new TableQuery <DynamicTableEntity>().Where(filter).Take(request.PageSize);

            var numberRetries = 5;
            var retryCount    = 0;
            var completed     = false;

            while (retryCount++ < numberRetries && !completed)
            {
                try
                {
                    TableQuerySegment <DynamicTableEntity> results;
                    if (!String.IsNullOrEmpty(request.NextPartitionKey) && !String.IsNullOrEmpty(request.NextRowKey))
                    {
                        var token = new TableContinuationToken()
                        {
                            NextPartitionKey = request.NextPartitionKey,
                            NextRowKey       = request.NextRowKey
                        };

                        results = await _cloudTable.ExecuteQuerySegmentedAsync <DynamicTableEntity>(query, token);
                    }
                    else
                    {
                        results = await _cloudTable.ExecuteQuerySegmentedAsync <DynamicTableEntity>(query, new TableContinuationToken());
                    }

                    var listResponse = new ListResponse <DataStreamResult>
                    {
                        NextRowKey       = results.ContinuationToken?.NextRowKey,
                        NextPartitionKey = results.ContinuationToken?.NextPartitionKey,
                        PageSize         = results.Count(),
                        HasMoreRecords   = results.ContinuationToken != null,
                    };

                    var resultSet = new List <DataStreamResult>();

                    foreach (var item in results)
                    {
                        var result = new DataStreamResult();
                        foreach (var property in item.Properties)
                        {
                            result.Add(property.Key, property.Value.PropertyAsObject);
                        }

                        switch (_stream.DateStorageFormat.Value)
                        {
                        case DateStorageFormats.Epoch:
                            long epoch = Convert.ToInt64(item.Properties[_stream.TimestampFieldName]);
                            result.Timestamp = DateTimeOffset.FromUnixTimeSeconds(epoch).DateTime.ToJSONString();
                            break;

                        case DateStorageFormats.ISO8601:
                            result.Timestamp = item.Properties[_stream.TimestampFieldName].StringValue.ToDateTime().ToJSONString();
                            break;
                        }

                        resultSet.Add(result);
                    }


                    listResponse.Model = resultSet;

                    return(listResponse);
                }
                catch (Exception ex)
                {
                    if (retryCount == numberRetries)
                    {
                        _logger.AddException("AzureTableStorageConnector_GetItemsAsync", ex);
                        return(ListResponse <DataStreamResult> .FromError(ex.Message));
                    }
                    else
                    {
                        _logger.AddCustomEvent(LagoVista.Core.PlatformSupport.LogLevel.Warning, "AzureTableStorageConnector_GetItemsAsync", "", ex.Message.ToKVP("exceptionMessage"), ex.GetType().Name.ToKVP("exceptionType"), retryCount.ToString().ToKVP("retryCount"));
                    }
                    await Task.Delay(retryCount * 250);
                }
                finally
                {
                }
            }

            _logger.AddException("AzureTableStorageConnector_GetItemsAsync", new Exception("Unexpected end of method"));
            return(ListResponse <DataStreamResult> .FromError("Unexpected end of method."));
        }
        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", Math.Max(request.PageIndex - 1, 0));

                    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)
                            {
                                try
                                {
                                    resultItem.Add(fld.FieldName, rdr[fld.FieldName]);
                                }
                                catch (Exception)
                                {
                                    Debugger.Break();
                                }
                            }

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