C# SqlParameterHelper.ExecuteReaderAsync方法代码示例

 public async Task<DbDataReader> SchemaVersionGetAll(CancellationToken cancellationToken = default(CancellationToken))
 {
     SqlParameterHelper sph = new SqlParameterHelper(
         logFactory,
         readConnectionString, 
         "mp_SchemaVersion_SelectAll", 
         0
         );
     return await sph.ExecuteReaderAsync(cancellationToken);
 }

        public async Task<DbDataReader> GetPageNotApprovedUsers(
            int siteId,
            int pageNumber,
            int pageSize)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_Users_SelectNotApprovedPage", 
                3);

            sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
            sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
            sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
            return await sph.ExecuteReaderAsync();

        }

        public async Task<DbDataReader> GetUserByRegistrationGuid(int siteId, Guid registerConfirmGuid)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_Users_SelectByRegisterGuid", 
                2);

            sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
            sph.DefineSqlParameter("@RegisterConfirmGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, registerConfirmGuid);
            return await sph.ExecuteReaderAsync();
        }

        public async Task<DbDataReader> GetSingleUser(int userId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_Users_SelectOne", 
                1);

            sph.DefineSqlParameter("@UserID", SqlDbType.Int, ParameterDirection.Input, userId);
            return await sph.ExecuteReaderAsync();
        }

        public async Task<DbDataReader> GetUserListPage(
            int siteId,
            int pageNumber,
            int pageSize,
            string userNameBeginsWith,
            int sortMode
            )
        {
            //totalPages = 1;
            //int totalRows = UserCount(siteId, userNameBeginsWith);

            //if (pageSize > 0) totalPages = totalRows / pageSize;

            //if (totalRows <= pageSize)
            //{
            //    totalPages = 1;
            //}
            //else
            //{
            //    int remainder;
            //    Math.DivRem(totalRows, pageSize, out remainder);
            //    if (remainder > 0)
            //    {
            //        totalPages += 1;
            //    }
            //}

            SqlParameterHelper sph;

            switch (sortMode)
            {
                case 1:
                    sph = new SqlParameterHelper(
                        logFactory,
                        readConnectionString, 
                        "mp_Users_SelectPageByDateDesc", 
                        4);

                    break;

                case 2:
                    sph = new SqlParameterHelper(
                        logFactory,
                        readConnectionString, 
                        "mp_Users_SelectPageSortLF", 
                        4);

                    break;

                case 0:
                default:
                    sph = new SqlParameterHelper(
                        logFactory,
                        readConnectionString, 
                        "mp_Users_SelectPage", 
                        4);

                    break;
            }
            
            sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
            sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
            sph.DefineSqlParameter("@UserNameBeginsWith", SqlDbType.NVarChar, 50, ParameterDirection.Input, userNameBeginsWith);
            sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
            return await sph.ExecuteReaderAsync();
        }

        public async Task<DbDataReader> GetHost(int hostId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_SiteHosts_SelectOne", 
                1);

            sph.DefineSqlParameter("@HostID", SqlDbType.Int, ParameterDirection.Input, hostId);
            return await sph.ExecuteReaderAsync();
        }

        public async Task<DbDataReader> GetCrossSiteUserListByEmail(string email)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_Users_SelectAllByEmail", 
                1);

            sph.DefineSqlParameter("@Email", SqlDbType.NVarChar, 100, ParameterDirection.Input, email);
            return await sph.ExecuteReaderAsync();
        }

        /// <summary>
        /// Gets an IDataReader with one row from the mp_GeoCountry table.
        /// </summary>
        /// <param name="countryISOCode2"> countryISOCode2 </param>
        public async Task<DbDataReader> GetByISOCode2(
            string countryISOCode2,
            CancellationToken cancellationToken)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_GeoCountry_SelectByISOCode2", 
                1);

            sph.DefineSqlParameter("@ISOCode2", SqlDbType.NChar, 2, ParameterDirection.Input, countryISOCode2);
            return await sph.ExecuteReaderAsync(cancellationToken);

        }

        public async Task<DbDataReader> AutoComplete(
            string query, 
            int maxRows,
            CancellationToken cancellationToken)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_GeoCountry_AutoComplete", 
                2);

            sph.DefineSqlParameter("@Query", SqlDbType.NVarChar, 255, ParameterDirection.Input, query);
            sph.DefineSqlParameter("@RowsToGet", SqlDbType.NVarChar, 255, ParameterDirection.Input, maxRows);
            return await sph.ExecuteReaderAsync(cancellationToken);

        }

        ///// <summary>
        ///// Returns true if the given userguid/ip is found
        ///// </summary>
        ///// <param name="userGuid"> userGuid </param>
        //public static bool Exists(Guid userGuid, long ipAsLong)
        //{
        //    SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "mp_UserLocation_Exists", 1);
        //    sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
        //    return Convert.ToInt32(sph.ExecuteScalar()) > 0;

        //}

        ///// <summary>
        ///// Gets a count of rows in the mp_UserLocation table for the passed in userGuid.
        ///// </summary>
        ///// <param name="siteGuid"> siteGuid </param>
        //public int GetCountBySite(Guid siteGuid)
        //{
        //    SqlParameterHelper sph = new SqlParameterHelper(
        //        logFactory,
        //        readConnectionString, 
        //        "mp_UserLocation_GetCountBySite", 
        //        1);

        //    sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
        //    return Convert.ToInt32(sph.ExecuteScalar());


        //}



        /// <summary>
        /// Gets a page of data from the mp_UserLocation table.
        /// </summary>
        /// <param name="userGuid"> userGuid </param>
        /// <param name="pageNumber">The page number.</param>
        /// <param name="pageSize">Size of the page.</param>
        /// <param name="totalPages">total pages</param>
        public  async Task<DbDataReader> GetPageByUser(
            Guid userGuid,
            int pageNumber,
            int pageSize,
            CancellationToken cancellationToken
            )
        {   
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_UserLocation_SelectPageByUser", 
                3);

            sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
            sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
            sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
            return await sph.ExecuteReaderAsync(cancellationToken);

        }

        /// <summary>
        /// Gets an IDataReader with one row from the mp_GeoCountry table.
        /// </summary>
        /// <param name="guid"> guid </param>
        public async Task<DbDataReader> GetOne(
            Guid guid,
            CancellationToken cancellationToken)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_GeoCountry_SelectOne",
                1);

            sph.DefineSqlParameter("@Guid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, guid);
            return await sph.ExecuteReaderAsync(cancellationToken);

        }

        ///// <summary>
        ///// Gets an IDataReader with one row from the mp_UserLocation table.
        ///// </summary>
        ///// <param name="userGuid"> userGuid </param>
        //public DbDataReader GetByUser(Guid userGuid)
        //{
        //    SqlParameterHelper sph = new SqlParameterHelper(
        //        logFactory,
        //        readConnectionString, 
        //        "mp_UserLocation_SelectByUser", 
        //        1);

        //    sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
        //    return sph.ExecuteReader();
        //}



        ///// <summary>
        ///// Gets an IDataReader with one row from the mp_UserLocation table.
        ///// </summary>
        ///// <param name="siteGuid"> siteGuid </param>
        //public DbDataReader GetBySite(Guid siteGuid)
        //{
        //    SqlParameterHelper sph = new SqlParameterHelper(
        //        logFactory,
        //        readConnectionString, 
        //        "mp_UserLocation_SelectBySite", 
        //        1);

        //    sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
        //    return sph.ExecuteReader();
        //}

        /// <summary>
        /// Gets an IDataReader with rows from the mp_Users table which have the passed in IP Address
        /// </summary>
        /// <param name="siteGuid"> siteGuid </param>
        public async Task<DbDataReader> GetUsersByIPAddress(
            Guid siteGuid, 
            string ipv4Address,
            CancellationToken cancellationToken)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_UserLocation_SelectUsersByIP", 
                2);

            sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
            sph.DefineSqlParameter("@IPAddress", SqlDbType.NVarChar, 50, ParameterDirection.Input, ipv4Address);
            return await sph.ExecuteReaderAsync(cancellationToken);
        }

        ///// <summary>
        ///// Gets an IDataReader with one row from the mp_UserLocation table.
        ///// </summary>
        ///// <param name="rowID"> rowID </param>
        //public DbDataReader GetOne(Guid rowID)
        //{
        //    SqlParameterHelper sph = new SqlParameterHelper(
        //        logFactory,
        //        readConnectionString, 
        //        "mp_UserLocation_SelectOne", 
        //        1);

        //    sph.DefineSqlParameter("@RowID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, rowID);
        //    return sph.ExecuteReader();
        //}

        /// <summary>
        /// Gets an IDataReader with one row from the mp_UserLocation table.
        /// </summary>
        /// <param name="userGuid"> userGuid </param>
        /// <param name="iPAddress"> iPAddress </param>
        public async Task<DbDataReader> GetOne(
            Guid userGuid, 
            long iPAddressLong,
            CancellationToken cancellationToken
            )
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_UserLocation_SelectOneByUserAndIP", 
                2);

            sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
            sph.DefineSqlParameter("@IPAddressLong", SqlDbType.BigInt, ParameterDirection.Input, iPAddressLong);
            return await sph.ExecuteReaderAsync(cancellationToken);
        }

        public async Task<DbDataReader> GetUsersByClaim(int siteId, string claimType, string claimValue)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString,
                "mp_Users_SelectAllByClaim",
                3);

            sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
            sph.DefineSqlParameter("@ClaimType", SqlDbType.NVarChar, -1, ParameterDirection.Input, claimType);
            sph.DefineSqlParameter("@ClaimValue", SqlDbType.NVarChar, -1, ParameterDirection.Input, claimValue);
            return await sph.ExecuteReaderAsync();

        }

        public async Task<DbDataReader> GetPageHosts(
            int pageNumber,
            int pageSize)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_SiteHosts_SelectPage", 
                2);

            sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
            sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
            return await sph.ExecuteReaderAsync();

        }