/// <summary>
/// Gets a page of data from the mp_GeoCountry table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public async Task <DbDataReader> GetPage(
int pageNumber,
int pageSize)
{
//totalPages = 1;
//int totalRows
// = GetCount();
//if (pageSize > 0) totalPages = totalRows / pageSize;
//if (totalRows <= pageSize)
//{
// totalPages = 1;
//}
//else
//{
// int remainder;
// Math.DivRem(totalRows, pageSize, out remainder);
// if (remainder > 0)
// {
// totalPages += 1;
// }
//}
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_GeoCountry_SelectPage",
2);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return(await sph.ExecuteReaderAsync());
}
public async Task <DbDataReader> GetSiteList(CancellationToken cancellationToken)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Sites_SelectAll",
0);
return(await sph.ExecuteReaderAsync(cancellationToken));
}
public async Task <DbDataReader> GetSite(string hostName, CancellationToken cancellationToken)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Sites_SelectOneByHost",
1);
sph.DefineSqlParameter("@HostName", SqlDbType.NVarChar, 50, ParameterDirection.Input, hostName);
return(await sph.ExecuteReaderAsync(cancellationToken));
}
public async Task <DbDataReader> GetOne(string folderName)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_SiteFolders_SelectOneByFolder",
1);
sph.DefineSqlParameter("@FolderName", SqlDbType.NVarChar, 255, ParameterDirection.Input, folderName);
return(await sph.ExecuteReaderAsync());
}
public async Task <DbDataReader> GetBySite(Guid siteGuid)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_SiteFolders_SelectBySite",
1);
sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
return(await sph.ExecuteReaderAsync());
}
public async Task <DbDataReader> GetById(int roleId)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Roles_SelectOne",
1);
sph.DefineSqlParameter("@RoleID", SqlDbType.Int, ParameterDirection.Input, roleId);
return(await sph.ExecuteReaderAsync());
}
/// <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)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_GeoCountry_SelectOne",
1);
sph.DefineSqlParameter("@Guid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, guid);
return(await sph.ExecuteReaderAsync());
}
/// <summary>
/// Gets an IDataReader with one row from the mp_GeoCountry table.
/// </summary>
/// <param name="countryISOCode2"> countryISOCode2 </param>
public async Task <DbDataReader> GetByISOCode2(string countryISOCode2)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_GeoCountry_SelectByISOCode2",
1);
sph.DefineSqlParameter("@ISOCode2", SqlDbType.NChar, 2, ParameterDirection.Input, countryISOCode2);
return(await sph.ExecuteReaderAsync());
}
public async Task <DbDataReader> GetHost(int hostId)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_SiteHosts_SelectOne",
1);
sph.DefineSqlParameter("@HostID", SqlDbType.Int, ParameterDirection.Input, hostId);
return(await sph.ExecuteReaderAsync());
}
public async Task <DbDataReader> SchemaVersionGetAll(CancellationToken cancellationToken = default(CancellationToken))
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_SchemaVersion_SelectAll",
0
);
return(await sph.ExecuteReaderAsync(cancellationToken));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_GeoZone table.
/// </summary>
/// <param name="guid"> guid </param>
public async Task <DbDataReader> GetByCode(Guid countryGuid, string code)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_GeoZone_SelectByCode",
2);
sph.DefineSqlParameter("@CountryGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, countryGuid);
sph.DefineSqlParameter("@Code", SqlDbType.NVarChar, 255, ParameterDirection.Input, code);
return(await sph.ExecuteReaderAsync());
}
/// <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)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_UserLocation_SelectUsersByIP",
2);
sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
sph.DefineSqlParameter("@IPAddress", SqlDbType.NVarChar, 50, ParameterDirection.Input, ipv4Address);
return(await sph.ExecuteReaderAsync());
}
public async Task <DbDataReader> GetByName(int siteId, string roleName)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Roles_SelectOneByName",
2);
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
sph.DefineSqlParameter("@RoleName", SqlDbType.NVarChar, 50, ParameterDirection.Input, roleName);
return(await sph.ExecuteReaderAsync());
}
public async Task <DbDataReader> AutoComplete(string query, int maxRows)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_GeoCountry_AutoComplete",
2);
sph.DefineSqlParameter("@Query", SqlDbType.NVarChar, 255, ParameterDirection.Input, query);
sph.DefineSqlParameter("@RowsToGet", SqlDbType.NVarChar, 255, ParameterDirection.Input, maxRows);
return(await sph.ExecuteReaderAsync());
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_GeoZone table.
/// </summary>
public async Task <DbDataReader> GetByCountry(
Guid countryGuid,
CancellationToken cancellationToken)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_GeoZone_SelectByCountry",
1);
sph.DefineSqlParameter("@CountryGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, countryGuid);
return(await sph.ExecuteReaderAsync(cancellationToken));
}
public async Task <DbDataReader> GetHostList(
int siteId,
CancellationToken cancellationToken)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_SiteHosts_Select",
1);
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
return(await sph.ExecuteReaderAsync(cancellationToken));
}
public async Task <DbDataReader> GetUsersByClaim(int siteId, string claimType, string claimValue)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectAllByClaim",
3);
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
sph.DefineSqlParameter("@ClaimType", SqlDbType.NVarChar, -1, ParameterDirection.Input, claimType);
sph.DefineSqlParameter("@ClaimValue", SqlDbType.NVarChar, -1, ParameterDirection.Input, claimValue);
return(await sph.ExecuteReaderAsync());
}
public async Task <DbDataReader> GetByUser(
int siteId,
string userId)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_UserLogins_SelectByUser",
2);
sph.DefineSqlParameter("@UserId", SqlDbType.NVarChar, 128, ParameterDirection.Input, userId);
sph.DefineSqlParameter("@SiteId", SqlDbType.Int, ParameterDirection.Input, siteId);
return(await sph.ExecuteReaderAsync());
}
public async Task <DbDataReader> GetPageHosts(
int pageNumber,
int pageSize)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_SiteHosts_SelectPage",
2);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return(await sph.ExecuteReaderAsync());
}
/// <summary>
/// Gets a page of data from the mp_Language table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public async Task <DbDataReader> GetPage(
int pageNumber,
int pageSize,
CancellationToken cancellationToken)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Language_SelectPage",
2);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return(await sph.ExecuteReaderAsync(cancellationToken));
}
public async Task <DbDataReader> Find(
int siteId,
string loginProvider,
string providerKey)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_UserLogins_Find",
3);
sph.DefineSqlParameter("@LoginProvider", SqlDbType.NVarChar, 128, ParameterDirection.Input, loginProvider);
sph.DefineSqlParameter("@ProviderKey", SqlDbType.NVarChar, 128, ParameterDirection.Input, providerKey);
sph.DefineSqlParameter("@SiteId", SqlDbType.Int, ParameterDirection.Input, siteId);
return(await sph.ExecuteReaderAsync());
}
public async Task <DbDataReader> GetPageOfOtherSites(
int currentSiteId,
int pageNumber,
int pageSize)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Sites_SelectPageOtherSites",
3);
sph.DefineSqlParameter("@CurrentSiteID", SqlDbType.Int, ParameterDirection.Input, currentSiteId);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return(await sph.ExecuteReaderAsync());
}
public async Task <DbDataReader> AutoComplete(
Guid countryGuid,
string query,
int maxRows,
CancellationToken cancellationToken)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_GeoZone_AutoComplete",
3);
sph.DefineSqlParameter("@CountryGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, countryGuid);
sph.DefineSqlParameter("@Query", SqlDbType.NVarChar, 255, ParameterDirection.Input, query);
sph.DefineSqlParameter("@RowsToGet", SqlDbType.NVarChar, 255, ParameterDirection.Input, maxRows);
return(await sph.ExecuteReaderAsync(cancellationToken));
}
public async Task <DbDataReader> GetPage(
int siteId,
string searchInput,
int pageNumber,
int pageSize,
CancellationToken cancellationToken)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Roles_SelectPage",
4);
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
sph.DefineSqlParameter("@SearchInput", SqlDbType.NVarChar, 50, ParameterDirection.Input, searchInput);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return(await sph.ExecuteReaderAsync(cancellationToken));
}
public async Task <DbDataReader> GetUsersNotInRole(
int siteId,
int roleId,
string searchInput,
int pageNumber,
int pageSize,
CancellationToken cancellationToken)
{
//totalPages = 1;
//int totalRows = GetCountOfUsersNotInRole(siteId, roleId, searchInput);
//if (pageSize > 0) totalPages = totalRows / pageSize;
//if (totalRows <= pageSize)
//{
// totalPages = 1;
//}
//else
//{
// int remainder;
// Math.DivRem(totalRows, pageSize, out remainder);
// if (remainder > 0)
// {
// totalPages += 1;
// }
//}
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_UserRoles_SelectNotInRole",
5);
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
sph.DefineSqlParameter("@RoleID", SqlDbType.Int, ParameterDirection.Input, roleId);
sph.DefineSqlParameter("@SearchInput", SqlDbType.NVarChar, 50, ParameterDirection.Input, searchInput);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return(await sph.ExecuteReaderAsync(cancellationToken));
}
/// <summary>
/// Gets a page of data from the mp_SystemLog table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public async Task<DbDataReader> GetPageDescending(
int pageNumber,
int pageSize)
{
//totalPages = 1;
//int totalRows = GetCount();
//if (pageSize > 0) totalPages = totalRows / pageSize;
//if (totalRows <= pageSize)
//{
// totalPages = 1;
//}
//else
//{
// int remainder;
// Math.DivRem(totalRows, pageSize, out remainder);
// if (remainder > 0)
// {
// totalPages += 1;
// }
//}
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_SystemLog_SelectPageDescending",
2);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return await sph.ExecuteReaderAsync();
}
/// <summary>
/// Gets a page of data from the mp_GeoZone table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public async Task<DbDataReader> GetPage(
Guid countryGuid,
int pageNumber,
int pageSize)
{
//totalPages = 1;
//int totalRows
// = GetCount(countryGuid);
//if (pageSize > 0) totalPages = totalRows / pageSize;
//if (totalRows <= pageSize)
//{
// totalPages = 1;
//}
//else
//{
// int remainder;
// Math.DivRem(totalRows, pageSize, out remainder);
// if (remainder > 0)
// {
// totalPages += 1;
// }
//}
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_GeoZone_SelectPageByCountry",
3);
sph.DefineSqlParameter("@CountryGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, countryGuid);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return await sph.ExecuteReaderAsync();
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_GeoZone table.
/// </summary>
public async Task<DbDataReader> GetByCountry(Guid countryGuid)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_GeoZone_SelectByCountry",
1);
sph.DefineSqlParameter("@CountryGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, countryGuid);
return await sph.ExecuteReaderAsync();
}
///// <summary>
///// Gets an IDataReader with one row from the mp_UserLocation table.
///// </summary>
///// <param name="userGuid"> userGuid </param>
//public DbDataReader GetByUser(Guid userGuid)
//{
// SqlParameterHelper sph = new SqlParameterHelper(
// logFactory,
// readConnectionString,
// "mp_UserLocation_SelectByUser",
// 1);
// sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
// return sph.ExecuteReader();
//}
///// <summary>
///// Gets an IDataReader with one row from the mp_UserLocation table.
///// </summary>
///// <param name="siteGuid"> siteGuid </param>
//public DbDataReader GetBySite(Guid siteGuid)
//{
// SqlParameterHelper sph = new SqlParameterHelper(
// logFactory,
// readConnectionString,
// "mp_UserLocation_SelectBySite",
// 1);
// sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
// return sph.ExecuteReader();
//}
/// <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)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_UserLocation_SelectUsersByIP",
2);
sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
sph.DefineSqlParameter("@IPAddress", SqlDbType.NVarChar, 50, ParameterDirection.Input, ipv4Address);
return await sph.ExecuteReaderAsync(cancellationToken);
}
public async Task<DbDataReader> GetPageHosts(
int pageNumber,
int pageSize)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_SiteHosts_SelectPage",
2);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return await sph.ExecuteReaderAsync();
}
public async Task<DbDataReader> GetUserByRegistrationGuid(int siteId, Guid registerConfirmGuid)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectByRegisterGuid",
2);
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
sph.DefineSqlParameter("@RegisterConfirmGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, registerConfirmGuid);
return await sph.ExecuteReaderAsync();
}
public async Task<DbDataReader> GetHost(int hostId)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_SiteHosts_SelectOne",
1);
sph.DefineSqlParameter("@HostID", SqlDbType.Int, ParameterDirection.Input, hostId);
return await sph.ExecuteReaderAsync();
}
public async Task<DbDataReader> GetPageNotApprovedUsers(
int siteId,
int pageNumber,
int pageSize)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectNotApprovedPage",
3);
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return await sph.ExecuteReaderAsync();
}
public async Task<DbDataReader> GetUserListPage(
int siteId,
int pageNumber,
int pageSize,
string userNameBeginsWith,
int sortMode
)
{
//totalPages = 1;
//int totalRows = UserCount(siteId, userNameBeginsWith);
//if (pageSize > 0) totalPages = totalRows / pageSize;
//if (totalRows <= pageSize)
//{
// totalPages = 1;
//}
//else
//{
// int remainder;
// Math.DivRem(totalRows, pageSize, out remainder);
// if (remainder > 0)
// {
// totalPages += 1;
// }
//}
SqlParameterHelper sph;
switch (sortMode)
{
case 1:
sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectPageByDateDesc",
4);
break;
case 2:
sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectPageSortLF",
4);
break;
case 0:
default:
sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectPage",
4);
break;
}
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
sph.DefineSqlParameter("@UserNameBeginsWith", SqlDbType.NVarChar, 50, ParameterDirection.Input, userNameBeginsWith);
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
return await sph.ExecuteReaderAsync();
}
public async Task<DbDataReader> GetSingleUser(int userId)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectOne",
1);
sph.DefineSqlParameter("@UserID", SqlDbType.Int, ParameterDirection.Input, userId);
return await sph.ExecuteReaderAsync();
}
public async Task<DbDataReader> GetCrossSiteUserListByEmail(string email)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectAllByEmail",
1);
sph.DefineSqlParameter("@Email", SqlDbType.NVarChar, 100, ParameterDirection.Input, email);
return await sph.ExecuteReaderAsync();
}
///// <summary>
///// Returns true if the given userguid/ip is found
///// </summary>
///// <param name="userGuid"> userGuid </param>
//public static bool Exists(Guid userGuid, long ipAsLong)
//{
// SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "mp_UserLocation_Exists", 1);
// sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
// return Convert.ToInt32(sph.ExecuteScalar()) > 0;
//}
///// <summary>
///// Gets a count of rows in the mp_UserLocation table for the passed in userGuid.
///// </summary>
///// <param name="siteGuid"> siteGuid </param>
//public int GetCountBySite(Guid siteGuid)
//{
// SqlParameterHelper sph = new SqlParameterHelper(
// logFactory,
// readConnectionString,
// "mp_UserLocation_GetCountBySite",
// 1);
// sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
// return Convert.ToInt32(sph.ExecuteScalar());
//}
/// <summary>
/// Gets a page of data from the mp_UserLocation table.
/// </summary>
/// <param name="userGuid"> userGuid </param>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public async Task<DbDataReader> GetPageByUser(
Guid userGuid,
int pageNumber,
int pageSize,
CancellationToken cancellationToken
)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_UserLocation_SelectPageByUser",
3);
sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return await sph.ExecuteReaderAsync(cancellationToken);
}
public async Task<DbDataReader> GetUsersByClaim(int siteId, string claimType, string claimValue)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectAllByClaim",
3);
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
sph.DefineSqlParameter("@ClaimType", SqlDbType.NVarChar, -1, ParameterDirection.Input, claimType);
sph.DefineSqlParameter("@ClaimValue", SqlDbType.NVarChar, -1, ParameterDirection.Input, claimValue);
return await sph.ExecuteReaderAsync();
}
public async Task<DbDataReader> SchemaVersionGetAll(CancellationToken cancellationToken = default(CancellationToken))
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_SchemaVersion_SelectAll",
0
);
return await sph.ExecuteReaderAsync(cancellationToken);
}
public async Task<DbDataReader> GetSiteList()
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Sites_SelectAll",
0);
return await sph.ExecuteReaderAsync();
}
public async Task<DbDataReader> AutoComplete(Guid countryGuid, string query, int maxRows)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_GeoZone_AutoComplete",
3);
sph.DefineSqlParameter("@CountryGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, countryGuid);
sph.DefineSqlParameter("@Query", SqlDbType.NVarChar, 255, ParameterDirection.Input, query);
sph.DefineSqlParameter("@RowsToGet", SqlDbType.NVarChar, 255, ParameterDirection.Input, maxRows);
return await sph.ExecuteReaderAsync();
}
public async Task<DbDataReader> GetHost(string hostName)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_SiteHosts_SelectOneByHost",
1);
sph.DefineSqlParameter("@HostName", SqlDbType.NVarChar, 255, ParameterDirection.Input, hostName);
return await sph.ExecuteReaderAsync();
}
///// <summary>
///// Gets an IDataReader with one row from the mp_UserLocation table.
///// </summary>
///// <param name="rowID"> rowID </param>
//public DbDataReader GetOne(Guid rowID)
//{
// SqlParameterHelper sph = new SqlParameterHelper(
// logFactory,
// readConnectionString,
// "mp_UserLocation_SelectOne",
// 1);
// sph.DefineSqlParameter("@RowID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, rowID);
// return sph.ExecuteReader();
//}
/// <summary>
/// Gets an IDataReader with one row from the mp_UserLocation table.
/// </summary>
/// <param name="userGuid"> userGuid </param>
/// <param name="iPAddress"> iPAddress </param>
public async Task<DbDataReader> GetOne(
Guid userGuid,
long iPAddressLong,
CancellationToken cancellationToken
)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_UserLocation_SelectOneByUserAndIP",
2);
sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
sph.DefineSqlParameter("@IPAddressLong", SqlDbType.BigInt, ParameterDirection.Input, iPAddressLong);
return await sph.ExecuteReaderAsync(cancellationToken);
}
public async Task<DbDataReader> GetPageOfOtherSites(
int currentSiteId,
int pageNumber,
int pageSize)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Sites_SelectPageOtherSites",
3);
sph.DefineSqlParameter("@CurrentSiteID", SqlDbType.Int, ParameterDirection.Input, currentSiteId);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return await sph.ExecuteReaderAsync();
}
public async Task<DbDataReader> GetByUser(
int siteId,
string userId)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_UserLogins_SelectByUser",
2);
sph.DefineSqlParameter("@UserId", SqlDbType.NVarChar, 128, ParameterDirection.Input, userId);
sph.DefineSqlParameter("@SiteId", SqlDbType.Int, ParameterDirection.Input, siteId);
return await sph.ExecuteReaderAsync();
}
public async Task<DbDataReader> GetSingleUserByLoginName(int siteId, string loginName, bool allowEmailFallback)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectByLoginName",
3);
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
sph.DefineSqlParameter("@LoginName", SqlDbType.NVarChar, 50, ParameterDirection.Input, loginName);
sph.DefineSqlParameter("@AllowEmailFallback", SqlDbType.Bit, ParameterDirection.Input, allowEmailFallback);
return await sph.ExecuteReaderAsync();
}
/// <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)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_GeoCountry_SelectOne",
1);
sph.DefineSqlParameter("@Guid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, guid);
return await sph.ExecuteReaderAsync(cancellationToken);
}
public async Task<DbDataReader> GetSingleUser(Guid userGuid)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectByGuid",
1);
sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
return await sph.ExecuteReaderAsync();
}
/// <summary>
/// Gets an IDataReader with one row from the mp_GeoCountry table.
/// </summary>
/// <param name="countryISOCode2"> countryISOCode2 </param>
public async Task<DbDataReader> GetByISOCode2(
string countryISOCode2,
CancellationToken cancellationToken)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_GeoCountry_SelectByISOCode2",
1);
sph.DefineSqlParameter("@ISOCode2", SqlDbType.NChar, 2, ParameterDirection.Input, countryISOCode2);
return await sph.ExecuteReaderAsync(cancellationToken);
}
public async Task<DbDataReader> GetUserAdminSearchPage(
int siteId,
int pageNumber,
int pageSize,
string searchInput,
int sortMode)
{
SqlParameterHelper sph;
switch (sortMode)
{
case 1:
sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectAdminSearchPageByDateDesc",
4);
break;
case 2:
sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectAdminSearchPageByLF",
4);
break;
case 0:
default:
sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectAdminSearchPage",
4);
break;
}
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
sph.DefineSqlParameter("@SearchInput", SqlDbType.NVarChar, 50, ParameterDirection.Input, searchInput);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return await sph.ExecuteReaderAsync();
}
public async Task<DbDataReader> AutoComplete(
string query,
int maxRows,
CancellationToken cancellationToken)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_GeoCountry_AutoComplete",
2);
sph.DefineSqlParameter("@Query", SqlDbType.NVarChar, 255, ParameterDirection.Input, query);
sph.DefineSqlParameter("@RowsToGet", SqlDbType.NVarChar, 255, ParameterDirection.Input, maxRows);
return await sph.ExecuteReaderAsync(cancellationToken);
}
public async Task<DbDataReader> GetRolesByUser(int siteId, int userId)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_GetUserRoles",
2);
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
sph.DefineSqlParameter("@UserID", SqlDbType.Int, ParameterDirection.Input, userId);
return await sph.ExecuteReaderAsync();
}
/// <summary>
/// Gets a page of data from the mp_GeoCountry table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public async Task<DbDataReader> GetPage(
int pageNumber,
int pageSize,
CancellationToken cancellationToken)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_GeoCountry_SelectPage",
2);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return await sph.ExecuteReaderAsync(cancellationToken);
}
public async Task<DbDataReader> GetSingleUser(int siteId, string email)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_Users_SelectByEmail",
2);
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
sph.DefineSqlParameter("@Email", SqlDbType.NVarChar, 100, ParameterDirection.Input, email);
return await sph.ExecuteReaderAsync();
}
public async Task<DbDataReader> Find(
int siteId,
string loginProvider,
string providerKey)
{
SqlParameterHelper sph = new SqlParameterHelper(
logFactory,
readConnectionString,
"mp_UserLogins_Find",
3);
sph.DefineSqlParameter("@LoginProvider", SqlDbType.NVarChar, 128, ParameterDirection.Input, loginProvider);
sph.DefineSqlParameter("@ProviderKey", SqlDbType.NVarChar, 128, ParameterDirection.Input, providerKey);
sph.DefineSqlParameter("@SiteId", SqlDbType.Int, ParameterDirection.Input, siteId);
return await sph.ExecuteReaderAsync();
}