示例#1
0
        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);
        }
示例#2
0
        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));
        }
示例#3
0
        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));
        }
示例#4
0
        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));
        }
示例#5
0
        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));
        }
示例#6
0
        /// <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));
        }
示例#7
0
        /// <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));
        }
示例#8
0
        //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));
        }
示例#9
0
        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));
        }
示例#10
0
        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));
        }
示例#11
0
        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));
        }
示例#12
0
        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));
        }
示例#13
0
        /// <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));
        }
示例#14
0
        /// <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));
        }
示例#15
0
        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));
        }
示例#16
0
        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);
        }
示例#17
0
        /// <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));
        }
示例#18
0
        /// <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));
        }
示例#19
0
        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));
        }
示例#20
0
        /// <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));
        }
示例#21
0
        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));
        }
示例#22
0
        /// <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));
        }
示例#23
0
        /// <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));
        }
示例#24
0
        /// <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));
        }
示例#25
0
        /// <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));
        }
示例#26
0
        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));
        }
示例#27
0
        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));
        }
示例#28
0
        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));
        }
示例#29
0
        /// <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));
        }
示例#30
0
        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));
        }