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