private SqlAndDps DeviceDataFilterSql(GetDeviceDataListCmd cmd) { var paras = new DynamicParameters(); var sql = " 1=1 "; if (cmd.Type.HasValue && (int)cmd.Type.Value != -1) { sql += " AND sensor_type = @type "; paras.Add("type", cmd.Type.Value); } if (cmd.StartOn.HasValue) { sql += " AND update_on > @upOn "; paras.Add("upOn", cmd.StartOn.Value); } if (cmd.EndOn.HasValue) { sql += " AND update_on > @endOn "; paras.Add("endOn", cmd.EndOn.Value); } if (!cmd.DeviceId.IsNullOrWhiteSpace()) { sql += " AND device_id=@deviceId"; paras.Add("deviceId", cmd.DeviceId.Trim()); } return(new SqlAndDps { Sql = sql, Dps = paras }); }
public async Task <IEnumerable <DeviceData> > GetDeviceDataSAsync(GetDeviceDataListCmd cmd, PagedListQuery plQuery) { using (var conn = CreateConnection()) { conn.Open(); var sql = "SELECT * FROM device_data WHERE "; var demo = DeviceDataFilterSql(cmd); sql += demo.Sql; sql += " ORDER BY update_on DESC "; sql += plQuery.PostgresLimitPartialSql(); return(await conn.QueryAsync <DeviceData>(sql, demo.Dps).ConfigureAwait(false)); } }