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