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(";");
MySqlParameter[] arParams = new MySqlParameter[2];
arParams[0] = new MySqlParameter("?UserId", MySqlDbType.VarChar, 128);
arParams[0].Value = userId;
arParams[1] = new MySqlParameter("?SiteId", MySqlDbType.Int32);
arParams[1].Value = siteId;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
sqlCommand.ToString(),
arParams,
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(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Varchar, 128);
arParams[0].Value = userId;
arParams[1] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Value = siteId;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams,
cancellationToken));
}
public async Task <DbDataReader> GetByName(int siteId, string roleName)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("rolename", NpgsqlTypes.NpgsqlDbType.Text, 50);
arParams[1].Value = roleName;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_roles ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteid = :siteid ");
sqlCommand.Append("AND rolename = :rolename ");
sqlCommand.Append(";");
//return await AdoHelper.ExecuteReaderAsync(
// readConnectionString,
// CommandType.StoredProcedure,
// "mp_roles_selectbyname(:siteid,:rolename)",
// arParams);
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public async Task <DbDataReader> AutoComplete(string query, int maxRows)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_GeoCountry ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("(Name LIKE ?Query) ");
sqlCommand.Append("OR (ISOCode2 LIKE ?Query) ");
sqlCommand.Append(") ");
sqlCommand.Append("ORDER BY ISOCode2 ");
sqlCommand.Append("LIMIT " + maxRows.ToString());
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?Query", MySqlDbType.VarChar, 255);
arParams[0].Value = query + "%";
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
sqlCommand.ToString(),
arParams));
}
public async Task <DbDataReader> GetBySite(Guid siteGuid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Value = siteGuid.ToString();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_sitefolders ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteguid = :siteguid ");
sqlCommand.Append(";");
//return await AdoHelper.ExecuteReaderAsync(
// readConnectionString,
// CommandType.StoredProcedure,
// "mp_sitefolders_select_bysite(:siteguid)",
// arParams);
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public async Task <DbDataReader> AutoComplete(Guid countryGuid, string query, int maxRows)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("countryguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Value = countryGuid.ToString();
arParams[1] = new NpgsqlParameter("query", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[1].Value = query + "%";
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_geozone ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("countryguid = :countryguid ");
sqlCommand.Append("AND ( ");
sqlCommand.Append(" (name LIKE :query) ");
sqlCommand.Append(" OR (code LIKE :query) ");
sqlCommand.Append(") ");
sqlCommand.Append("ORDER BY code ");
sqlCommand.Append("LIMIT " + maxRows.ToString());
sqlCommand.Append(";");
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <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)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("countryguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Value = countryGuid.ToString();
arParams[1] = new NpgsqlParameter("code", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[1].Value = code;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_geozone ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("countryguid = :countryguid ");
sqlCommand.Append("AND code = :code ");
sqlCommand.Append(";");
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public async Task <DbDataReader> GetByUser(int siteId, string userId)
{
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,
sqlCommand.ToString(),
arParams));
}
public async Task <DbDataReader> AutoComplete(Guid countryGuid, string query, int maxRows)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT First " + maxRows.ToString());
sqlCommand.Append(" * ");
sqlCommand.Append("FROM mp_GeoZone ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("(CountryGuid = @CountryGuid ");
sqlCommand.Append("OR CountryGuid = UPPER(@CountryGuid)) ");
sqlCommand.Append("AND (");
sqlCommand.Append("(Name LIKE @Query) ");
sqlCommand.Append("OR (Code LIKE @Query) ");
sqlCommand.Append(") ");
sqlCommand.Append("ORDER BY Code ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@CountryGuid", FbDbType.Char, 36);
arParams[0].Value = countryGuid.ToString();
arParams[1] = new FbParameter("@Query", FbDbType.VarChar, 255);
arParams[1].Value = query + "%";
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
sqlCommand.ToString(),
arParams));
}
/// <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)
{
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(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Value = siteGuid.ToString();
arParams[1] = new NpgsqlParameter("ipaddress", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[1].Value = ipv4Address;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <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)
{
int pageLowerBound = (pageSize * pageNumber) - 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;
// }
//}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT gz.*, ");
sqlCommand.Append("gc.Name As CountryName ");
sqlCommand.Append("FROM mp_GeoZone gz ");
sqlCommand.Append("JOIN mp_GeoCountry gc ");
sqlCommand.Append("ON gz.CountryGuid = gc.Guid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("gz.CountryGuid = ?CountryGuid ");
sqlCommand.Append("ORDER BY gz.Name ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[3];
arParams[0] = new MySqlParameter("?CountryGuid", MySqlDbType.VarChar, 36);
arParams[0].Value = countryGuid.ToString();
arParams[1] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[1].Value = pageSize;
arParams[2] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[2].Value = pageLowerBound;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
sqlCommand.ToString(),
arParams));
}
public async Task <DbDataReader> Find(
int siteId,
string loginProvider,
string providerKey)
{
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(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("loginprovider", NpgsqlTypes.NpgsqlDbType.Varchar, 128);
arParams[0].Value = loginProvider;
arParams[1] = new NpgsqlParameter("providerkey", NpgsqlTypes.NpgsqlDbType.Varchar, 128);
arParams[1].Value = providerKey;
arParams[2] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Value = siteId;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public async Task <DbDataReader> GetAll()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("s.SiteID, ");
sqlCommand.Append("s.SiteGuid, ");
sqlCommand.Append("sf.Guid, ");
sqlCommand.Append("sf.FolderName ");
sqlCommand.Append("FROM mp_SiteFolders sf ");
sqlCommand.Append("JOIN mp_Sites s ");
sqlCommand.Append("ON sf.SiteGuid = s.SiteGuid ");
sqlCommand.Append("ORDER BY sf.FolderName ");
sqlCommand.Append(";");
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
sqlCommand.ToString(),
null));
}
/// <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)
{
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,
sqlCommand.ToString(),
arParams));
}
public async Task <DbDataReader> GetAll()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("s.siteid, ");
sqlCommand.Append("s.siteguid, ");
sqlCommand.Append("sf.guid, ");
sqlCommand.Append("sf.foldername ");
sqlCommand.Append("FROM mp_sitefolders sf ");
sqlCommand.Append("JOIN mp_sites s ");
sqlCommand.Append("ON sf.siteguid = s.siteguid ");
sqlCommand.Append("ORDER BY sf.foldername ");
sqlCommand.Append(";");
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
null));
}
public async Task <DbDataReader> GetById(int roleId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = roleId;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_roles ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("roleid = :roleid ");
sqlCommand.Append(";");
//return await AdoHelper.ExecuteReaderAsync(
// readConnectionString,
// CommandType.StoredProcedure,
// "mp_roles_selectone(:roleid)",
// arParams);
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public async Task <DbDataReader> Find(
int siteId,
string loginProvider,
string providerKey)
{
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(";");
MySqlParameter[] arParams = new MySqlParameter[3];
arParams[0] = new MySqlParameter("?LoginProvider", MySqlDbType.VarChar, 128);
arParams[0].Value = loginProvider;
arParams[1] = new MySqlParameter("?ProviderKey", MySqlDbType.VarChar, 128);
arParams[1].Value = providerKey;
arParams[2] = new MySqlParameter("?SiteId", MySqlDbType.Int32);
arParams[2].Value = siteId;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_GeoCountry table.
/// </summary>
public async Task <DbDataReader> GetAll()
{
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.StoredProcedure,
"mp_GeoCountry_SelectAll",
null));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_Currency table.
/// </summary>
public async Task <DbDataReader> GetAll(CancellationToken cancellationToken)
{
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.StoredProcedure,
"mp_Currency_SelectAll",
null,
cancellationToken));
}
/// <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)
{
int pageLowerBound = (pageSize * pageNumber) - 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;
// }
//}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT FIRST " + pageSize.ToString(CultureInfo.InvariantCulture) + " ");
if (pageNumber > 1)
{
sqlCommand.Append(" SKIP " + pageLowerBound.ToString(CultureInfo.InvariantCulture) + " ");
}
sqlCommand.Append(" gz.*, ");
sqlCommand.Append("gc.Name As CountryName ");
sqlCommand.Append("FROM mp_GeoZone gz ");
sqlCommand.Append("JOIN mp_GeoCountry gc ");
sqlCommand.Append("ON gz.CountryGuid = gc.Guid ");
sqlCommand.Append("OR gc.Guid = UPPER(@CountryGuid) ");
sqlCommand.Append("WHERE gz.CountryGuid = @CountryGuid ");
sqlCommand.Append("OR gz.CountryGuid = UPPER(@CountryGuid) ");
sqlCommand.Append("ORDER BY gz.Name ");
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,
sqlCommand.ToString(),
arParams));
}
/// <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)
{
int pageLowerBound = (pageSize * pageNumber) - 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;
// }
//}
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = pageSize;
arParams[1] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Value = pageLowerBound;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_systemlog ");
//sqlCommand.Append("WHERE ");
sqlCommand.Append("ORDER BY id DESC ");
//sqlCommand.Append(" ");
sqlCommand.Append("LIMIT :pagesize");
if (pageNumber > 1)
{
sqlCommand.Append(" OFFSET :pageoffset ");
}
sqlCommand.Append(";");
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public async Task <DbDataReader> GetPage(
int pageNumber,
int pageSize)
{
int pageLowerBound = (pageSize * pageNumber) - 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;
// }
//}
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("s.SiteID, ");
sqlCommand.Append("s.SiteGuid, ");
sqlCommand.Append("sf.Guid, ");
sqlCommand.Append("sf.FolderName ");
sqlCommand.Append("FROM mp_SiteFolders sf ");
sqlCommand.Append("JOIN mp_Sites s ");
sqlCommand.Append("ON sf.SiteGuid = s.SiteGuid ");
sqlCommand.Append("ORDER BY sf.FolderName ");
sqlCommand.Append(" ; ");
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
sqlCommand.ToString(),
null));
}
/// <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> GetPageAscending(
int pageNumber,
int pageSize)
{
int pageLowerBound = (pageSize * pageNumber) - 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;
// }
//}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SystemLog ");
//sqlCommand.Append("WHERE ");
sqlCommand.Append("ORDER BY ID ");
//sqlCommand.Append(" ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[2];
arParams[0] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[0].Value = pageSize;
arParams[1] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[1].Value = pageLowerBound;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
sqlCommand.ToString(),
arParams));
}
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 ; ");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?RoleID", MySqlDbType.Int32);
arParams[0].Value = roleId;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
sqlCommand.ToString(),
arParams,
cancellationToken));
}
public async Task <DbDataReader> GetBySite(Guid siteGuid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Value = siteGuid.ToString();
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.StoredProcedure,
"mp_sitefolders_select_bysite(:siteguid)",
arParams));
}
public async Task <DbDataReader> GetById(int roleId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = roleId;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.StoredProcedure,
"mp_roles_selectone(:roleid)",
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_Currency table.
/// </summary>
public async Task <DbDataReader> GetAll()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_Currency ");
sqlCommand.Append(";");
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
sqlCommand.ToString(),
null));
}
public async Task <DbDataReader> GetPageAscending(
int pageNumber,
int pageSize,
CancellationToken cancellationToken)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SystemLog ");
//sqlCommand.Append("WHERE ");
sqlCommand.Append("ORDER BY ID ");
//sqlCommand.Append(" ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[2];
arParams[0] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[0].Value = pageSize;
arParams[1] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[1].Value = pageLowerBound;
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;
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = pageSize;
arParams[1] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Value = pageLowerBound;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_systemlog ");
//sqlCommand.Append("WHERE ");
sqlCommand.Append("ORDER BY id ");
//sqlCommand.Append(" ");
sqlCommand.Append("LIMIT :pagesize");
if (pageNumber > 1)
{
sqlCommand.Append(" OFFSET :pageoffset ");
}
sqlCommand.Append(";");
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams,
cancellationToken));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_GeoZone 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_GeoZone ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Guid = ?Guid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Value = guid.ToString();
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
sqlCommand.ToString(),
arParams,
cancellationToken));
}
