private static void BindAuthenticationQueryParams(AuthenticationInfoQuery query, IStatement statement) { if (!string.IsNullOrEmpty(query.AccessToken)) { statement.TryBind("@AccessToken", query.AccessToken); } if (!query.UserId.Equals(Guid.Empty)) { statement.TryBind("@UserId", query.UserId.ToString("N", CultureInfo.InvariantCulture)); } if (!string.IsNullOrEmpty(query.DeviceId)) { statement.TryBind("@DeviceId", query.DeviceId); } }
private void BindAuthenticationQueryParams(AuthenticationInfoQuery query, IStatement statement) { if (!string.IsNullOrWhiteSpace(query.AccessToken)) { statement.TryBind("@AccessToken", query.AccessToken); } if (!string.IsNullOrWhiteSpace(query.UserId)) { statement.TryBind("@UserId", query.UserId); } if (!string.IsNullOrWhiteSpace(query.DeviceId)) { statement.TryBind("@DeviceId", query.DeviceId); } if (query.IsActive.HasValue) { statement.TryBind("@IsActive", query.IsActive.Value); } }
private void BindAuthenticationQueryParams(AuthenticationInfoQuery query, IStatement statement) { if (!string.IsNullOrEmpty(query.AccessToken)) { statement.TryBind("@AccessToken", query.AccessToken); } if (!query.UserId.Equals(Guid.Empty)) { statement.TryBind("@UserId", query.UserId.ToString("N")); } if (!string.IsNullOrEmpty(query.DeviceId)) { statement.TryBind("@DeviceId", query.DeviceId); } if (query.IsActive.HasValue) { statement.TryBind("@IsActive", query.IsActive.Value); } }
public QueryResult <AuthenticationInfo> Get(AuthenticationInfoQuery query) { if (query == null) { throw new ArgumentNullException(nameof(query)); } var commandText = BaseSelectText; var whereClauses = new List <string>(); if (!string.IsNullOrEmpty(query.AccessToken)) { whereClauses.Add("AccessToken=@AccessToken"); } if (!string.IsNullOrEmpty(query.DeviceId)) { whereClauses.Add("DeviceId=@DeviceId"); } if (!query.UserId.Equals(Guid.Empty)) { whereClauses.Add("UserId=@UserId"); } if (query.HasUser.HasValue) { if (query.HasUser.Value) { whereClauses.Add("UserId not null"); } else { whereClauses.Add("UserId is null"); } } var whereTextWithoutPaging = whereClauses.Count == 0 ? string.Empty : " where " + string.Join(" AND ", whereClauses.ToArray()); commandText += whereTextWithoutPaging; commandText += " ORDER BY DateLastActivity desc"; if (query.Limit.HasValue || query.StartIndex.HasValue) { var offset = query.StartIndex ?? 0; if (query.Limit.HasValue || offset > 0) { commandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); } if (offset > 0) { commandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); } } var statementTexts = new[] { commandText, "select count (Id) from Tokens" + whereTextWithoutPaging }; var list = new List <AuthenticationInfo>(); var result = new QueryResult <AuthenticationInfo>(); using (var connection = GetConnection(true)) { connection.RunInTransaction( db => { var statements = PrepareAll(db, statementTexts) .ToList(); using (var statement = statements[0]) { BindAuthenticationQueryParams(query, statement); foreach (var row in statement.ExecuteQuery()) { list.Add(Get(row)); } using (var totalCountStatement = statements[1]) { BindAuthenticationQueryParams(query, totalCountStatement); result.TotalRecordCount = totalCountStatement.ExecuteQuery() .SelectScalarInt() .First(); } } }, ReadTransactionMode); } result.Items = list.ToArray(); return(result); }
public QueryResult <AuthenticationInfo> Get(AuthenticationInfoQuery query) { if (query == null) { throw new ArgumentNullException("query"); } var commandText = BaseSelectText; var whereClauses = new List <string>(); var startIndex = query.StartIndex ?? 0; if (!string.IsNullOrWhiteSpace(query.AccessToken)) { whereClauses.Add("AccessToken=@AccessToken"); } if (!string.IsNullOrWhiteSpace(query.UserId)) { whereClauses.Add("UserId=@UserId"); } if (!string.IsNullOrWhiteSpace(query.DeviceId)) { whereClauses.Add("DeviceId=@DeviceId"); } if (query.IsActive.HasValue) { whereClauses.Add("IsActive=@IsActive"); } if (query.HasUser.HasValue) { if (query.HasUser.Value) { whereClauses.Add("UserId not null"); } else { whereClauses.Add("UserId is null"); } } var whereTextWithoutPaging = whereClauses.Count == 0 ? string.Empty : " where " + string.Join(" AND ", whereClauses.ToArray(whereClauses.Count)); if (startIndex > 0) { var pagingWhereText = whereClauses.Count == 0 ? string.Empty : " where " + string.Join(" AND ", whereClauses.ToArray(whereClauses.Count)); whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM AccessTokens {0} ORDER BY DateCreated LIMIT {1})", pagingWhereText, startIndex.ToString(_usCulture))); } var whereText = whereClauses.Count == 0 ? string.Empty : " where " + string.Join(" AND ", whereClauses.ToArray(whereClauses.Count)); commandText += whereText; commandText += " ORDER BY DateCreated"; if (query.Limit.HasValue) { commandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); } var list = new List <AuthenticationInfo>(); using (WriteLock.Read()) { using (var connection = CreateConnection(true)) { return(connection.RunInTransaction(db => { var result = new QueryResult <AuthenticationInfo>(); var statementTexts = new List <string>(); statementTexts.Add(commandText); statementTexts.Add("select count (Id) from AccessTokens" + whereTextWithoutPaging); var statements = PrepareAllSafe(db, statementTexts) .ToList(); using (var statement = statements[0]) { BindAuthenticationQueryParams(query, statement); foreach (var row in statement.ExecuteQuery()) { list.Add(Get(row)); } using (var totalCountStatement = statements[1]) { BindAuthenticationQueryParams(query, totalCountStatement); result.TotalRecordCount = totalCountStatement.ExecuteQuery() .SelectScalarInt() .First(); } } result.Items = list.ToArray(list.Count); return result; }, ReadTransactionMode)); } } }
public QueryResult <AuthenticationInfo> Get(AuthenticationInfoQuery query) { if (query == null) { throw new ArgumentNullException("query"); } using (var cmd = _connection.CreateCommand()) { cmd.CommandText = BaseSelectText; var whereClauses = new List <string>(); var startIndex = query.StartIndex ?? 0; if (!string.IsNullOrWhiteSpace(query.AccessToken)) { whereClauses.Add("AccessToken=@AccessToken"); cmd.Parameters.Add(cmd, "@AccessToken", DbType.String).Value = query.AccessToken; } if (!string.IsNullOrWhiteSpace(query.UserId)) { whereClauses.Add("UserId=@UserId"); cmd.Parameters.Add(cmd, "@UserId", DbType.String).Value = query.UserId; } if (!string.IsNullOrWhiteSpace(query.DeviceId)) { whereClauses.Add("DeviceId=@DeviceId"); cmd.Parameters.Add(cmd, "@DeviceId", DbType.String).Value = query.DeviceId; } if (query.IsActive.HasValue) { whereClauses.Add("IsActive=@IsActive"); cmd.Parameters.Add(cmd, "@IsActive", DbType.Boolean).Value = query.IsActive.Value; } if (query.HasUser.HasValue) { if (query.HasUser.Value) { whereClauses.Add("UserId not null"); } else { whereClauses.Add("UserId is null"); } } var whereTextWithoutPaging = whereClauses.Count == 0 ? string.Empty : " where " + string.Join(" AND ", whereClauses.ToArray()); if (startIndex > 0) { var pagingWhereText = whereClauses.Count == 0 ? string.Empty : " where " + string.Join(" AND ", whereClauses.ToArray()); whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM AccessTokens {0} ORDER BY DateCreated LIMIT {1})", pagingWhereText, startIndex.ToString(_usCulture))); } var whereText = whereClauses.Count == 0 ? string.Empty : " where " + string.Join(" AND ", whereClauses.ToArray()); cmd.CommandText += whereText; cmd.CommandText += " ORDER BY DateCreated"; if (query.Limit.HasValue) { cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); } cmd.CommandText += "; select count (Id) from AccessTokens" + whereTextWithoutPaging; var list = new List <AuthenticationInfo>(); var count = 0; using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { while (reader.Read()) { list.Add(Get(reader)); } if (reader.NextResult() && reader.Read()) { count = reader.GetInt32(0); } } return(new QueryResult <AuthenticationInfo>() { Items = list.ToArray(), TotalRecordCount = count }); } }