/// <summary>
/// Gets an IDataReader with one row from the mp_GeoCountry table.
/// </summary>
/// <param name="guid"> guid </param>
public async Task <DbDataReader> GetOne(
Guid guid,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_GeoCountry ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Guid = @Guid ");
sqlCommand.Append("OR Guid = UPPER(@Guid) ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@Guid", FbDbType.Char, 36);
arParams[0].Value = guid.ToString();
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams,
cancellationToken));
}
public async Task <DbDataReader> GetPageAscending(
int pageNumber,
int pageSize,
CancellationToken cancellationToken)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT FIRST " + pageSize.ToString(CultureInfo.InvariantCulture) + " ");
if (pageNumber > 1)
{
sqlCommand.Append(" SKIP " + pageLowerBound.ToString(CultureInfo.InvariantCulture) + " ");
}
sqlCommand.Append(" * ");
sqlCommand.Append("FROM mp_SystemLog ");
//sqlCommand.Append("WHERE ");
sqlCommand.Append("ORDER BY ID ");
sqlCommand.Append(" ; ");
//FbParameter[] arParams = new FbParameter[1];
//arParams[0] = new FbParameter("@CountryGuid", FbDbType.Char, 36);
//arParams[0].Value = countryGuid.ToString();
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
null,
cancellationToken));
}
public async Task <DbDataReader> GetByUser(
int siteId,
string userId,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_UserClaims ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteId = @SiteId ");
sqlCommand.Append(" AND ");
sqlCommand.Append("UserId = @UserId ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@UserId", FbDbType.VarChar, 128);
arParams[0].Value = userId;
arParams[1] = new FbParameter("@SiteId", FbDbType.Integer);
arParams[1].Value = siteId;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams,
cancellationToken));
}
/// <summary>
/// Gets an IDataReader with rows from the mp_Users table which have the passed in IP Address
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
public async Task <DbDataReader> GetUsersByIPAddress(
Guid siteGuid,
string ipv4Address,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT u.* ");
sqlCommand.Append("FROM mp_UserLocation ul ");
sqlCommand.Append("JOIN mp_Users u ");
sqlCommand.Append("ON ul.UserGuid = u.UserGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("(u.SiteGuid = @SiteGuid OR @SiteGuid = '00000000-0000-0000-0000-000000000000') ");
sqlCommand.Append("AND ul.IPAddress = @IPAddress ");
sqlCommand.Append("ORDER BY ul.LastCaptureUTC DESC ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Value = siteGuid.ToString();
arParams[1] = new FbParameter("@IPAddress", FbDbType.VarChar, 50);
arParams[1].Value = ipv4Address;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams,
cancellationToken));
}
public async Task <DbDataReader> GetById(
int roleId,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_Roles ");
sqlCommand.Append("WHERE RoleID = @RoleID ; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@RoleID", FbDbType.Integer);
arParams[0].Value = roleId;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams,
cancellationToken));
}
public async Task <DbDataReader> Find(
int siteId,
string loginProvider,
string providerKey,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_UserLogins ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteId = @SiteId ");
sqlCommand.Append(" AND ");
sqlCommand.Append("LoginProvider = @LoginProvider ");
sqlCommand.Append(" AND ");
sqlCommand.Append("ProviderKey = @ProviderKey ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter("@LoginProvider", FbDbType.VarChar, 128);
arParams[0].Value = loginProvider;
arParams[1] = new FbParameter("@ProviderKey", FbDbType.VarChar, 128);
arParams[1].Value = providerKey;
arParams[2] = new FbParameter("@SiteId", FbDbType.Integer);
arParams[2].Value = siteId;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams,
cancellationToken));
}
public async Task <DbDataReader> SchemaVersionGetAll(CancellationToken cancellationToken = default(CancellationToken))
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SchemaVersion ");
sqlCommand.Append("ORDER BY ApplicationName ");
sqlCommand.Append(";");
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
null,
cancellationToken));
}
public async Task <DbDataReader> GetOne(
string folderName,
CancellationToken cancellationToken)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SiteFolders ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("FolderName = @FolderName ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@FolderName", FbDbType.VarChar, 255);
arParams[0].Value = folderName;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams,
cancellationToken));
}