public bool TableExists(string tableName) { try { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM " + tableName + " "); using (DbDataReader reader = AdoHelper.ExecuteReader( readConnectionString, CommandType.Text, sqlCommand.ToString(), null)) { if (reader.Read()) { return(true); } // need to return true here if no error // the table may be empty but it does exist if no error return(true); } } catch { } return(false); }
public 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(";"); SqliteParameter[] arParams = new SqliteParameter[3]; arParams[0] = new SqliteParameter(":LoginProvider", DbType.String); arParams[0].Value = loginProvider; arParams[1] = new SqliteParameter(":ProviderKey", DbType.String); arParams[1].Value = providerKey; arParams[2] = new SqliteParameter(":SiteId", DbType.Int32); arParams[2].Value = siteId; return(AdoHelper.ExecuteReader( connectionString, sqlCommand.ToString(), arParams)); }
public DbDataReader GetSiteSettingsExList(int siteId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT e.* "); sqlCommand.Append("FROM mp_sitesettingsex e "); sqlCommand.Append("JOIN "); sqlCommand.Append("mp_sitesettingsexdef d "); sqlCommand.Append("ON "); sqlCommand.Append("e.keyname = d.keyname "); sqlCommand.Append("AND e.groupname = d.groupname "); sqlCommand.Append("WHERE "); sqlCommand.Append("e.siteid = :siteid "); sqlCommand.Append("ORDER BY d.groupname, d.sortorder "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Value = siteId; return(AdoHelper.ExecuteReader( readConnectionString, CommandType.Text, sqlCommand.ToString(), arParams)); }
public DbDataReader GetSiteSettingsExList(int siteId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT e.* "); sqlCommand.Append("FROM mp_SiteSettingsEx e "); sqlCommand.Append("JOIN "); sqlCommand.Append("mp_SiteSettingsExDef d "); sqlCommand.Append("ON "); sqlCommand.Append("e.KeyName = d.KeyName "); sqlCommand.Append("AND e.GroupName = d.GroupName "); sqlCommand.Append("WHERE "); sqlCommand.Append("e.SiteID = ?SiteID "); sqlCommand.Append("ORDER BY d.GroupName, d.SortOrder "); sqlCommand.Append(";"); MySqlParameter[] arParams = new MySqlParameter[1]; arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32); arParams[0].Value = siteId; return(AdoHelper.ExecuteReader( readConnectionString, sqlCommand.ToString(), arParams)); }
public 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(";"); SqliteParameter[] arParams = new SqliteParameter[2]; arParams[0] = new SqliteParameter(":UserId", DbType.String); arParams[0].Value = userId; arParams[1] = new SqliteParameter(":SiteId", DbType.Int32); arParams[1].Value = siteId; return(AdoHelper.ExecuteReader( connectionString, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with one row from the mp_UserLocation table. /// </summary> /// <param name="rowID"> rowID </param> public DbDataReader GetOne(Guid rowID) { NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("rowid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Value = rowID.ToString(); StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_userlocation "); sqlCommand.Append("WHERE "); sqlCommand.Append("rowid = :rowid "); sqlCommand.Append(";"); //return AdoHelper.ExecuteReader( // readConnectionString, // CommandType.StoredProcedure, // "mp_userlocation_select_one(:rowid)", // arParams); return(AdoHelper.ExecuteReader( readConnectionString, CommandType.Text, 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 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(";"); SqliteParameter[] arParams = new SqliteParameter[2]; arParams[0] = new SqliteParameter(":PageSize", DbType.Int32); arParams[0].Value = pageSize; arParams[1] = new SqliteParameter(":OffsetRows", DbType.Int32); arParams[1].Value = pageLowerBound; return(AdoHelper.ExecuteReader( connectionString, sqlCommand.ToString(), arParams)); }
//disable warning about not really being async // we know it is not, and for Sqlite there is probably no benefit to making it really async #pragma warning disable 1998 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(AdoHelper.ExecuteReader( connectionString, sqlCommand.ToString(), null)); }
public DbDataReader GetRolesUserIsNotIn( int siteId, int userId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT r.* "); sqlCommand.Append("FROM mp_Roles r "); sqlCommand.Append("LEFT OUTER JOIN mp_UserRoles ur "); sqlCommand.Append("ON r.RoleID = ur.RoleID "); sqlCommand.Append("AND ur.UserID = ?UserID "); sqlCommand.Append("WHERE r.SiteID = ?SiteID "); sqlCommand.Append("AND ur.UserID IS NULL "); sqlCommand.Append("ORDER BY r.DisplayName ;"); MySqlParameter[] arParams = new MySqlParameter[2]; arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32); arParams[0].Value = siteId; arParams[1] = new MySqlParameter("?UserID", MySqlDbType.Int32); arParams[1].Value = userId; return(AdoHelper.ExecuteReader( readConnectionString, sqlCommand.ToString(), arParams)); }
public DbDataReader GetRoleMembers(int roleId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT "); sqlCommand.Append("mp_UserRoles.UserID, "); sqlCommand.Append("mp_Users.Name, "); sqlCommand.Append("mp_Users.LoginName, "); sqlCommand.Append("mp_Users.Email "); sqlCommand.Append("FROM mp_UserRoles "); sqlCommand.Append("INNER JOIN mp_Users "); sqlCommand.Append("ON mp_Users.UserID = mp_UserRoles.UserID "); sqlCommand.Append("WHERE mp_UserRoles.RoleID = ?RoleID ; "); MySqlParameter[] arParams = new MySqlParameter[1]; arParams[0] = new MySqlParameter("?RoleID", MySqlDbType.Int32); arParams[0].Value = roleId; return(AdoHelper.ExecuteReader( readConnectionString, sqlCommand.ToString(), arParams)); }
public DbDataReader AutoComplete(string query, int maxRows) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT TOP(" + maxRows.ToString(CultureInfo.InvariantCulture) + ") "); sqlCommand.Append(" * "); 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(";"); SqlCeParameter[] arParams = new SqlCeParameter[1]; arParams[0] = new SqlCeParameter("@Query", SqlDbType.NVarChar, 255); arParams[0].Value = query; return(AdoHelper.ExecuteReader( connectionString, CommandType.Text, sqlCommand.ToString(), arParams)); }
public DbDataReader GetRoleMembers(int roleId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT "); sqlCommand.Append("mp_userroles.userid, "); sqlCommand.Append("mp_users.name, "); sqlCommand.Append("mp_users.loginname, "); sqlCommand.Append("mp_users.email "); sqlCommand.Append("FROM mp_userroles "); sqlCommand.Append("INNER JOIN mp_users "); sqlCommand.Append("ON mp_users.userid = mp_userroles.userid "); sqlCommand.Append("WHERE mp_userroles.roleid = :roleid ; "); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Value = roleId; return(AdoHelper.ExecuteReader( 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 DbDataReader GetByCode(Guid countryGuid, string code) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT TOP(1) * "); sqlCommand.Append("FROM mp_GeoZone "); sqlCommand.Append("WHERE "); sqlCommand.Append("CountryGuid = @CountryGuid "); sqlCommand.Append("AND "); sqlCommand.Append("[Code] = @Code "); sqlCommand.Append(";"); SqlCeParameter[] arParams = new SqlCeParameter[2]; arParams[0] = new SqlCeParameter("@CountryGuid", SqlDbType.UniqueIdentifier); arParams[0].Value = countryGuid; arParams[1] = new SqlCeParameter("@Code", SqlDbType.NVarChar, 255); arParams[1].Value = code; return(AdoHelper.ExecuteReader( connectionString, CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with one row from the mp_GeoZone table. /// </summary> /// <param name="guid"> guid </param> public DbDataReader GetByCode(Guid countryGuid, string code) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_GeoZone "); sqlCommand.Append("WHERE "); sqlCommand.Append("(CountryGuid = @CountryGuid "); sqlCommand.Append("OR CountryGuid = UPPER(@CountryGuid)) "); sqlCommand.Append("AND Code = @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("@Code", FbDbType.VarChar, 255); arParams[1].Value = code; return(AdoHelper.ExecuteReader( readConnectionString, sqlCommand.ToString(), arParams)); }
public DbDataReader GetRoleMembers(int roleId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT "); sqlCommand.Append("ur.UserID, "); sqlCommand.Append("u.[Name], "); sqlCommand.Append("u.Email, "); sqlCommand.Append("u.LoginName "); sqlCommand.Append("FROM mp_UserRoles ur "); sqlCommand.Append("JOIN mp_Users u "); sqlCommand.Append("ON u.UserID = ur.UserID "); sqlCommand.Append("WHERE "); sqlCommand.Append("ur.RoleID = @RoleID "); sqlCommand.Append("ORDER BY u.[Name] "); sqlCommand.Append(";"); SqlCeParameter[] arParams = new SqlCeParameter[1]; arParams[0] = new SqlCeParameter("@RoleID", SqlDbType.Int); arParams[0].Value = roleId; return(AdoHelper.ExecuteReader( connectionString, CommandType.Text, sqlCommand.ToString(), arParams)); }
public List <T> Find <T>(string strSql) where T : new() { List <T> list = new List <T>(); IDataReader sdr = null; IDbConnection connection = null; try { connection = GetConnection(); bool closeConnection = GetWillConnectionState(); strSql = strSql.ToUpper(); String columns = SQLBuilderHelper.fetchColumns(strSql); T entity = new T(); PropertyInfo[] properties = ReflectionHelper.GetProperties(entity.GetType()); TableInfo tableInfo = EntityHelper.GetTableInfo(entity, DbOperateType.SELECT, properties); sdr = AdoHelper.ExecuteReader(closeConnection, connection, CommandType.Text, strSql, null); list = EntityHelper.toList <T>(sdr, tableInfo, properties); } catch (Exception ex) { throw ex; } finally { if (sdr != null) { sdr.Close(); } } return(list); }
/// <summary> /// Gets a page of data from the mp_UserLocation table. /// </summary> /// <param name="siteGuid"> siteGuid </param> /// <param name="pageNumber">The page number.</param> /// <param name="pageSize">Size of the page.</param> /// <param name="totalPages">total pages</param> public IDataReader GetPageBySite( Guid siteGuid, int pageNumber, int pageSize) { int pageLowerBound = (pageSize * pageNumber) - pageSize; //totalPages = 1; //int totalRows = GetCountBySite(siteGuid); //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 * FROM "); sqlCommand.Append("("); sqlCommand.Append("SELECT TOP (" + pageSize.ToString(CultureInfo.InvariantCulture) + ") * FROM "); sqlCommand.Append("("); sqlCommand.Append("SELECT TOP (" + pageNumber.ToString(CultureInfo.InvariantCulture) + " * " + pageSize.ToString(CultureInfo.InvariantCulture) + ") * "); sqlCommand.Append("FROM mp_UserLocation "); sqlCommand.Append("WHERE "); sqlCommand.Append("SiteGuid = @SiteGuid "); sqlCommand.Append("ORDER BY "); sqlCommand.Append("[IPAddressLong] "); sqlCommand.Append(") AS t1 "); //sqlCommand.Append("ORDER BY "); sqlCommand.Append(") AS t2 "); //sqlCommand.Append("WHERE "); //sqlCommand.Append("ORDER BY "); sqlCommand.Append(";"); SqlCeParameter[] arParams = new SqlCeParameter[1]; arParams[0] = new SqlCeParameter("@SiteGuid", SqlDbType.UniqueIdentifier); arParams[0].Value = siteGuid; return(AdoHelper.ExecuteReader( connectionString, CommandType.Text, 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 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 "); sqlCommand.Append("ul.IPAddress = @IPAddress "); sqlCommand.Append("ORDER BY "); sqlCommand.Append("ul.[LastCaptureUTC] DESC "); sqlCommand.Append(";"); SqlCeParameter[] arParams = new SqlCeParameter[2]; arParams[0] = new SqlCeParameter("@SiteGuid", SqlDbType.UniqueIdentifier); arParams[0].Value = siteGuid; arParams[1] = new SqlCeParameter("@IPAddress", SqlDbType.NVarChar, 50); arParams[1].Value = ipv4Address; return(AdoHelper.ExecuteReader( connectionString, CommandType.Text, sqlCommand.ToString(), arParams)); }
public DbDataReader GetByName(int siteId, string 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(";"); SqlCeParameter[] arParams = new SqlCeParameter[2]; arParams[0] = new SqlCeParameter("@SiteID", SqlDbType.Int); arParams[0].Value = siteId; arParams[1] = new SqlCeParameter("@RoleName", SqlDbType.NVarChar, 50); arParams[1].Value = roleName; return(AdoHelper.ExecuteReader( connectionString, CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with one row from the mp_RedirectList table. /// </summary> /// <param name="rowGuid"> rowGuid </param> public DbDataReader GetBySiteAndUrl(int siteId, string oldUrl) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_redirectlist "); sqlCommand.Append("WHERE "); sqlCommand.Append("siteid = :siteid "); sqlCommand.Append("AND oldUrl = :oldurl "); sqlCommand.Append("AND expireutc < :currenttime "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[3]; arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Value = siteId; arParams[1] = new NpgsqlParameter("oldurl", NpgsqlTypes.NpgsqlDbType.Varchar, 255); arParams[1].Value = oldUrl; arParams[2] = new NpgsqlParameter("currenttime", NpgsqlTypes.NpgsqlDbType.Timestamp); arParams[2].Value = DateTime.UtcNow; return(AdoHelper.ExecuteReader( readConnectionString, CommandType.Text, sqlCommand.ToString(), arParams)); }
public DbDataReader GetRolesUserIsNotIn( int siteId, int userId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT r.* "); sqlCommand.Append("FROM mp_Roles r "); sqlCommand.Append("LEFT OUTER JOIN mp_UserRoles ur "); sqlCommand.Append("ON r.RoleID = ur.RoleID "); sqlCommand.Append("AND ur.UserID = @UserID "); sqlCommand.Append("WHERE "); sqlCommand.Append("r.SiteID = @SiteID "); sqlCommand.Append("AND ur.UserID IS NULL "); sqlCommand.Append("ORDER BY r.[DisplayName] "); sqlCommand.Append(";"); SqlCeParameter[] arParams = new SqlCeParameter[2]; arParams[0] = new SqlCeParameter("@SiteID", SqlDbType.Int); arParams[0].Value = siteId; arParams[1] = new SqlCeParameter("@UserID", SqlDbType.Int); arParams[1].Value = userId; return(AdoHelper.ExecuteReader( connectionString, CommandType.Text, 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 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(";"); SqliteParameter[] arParams = new SqliteParameter[2]; arParams[0] = new SqliteParameter(":SiteGuid", DbType.String); arParams[0].Value = siteGuid.ToString(); arParams[1] = new SqliteParameter(":IPAddress", DbType.String); arParams[1].Value = ipv4Address; return(AdoHelper.ExecuteReader( connectionString, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with one row from the mp_RedirectList table. /// </summary> /// <param name="rowGuid"> rowGuid </param> public DbDataReader GetBySiteAndUrl(int siteId, string oldUrl) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_RedirectList "); sqlCommand.Append("WHERE "); sqlCommand.Append("SiteID = :SiteID "); sqlCommand.Append("AND OldUrl = :OldUrl "); sqlCommand.Append("AND ExpireUtc < :CurrentTime "); sqlCommand.Append(";"); SQLiteParameter[] arParams = new SQLiteParameter[3]; arParams[0] = new SQLiteParameter(":SiteID", DbType.Int32); arParams[0].Value = siteId; arParams[1] = new SQLiteParameter(":OldUrl", DbType.String); arParams[1].Value = oldUrl; arParams[2] = new SQLiteParameter(":CurrentTime", DbType.DateTime); arParams[2].Value = DateTime.UtcNow; return(AdoHelper.ExecuteReader( connectionString, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets a page of data from the mp_BannedIPAddresses table. /// </summary> /// <param name="pageNumber">The page number.</param> /// <param name="pageSize">Size of the page.</param> /// <param name="totalPages">total pages</param> public 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 * FROM "); sqlCommand.Append("("); sqlCommand.Append("SELECT TOP (" + pageSize.ToString(CultureInfo.InvariantCulture) + ") * FROM "); sqlCommand.Append("("); sqlCommand.Append("SELECT TOP (" + pageNumber.ToString(CultureInfo.InvariantCulture) + " * " + pageSize.ToString(CultureInfo.InvariantCulture) + ") * "); sqlCommand.Append("FROM mp_BannedIPAddresses "); sqlCommand.Append("ORDER BY "); sqlCommand.Append("BannedIP "); //sqlCommand.Append("DESC "); sqlCommand.Append(") AS t1 "); sqlCommand.Append("ORDER BY "); sqlCommand.Append("t1.BannedIP "); sqlCommand.Append("DESC "); sqlCommand.Append(") AS t2 "); //sqlCommand.Append("WHERE "); sqlCommand.Append("ORDER BY "); sqlCommand.Append("t2.BannedIP "); sqlCommand.Append(";"); //SqlCeParameter[] arParams = new SqlCeParameter[1]; //arParams[0] = new SqlCeParameter("@ApplicationID", SqlDbType.UniqueIdentifier); //arParams[0].Value = applicationId; return(AdoHelper.ExecuteReader( connectionString, CommandType.Text, sqlCommand.ToString(), null)); }
/// <summary> /// Gets an IDataReader with one row from the mp_RedirectList table. /// </summary> /// <param name="rowGuid"> rowGuid </param> public DbDataReader GetBySiteAndUrl(int siteId, string oldUrl) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_RedirectList "); sqlCommand.Append("WHERE "); sqlCommand.Append("SiteID = @SiteID "); sqlCommand.Append("AND OldUrl = @OldUrl "); sqlCommand.Append("AND ExpireUtc < @CurrentTime "); sqlCommand.Append(";"); FbParameter[] arParams = new FbParameter[3]; arParams[0] = new FbParameter("@SiteID", FbDbType.Integer); arParams[0].Value = siteId; arParams[1] = new FbParameter("@OldUrl", FbDbType.VarChar, 255); arParams[1].Value = oldUrl; arParams[2] = new FbParameter("@CurrentTime", FbDbType.TimeStamp); arParams[2].Value = DateTime.UtcNow; return(AdoHelper.ExecuteReader( readConnectionString, sqlCommand.ToString(), arParams)); }
private DbDataReader GetSchemaId(string applicationName) { //return GetReader( // connectionString, // "mp_SchemaVersion", // " WHERE LOWER(ApplicationName) = '" + applicationName.ToLower() + "'"); StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_SchemaVersion "); sqlCommand.Append("WHERE "); sqlCommand.Append("LOWER(ApplicationName) = @ApplicationName "); sqlCommand.Append(";"); SqlCeParameter[] arParams = new SqlCeParameter[1]; arParams[0] = new SqlCeParameter("@ApplicationName", SqlDbType.NVarChar, 255); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = applicationName.ToLowerInvariant(); return(AdoHelper.ExecuteReader( connectionString, CommandType.Text, sqlCommand.ToString(), arParams)); }
public DbDataReader AutoComplete(Guid countryGuid, string query, int maxRows) { 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(";"); SqliteParameter[] arParams = new SqliteParameter[2]; arParams[0] = new SqliteParameter(":CountryGuid", DbType.String); arParams[0].Value = countryGuid.ToString(); arParams[1] = new SqliteParameter(":Query", DbType.String); arParams[1].Value = query + "%"; return(AdoHelper.ExecuteReader( connectionString, sqlCommand.ToString(), arParams)); }
public 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(AdoHelper.ExecuteReader( connectionString, CommandType.Text, sqlCommand.ToString(), null)); }
/// <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> public DbDataReader GetPage( int pageNumber, int pageSize) { int pageLowerBound = (pageSize * pageNumber) - pageSize; StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_Language "); //sqlCommand.Append("WHERE "); sqlCommand.Append("ORDER BY [Sort] "); //sqlCommand.Append(" "); sqlCommand.Append("LIMIT :PageSize "); if (pageNumber > 1) { sqlCommand.Append("OFFSET :OffsetRows "); } sqlCommand.Append(";"); SQLiteParameter[] arParams = new SQLiteParameter[2]; arParams[0] = new SQLiteParameter(":PageSize", DbType.Int32); arParams[0].Value = pageSize; arParams[1] = new SQLiteParameter(":OffsetRows", DbType.Int32); arParams[1].Value = pageLowerBound; return(AdoHelper.ExecuteReader( connectionString, sqlCommand.ToString(), arParams)); }
public 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; // } //} int offset = 0; if (pageNumber > 1) { offset = (pageSize * pageNumber) - pageSize; } 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("OFFSET " + offset.ToString(CultureInfo.InvariantCulture) + " ROWS "); sqlCommand.Append("FETCH NEXT " + pageSize.ToString(CultureInfo.InvariantCulture) + "ROWS ONLY "); sqlCommand.Append(";"); return(AdoHelper.ExecuteReader( connectionString, CommandType.Text, sqlCommand.ToString(), null)); }